By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,948 Members | 1,958 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,948 IT Pros & Developers. It's quick & easy.

date problem between vba and query grid

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Access VBA uses american date format mm/dd/yyyy
Nov 13 '05 #2

P: n/a
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********@hotmail.com> wrote in message
news:bf*************************@posting.google.co m...
Access VBA uses american date format mm/dd/yyyy

Nov 13 '05 #3

P: n/a
-----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.


Nov 13 '05 #4

P: n/a
"Peter Bailey" <pe*********@andaluz.fsbusiness.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
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.