473,327 Members | 1,952 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Type Mismatch Error in Loop

Hi everyone and thanks in advance for any help on this. I have some VBA code that I would like to use to loop through different criteria in a SQL statement. I actually pulled the SQL statement seen in the VBA from a query that I know is functioning properly.

I have three tables: tblSampleLogin, tblDataEntryStorage and AR_tblActiveParameters. I want to loop through the Parameter column (Short Text)in AR_tblActiveParameters to pull out data from tblDataEntryStorage. I feel like I am missing something, like brackets or quotes, and I have tried a various combination of them but I keep getting a Type Mismatch error.

Below is my code. I am new to VBA and its quite possible that I just can't see where the issue is. I am also new to this site, so please let me know if I need to provide more information.

Expand|Select|Wrap|Line Numbers
  1. Public Function TestLoop()
  2.  On Error GoTo TestLoop_Err
  3.  Dim rsLoop As Recordset
  4.  Dim strSQL As String
  5.  Dim db As Database
  6.  
  7.  Set db = CurrentDb
  8.  Set rsLoop = db.OpenRecordset("Select * from AR_tblActiveParameters;")
  9.  
  10.  
  11.  rsLoop.MoveFirst
  12.  While Not rsLoop.EOF
  13.    strSQL = "SELECT DISTINCT TOP 10 tblDataEntryStorage.[Replicate Number], tblDataEntryStorage.Parameter, tblDataEntryStorage.[Date Processed], tblDataEntryStorage.[Batch ID], tblDataEntryStorage.Fraction, tblDataEntryStorage.Method, tblSampleLogin.MatrixID"
  14.    strSQL = strSQL & "FROM tblSampleLogin INNER JOIN tblDataEntryStorage ON tblSampleLogin.PhysisSampleID = tblDataEntryStorage.[Sample ID]"
  15.    strSQL = strSQL & "WHERE (((tblDataEntryStorage.[Replicate Number]) = 'bs1') AND ((tblDataEntryStorage.Parameter) = '" & [rsLoop]![Parameter] & "') AND ((tblDataEntryStorage.Method) Like " * 8270 * ") AND ((tblSampleLogin.MatrixID)=6))"
  16.    strSQL = strSQL & "ORDER BY tblDataEntryStorage.[Batch ID] DESC"
  17.  
  18.    db.Execute (strSQL), dbFailOnError
  19.  
  20.   rsLoop.MoveNext
  21.  Wend
  22.  rsLoop.Close
  23.  Set rsLoop = Nothing
  24.  Set db = Nothing
  25.  
  26. TestLoop_Exit:
  27.     Exit Function
  28. TestLoop_Err:
  29.     MsgBox Err.Description & " in TestLoop"
  30.  
  31.     Resume TestLoop_Exit
  32.  End Function
Jul 18 '15 #1

✓ answered by jimatqsi

Rhonda,
There's no need to apologize for being new to this game. Every poster here has had that same experience. It takes a lot of fortitude to stick with it in the early part of the game.

It seems to me that one of two things could be happening. Either the Select statement is not returning anything for the Insert statement to process, or the Insert is failing. But if the insert is failing then dbFailOnError would cause you to display the error ....

Try taking out the Order By at the end of your SQL string, or moving it from the Insert to the () around the Select. I'm not sure Order By on the Insert makes any sense.

Have you ever used the debugger? Try this. Set a debug stop at line 17. Start the program, you'll get the yellow highlighting of line 17. Enter control/g to open the immediate window. Type ?strSQL to get the SQL code. Copy that. Go to the query builder and start a new query. Go into SQL view and paste. (sometimes at this point Access will mess up your sql by inserting a line break in the middle of a string where it doesn't belong. It is easily fixed manually.)

Go into Datasheet mode. That will run the Select part of the query only. Do you get anything? If so, go into Design mode and click the exclamation point at the top to run the code. Do you get any errors?

You're getting close, I can feel it. :)

Jim

7 1821
jimatqsi
1,271 Expert 1GB
Make sure your join statement is joining fields that are similar data types. That is often the problem in a case like this.

jim
Jul 19 '15 #2
Thank You Jim,

Both fields in the join statement are numbers (one is an automumber). Please let me know if autonumber vs. number is a problem.

Rhonda
Jul 19 '15 #3
jimatqsi
1,271 Expert 1GB
I didn't take the time this morning to look closely at your code. I see some obvious things now.

Expand|Select|Wrap|Line Numbers
  1. tblDataEntryStorage.[Replicate Number]) = 'bs1'
clearly looks like an attempt to compare a number to a string, the string being 'bs1'. That would give you the error you are seeing. Is [Replicate Number] numeric?

Are tblDataEntryStorage.Parameter and [rsLoop]![Parameter] strings? They must be, due to the use of '' in the comparison.

I don't really know what this will do:
Expand|Select|Wrap|Line Numbers
  1. if tblDataEntryStorage.Method) Like " * 8270 * "
