Date Column in Access

[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).

 

[Rick Brandt]

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).

Surrounding an "illegal" field name with square brackets will usually solve
such problems [Date].

 

[Jerry Porter]

Enclose the field name in brackets to show Access it's a field name:

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

Jerry

 

[John Vinson]

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.

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]

 

[JoeBurmeister]

Awsome!

Thanks for everyone's help!

Joe

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.

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]

 

[Larry Linson]

"Date" is a reserved word in Access, and, thus, not at all a good choice for
a Field name.

Larry Linson
Microsoft Access MVP