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:
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
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:
Code:
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
