Insert into sql DATE Nightmare 1

[Ollie71]

I have a date issue with a table that recieves data from a form via insert into sql statements.

I have a form that selects a customer, gives a code and then inputs the details into two tables.

The problem table "LabDespatch" has these fields "[TransID], [DespatchDate], [DateCreated], [CurrentDespatch]" and a linked subtable, linked on TransID that stores the details of the customers that will/have despatched on a particular date.

The current people being despatched to the lab are entered under the [CurrentDespatch] number (automatically via a form button). When the time comes to despatch the current people to the lab, a form button is pushed and the despatched date is updated in the master table and the linked details table.

Then the [CurrentDespatch] for that group is set to false and new group is created under the next group number. At this time the current date is saved to the field [DateCreated] and its [CurrentDespatch] is set to true.

This is done with the following code:
DespatchSql = " INSERT INTO LabDespatch ([TransId], [DateCreated], [CurrentDespatch]) " _
& " VALUES (" & DMax("TransId", "LabDespatch") + 1 & ", #" & CurrentDate & "#, " & True & ") "

CurrentDate is a date variable that is set to the following:
CurrentDate = Format(Date, "dd/mm/yyyy")

The problem is that the date that appears in the table is the wrong way around in the mm/dd/yyyy (like americanized, I am in Australia and want dd/mm/yyyy).

the current date 09/06/2009 appears as 06/09/2009 in the table. Yet the table is set to date/time (format is dd/mm/yyyy), as you can see (above) I format a variable to dd/mm/yyyy to insert in case the computers short date is set wrong.

I have used the # for date literal in the insert into sql statement and have verifed (by adding a watch on the variable and a stop)that the variable does indeed get the correct format 09/06/2009.

As far as I can see I have everything set to the format dd/mm/yyyy in both the forms vb code and the table (and verified that the variable does indeed contain it correctly as 09/06/2009 before its written to the table), yet every stinking time I despatch a group and new master group is created in the table, the [DateCreated] field is always saved wrong i.e. 06/09/2009 (being the 6th of sept 09)

The table is formated right as I can manually input the date correctly, but when its done via code with the insert sql statement its wrong.

The following is an excel export showing how they have been saved, 27-34 have been manually changed in the table the last, 35 shows the sql save and wrongful format:

[TransId][DespatchDate][Datecreated] [CurrentDespatch]
27 21/01/2009 29/05/2009 FALSE
28 28/01/2009 03/06/2009 FALSE
29 04/02/2009 03/06/2009 FALSE
30 11/02/2009 09/06/2009 FALSE
31 18/02/2009 09/06/2009 FALSE
32 25/02/2009 09/06/2009 FALSE
33 04/03/2009 09/06/2009 FALSE
34 23/03/2009 09/06/2009 FALSE
35 06/09/2009 TRUE


Any ideas? as I cant see why or even how this is happening with all the settings as they are, stumped.

 

[PHV]

& " VALUES (" & DMax("TransId", "LabDespatch") + 1 & ", #" & Format(Date,"yyyy-mm-dd") & "#, True) "

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ollie71]

Thanks PHV that worked a treat, though its strange it has to be formatted ass about like that to save properly in the table, some sort of translation problem with date fields?

wierd but working now, thanks again, that one had me frustrated all afternoon, access peculiarities continue to confound me at times.

But at least im always learning!

 

[huggyboy]

I have had some problems like this myself and the safest way seems to be to use Format(Date, "dd/mmm/yyyy") (in your case formats to 09/Jun/2009) to be implicit about the date

 

[PHV]

the safest way
In fact I suggested the standard way, ie the ISO way.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeAtWork]

I format a variable to dd/mm/yyyy to insert in case the computers short date is set wrong.

Stop thinking of dates as being in a certain "format". A date is stored in the database as a number. The short date setting of the computer is how the computer interprets an ambigious date, and you shouldn't try to second guess it. The best strategy is to assume the user has entered the date correctly. Changing the format around will not help you if the user is assuming mm/dd/yyyy and you are assuming it should be dd/mm/yyyy.

The only way to be sure the user has entered the date he actually wants is to give him controls that do not assume one format or another. Date Pickers are the best, but you could also use dropdowns for day, month, year.