Type Mismatch Error in Loop

[Rmoeller]

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, tblDataEntrySto rage and AR_tblActivePar ameters. I want to loop through the Parameter column (Short Text)in AR_tblActivePar ameters to pull out data from tblDataEntrySto rage. 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.

Public Function TestLoop()
 On Error GoTo TestLoop_Err
 Dim rsLoop As Recordset
 Dim strSQL As String
 Dim db As Database
 
 Set db = CurrentDb
 Set rsLoop = db.OpenRecordset("Select * from AR_tblActiveParameters;")

 
 rsLoop.MoveFirst
 While Not rsLoop.EOF
   strSQL = "SELECT DISTINCT TOP 10 tblDataEntryStorage.[Replicate Number], tblDataEntryStorage.Parameter, tblDataEntryStorage.[Date Processed], tblDataEntryStorage.[Batch ID], tblDataEntryStorage.Fraction, tblDataEntryStorage.Method, tblSampleLogin.MatrixID"
   strSQL = strSQL & "FROM tblSampleLogin INNER JOIN tblDataEntryStorage ON tblSampleLogin.PhysisSampleID = tblDataEntryStorage.[Sample ID]"
   strSQL = strSQL & "WHERE (((tblDataEntryStorage.[Replicate Number]) = 'bs1') AND ((tblDataEntryStorage.Parameter) = '" & [rsLoop]![Parameter] & "') AND ((tblDataEntryStorage.Method) Like " * 8270 * ") AND ((tblSampleLogin.MatrixID)=6))"
   strSQL = strSQL & "ORDER BY tblDataEntryStorage.[Batch ID] DESC"
  
   db.Execute (strSQL), dbFailOnError
 
  rsLoop.MoveNext
 Wend
 rsLoop.Close
 Set rsLoop = Nothing
 Set db = Nothing
 
TestLoop_Exit:
    Exit Function
TestLoop_Err:
    MsgBox Err.Description & " in TestLoop"

    Resume TestLoop_Exit
 End Function

[jimatqsi]

Make sure your join statement is joining fields that are similar data types. That is often the problem in a case like this.

jim

[Rmoeller]

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

[jimatqsi]

I didn't take the time this morning to look closely at your code. I see some obvious things now.

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 tblDataEntrySto rage.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:

if tblDataEntryStorage.Method) Like " * 8270 * "

Presumably tblDataEntrySto rage.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 tblDataEntrySto rage.Method is a string then the test should be

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

[Rmoeller]

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 tblDataEntrySto rage (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_tblActivePar ameters. I than use that Union query to append the data I am interested in (only the last 10 records for each compound) from tblDataEntrySto rage 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_tblActivePar ameters 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

[Rmoeller]

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....

Public Function TestLoop()
  On Error GoTo TestLoop_Err
  Dim rsLoop As DAO.Recordset
  Dim strSQL As String
  Dim db As DAO.Database

  Set db = CurrentDb
  Set rsLoop = db.OpenRecordset("Select * from AR_tblActiveParameters;")

  rsLoop.MoveFirst
  While Not rsLoop.EOF
    strSQL = "INSERT INTO AR_tblLast10Batches ([Replicate Number], Parameter, [Date Processed], [Batch ID], Fraction, Method, MatrixID)"
    strSQL = strSQL & " SELECT DISTINCT TOP 10 tblDataEntryStorage.[Replicate Number], tblDataEntryStorage.Parameter, tblDataEntryStorage.[Date Processed], tblDataEntryStorage.[Batch ID], tblDataEntryStorage.Fraction, tblDataEntryStorage.Method, tblSampleLogin.MatrixID"
    strSQL = strSQL & " FROM tblSampleLogin INNER JOIN tblDataEntryStorage ON tblSampleLogin.PhysisSampleID = tblDataEntryStorage.[Sample ID]"
    strSQL = strSQL & " WHERE (((tblDataEntryStorage.[Replicate Number]) = 'bs1') AND ((tblDataEntryStorage.Parameter) = '& rsLoop!Parameter &') AND ((tblDataEntryStorage.Method) Like '*8270*') AND ((tblSampleLogin.MatrixID)=6))"
    strSQL = strSQL & " ORDER BY tblDataEntryStorage.[Date Processed] DESC, tblDataEntryStorage.[Batch ID] DESC"
    db.Execute (strSQL), dbFailOnError

   rsLoop.MoveNext
  Wend
  rsLoop.Close
  Set rsLoop = Nothing
  Set db = Nothing

TestLoop_Exit:
     Exit Function
TestLoop_Err:
     MsgBox Err.Description & " in TestLoop"

     Resume TestLoop_Exit
  End Function

[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

[Rmoeller]

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!Paramete r &' 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:

Public Function TestLoop()
  On Error GoTo TestLoop_Err
  Dim rsLoop As DAO.Recordset
  Dim strSQL As String
  Dim db As DAO.Database

  Set db = CurrentDb
  Set rsLoop = db.OpenRecordset("Select [parameter] from AR_tblActiveParameters")
  
  rsLoop.MoveFirst
  While Not rsLoop.EOF
   
    strSQL = "INSERT INTO AR_tblLast10Batches ([Replicate Number], Parameter, [Date Processed], [Batch ID], Fraction, Method, MatrixID)"
    strSQL = strSQL & " SELECT DISTINCT TOP 10 tblDataEntryStorage.[Replicate Number], tblDataEntryStorage.Parameter, tblDataEntryStorage.[Date Processed], tblDataEntryStorage.[Batch ID], tblDataEntryStorage.Fraction, tblDataEntryStorage.Method, tblSampleLogin.MatrixID"
    strSQL = strSQL & " FROM tblSampleLogin INNER JOIN tblDataEntryStorage ON tblSampleLogin.PhysisSampleID = tblDataEntryStorage.[Sample ID]"
    strSQL = strSQL & " WHERE (((tblDataEntryStorage.[Replicate Number]) = 'bs1') AND ((tblDataEntryStorage.Parameter) = '" & rsLoop!Parameter & "') AND ((tblDataEntryStorage.Method) Like '*8270*') AND ((tblSampleLogin.MatrixID)=6))"
    strSQL = strSQL & " ORDER BY tblDataEntryStorage.[Date Processed] DESC, tblDataEntryStorage.[Batch ID] DESC"
    db.Execute (strSQL), dbFailOnError

   rsLoop.MoveNext
  Wend
  rsLoop.Close
  Set rsLoop = Nothing
  Set db = Nothing

TestLoop_Exit:
     Exit Function
TestLoop_Err:
     MsgBox Err.Description & " in TestLoop"

     Resume TestLoop_Exit
  End Function