Presumably tblDataEntryStorage.Method is a numeric. If you can use wild cards in a numeric test I suppose this could work but I doubt it will work with the spaces around 8270. Probably *8270* could work but I've never searched numerics like that. if tblDataEntryStorage.Method is a string then the test should be
Expand|Select|Wrap|Line Numbers
  1. if tblDataEntryStorage.Method) Like "' * 8270 * '"
Finally, your loop doesn't seem to anything useful. It simply runs a select query, doing nothing with the resulting recordset and then runs another select query. the db.Execute statement is not going to be helpful to you. You're going to need to replace that with some code to do something, but you'll have to give us more information about what you want to do with the data returned by the select statements.

Jim
Jul 19 '15 #4
Hi Jim,

Thank you again for looking at the code. [Replicate Number] is actually a string. [Method] is also a string. I am looking to pull all variations of a method 8270, as the data I am looking to pull can have EPA 8270C, EPA 8270D, EPA 8270D-NCI, EPA8270 (no spaces) etc. Thank you for catching the missing single quotes, I'll add those in.

I have my current database set up using about 40 or so Union queries to select the last 10 records from tblDataEntryStorage (based on descending order of [Date Processed] and [Batch ID] columns) for every compound that my lab tests for. There are currently about 550 different compounds which can be found in AR_tblActiveParameters. I than use that Union query to append the data I am interested in (only the last 10 records for each compound) from tblDataEntryStorage to another table that I use to perform statistical analysis. The SQL statement from the VBA code is actually pulled straight from the Union query, hence the " instead of '. As you can imagine, setting up all the Union queries was tedious, and making adjustments to it is not an easy task neither. So instead, I wanted to write some VBA to be able to loop through AR_tblActiveParameters for each of the parameters in the SQLstr. You are correct, I don't have code yet to append the data from the recordset. Honestly, I am not sure how to modify the code to do that (just yet).

My apologies, Jim! I am a trained biochemist trying to learn VBA. Sometimes I get a bit lost :). Thank you again for your help. Any further comments/suggestions would be welcome.

Please let me know if you need more information.
Rhonda
Jul 20 '15 #5
Hi again Jim,

I fixed the quotes, a few spacing issues, and added an "insert into" command above the select statement. Now when I call the function, the database looks like its doing something for about 30 seconds, but when it is finished, I check the table, and there is nothing there. I must be missing something....

Expand|Select|Wrap|Line Numbers
  1. Public Function TestLoop()
  2.   On Error GoTo TestLoop_Err
  3.   Dim rsLoop As DAO.Recordset
  4.   Dim strSQL As String
  5.   Dim db As DAO.Database
  6.  
  7.   Set db = CurrentDb
  8.   Set rsLoop = db.OpenRecordset("Select * from AR_tblActiveParameters;")
  9.  
  10.   rsLoop.MoveFirst
  11.   While Not rsLoop.EOF
  12.     strSQL = "INSERT INTO AR_tblLast10Batches ([Replicate Number], Parameter, [Date Processed], [Batch ID], Fraction, Method, MatrixID)"
  13.     strSQL = strSQL & " SELECT DISTINCT TOP 10 tblDataEntryStorage.[Replicate Number], tblDataEntryStorage.Parameter, tblDataEntryStorage.[Date Processed], tblDataEntryStorage.[Batch ID], tblDataEntryStorage.Fraction, tblDataEntryStorage.Method, tblSampleLogin.MatrixID"
  14.     strSQL = strSQL & " FROM tblSampleLogin INNER JOIN tblDataEntryStorage ON tblSampleLogin.PhysisSampleID = tblDataEntryStorage.[Sample ID]"
  15.     strSQL = strSQL & " WHERE (((tblDataEntryStorage.[Replicate Number]) = 'bs1') AND ((tblDataEntryStorage.Parameter) = '& rsLoop!Parameter &') AND ((tblDataEntryStorage.Method) Like '*8270*') AND ((tblSampleLogin.MatrixID)=6))"
  16.     strSQL = strSQL & " ORDER BY tblDataEntryStorage.[Date Processed] DESC, tblDataEntryStorage.[Batch ID] DESC"
  17.     db.Execute (strSQL), dbFailOnError
  18.  
  19.    rsLoop.MoveNext
  20.   Wend
  21.   rsLoop.Close
  22.   Set rsLoop = Nothing
  23.   Set db = Nothing
  24.  
  25. TestLoop_Exit:
  26.      Exit Function
  27. TestLoop_Err:
  28.      MsgBox Err.Description & " in TestLoop"
  29.  
  30.      Resume TestLoop_Exit
  31.   End Function
Jul 20 '15 #6
jimatqsi
1,271 Expert 1GB
Rhonda,
There's no need to apologize for being new to this game. Every poster here has had that same experience. It takes a lot of fortitude to stick with it in the early part of the game.

It seems to me that one of two things could be happening. Either the Select statement is not returning anything for the Insert statement to process, or the Insert is failing. But if the insert is failing then dbFailOnError would cause you to display the error ....

Try taking out the Order By at the end of your SQL string, or moving it from the Insert to the () around the Select. I'm not sure Order By on the Insert makes any sense.

