Short Date Format Getting Mixed Up!

wjoc1 · Sep 3, 2003

Hi,

I have an SQL query which is appending a record onto a table. Part of this append query is to insert a date which is returned from a function into a field in the record. This field has it's format set to "Short Date" and the input mask is "99/99/0000;0;_"

Here's the problem: The getStartDate() function will return a date like, 06/01/2003 i.e. 06 January 2003 but when this is inserted into the record it changes to 01/06/2003 i.e. 01 June 2003! It keeps mixing up/swapping the day and month parts of the date.

Why the hell is this happening! The function returns the date correctly and in the correct format? I cannot figure it out.

Below is the SQL. The StartDate is the one that's giving trouble. The EndDate value I take off a form and I have no problems with this. Both date fields have identical formats. Any help would be greatly appreciated.

Thanks,
Liam

"INSERT INTO PREV_ISSUED ( [Stock Item No], [Start Date],[End Date], Place, Team, Person ) SELECT " & getStockItemNo() & ", #" & getStartDate() & "#, #" & Me!EndDate & "#, " & getPlace() & ", " & getTeam() & ", " & getPerson()

Fornatian · Sep 3, 2003

Without knowing the get start date function it is difficult give any exact advice, however I do know that SQL uses mm/dd/yy as default. You may be able to get round this by formatting the return of the getStartDate function into something which SQL will definitely interpret how you want, such as Format(getStartDate(),"dd mmmm yyyy"). Just for starters.

namliam · Sep 4, 2003

Fornatian said:
however I do know that SQL uses mm/dd/yy as default.

Not just SQL but whole of Access....

Just an addendum.... otherwise take his advise (or change the format to mm/dd/yyyy if you prefer, but his will work (better))

Regards

Rich · Sep 4, 2003

namliam said:
Not just SQL but whole of Access....

Regards

That's not true, Access will use the regional settings to determine the format in queries/forms and even some vba. The problem occurs mainly when returning/comparing dates in strings

wjoc1 · Sep 4, 2003

Lads,

Thanks for the advice but I'm still in trouble. It does seem to be the SQL alright. The getStartDate() function is returning it in the correct format. I know this because I simply have a message box popping up displaying the date before running the sql. Ths msgbox is simply for verification purposes until I get this mess sorted out.

MsgBox "The Start Date : " & getStartDate()

After the date is appended to the table however the format suddenly changes. I tried using the format function as follows:

"INSERT INTO PREV_ISSUED ( [Stock Item No], [Start Date],[End Date], Place, Team, Person ) SELECT " & getStockItemNo() & ", #" & Format(getStartDate(), "dd mm yyyy") & "#, #" & Format(Me!EndDate, "dd mm yyyy") & "#, " & getPlace() & ", " & getTeam() & ", " & getPerson()

Unfortunately I still have the same problem. Good ol' Microsoft is all I can say at this stage

Thanks,
Liam

Fornatian · Sep 4, 2003

try using the long month format "dd mmmm yyyy" so it comes out
01 September 200X, this should then be correctly converted. Using your format() above, it would still return 01 09 200X which could still be converted to american date format without explicitly recognising its month.

Rich · Sep 4, 2003

Did you try
SELECT " & getStockItemNo() & ", #" & Format(getStartDate(), "mm dd yyyy") & "#, #" & Format(Me!EndDate, "mm dd yyyy") & "#, " & getPlace() & ", " & getTeam() & ", " & getPerson() ?

Pat Hartman · Sep 5, 2003

There's a good article in this month's Smart Access (Pinnacle Publishing) that examines this problem in some depth. Given that I live in the US where the standard short date format is mm/dd/yy, I don't run into problems with date strings but I see a lot of posts on the subject. The article points out that when string dates are delimited with the pound sign (#), the strings are interpreted as mm/dd/yy. However, when the string dates are delimited with double quotes ("), Access uses the windows setting to determine the format. So, if you switch to using quotes as the delimiter, that may solve your problem. An example from the article:

And, finally, with the Short Date format set to dd/mm/yyyy:

?Format(Date())
31/05/2003
?Format(#13/12/11#, "dd mmm, yyyy")
11 Dec, 2013
?Format("13/12/11", "dd mmm, yyyy")
13 Dec, 2011

The Smart Access newsletter is pricey but contains useful articles every month.

wjoc1 · Sep 5, 2003

Pat,

Thanks a million, that's the answer. I had to re-jig the query
slightly to make it work but it does work

Code:

"INSERT INTO PREV_ISSUED ( [Stock Item No], [Start Date],[End 
Date], Place, Team, Person ) SELECT " & getStockItemNo() & ", " & Format("getStartDate()", "dd/mm/yyyy") & ", " & Format("Forms
![UpdatePREV_ISSUED-CustomDialog]!EndDate", "dd/mm/yyyy") & ", " & getPlace() & ", " & getTeam() & ", " & getPerson()

I tried replacing the literals without using the format() function
but I just got type conversion errors

Code:

...", #" & getStartDate() & "#, " &...

replaced with

Code:

...", "" & getStartDate() & "", " &...

The only other slight change I had to make was to refer to the full
name of the form when referencing the EndDate txtbox.

Thanks again,
Liam

Fornatian · Sep 6, 2003

Nice one Pat, well worth remembering

raskew · Sep 15, 2003

Pat-

That's really informative (and totally non-intuitive, think it'd be a miracle if someone just 'stumbled' across this difference). Who woulda thought?

Thanks!

Bob

Pat Hartman · Sep 15, 2003

The occasional gem like this is why I subscribe although with their latest increase (almost double) they have probably increased the price to one which I am not willing to pay.

Here's a link to their website:
Smart Access

The Back issue CD's are still worth buying though.

Short Date Format Getting Mixed Up!

wjoc1

Registered User.

Fornatian

Dim Person

namliam

The Mailman - AWF VIP

Rich

Guest

wjoc1

Registered User.

Fornatian

Dim Person

Rich

Guest

Pat Hartman

Super Moderator

wjoc1

Registered User.

Fornatian

Dim Person

raskew

AWF VIP

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread