Problem with dates in query

[m1kee]

Hi folks - 2nd attempt as the first thread stopped appering on the forum!?

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

Mnay thanks, Mikee

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

 

[SkipVought]

Hi,

Is Me.DateFrom ...

Validated text string containing a date string

Unvalidated text string

A REAL Date

???

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[lupins46]

You can only have one WHERE.
Your outcomes could concatenate several WHEREs.

Put the WHERE in the Select string and remove them from the strWhere strings.

 

[SkipVought]

Good point,

The general syntax in the select is

Select * From MyTable Where f1=val1 And f2=val2 And ...

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}