I hope this is the right place to post this question. It deals more with MS Access than with Perl, but the code is written in Perl, so I thought I’d try to get the answer here first.

I’m working on a 401K investment tracking program written in Perl using an ODBC connection to a MS Access database. I’m having trouble inserting records that include dates. The actual code is at the end of this post, the date has been hard coded in the INSERT statement for testing purposes. The sub selects a record from the table with the requested date and if found UPDATEs the record, if not found INSERTs the record. This logic is working fine with the date format as shown in the SELECT statement below:

Finally found a reference that talked about field names in MS Access. Since my field is called Date, for some reason it works fine in the select statement and the update statement, but the insert statement requires the field name to be bracketed in [] braces.

I do not know for Access, but in most database engines I have used, you need to convert date strings into actual internal date formats before storing data into a database (for example a "to_date()" function). But it depends on the format of your data column and there may be shortcuts that I am not aware of in Access.

I am familiar with the date string issues in database engines. As I was doing the research on this problem I found that this or a similar problem was posted on many boards and I followed all of the different suggestions, hence the list of various formats that I used, and what I posted was only a partial list that I remembered!

As I see it now, there seems to be a glitch in the ODBC SQL preparation to the MS Access database. The proper format for a date is, or at least includes, #21-Jun-2014#, but the problem was that I called the field a reserved name, “Date”, and that field name does not cause a problem in a SELECT or UPDATE $dbh->prepare($SQL) statement, but does cause the given errors when “Date” is used in an INSERT prepare statement.

I’m updating this post here so that others might find it and realize the in some cases there are reserved words that act differently in different situations.

Be it in Perl or in a database or more generally, it is a relatively bad idea to call a variable or column indentifier "date", because it does not say enough about the content. I would sugest that you use name such as start_date, end_date, termination_date, next_bill_date, last_invoice_date, birth_date, action_date, match_date, etc. Not only is this much clearer, but the chances of accidental collision with a key or reserved word are considerably smaller.