Between Dates

[Guest]

I capture a field 'SDate' in records. I have a search form where I want to show records between 'date1' and 'date'. I can't seem to get the query to work though. I've tried:

SELECT * FROM Table WHERE (SDate >= :ate1: AND (SDate <= :ate2:

And

SELECT * FROM Table WHERE SDate BETWEEN :ate1:: AND :ate2::

Neither seem to work for me. What am I missing?

 

[Kevin Spencer]

The SQL language uses punctuation to identify data types. If you're using
Access, you would most likely use the "#" character to delimit your values;
in most other DBs you would use single quotes (as well as in the latest
version of Access, which can use either). In your case, something like the
following will work:

SELECT * FROM Table WHERE SDate BETWEEN ':ate1::' AND ':ate2::'

or

SELECT * FROM Table WHERE SDate BETWEEN #:ate1::# AND #:ate2::#

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

I capture a field 'SDate' in records. I have a search form where I want

to show records between 'date1' and 'date'. I can't seem to get the query
to work though. I've tried:

 

[Guest]

Well, that's what I thought too but neither of those work for me either. Using the # signs spit out an error of incorrect syntax right in the wizard when I try to "verify query". The query with the single quotes seems to work OK in the wizard but not when the rubber hits the road. With the quotes, the results page kick out the incorrect syntax error. What else ya got?

 

[Guest]

Should also mention that neglecting both #'s and ''s return "No records returned".

 

[Kevin Spencer]

Well, that's what I thought too but neither of those work for me either.

Using the # signs spit out an error of incorrect syntax right in the wizard
when I try to "verify query". The query with the single quotes seems to
work OK in the wizard but not when the rubber hits the road. With the
quotes, the results page kick out the incorrect syntax error. What else ya
got?

I got very little. Your description of the various things you tried and the
various results you got is both sketchy and all mixed up. A better
descrription would be something like "When I did A, then B, then C, I got
this specific error message: message. When I did X, then Y, then Z, I got
this other specific error message: message." Debugging is a process of
identifying the specific actions taken and the specific results that
occurred, in order to identify the specific actions that caused the specific
results, and deal with them. Mentioning the database product name would be
helpful. Also, telling me what data type your date field is would be
helpful.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.