-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
You've used American date format delimiters (#) in your query, which
indicates, to me, that the date value is stored as a DateTime data type.
The DateTime data type is a floating point number with the integer part
representing the number of days since December 31, 1899, and the decimal
part representing the number of milliseconds from the date's midnight.
When you're creating the SQL string use the CDate() function on your
Euro-date formatted date string. E.g.:
CDate("19/4/2004")
I believe it will convert it to a correct date number (April 19, 2004)
'cuz VBA will use your PC's time/date settings to "understand" the
19/4/2004 date string.
Another thing you can do is use PARAMETERS. E.g.:
PARAMETERS StartDate Date, EndDate Date;
SELECT [Date], [DOSM-DSName]
FROM DOSMBK
WHERE DOSMBK.[Date] < StartDate
AND ([DOSM-DSName]="M9S" Or [DOSM-DSName]="M9SA")
AND [From] = EndDate
If you're running the query from the db window, you will be prompted for
the StartDate and EndDate. Enter the dates in your area's date format.
The date value will be converted to the correct number value.
From VBA use the DAO.QueryDef object to set the parameters. E.g.:
dim db as dao.database
dim qd as dao.querydef
set db = currentdb
set qd = db.querydefs("query_name)
qd!StartDate = CDate("19/4/2004")
qd!EndDate = CDate("6/9/2004")
qd.Close ' or do something w/ the QueryDef
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQUnbyoechKqOuFEgEQKoaACfaPUk0Vf9Nz3x1joA4KJj5f lU9mQAniFb
cruYZPRYA8vCGmXA3GtSbGNM
=+ZF+
-----END PGP SIGNATURE-----
Peter Bailey wrote:
I have a vba string taht dynamically creates the query which has two dates
in it that it grabs off an open form as a string from the textbox.
What I generate in vba is:
SELECT DOSMBK.Date, DOSMBK.[DOSM-DSName] FROM DOSMBK WHERE
(((DOSMBK.Date)<#19/04/2004#) AND ((DOSMBK.[DOSM-DSName])="M9S" Or
(DOSMBK.[DOSM-DSName])="M9SA") AND ((DOSMBK.From)=#06/09/2004#));
If I view this as the sql view after opening the query grid I get this:
SELECT DOSMBK.Date, DOSMBK.[DOSM-DSName]
FROM DOSMBK
WHERE (((DOSMBK.Date)<#4/19/2004#) AND ((DOSMBK.[DOSM-DSName])="M9S" Or
(DOSMBK.[DOSM-DSName])="M9SA") AND ((DOSMBK.From)=#6/9/2004#));
it has changed the date to an american style ie #4/19/2004#
Any ideas why? I guess I have done something stupid here.
If I view the query grid in design view the criteria shows <#19/04/2004#
which is how it should be.
