Too Few Parameters in VB, but works in Query

[TesseractE]

(Access 2010)

I'm getting 'Too Few Parameters: Expected 1' from VB. Usually, when I'm trying to get OpenRecordset to work in VB and it fails due to 'Too Few Parameters', I have a debug.print spit out the SQL String, plug the string into a Query and get a better, more precise error message to work with. Not this time, though. The output works perfectly fine in SQL View.

I've checked a few suggestions I found on other resources and some issues with this that I've resolved in the past...

• The 'A_TotalAudits' Query's working just fine
• It doesn't look like any of the field names are Reserved.
• The only fields that are accepting strings from VB are 'Text', and are being fed text values, so it shouldn't be a type mismatch.
• It's not referring to any fields on Forms or TempVars (So no QueryDefs should be needed)
• Option Compare Database, Option Explicit at the top of the Form Object

I can't post all of the code or any of my DBs, as some of it's confidential info, but I can share the script that's generating the issue:

QMSkills = Split(TempVars!tv_QMLive & TempVars!tv_QMTraining, "-")
  
  Do Until x = UBound(QMSkills)
    If SearchString = "" Then
      SearchString = "(InStr(1,[Live Regions] & [Training Regions],'" & QMSkills(x) & "-')>0)"
      Else
        SearchString = SearchString & " or (InStr(1,[Live Regions] & [Training Regions],'" & QMSkills(x) & "-')>0)"
    End If
    x = x + 1
  Loop
  
  Debug.Print Len(SearchString)
  
  x = 0
  
  If SearchString = "" Then Exit Sub
  
  SQLString = "SELECT [Agent Master].[Rep Name], [Agent Master].Site, [Agent Master].[Indigo ID], [Agent Master].[Agent Comments], [Agent Master].ACD, [Agent Master].[LAN ID], [Agent Master].EXT, [Agent Master].System, [Agent Master].[Fidelity ID], [Agent Master].Manager, [Agent Master].[2nd Level Mgr], [Agent Master].[Delta Comments], [Agent Master].[Delta Change Date], [Agent Master].[Delta End Date], [Agent Master].[Current Target], [Agent Master].[Live Regions], [Agent Master].[Training Regions], [Agent Master].[Last Click], A_TotalAudits.Audits " & _
              "FROM [Agent Master] INNER JOIN A_TotalAudits ON [Agent Master].[Rep Name] = A_TotalAudits.[Rep Name] " & _
              "WHERE ((" & SearchString & ") and (A_TotalAudits.Audits < [Agent Master].[Current Target]) and (Int((Now()-[Last Click])*1440)>359)) " & _
              "ORDER BY A_TotalAudits.Audits, [Agent Master].[Indigo ID]; "
  Debug.Print SQLString
  Set AM = DB.OpenRecordset(SQLString)

And the "Debug.Print SQLString" resolves in this case to:

SELECT [Agent Master].[Rep Name], [Agent Master].Site, [Agent Master].[Indigo ID], [Agent Master].[Agent Comments], [Agent Master].ACD, [Agent Master].[LAN ID], [Agent Master].EXT, [Agent Master].System, [Agent Master].[Fidelity ID], [Agent Master].Manager, [Agent Master].[2nd Level Mgr], [Agent Master].[Delta Comments], [Agent Master].[Delta Change Date], [Agent Master].[Delta End Date], [Agent Master].[Current Target], [Agent Master].[Live Regions], [Agent Master].[Training Regions], [Agent Master].[Last Click], A_TotalAudits.Audits FROM [Agent Master] INNER JOIN A_TotalAudits ON [Agent Master].[Rep Name] = A_TotalAudits.[Rep Name] WHERE (((InStr(1,[Live Regions] & [Training Regions],'[COL]-')>0) or (InStr(1,[Live Regions] & [Training Regions],'[LIQ]-')>0) or (InStr(1,[Live Regions] & [Training Regions],'[HPS]-')>0) or (InStr(1,[Live Regions] & [Training Regions],'[BK]-')>0) or (InStr(1,[Live Regions] & [Training Regions],'[MED]-')>0) or (InStr(1,[Live Regions] & [Training Regions],'[DRAC]-')>0) or (InStr(
1,[Live Regions] & [Training Regions],'[CCRG]-')>0) or (InStr(1,[Live Regions] & [Training Regions],'[LIT]-')>0)) and (A_TotalAudits.Audits < [Agent Master].[Current Target]) and (Int((Now()-[Last Click])*1440)>359)) ORDER BY A_TotalAudits.Audits, [Agent Master].[Indigo ID];

