Date Column in Access

  • Thread starter Thread starter JoeBurmeister
  • Start date Start date
J

JoeBurmeister

Some programmer before me created an ACCESS database with a column
called 'Date' (original huh?) to insert date in 'short date' format.

I wrote a VB code to insert data into this database but my INSERT fails
(states Syntax Error in INSERT INTO) due to the fact that I am trying
to address a column call 'Date'. If I change this column name in ACCESS
to 'Datexxx' - the VB INSERT statement works fine.

sqlText = "INSERT INTO detail_log (date,name,map_ref,
account_number,cutoff, cycle,signal_codes) VALUES ( '" & Now & "','" &
COName & "','" & COLoc & "','" & MbrSep & "','" & CONbr & "','" & cycle
& "','" & 5 & "')"

How can I get around this problem without resorting to changing the
ACCESS database column name to something other then 'date' (the
database is heavily referenced by other programs so changing the
structure is out of the question).
 
Some programmer before me created an ACCESS database with a column
called 'Date' (original huh?) to insert date in 'short date' format.

I wrote a VB code to insert data into this database but my INSERT
fails (states Syntax Error in INSERT INTO) due to the fact that I am
trying to address a column call 'Date'. If I change this column name
in ACCESS to 'Datexxx' - the VB INSERT statement works fine.

sqlText = "INSERT INTO detail_log (date,name,map_ref,
account_number,cutoff, cycle,signal_codes) VALUES ( '" & Now & "','" &
COName & "','" & COLoc & "','" & MbrSep & "','" & CONbr & "','" &
cycle & "','" & 5 & "')"

How can I get around this problem without resorting to changing the
ACCESS database column name to something other then 'date' (the
database is heavily referenced by other programs so changing the
structure is out of the question).
Click to expand...

Surrounding an "illegal" field name with square brackets will usually solve
such problems [Date].
 
Enclose the field name in brackets to show Access it's a field name:

sqlText = "INSERT INTO detail_log ([date]...

Jerry
 
I wrote a VB code to insert data into this database but my INSERT fails
(states Syntax Error in INSERT INTO) due to the fact that I am trying
to address a column call 'Date'. If I change this column name in ACCESS
to 'Datexxx' - the VB INSERT statement works fine.
Click to expand...

I'd suggest - as noted elsethread - bracketing the illegal fieldnames
(Name is another), but perhaps also avoiding a data translation by
delimiting the date value with # rather than quotemarks:

sqlText = "INSERT INTO detail_log ([date],[name],map_ref,
account_number,cutoff, cycle,signal_codes) VALUES ( #" & Format(Now,
"mm/dd/yyyy") & "#,'" & COName & "','" & COLoc & "','" & MbrSep &
"','" & CONbr & "','" & cycle & "','" & 5 & "')"

If COName might contain an apostrophe, you'll want to delimit it with
" rather than ' as well.

John W. Vinson[MVP]
 
Awsome!

Thanks for everyone's help!

Joe



John said:
I wrote a VB code to insert data into this database but my INSERT fails
(states Syntax Error in INSERT INTO) due to the fact that I am trying
to address a column call 'Date'. If I change this column name in ACCESS
to 'Datexxx' - the VB INSERT statement works fine.
Click to expand...

I'd suggest - as noted elsethread - bracketing the illegal fieldnames
(Name is another), but perhaps also avoiding a data translation by
delimiting the date value with # rather than quotemarks:

sqlText = "INSERT INTO detail_log ([date],[name],map_ref,
account_number,cutoff, cycle,signal_codes) VALUES ( #" & Format(Now,
"mm/dd/yyyy") & "#,'" & COName & "','" & COLoc & "','" & MbrSep &
"','" & CONbr & "','" & cycle & "','" & 5 & "')"

If COName might contain an apostrophe, you'll want to delimit it with
" rather than ' as well.

John W. Vinson[MVP]
Click to expand...
 
"Date" is a reserved word in Access, and, thus, not at all a good choice for
a Field name.

Larry Linson
Microsoft Access MVP
 
Back
Top