Syntax Error on Query

[cooldisk2005]

All,
I have the following code giving me this error:

Run-time error '3075':

Syntax error in date in query expression
'(tbl_showmoney.dateEntered) >=#01-Mar-2006# AND
(tbl_showmoney.dateEntered) <=#04-Mar-2006ORDER BY
tbl_showmoney.regID'.

Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the Query definition
'Constant Select statement for the Query definition
strSQL = "SELECT tbl_showmoney.regID, tbl_showmoney.dateEntered, tbl_showmoney.name, tbl_showmoney.company, tbl_showmoney.mailingAddress, tbl_showmoney.city, tbl_showmoney.stateCan, tbl_showmoney.zipCode, tbl_showmoney.country, tbl_showmoney.telephoneNo, tbl_showmoney.faxNo, tbl_showmoney.email, tbl_showmoney.contype, tbl_showmoney.totalCamp, tbl_showmoney.idMtg, tbl_showmoney.day1Con, tbl_showmoney.day2Con, tbl_showmoney.day3Con, tbl_showmoney.day4Con, tbl_showmoney.day5Con, tbl_showmoney.day6Con, tbl_showmoney.day7Con, tbl_showmoney.bkstCount, tbl_showmoney.lunchTickets, tbl_showmoney.dinnerTickets, tbl_showmoney.drinkTickets " & _
"FROM tbl_showmoney"

strWhere = "WHERE"

strOrder = "ORDER BY tbl_showmoney.regID;"


'Set the WHERE clause for the QueryDef if information has been entered into a field on the form
If Not IsNull(Me.name1) Then '<--If the textbox txtFName contains no data THEN do nothing
strWhere = strWhere & " (tbl_showmoney.name) Like '*" & Me.name1 & "*'  AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.company) Then
strWhere = strWhere & " (tbl_showmoney.company) Like '*" & Me.company & "*'  AND"
End If

If Not IsNull(Me.contype) Then
strWhere = strWhere & " (tbl_showmoney.contype) Like '*" & Me.contype & "*'  AND"
End If

If Not IsNull(Me.dateBegin) Then
strWhere = strWhere & " (tbl_showmoney.dateEntered) >=#" & Format(Me.dateBegin, "dd-mmm-yyyy") & "# AND"
End If

If Not IsNull(Me.dateEnd) Then
strWhere = strWhere & " (tbl_showmoney.dateEntered) <=#" & Format(Me.dateEnd, "dd-mmm-yyyy") & "# AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("qry_showmoney")
   qryDef.SQL = strSQL & " " & strWhere & "" & strOrder
   
'Open the Query
    
    Dim dbs As DAO.Database, rst As DAO.Recordset
Dim rsCnt As Integer 'the counter
 
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("qry_showmoney", dbOpenDynaset)
 
With rst
  If .RecordCount > 0 Then 'What you want done
    DoCmd.OpenForm ("frm_registrationDeskSearch"), acNormal, "[regID]"
    DoCmd.Close acForm, "frm_search"
    
    
  Else
    MsgBox "There is no data that meets your criteria.  Please try again.", vbOKOnly, "No Data Found" 'What you want done
    name1.Value = ""
    company.Value = ""
    contype.Value = ""
    name1.SetFocus
  End If
End With

Set dbs = Nothing
Set rst = Nothing

   

End Sub

Can anyone help me figure out why I am getting this error?

Any help will be appreciated.

Thanks,
Jerome

 

[Remou]

You seem to be missing a hash (#) and a space. A space here
[tt]strOrder = [red]" O[/red]RDER BY tbl_showmoney.regID;"[/tt]
And this:
[tt]strWhere = Mid(strWhere, 1, Len(strWhere) - 5)[/tt]
seems to be stripping the hash, perhaps -4, rather than -5.

 

[cooldisk2005]

That worked perfectly!!!!....Thank you so MUCH!!!!!!

One more question:

I want to format the date field to be in a format like this:

2006-3-4

How do I do this?

 

[Remou]

Do you mean (?):
Format(Me.dateEnd, "yyyy-m-d")

 

[cooldisk2005]

Remou,
Here is my problem:

I need the dateBegin and dateEnd field to have a date format like this: 2006-3-1.

I have that in the code above for those fields, but when you actually look at the design view of the query it is formatting the date like this: >=#3/1/2006#, which is not what I want. Again I want: 2006-3-1.

I actually have the following:

Format(Me.dateEnd, "yyyy-m-d") & "# AND"

but it is still not working.

How can I get this to work?

 

[Remou]

Queries have their own ideas about dates and that is that they should be in the format mm/dd/yy, as far as I recall.
You may find RoyVidar's post in this:
Date format problem
thread705-1081480
Useful.