The purpose of this is to take the list of a QM's Skills that are stored as strings (TempVars!tv_QMLive & TempVars!tv_QMTraining) and find the Representatives ('Reps') that they would be qualified to handle (Rep Skills are listed in [Live Regions] & [Training Regions]). We also want to only bring up Reps who've had fewer audits than their target (A_TotalAudits.Audits < [Agent Master].[Current Target]) and who've NOT been selected within the last 6 hours (Int((Now()-[Last Click])*1440)>359)).

Any suggestions?

 

[alansidman]

To make sure my VBA SQL code works as intended, I usually build the SQL statement directly in the QBE and then convert it for VBA usage. I have short form that I import into my data base that does this for me. I have posted a copy of this form in a database and it can be downloaded at:

https://app.box.com/s/qc7hiqu9n08janxuhmzf

 

[TesseractE]

Heh. I appreciate the offer of the sample, but I can't access Box (or really ANY online drive) from work due to information security restrictions, and I can't work on my project from home yet. XD

But if you're suggesting that I create the Query in the Query Builder, then bring it over to VB, that's what I usually do. It's a little tricky in this case, since I have to create part of that query on the fly with variables, but the compiled query after those variables are inserted works perfectly fine in a direct Query environment in Access. Just doesn't seem to like it in VB.

 

[TesseractE]

Well, I can't seem to edit prior posts, so I'll just update here with the latest...

I tracked the problem down to the FROM statement. Removing every other reference to the Query I was trying to JOIN with, but leaving the INNER JOIN in place still gave me the error.

The Query [A_TotalAudits] is just there to provide a count of how many times [Agent Master].[Rep Name] appears in the Table [Audit History], so I tore out the Query and plugged in a simple 'Count' instead, joining directly to the [Audit History] table. It took some additional fiddling, but here's the finished (WORKING!) product:

SQLString = "SELECT [Agent Master].[Rep Name], [Agent Master].Site, [Agent Master].[Indigo ID], [Agent Master].[Agent Comments], [Agent Master].ACD, [Agent Master].[LAN ID], [Agent Master].EXT, [Agent Master].System, [Agent Master].[Fidelity ID], [Agent Master].Manager, [Agent Master].[2nd Level Mgr], [Agent Master].[Delta Comments], [Agent Master].[Delta Change Date], [Agent Master].[Delta End Date], [Agent Master].[Current Target], [Agent Master].[Live Regions], [Agent Master].[Training Regions], [Agent Master].[Last Click], Count([Audit History].Agent) AS CountOfAgent " & _
              "FROM [Agent Master] LEFT JOIN [Audit History] ON [Agent Master].[Rep Name] = [Audit History].Agent " & _
              "WHERE ((" & SearchString & ") and (Format([Audit Date],'mmmm')=Format('" & TempVars!DateOverride & "','mmmm')) and (Int((Now()-[Last Click])*1440)>359)) " & _
              "GROUP BY [Agent Master].[Rep Name], [Agent Master].Site, [Agent Master].[Indigo ID], [Agent Master].[Agent Comments], [Agent Master].ACD, [Agent Master].[LAN ID], [Agent Master].EXT, [Agent Master].System, [Agent Master].[Fidelity ID], [Agent Master].Manager, [Agent Master].[2nd Level Mgr], [Agent Master].[Delta Comments], [Agent Master].[Delta Change Date], [Agent Master].[Delta End Date], [Agent Master].[Current Target], [Agent Master].[Live Regions], [Agent Master].[Training Regions], [Agent Master].[Last Click] " & _
              "HAVING (((Count([Audit History].Agent)) < [Current Target])) " & _
              "ORDER BY [Agent Master].[Indigo ID], Count([Audit History].Agent); "

So I guess the lesson here is to avoid Joining Queries for a VB Recordset?

I don't like the fact that I had to explicitly define every GROUP BY and SELECT field, but at least it works! I welcome any recommendations on how to simplify this, of course.