format dates - very strange phenomon

[Guest]

In order to adapt the correct format for a date in a SQL code,
I set as 'date function'
strBegin = Format(strBegindatum, "MM-dd-yyyy")
strEinde = Format(strEinddatum, "MM-dd-yyyy")

for strBeging= 1-08-2004 : this results in 08-01-2004
for strEinde = 10-08-2004 : this results in 10-08-2004
or if the day is > then 9,
the format function doesn't change the date ....
thanks for any support / help / advice

 

[Allen Browne]

The Format() function works if you pass in a date/time type variable, but
your variable names suggest you are passing in a string variable instead.
You could try wrapping the string in CDate() to convert it to a date, and
the pass the result to Format().

For literal dates that you concatenate into a SQL string, be sure to use the
# delimiter as well.

For ideas on how to avoid the 3 situations where Access misunderstands your
dates, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Brendan Reynolds]

Are 'strBegindatum' and 'strEindatum' date variables or string variables?
The code should work if they are date variables, but the 'str' prefix
indicates they may be strings? If so, VBA will first have to convert them to
dates, then back to strings again, making it's usual 'guess' as to which
part of the string represents the month. Hand the Format function a date
variable, not a string, and it should work.

 

[Guest]

thks for your kind advice …

but,

after analysis of everything, I found the mistake in the initial declaration
of my code …

Dim strBegin, strBegindatum, strEinde, strEinddatum
Dim strSQL As String


'datums bepalen
strBegindatum = Begindatum
strEinddatum = Einddatum


'formateren van de datums voor de SQL[us- format]
strBegin = Format(strBegindatum, "MM/dd/yy")
strEinde = Format(strEinddatum, "MM/dd/yy")


and I started with …

Dim strBegin As Date, strEinde As Date
Dim strBegindatum As Date, strEinddatum As Date

and do so, the format of all dates was wrong !!!