sql select error- Help!

[Guest]

In the following sql statement, start_date_cal.Value and end_date_cal.Value
are coming from calendar objects. The values are ok and displayed in text
boxes. When I try this sql statement, I get a syntax error " can't find
field | in expression". What am I doing wrong? Thanks.

strSQL = "select * " _
& "where DATE Between " _
& "#[start_date_cal.value]# And #[end_date_cal.value]#;"

 

[Guest]

YOu need to put your reference to the field outside of the quotes otherwise
it literally thinks you are entering a date of '[start_date_cal.value]' into
the sql string. Try this...

strSQL = "select * " _
& "where DATE Between #" _
& [start_date_cal.value] & "# And #" & [end_date_cal.value] & "#;"

 

[Douglas J. Steele]

You're missing the FROM part of the query that tells what table to select
from.

If Date is the name of a field in your table, change it if you can. Date is
a reserved word, so should not be used for your own purposes. If you can't
change it, put square brackets around it: [Date]

What are [start_date_cal.value] and [end_date_cal.value]? Assuming they're
controls on a form, you need to put their values into the SQL statement not
their names.

strSQL = "select * " _
& "where [DATE] Between " _
& "#" & [start_date_cal.value] & "# And # " & [end_date_cal.value] & "#"

And, in case you weren't aware, the values must be in mm/dd/yyyy format,
regardless of what your Regional Settings are. (Okay, so that's not
completely true. They must be mm/dd/yyyy, or any unambiguous format, such as
yyyy-mm-dd or dd mmm yyyy. The point is, it will not work if the dates are
in dd/mm/yyyy format, unless the day parts are greater than 13)