Date Format

[hedidit]

Hi I have a form that a user enters various bits of data onto. , the data is then inserted into a table called 'TblInventory'. The code works fine accept that the field called D8Rec changes format from dd/mm/yyyy to mm/dd/yyyy once it's inserted into the table.

The code I'm using for the insert is:

CurrentDb.Execute "INSERT INTO TblInventory (Location, IDNum, OrderID, Qty, DateRec) SELECT " & [Forms]![Inventory_Receive]![Location] & " AS Expr7, '" & [Forms]![Inventory_Receive]![D8Rec] & "-1' AS Expr2, " & [Forms]![Inventory_Receive]![idi] & " AS Expr3, 1 AS Expr4, #" & [Forms]![Inventory_Receive]![D8Rec] & "# AS Expr1"

I've tried using:

format([Forms]![Inventory_Receive]![D8Rec],"dd/mm/yyyy")

To correct the format but it doesn't work.

Can anyone point out what I'm doing wrong?

Thanks

 

[RuralGuy]

Date are *always stored in the same manner regardless of language. What you are describing is simply a display option. Here's a couple of links to help make it clearer.
Storing, Calculating, and Comparing Date/Time Data

http://support.microsoft.com/default.aspx/kb/q130514/

Return Dates in US #mm/dd/yyyy# format

http://www.mvps.org/access/datetime/date0005.htm

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[PHV]

Use a non-ambiguous format:
Format(Forms![Inventory_Receive]!D8Rec, "yyyy-mm-dd")

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

 

[hedidit]

Thanks guys got it working after all using 'format'

Thanks again for the help...