Incorrectly updating MS-Access date field

[jrajesh]

Hi,
I'm using MS-Access as my back-end & the app. is in VFP8.
The application's set Date is British.
Current date is "02/09/2004"
When I run the following code:

lnRes=SQLEXEC(Handle,"Insert into LastDone (UpdatedOn) VALUES ("+"#"+DTOC(DATE())+"#)")

In Access, UpdatedOn is populated with "09/02/2004" incorrectly instead of the correct "02/09/2004".
The format for the "Lastdone" field is set to dd/mm/yyyy.

When I retrieve this date from Access for displaying, It shows up as "09/02/2004" :-(

Could someone please guide me on where I'm going wrong and what is to be done to get the updating work properly?

Thanks and Regards,
Rajesh

 

[Mike Lewis]

Rajesh,

This behaviour is what I would expect. Access doesn't know anything about VFP's SET DATE BRITISH. Instead, it has its own format setting for date fields. By default, it recognises the setting used for Short Date in Control Panel, but this can be overriden for specific date fields.

I suggest that you check the formatting of the field within Access. If there isn't any formatting specified, check the Control Panel format. If all else fails, do a SET DATE AMERICAN in VFP; this will cause the DTOC() to format the date in the way that Access is probably expecting it.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[jrajesh]

Mike Lewis,
Thanks for your assistance.

I'm not much into access. How do go about overriding the default setting in the control panel for specific date fields?

As such your other suggestion of setting date to American and updating using SQLEXEC works.

Thanks again and regards,
Rajesh

 

[Mike Lewis]

Rajesh,

As such your other suggestion of setting date to American and updating using SQLEXEC works.

Well, if that works, I'd go with that. It's a better solution than having to make changes to the Access database.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com