Short Date Format Getting Mixed Up!

[wjoc1]

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]

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]

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]

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]

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]

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]

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

 

[wjoc1]

Pat,

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

"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

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

replaced with

...", "" & 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]

Nice one Pat, well worth remembering

 

[raskew]

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