Referencing query criteria to a control on a form

[RFletch]

Hi,

I have a query which returns values based on financial years, stored as strings. In the criteria box I reference a text box called 'daterange' on a form called 'grant discounts' In the criteria box I have the reference
Forms![Grant Discounts]![Daterange]
which returns no records.
Here is an example of the string in the text box 'daterange':
In ("99/00","00/01","01/02","02/03&quot
If I cut and paste this string into the criteria box, I get loads of records returned. However if I reference the control using Forms![Grant Discounts]![Daterange] I get nothing.
Can anyone help?

Thanks in advance,

RFletch

 

[LarryDeLaruelle]

Fletch:

I had a similar situation where I was trying to create a criteria string from a multi-select list box.

The problem is that when the string is passed to the query the whole string is enclosed in quotes and the query looks for that match.

My solution was to build the query in SQL; I don't think there is a way to do this as a query by form as you have it set up.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[RFletch]

Hi,

Thanks for your reply. So how would you build this in SQL?

RFletch

 

[LarryDeLaruelle]

Fletch:

This requires some VBA coding and a familiarity with SQL syntax. If you are not conversant with VBA or SQL let me know and I'll try to walk you through it.

In my situation, the users want to be able to select a number of employees on which to produce a report. I created a multi select list box populated with the employee name and ID Numbers (hidden) and then use this code to identify which are selected, write those IDs to an array and then use the array to build the Where clause for the sql string. You'll need to dimension the variables.

'Identifies and records in intIDs all selected Therapists' ID Numbers
intCount = lstTherapist.ListCount
For intLoop = 0 To intCount - 1
If lstTherapist.Selected(intLoop) = True Then
intArrCnt = intArrCnt + 1
ReDim Preserve intIDs(intArrCnt)
intIDs(intArrCnt) = lstTherapist.Column(0, intLoop)
End If
Next intLoop

'Test the intArrCnt to determine that at least one Therapist has been selected.
If intArrCnt = 0 Then
MsgBox "You must select at least one Therapist", vbOKOnly + vbCritical, "TCC Fee Slip Error"
lstTherapist.SetFocus
Exit Sub
End If

'Reads the Therapist ID numbers out of the array and into the Where string for the query
For intLoop = 1 To intArrCnt
strWhere = strWhere & "(Employee.EmployeeID)= " & intIDs(intLoop) & " Or "
Next intLoop

'Truncates the last OR and spaces from the Where string and finishes the string
intLen = Len(strWhere)
strWhere = "(" & Left(strWhere, intLen - 4) & &quot);"

'Creates the SQL string to select all necessary fields and criteria
strSQL = "SELECT --your select code-- FROM --your from code"
strSQL = strSQL & strWhere

'Runs the SQL to create the working table tblFeeSlipRpt used to create the report itself
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

In my case, this is a make table query and is initiated directly. You will probably need to do something like this to delete any previously saved query and add the new one. the query can then be used as the data source for the report.

If ObjectExists(acQuery, "Your Query Name&quot Then DoCmd.DeleteObject acQuery, "Your Query Name"
Set db = CurrentDb()
db.CreateQueryDef "Your Query Name", strSQL
DoCmd.OpenQuery "Your Query Name"

ObjectExists is a function to determine, obviously, if a given object exists in the database and you'll need to add it. Here's the code:

Function ObjectExists(ObjType As Integer, ObjName As String) As Boolean
'Determines whether or not a given object exists in database
'If ObjectExists(acTable, "tblOrders&quot then ...

Dim strTemp As String
Dim db As Database
Dim strContainer As String

On Error Resume Next
Set db = CurrentDb()

Select Case ObjType
Case acTable
strTemp = db.TableDefs(ObjName).Name
ObjectExists = (Err.Number = 0)
Case acQuery
strTemp = db.QueryDefs(ObjName).Name
ObjectExists = (Err.Number = 0)
Case acMacro, acModule, acForm, acReport
Select Case ObjType
Case acMacro
strContainer = "Scripts"
Case acModule
strContainer = "Modules"
Case acForm
strContainer = "Forms"
Case acReport
strContainer = "Reports"
End Select
strTemp = db.Containers(strContainer).Documents(ObjName).Name
ObjectExists = (Err.Number = 0)
End Select

End Function

I know this can look intimidating but if you take it a step at a time and test as you go, its not that difficult.

One of the best ways I've found for creating the strSQL is to actually create the query as you normally would and then look at the SQL code (from the view menu) and then copy and paste it into the event sub adding the quotation marks as needed.
Larry De Laruelle
ldelaruelle@familychildrenscenter.org