Dynamic QBF - concatenating - HELP!

  • Thread starter MSAccess Program Error
  • Start date
M

MSAccess Program Error

Since I couldn't get you to print the SQL, I did it myself
and think i found the error that will fix this.
Here's my suggestion.
Your date range ( between .. and ... criteria in the where
clause) should be seperated from the other "AND"'s. It's
not exactly the same. You do this by enclosing in
paranthesis.

where = Null
where = where & " AND [Clock Number]='" + Me![txtClock]
+ "'"
where = where & " AND [Last Name]='" + Me![text15] + "'"

'.... changed this part .........
where = where & " AND (([Date of Hire]) Between " & _
Format(Me![txtStart], "\#m\/d\/yyyy\#") & " And " & _
Format(Me![txtEnd], "\#m\/d\/yyyy\#") + ")"
'.... end changed ...............

where = where & " AND [Location]= '" + Me![text12] + "'"
where = where & " AND [Title]= '" + Me![combo23] + "'"
where = where & " AND [Union]= '" + Me![combo27] + "'"


I'm also sending the code that I put together (based on
your code) to find it.
To run this, create a new module and paste in the sub
below.
Then open the immediate window and type in:
printWhereClauseThenSQL
and hit enter.

===========================================
Code:

Public Sub printWhereClauseThenSQL()

Dim where As Variant

'Set MyDatabase = CurrentDb()
'
'If ObjectExists("Queries", "qryDynamic_QBF") = True Then
'MyDatabase.QueryDefs.Delete "qryDynamic_QBF"
'MyDatabase.QueryDefs.Refresh
'
'End If

Dim txtClock As String
Dim text15 As String
Dim txtStart As String
Dim txtEnd As String
Dim text12 As String
Dim combo23 As String
Dim combo27 As String

txtClock = "123478"
text15 = "text15"
txtStart = "1/4/2004"
txtEnd = "1/5/2004"
text12 = "text12"
combo23 = "combo23"
combo27 = "combo27"

where = Null
where = where & " AND [Clock Number]='" + txtClock + "'"
where = where & " AND [Last Name]='" + text15 + "'"
where = where & " AND (([Date of Hire]) Between " _
& Format(txtStart, "\#m\/d\/yyyy\#") & " And " _
& Format(txtEnd, "\#m\/d\/yyyy\#") + ")"
where = where & " AND [Location]= '" + text12 + "'"
where = where & " AND [Title]= '" + combo23 + "'"
where = where & " AND [Union]= '" + combo27 + "'"

'you can print the SQL and debug it

Debug.Print where
Debug.Print "Select * from EADDataForPhotos" & (" WHERE "
+ Mid(where, 6) & ";")

'Set MyQueryDef = MyDatabase.CreateQueryDef
("qryDynamic_QBF", _
'"Select * from EADDataForPhotos " & (" where " + Mid
(where, 6) & ";"))
'
'Set Recordset = MyDatabase.OpenRecordset("Select * from
EADDataForPhotos " & (" where " + Mid(where, 6) & ";"))
'
'If Recordset.RecordCount = 0 Then
'MsgBox "No Records were found"
'Else
'DoCmd.OpenQuery "qryDynamic_QBF"
'DoCmd.OpenForm "Search Results"
'Forms![Search Results].Requery
'DoCmd.Close acQuery, "qryDynamic_QBF"


'End If
 
Top