SQL Format for CDate Revisited

[Guest]

This message is a follow-up to a previous thread regarding the SQL syntax for
the CDate function. At the suggestion of Van Dinh, I now have the following
code for a between selection based on start and end dates entered on a form:

where = where & " AND [MAINT].[MAINT_PROD_DATE] BETWEEN " &
Format(Me!_[txtProdStartDate], "yyyy/mm/dd") & " AND " &
Format(Me!_[txtProdEndDate], "yyyy/mm/dd")

The problem I now having is that the code works properly for a given date
range but entering a range that should have no results will retrieve every
record that has a MAINT_PROD_DATE that is not null.

Any reasons why or suggestions on how to eliminate this problem?

 

[Allen Browne]

If these text boxes are unbound, set their Format property to Short Date so
that Access knows to interpret them as dates.

More info:
International Date Formats in Access
at:
http://members.iinet.net.au/~allenbrowne/ser-36.html

 

[Guest]

Allen-

The text boxes are, in fact, unbound; however, the dates are kept in the
database as strings with yyyy/mm/dd format so setting the text boxes to short
dates does not provide proper query results.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

String criteria requires quote delimiters:

where = where & " AND [MAINT].[MAINT_PROD_DATE] BETWEEN '" &
Format(Me!_[txtProdStartDate], "yyyy/mm/dd") & "' AND '" &
Format(Me!_[txtProdEndDate], "yyyy/mm/dd") & "'"

I used a single quote after Between; before and after AND; and at the
end of the string.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQfAdCoechKqOuFEgEQLqpQCg0LR7ZB+fiUYOyvNZ0Ohmc485IgcAoOkT
t0n9TXJkYs+g2Sn+qrb87QOA
=wrK0
-----END PGP SIGNATURE-----

 

[david epsom dot com dot au]

range but entering a range that should have no results will retrieve

record that has a MAINT_PROD_DATE that is not null.

I don't see that, unless you are using a subquery: can you
show us the whole query?

(david)

This message is a follow-up to a previous thread regarding the SQL syntax for
the CDate function. At the suggestion of Van Dinh, I now have the following
code for a between selection based on start and end dates entered on a form:

where = where & " AND [MAINT].[MAINT_PROD_DATE] BETWEEN " &
Format(Me!_[txtProdStartDate], "yyyy/mm/dd") & " AND " &
Format(Me!_[txtProdEndDate], "yyyy/mm/dd")

The problem I now having is that the code works properly for a given date
range but entering a range that should have no results will retrieve every
record that has a MAINT_PROD_DATE that is not null.

Any reasons why or suggestions on how to eliminate this problem?

 

[Allen Browne]

So you are matching strings, not dates?

It would still be an advantage to set the Format of the text boxes to Short
Date, so Access won't accept invalid dates. Then include quotes around the
string values in the SQL statement:

where = where & " AND [MAINT].[MAINT_PROD_DATE] BETWEEN """ &
Format(Me!_[txtProdStartDate], "yyyy/mm/dd") & """ AND """ &
Format(Me!_[txtProdEndDate], "yyyy/mm/dd") & """"