Problem in building dynamic query

[Jack]

Hi,
I am building a dynamic sql string in access
sql = " INSERT INTO tblExpense (ENO, EntryDate, ContractedServiceExpense, "
sql = sql & "TravelExpense, PersonnelExpense)"
sql = sql & " VALUES('" & l_ENO & "', '" & l_Date & "', " &
l_contractedserviceexpense & ", " & l_travelexpense & ", " &
l_personnelexpense & ")"
However, the date field is not working properly due to error. Any help is
appreciated. Thanks in advance. Regards.

 

[Douglas J. Steele]

Dates need to be delimited with # characters and the date must either be in
mm/dd/yyyy format (regardless of what your regional settings are) or an
unambiguous format such as yyyy-mm-dd or dd mmm yyyy.

My normal approach combines these in a single Format statement:

sql = " INSERT INTO tblExpense (ENO, EntryDate, ContractedServiceExpense, "
sql = sql & "TravelExpense, PersonnelExpense)"
sql = sql & " VALUES('" & l_ENO & "', " & Format(l_Date,
"\#mm\/dd\/yyyy\#") & ", " &
l_contractedserviceexpense & ", " & l_travelexpense & ", " &
l_personnelexpense & ")"