Date format apparently changes mid query!!

[terre]

I have this in the AfterUpdate of a Text box (Format=Short Date)

Private Sub InptDate_AfterUpdate()
Dim inDate As Date
Dim iStart As Date
Dim iStop As Date

iStart = InptDate
iStop = iStart + 27

For inDate = iStart To iStop
strSQL = "INSERT INTO tblTimeSheet (wDate)VALUES (#" & Format(inDate, "d/m/yyyy") & "#);"
DoCmd.RunSQL strSQL
Next
End Sub

The following appears as the output in tblTimeSheet.
wDate
5/05/2008
5/06/2008
5/07/2008
5/08/2008
5/09/2008
5/10/2008
5/11/2008
5/12/2008
13/05/2008
14/05/2008
15/05/2008
16/05/2008
17/05/2008
18/05/2008
19/05/2008
20/05/2008
21/05/2008
22/05/2008
23/05/2008
24/05/2008
25/05/2008
26/05/2008
27/05/2008
28/05/2008
29/05/2008
30/05/2008
31/05/2008
6/01/2008

Its probably so simple....but I'm at a loss.
Can anyone help please.

Terre

 

[MajP]

It is your format string
"d/m/yyyy"
should be
"dd/mm/yyyy"

you can not format oct,noc,dec with a single digit

http://www.mvps.org/access/datetime/date0005.htm

 

[MajP]

Also what are your regional settings?

 

[terre]

I realise that somewhere months and days are getting mixed......because the change aroud happens when the month reaches 13.

Regional settings Australia.... dd/mm/yyyy

changed strSQL = "INSERT INTO tblTimeSheet (wDate)VALUES (#" & Format(inDate, "d/m/yyyy") & "#);"

to

strSQL = "INSERT INTO tblTimeSheet (wDate)VALUES (#" & Format(inDate, "dd/mm/yyyy") & "#);"

Only difference is 5/06/2008 (etc) becomes 05/06/2008

s'a worry

Thanks

 

[MajP]

actually with that said, I think you are better off inserting into the table using the mm/dd/yyyy format and then formatting the table or query to display as dd/mm/yyyy.

 

[terre]

Whatever...whyever....that works a treat ...

strSQL = "INSERT INTO tblTimeSheet (wDate)VALUES (#" & Format(inDate, "mm/dd/yyyy") & "#);"

and then formatting the table or query to display as dd/mm/yyyy



Thanks MajP...much appreciated

 

[dhookom]

All queries in Access assume the US date format of mm/dd/yyyy.

Duane
Hook'D on Access
MS Access MVP

 

[terre]

hehe

bugga

does that mean Bill is an American?

Thanks Duane....and thanks also for all your great work on various Access help/example sites also. You and several others help we mugs a heck of a lot.

Thanks again