date on insert not following regional settings

[Bobnz]

I have field in a table that is date/time shortdate

my regional settings for date (short) are dd/mm/yyyy

when I perform an insert on this field the date gets inserted as mm/dd/yyy

could someone tell me how to correct this.

the date field is used in parameter query the user enters start/end date on a form in dd/mm/yyyy so I need access to store the date in this format.

B

 

[PHV]

A date is a date, ie a numeric field storing a number of days.
How are you inserting the date values ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Bobnz]

when the user clicks a button the current date is placed in a text box on the form this textbox is formatted dd/mm/yy and the date appears this way. Then the date from this textbox is assigned to a global varthis is then used as an argument for a function. Inside the function the date is used in an insert query the field in the table is formatted dd/mm/yyyy

heres query:

strSQL = "INSERT INTO [tblorder]"
    strSQL = strSQL + "(clientname, supplierid, productid, quantity, comment, dateordered, prodname, jobnumber  )"
    strSQL = strSQL + " values ('" & client_name & "'," & supplier_id & ""
    strSQL = strSQL + " ," & product_id & ", " & quantity & ", '" & comment & "', #" & date_ordered & "#,'" & product_name & "', " & job_number & "  )"
    
    DoCmd.RunSQL strSQL

thanks for the reply

B

 

[PHV]

Replace this:
#" & date_ordered & "#
By this:
#" & Format(date_ordered, "yyyy-mm-dd") & "#

Hopefully, date_ordered is Dimmed as Date ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Bobnz]

oh yeah! that did it, how can something so simple be so difficult to find.

curious though, formatting it yyyy-mm-dd
shouldn't this result in (say todays date) 2004/12/07 ??


thanks for that


B

 

[PHV]

Storing and displaying are 2 different things.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244