Use of dates in query

[m1kee]

Hi folks

The following code for creating a query works fine except for the date line, can someone please tell me what is worng with the line of code.

Private Sub cmdOpenQuery_Click()

On Error GoTo Err_cmdOpenQuery_Click
Dim db As Database
Dim qdef As QueryDef
Dim strSELECT As String
Dim strWHERE As String

Set db = CurrentDb()

strSELECT = "SELECT DISTINCT * FROM qryJobCostingsSurveyorsQBF "

If chkJobNumber = True Then
strWHERE = strWHERE & "' AND '" & " WHERE [JobNumber] BETWEEN '" & Me.JobNumberFrom & "' AND '" & Me.JobNumberTo & "';"
End If

If chkOffice = True Then
strWHERE = strWHERE & "' AND '" & " WHERE [Office] BETWEEN '" & Me.OfficeFrom & "' AND '" & Me.OfficeTo & "';"
End If

If chkDate = True Then
strWHERE = strWHERE & "' AND '" & " WHERE [Date] BETWEEN #" & Me.DateFrom & "# AND #" & Me.DateTo & "#;" <---- Problem code
End If

If chkCommissionID = True Then
strWHERE = strWHERE & "' AND '" & " WHERE [CommissionID] BETWEEN '" & Me.CommissionIDFrom & "' AND '" & Me.CommissionIDTo & "';"
End If

If chkStatusID = True Then
strWHERE = strWHERE & "' AND '" & " WHERE [StatusID] BETWEEN '" & Me.StatusIDFrom & "' AND '" & Me.StatusIDTo & "';"
End If

strSELECT = strSELECT & strWHERE

db.QueryDefs.Delete "qryJobCostingsSurveyors"
Set qdef = db.CreateQueryDef("qryJobCostingsSurveyors", strSELECT)

DoCmd.OpenQuery "qryJobCostingsSurveyors", acViewNormal

Exit_cmdOpenQuery_Click:
Exit Sub

Err_cmdOpenQuery_Click:

MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click

End Sub


Many thanks, Mike.



Do or do not, there is no try. - Yoda

 

[cLFlaVA]

Why do you have single-quotes around your AND connectors?

*cLFlaVA
----------------------------
When will I be able to see what other members can see about myself? It's been like, a freakin' month already!

 

[m1kee]

Good point, I have taken them out now, but the date line still dosn't work, the query runs, but there are no records to display.

regards, mike.

Do or do not, there is no try. - Yoda

 

[cLFlaVA]

Debug.Print your sql statement before you execute it. You can then view it in the "Immediate" window.

Does it look right to you?

*cLFlaVA
----------------------------
When will I be able to see what other members can see about myself? It's been like, a freakin' month already!

 

[m1kee]

Hi cLFlaVA

I have taken out the ' ' marks, but this wasn't causing a problem.

The date line still does the same thing, it runs the query, but no records are shown.

cheers, Mikee

Do or do not, there is no try. - Yoda

 

[cLFlaVA]

See additional answers in your duplicate post:
thread705-920284


*cLFlaVA
----------------------------
When will I be able to see what other members can see about myself? It's been like, a freakin' month already!