DAO dates again

[Joe]

Help! the following code complains: 3075 Syntax error
(missing operator) in query expression.

Set rcdSFNJob = dbSFNJob.OpenRecordset("SELECT * FROM
qrystafffitter WHERE INVDATE > '#' & forms!datepick!
start_date & '#' and INVDATE '#' < '#'& forms!datepick!
end_date '#'")

I would appreciate any help on sorting out the syntax and
missing operators. DAO has me scratching my head more
often than any other component of Access, I have had much
appreciated help via the group on several occasions and at
times I have thought that I have got the drift then there
is another date string trap waiting around the corner.

Thanks Joe

 

[Roger Carlson]

One thing that can help is to assign the SQL string to a string variable.
This will allow you to use Debug.Print to see how Access is interpreting the
string:

strSQL = "SELECT * FROM qrystafffitter WHERE INVDATE > '#' &
forms!datepick!
start_date & '#' and INVDATE '#' < '#'& forms!datepick!
end_date '#'"
Debug.Print
Set rcdSFNJob = dbSFNJob.OpenRecordset(strSQL)

Your SQL string will appear in the immediate window where you can copy it
into an empty query where the database engine will give you more help
identifying what is wrong.

At any rate, the string should look like this:

strSQL = "SELECT * FROM qrystafffitter WHERE INVDATE > #" & forms!datepick!
start_date & "# and INVDATE < #" & forms!datepick!end_date & "#"

 

[PC Datasheet]

....and INVDATE '#' ...

Remove '#'

< '#'& forms!datepick!end_date '#'")

Add & after date

 

[Chris]

Set rcdSFNJob = dbSFNJob.OpenRecordset("SELECT * FROM
qrystafffitter WHERE INVDATE > #" & forms!datepick!
start_date & "# and INVDATE < #"& forms!datepick!
end_date & "#")

OR:

Set rcdSFNJob = dbSFNJob.OpenRecordset("SELECT * FROM
qrystafffitter WHERE INVDATE BETWEEN #" & forms!datepick!
start_date & "# AND #" & forms!datepick!end_date & "#")



Chris