Have you ever used the debugger? Try this. Set a debug stop at line 17. Start the program, you'll get the yellow highlighting of line 17. Enter control/g to open the immediate window. Type ?strSQL to get the SQL code. Copy that. Go to the query builder and start a new query. Go into SQL view and paste. (sometimes at this point Access will mess up your sql by inserting a line break in the middle of a string where it doesn't belong. It is easily fixed manually.)

Go into Datasheet mode. That will run the Select part of the query only. Do you get anything? If so, go into Design mode and click the exclamation point at the top to run the code. Do you get any errors?

You're getting close, I can feel it. :)

Jim
Jul 20 '15 #7
Hi again Jim,

Thank you so much! Doing those above steps helped me pinpoint the issue with the code. When I hovered over the '& rsLoop!Parameter &' with the mouse in the above code I posted, I thought it was odd that I couldn't see the first Parameter in the loop. Turns out, the Parameter wasn't being read in the strSQL. Once I saw that in the ACCESS SQL query, I was able to play around with the quotes until I found the right combination so it would pull out the parameter correctly.

Final code that worked:

Expand|Select|Wrap|Line Numbers
  1. Public Function TestLoop()
  2.   On Error GoTo TestLoop_Err
  3.   Dim rsLoop As DAO.Recordset
  4.   Dim strSQL As String
  5.   Dim db As DAO.Database
  6.  
  7.   Set db = CurrentDb
  8.   Set rsLoop = db.OpenRecordset("Select [parameter] from AR_tblActiveParameters")
  9.  
  10.   rsLoop.MoveFirst
  11.   While Not rsLoop.EOF
  12.  
  13.     strSQL = "INSERT INTO AR_tblLast10Batches ([Replicate Number], Parameter, [Date Processed], [Batch ID], Fraction, Method, MatrixID)"
  14.     strSQL = strSQL & " SELECT DISTINCT TOP 10 tblDataEntryStorage.[Replicate Number], tblDataEntryStorage.Parameter, tblDataEntryStorage.[Date Processed], tblDataEntryStorage.[Batch ID], tblDataEntryStorage.Fraction, tblDataEntryStorage.Method, tblSampleLogin.MatrixID"
  15.     strSQL = strSQL & " FROM tblSampleLogin INNER JOIN tblDataEntryStorage ON tblSampleLogin.PhysisSampleID = tblDataEntryStorage.[Sample ID]"
  16.     strSQL = strSQL & " WHERE (((tblDataEntryStorage.[Replicate Number]) = 'bs1') AND ((tblDataEntryStorage.Parameter) = '" & rsLoop!Parameter & "') AND ((tblDataEntryStorage.Method) Like '*8270*') AND ((tblSampleLogin.MatrixID)=6))"
  17.     strSQL = strSQL & " ORDER BY tblDataEntryStorage.[Date Processed] DESC, tblDataEntryStorage.[Batch ID] DESC"
  18.     db.Execute (strSQL), dbFailOnError
  19.  
  20.    rsLoop.MoveNext
  21.   Wend
  22.   rsLoop.Close
  23.   Set rsLoop = Nothing
  24.   Set db = Nothing
  25.  
  26. TestLoop_Exit:
  27.      Exit Function
  28. TestLoop_Err:
  29.      MsgBox Err.Description & " in TestLoop"
  30.  
  31.      Resume TestLoop_Exit
  32.   End Function
Jul 21 '15 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: leslie_tighe | last post by:
Hello, I have a method on a com+ object that is returning an array of objects. I know the array is popluated as calls to check the ubound and lbound show valid values. However, any calls to...
4
by: Mike | last post by:
I am getting a type mismatch error when I do a bulk insert. ---Begin Error Msg--- Server: Msg 4864, Level 16, State 1, Line 1 Bulk insert data conversion error (type mismatch) for row 1, column...
0
by: news.paradise.net.nz | last post by:
I have been developing access databases for over 5 years. I have a large database and I have struck this problem with it before but can find nothing in help or online. Access 2000 I have a query...
6
by: shan | last post by:
What is the meaning for the error expression syntax and type mismatch error.I am using turbo c++.can anybody correct the errors in the folowing program. Following program is to find matrix...
1
by: jodyblau | last post by:
I am getting a type mismatch message under strange circumstances. Here's whats going on: 1. I have split the database into a front end and a back end. 2. I have compiled the project. 3. ...
5
by: kjworm | last post by:
Hello Everyone, I have been fighting with a type mismatch error for many hours today and I can't seem to find what the problem is. Hopefully it is more than a missing apostrophe! I have isolated...
7
by: Mike | last post by:
Type Mismatch error I recieve a type mismatch error on the following line of code which is based on a specific date. It does NOT break on all records only "some". The dates for the records...
5
by: Lara1 | last post by:
Hi, I'm a total beginner to VBA, so please bear with me if I seem a bit dense. What I'm Trying to Achieve I'm trying to write a procedure in Excel, which is supposed to - look at the pH...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.