date problem between vba and query grid

[Peter Bailey]

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.

Regards in advance
Peter

[Pieter Linden]

Access VBA uses american date format mm/dd/yyyy

[Peter Bailey]

Hi Pieter

because I am sending only a string and creating the query from that I
thought I might get around that problem as the data type for the date is a
string and not a date.

:(

"Pieter Linden" <pi********@hot mail.com> wrote in message
news:bf******** *************** **@posting.goog le.com...

Access VBA uses american date format mm/dd/yyyy

[MGFoster]

-----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("q uery_name)
qd!StartDate = CDate("19/4/2004")
qd!EndDate = CDate("6/9/2004")
qd.Close ' or do something w/ the QueryDef

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

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

iQA/AwUBQUnbyoechKq OuFEgEQKoaACfaP Uk0Vf9Nz3x1joA4 KJj5flU9mQAniFb
cruYZPRYA8vCGmX A3GtSbGNM
=+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.

[Tony Toews]

"Peter Bailey" <pe*********@an daluz.fsbusines s.co.uk> wrote:

it has changed the date to an american style ie #4/19/2004#
Any ideas why? I guess I have done something stupid here.

Return Dates in US #mm/dd/yyyy# format
http://www.mvps.org/access/datetime/date0005.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm