Mr. Browne,
Thank you for your response.
Any suggestions on how to get this to work? I took over this database
from
another person. She uses this code to allow the user to create a dynamic
WHERE clause statement that is used to append record ID to a table. The
record ID is joined with other tables in a query as the record source for
reports.
As you said, it works very well for text, but I'm struggling with the
numeric fields.
Should I put IF statements to veryify if a numeric field is populated, if
so... add to the SQL?
'************** START OF SAMPLE CODE ************
'here is the full sql routine, lines that produces errors are noted.
Private Sub BtnApply_Click()
Dim db As Database
Dim QD As QueryDef
Dim where As Variant
Dim Results As Recordset, Total As Long
Set db = DBEngine.Workspaces(0).Databases(0)
'Delete existing dynamic query, trap error if it does not exist.
On Error Resume Next
db.QueryDefs.Delete ("qryDynamic_Donors")
On Error GoTo 0
'Note Single quotes surrounding text fields
'Ex: (" AND [RepAssigned]='" + Me![RepAssigned] + "'")
'Note No Single quotes surrounding numeric field
where = Null
where = where & (" AND [Status]='" + Me![cbo_Status] + "'")
'AutoNumber field, error 13 type mismatch
where = where & (" AND [Donor ID Number]= " + Me![cbo_Donor_ID])
'Date field, error 13 type mismatch
where = where & (" AND [Date Added] " + Me.cbo_Operator_Date + "#" +
Me![txt_Date_Added] + "#")
where = where & (" AND [Donor Type]='" + Me![cbo_Donor_Type] + "'")
where = where & (" AND [Business Type]='" + Me![cbo_Business_Type] + "'")
where = where & (" AND [First Name]='" + Me![cbo_Name] + "'")
where = where & (" AND [Last Name]='" + Me![cbo_Name2] + "'")
where = where & (" AND [Company Name]='" + Me![cbo_Company] + "'")
where = where & (" AND [City]='" + Me![cbo_City] + "'")
where = where & (" AND [State]='" + Me![cbo_State] + "'")
where = where & (" AND [Zip]= " + Me![cbo_Zip])
'Currency field, error 13 type mismatch
where = where & (" AND [Value] " + cbo_Operator_Value + " " +
Me![cbo_Value])
'MsgBox "Select * From [qryDynamic_UsedDonors-Address]" & (" where " +
Mid(where, 6) & ";")
If IsNull(where) Then
Set QD = db.CreateQueryDef("qryDynamic_Donors", "Select * From
[qryDynamic_UsedDonors-Address]")
Set Results = db.OpenRecordset("qryDynamic_Donors")
Else
MsgBox where
Set QD = db.CreateQueryDef("qryDynamic_Donors", "Select * From
[qryDynamic_UsedDonors-Address]" & (" where " + Mid(where, 6) & ";"))
Set Results = db.OpenRecordset("qryDynamic_Donors")
End If
Total = Results.RecordCount
If Total = 0 Then
MsgBox "No records match selection criteria, try again!"
Else
'A special table Dynamic_Contacts is loaded to eliminate the duplicates
due to
'multiple addresses for one company
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDynamic_Donors DELETE"
DoCmd.OpenQuery "qryDynamic_Donors Append"
DoCmd.SetWarnings True
If Me.cbo_Reports <> "" Then
DoCmd.OpenReport Me.cbo_Reports, acViewPreview
End If
'************ END OF SAMPLE CODE *************************
Allen Browne said:
The + operator has 2 meanings in Access:
- concatenation, when applied to text/strings;
- numeric addition, when applied to numbers.
You may find that this is why your code works with text, but not with
dates
or numbers.
The ampersand operator is unambiguous, so try:
where = where & " AND ([Date Added] " & Me.cbo_Operator_Date & " #" &
Me![txt_Date_Added] & "#)"
Note that the brackets have been moved as well.
If that still fails, perhaps the combo or text box is null (which would
make
the string mal-formed), or perhaps the Regional Settings are not American
(which could make the literal date wrong unless you explicitly format it
as
US.)
Hello all,
Thank you for viewing this post. Can someone please help me with the
syntax.
The cbo_Operator_Date is for the equal to, greater than, less than.
The cbo_Operator_Value is for the equal to, greater than, less than.
'text field, WORKS
where = where & (" AND [Status]='" + Me![cbo_Status] + "'")
'AutoNumber field, error 13 type mismatch
where = where & (" AND [Donor ID Number]= " & Me![cbo_Donor_ID])
'Date field, error 13 type mismatch
where = where & (" AND [Date Added] " + Me.cbo_Operator_Date + " #" +
Me![txt_Date_Added] + "#")
Currency field, error 13 type mismatch
where = where & (" AND [Value] " + cbo_Operator_Value + " " +
Me![cbo_Value])
