Date format swapping around from form into back end. 3

[Egglar]

Hi,

I have a simple input form with a date field. I use an SQ INSERT statment in VB code, which takes the information from the form, and saves it to a table. The database is split into front end / back end.

When a user inputs a date in UK format (im in the UK), eg 01/05/03 (dd/mm/yy), once its saved to the table it reverts back to American format (mm/dd/yy). This problem doesn’t happen if the day is over 12 days, obviously because it cant save any thing more than that because there are only 12 months in a year.

I’ve set the format in the properties of both the field in the back end table, and the text box on the form to dd/mm/yy, this has not worked. I have also tried a txtDate.Format = "dd/mm/yy" just before the insert SQL statement in the VB code.

The only reason I can think for this to be happening is because the back end, and the front end originally was created by someone in America and emailed over. Although he did not create the particular table that this information is being saved too.

One other thing that may work would be a format function in the INSERT SQL statement, if this would work, can I have some example code of formatting a date in an SQL statement.

Any help greatly appreciated.

Many thanks, Elliot.

 

[danvlas]

The problem is not how the date is displayed, but how it is interpreted.
If you enter 10-09-2003 (10-Sep-2003) and it is stored correctly, you should not care how it is displayed in the back-end.

But if it is stored as 09-Oct-2003 (as I suspect), then you have a problem in code.

When using dates in VBA, the format is supposed to be in American format (mm/dd/yyyy).
FieldName = Me.DateBoxName may incorrectly evaluate the date, especially for European format.

I always use:
FieldName = Format(Me.DateBoxName, "dd-mmm-yyyy&quot

It never fails because the month is transferred as Sep and the field knows what to do.

Check how the dates are stored by opening the table and sorting it by that field. If necessary, make the correction.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Egglar]

Thanks mate that worked a treat!

Cheers, Elliot.