Concatenating Criteria for a Query - Help!

[Cavman]

Hi,

I'm trying to write some code that checks through a table and adds together criteria that is currently selected and then concatenates it to produce a string that is sent to a function and then called from a query. Hope that makes sense! The code is below:

 Dim rsSelectedGroups As DAO.Recordset
Dim strGroupString As String
 
strGroupString = ""
 
' Create recordset for all currently selected criteria
 
Set rsSelectedGroups = CurrentDb.OpenRecordset("SELECT tbl_List_Groups.GroupID, tbl_List_Groups.Current, tbl_List_Groups.Selected From tbl_List_Groups WHERE (((tbl_List_Groups.Current) = True) AND ((tbl_List_Groups.Selected) = True));")
 
' Open recordset and concatenate a criteria string
 
'Cycle through rsSelectedGroups recordset and create string
 
If Not (rsSelectedGroups.BOF And rsSelectedGroups.EOF) Then
rsSelectedGroups.MoveFirst ' move to first record in recordset
Do Until rsSelectedGroups.EOF = True
strStringHolder = rsSelectedGroups!GroupID
strGroupString = strGroupString & strStringHolder
rsSelectedGroups.MoveNext ' move to next record in recordset
If rsSelectedGroups.EOF Then Exit Do ' if at end of records, exit from loop
strGroupString = strGroupString & " OR "
Loop ' return to start of loop
rsSelectedGroups.Close ' close recordset
End If
 
' Insert the complete string into the relevant function
 
Call SetUpExportCriteria(strGroupString)
 
'Create Export table using CreateExportTable query
 
CurrentDb.Execute "qry_CreateExportTable"

The code works perfectly with only one criteria selected, so I know the section that puts the criteria into the query and then creates the table works. The problem is where the routine tries to concatenate more than one criteria together using OR. I get a run-time error - Data type mismatch in criteria expression.


Can anyone suggest the proper way to achieve what I'm trying to do?

Thanks

[alansidman]

crossposted: http://www.access-programmers.co.uk/...d.php?t=239378

Please read: http://www.excelguru.ca/content.php?184

[Cavman]

Sorry, I was unaware of the courtesies when crossposting, I'll keep that in mind in future.

[ssanfu]

The code works perfectly with only one criteria selected, so I know the section that puts the criteria into the query and then creates the table works.

While your code looks OK, I did massage it a little. It sounds like you have a make table query... I prefer to create the table once, then reuse the table by deleting the records and appending new records. Less dB bloat.

The problem is where the routine tries to concatenate more than one criteria together using OR. I get a run-time error - Data type mismatch in criteria expression.

I don't understand what you mean by "concatenate more than one criteria together using OR".
Would you provide a few records of (made up) data?

Option Compare Database   ' <<= you should ALWAYS have these two lines at the top of every module
Option Explicit           ' <<= you should ALWAYS have these two lines at the top of every module

Public Sub test33()
   Dim rsSelectedGroups As DAO.Recordset

   Dim strGroupString As String
   Dim sSQL As String

   strGroupString = ""

   ' Create recordset for all currently selected criteria
   sSQL = "SELECT tbl_List_Groups.GroupID, tbl_List_Groups.Current, tbl_List_Groups.Selected "
   sSQL = sSQL & " From tbl_List_Groups"
   sSQL = sSQL & " WHERE tbl_List_Groups.Current = True AND tbl_List_Groups.Selected = True;"
   'Debug.Print sSQL

   Set rsSelectedGroups = CurrentDb.OpenRecordset(sSQL)
   ' Open recordset and concatenate a criteria string
   'Cycle through rsSelectedGroups recordset and create string
   If Not (rsSelectedGroups.BOF And rsSelectedGroups.EOF) Then
      rsSelectedGroups.MoveFirst   ' move to first record in recordset
      Do Until rsSelectedGroups.EOF
         strGroupString = strGroupString & rsSelectedGroups!GroupID & " OR "
         rsSelectedGroups.MoveNext   ' move to next record in recordset
         '         If rsSelectedGroups.EOF Then Exit Do   ' if at end of records, exit from loop
      Loop   ' return to start of loop
      rsSelectedGroups.Close   ' close recordset
   Else
      MsgBox "No records found!"
      Exit Sub
   End If

   'remove the last 4 characters >> " OR "
   strGroupString = Left(strGroupString, Len(strGroupString) - 4)

   'open the Immediate window to view string
   Debug.Print strGroupString

   ' Insert the complete string into the relevant function
   Call SetUpExportCriteria(strGroupString)

   'Create Export table using CreateExportTable query
   CurrentDb.Execute "qry_CreateExportTable"
   
End Sub

Please post the results of the debug statement for "strGroupString". (The line in blue)


What is the code for this line or what does it do?
Call SetUpExportCriteria(strGroupString)

[Cavman]

Hi Steve, thanks for your reply. I managed to solve the problem by running the make table query from VBA rather than calling it. This allowed me to directly reference the variables, rather than having to put them into a function to be accessed from the query.

Here's my final code:

Set rsSelectedGroups = CurrentDb.OpenRecordset("SELECT tbl_List_Groups.GroupID, tbl_List_Groups.Current, tbl_List_Groups.Selected From tbl_List_Groups WHERE (((tbl_List_Groups.Current) = True) AND ((tbl_List_Groups.Selected) = True));")
    
' Open recordset and concatenate a criteria string
    
    'Cycle through rsSelectedGroups recordset and create string
    If Not (rsSelectedGroups.BOF And rsSelectedGroups.EOF) Then
        rsSelectedGroups.MoveFirst  '  move to first record in recordset
    Do Until rsSelectedGroups.EOF = True
                strStringHolder = rsSelectedGroups!GroupID
                strGroupString = strGroupString & BuildCriteria("tbl_AccountBase.Group", dbInteger, strStringHolder)
                rsSelectedGroups.MoveNext   '  move to next record in recordset
                If rsSelectedGroups.EOF Then Exit Do   '   if at end of records, exit from loop
                strGroupString = strGroupString & " Or "
    Loop  '    return to start of loop
            rsSelectedGroups.Close   ' close recordset
    End If

' Create table using SQL

strSQL = "SELECT 'GB' AS GB, tbl_AccountBase.SAPNumber, '' AS CallDate1, '' AS StartTime, '' AS CallDate2, '' AS EndTime, '' AS EmployeeNumber, tbl_AccountBase.Group, tbl_AccountBase.Segmentation, tbl_AccountBase.CallFrequency INTO tbl_ExportCSV FROM tbl_AccountBase WHERE (" & strGroupString & ") AND (" & strSegmentString & ") AND (" & strCallWeek & ");"

This only shows part of the code that creates the strings I use to filter in the SQL, but hopefully if anyone else is trying to achieve the same thing it might be helpful.

Thanks,

Simon