date format in an INSERT query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to put a date, in the form of dd/mm/yy into a date field in a
table using an array and the DoCmd.RunSQL command. However, when the query
runs, the form of the date written is something like "00.00.50". I tried
converting the Date Format to ShortDate in the Table Design View, but this
justs ends up making each date 30/12/1899. The code is something like this:

Public DateToWrite(1 to 4) as date

....

then, in the Public SUB...

Dim strQuery As String

strQuery = "UPDATE [Loan Laptops] "
strQuery = strQuery + "SET DateOut = " & DateToWrite(1)
strQuery = strQuery + " WHERE Loan_id = 87;"
DoCmd.RunSQL (strQuery)
 
The date format must be in mm/dd/yyyy format (or some unambiguous format
such as yyyy-mm-dd or dd mmm yyyy), regardless of what your Regional
Settings are, and the date must be delimited with #.

Try

strQuery = "UPDATE [Loan Laptops] "
strQuery = strQuery + "SET DateOut = " & Format(DateToWrite(1),
"\#mm\/dd\/yyyy\#")
strQuery = strQuery + " WHERE Loan_id = 87;"

if it's a date only. See http://www.mvps.org/access/datetime/date0005.htm at
"The Access Web" for formats to use in other cases such as time only and
date and time.
 
Thanks, Douglas, that works fine.

Douglas J. Steele said:
The date format must be in mm/dd/yyyy format (or some unambiguous format
such as yyyy-mm-dd or dd mmm yyyy), regardless of what your Regional
Settings are, and the date must be delimited with #.

Try

strQuery = "UPDATE [Loan Laptops] "
strQuery = strQuery + "SET DateOut = " & Format(DateToWrite(1),
"\#mm\/dd\/yyyy\#")
strQuery = strQuery + " WHERE Loan_id = 87;"

if it's a date only. See http://www.mvps.org/access/datetime/date0005.htm at
"The Access Web" for formats to use in other cases such as time only and
date and time.


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Italian Pete said:
I'm trying to put a date, in the form of dd/mm/yy into a date field in a
table using an array and the DoCmd.RunSQL command. However, when the query
runs, the form of the date written is something like "00.00.50". I tried
converting the Date Format to ShortDate in the Table Design View, but this
justs ends up making each date 30/12/1899. The code is something like this:

Public DateToWrite(1 to 4) as date

...

then, in the Public SUB...

Dim strQuery As String

strQuery = "UPDATE [Loan Laptops] "
strQuery = strQuery + "SET DateOut = " & DateToWrite(1)
strQuery = strQuery + " WHERE Loan_id = 87;"
DoCmd.RunSQL (strQuery)
Click to expand...
Click to expand...
 
Hi Douglas

I am having the same issue... I tried your version of code (changing the
mm/dd for dd/mm) and I get the INSERT SYNTAX is wrong.

here is mu code
SQL = "insert into tblTrans (AccNo, Date, Ref, Amount, Bal, CatID, Income)"

SQL = SQL & "values ('" & AccNo & "', " &
Format(TDate, "\#dd\/mm\/yyyy\#") & ", '" & Ref & "', " & Amount & ", "

SQL = SQL & Bal & ", " & CatID & ", " & Income & ")"

DoCmd.RunSQL SQL

if you could perhaps provide some insight, I would be most grateful

Thanks

Douglas J. Steele said:
The date format must be in mm/dd/yyyy format (or some unambiguous format
such as yyyy-mm-dd or dd mmm yyyy), regardless of what your Regional
Settings are, and the date must be delimited with #.

Try

strQuery = "UPDATE [Loan Laptops] "
strQuery = strQuery + "SET DateOut = " & Format(DateToWrite(1),
"\#mm\/dd\/yyyy\#")
strQuery = strQuery + " WHERE Loan_id = 87;"

if it's a date only. See http://www.mvps.org/access/datetime/date0005.htm at
"The Access Web" for formats to use in other cases such as time only and
date and time.


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Italian Pete said:
I'm trying to put a date, in the form of dd/mm/yy into a date field in a
table using an array and the DoCmd.RunSQL command. However, when the query
runs, the form of the date written is something like "00.00.50". I tried
converting the Date Format to ShortDate in the Table Design View, but this
justs ends up making each date 30/12/1899. The code is something like this:

Public DateToWrite(1 to 4) as date

...

then, in the Public SUB...

Dim strQuery As String

strQuery = "UPDATE [Loan Laptops] "
strQuery = strQuery + "SET DateOut = " & DateToWrite(1)
strQuery = strQuery + " WHERE Loan_id = 87;"
DoCmd.RunSQL (strQuery)
Click to expand...
Click to expand...
 
Reread what I said. "The date format must be in mm/dd/yyyy format (or some
unambiguous format such as yyyy-mm-dd or dd mmm yyyy), regardless of what
your Regional Settings are, and the date must be delimited with #."
dd/mm/yyyy is NOT an unambiguous format. (I agree that mm/dd/yyyy isn't
unambiguous either, but Access was developed in the US...)

Based on your quotes, AccNo and Ref are text fields, and Amount, Bal, CatID
and Income are numeric. Is that correct?

You've got a field named Date. Date is a reserved word and should never be
used for your own purposes. For a list of names to avoid (as well as a link
to a utility that will check your application for compliance), check what
Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename, the field, you must put square brackets
around the field name: [Date]

If you stil can't get it to work, add a line of code

Debug.Print SQL

immediate before the line

DoCmd.RunSQL SQL

After your code runs (and fails), go to the Immediate window (Ctrl-G) and
check what was printed there.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Skeniver said:
Hi Douglas

I am having the same issue... I tried your version of code (changing the
mm/dd for dd/mm) and I get the INSERT SYNTAX is wrong.

here is mu code
SQL = "insert into tblTrans (AccNo, Date, Ref, Amount, Bal, CatID,
Income)"

SQL = SQL & "values ('" & AccNo & "', " &
Format(TDate, "\#dd\/mm\/yyyy\#") & ", '" & Ref & "', " & Amount & ", "

SQL = SQL & Bal & ", " & CatID & ", " & Income & ")"

DoCmd.RunSQL SQL

if you could perhaps provide some insight, I would be most grateful

Thanks

Douglas J. Steele said:
The date format must be in mm/dd/yyyy format (or some unambiguous format
such as yyyy-mm-dd or dd mmm yyyy), regardless of what your Regional
Settings are, and the date must be delimited with #.

Try

strQuery = "UPDATE [Loan Laptops] "
strQuery = strQuery + "SET DateOut = " & Format(DateToWrite(1),
"\#mm\/dd\/yyyy\#")
strQuery = strQuery + " WHERE Loan_id = 87;"

if it's a date only. See http://www.mvps.org/access/datetime/date0005.htm
at
"The Access Web" for formats to use in other cases such as time only and
date and time.


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Italian Pete said:
I'm trying to put a date, in the form of dd/mm/yy into a date field in
a
table using an array and the DoCmd.RunSQL command. However, when the
query
runs, the form of the date written is something like "00.00.50". I
tried
converting the Date Format to ShortDate in the Table Design View, but this
justs ends up making each date 30/12/1899. The code is something like this:

Public DateToWrite(1 to 4) as date

...

then, in the Public SUB...

Dim strQuery As String

strQuery = "UPDATE [Loan Laptops] "
strQuery = strQuery + "SET DateOut = " & DateToWrite(1)
strQuery = strQuery + " WHERE Loan_id = 87;"
DoCmd.RunSQL (strQuery)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top