Handling date variables in SQL statements

[ppullag1]

Hi there.....


I am trying to get parts of the date from combo boxes in a form in Access, combine them to form a date and insert it into a table. I am able to extract the parts and form the date but I am having trouble inserting the date field into the table due to some datatype conversion issue. The following is the code I am using:

Dim strSQL As String
Dim d As Date
Dim a, b, c, e As String
Dim day, mon, yr As Integer
day = [Forms]![Enter Participation Count]![DayCombo]
mon = [Forms]![Enter Participation Count]![MonthCombo]
yr = [Forms]![Enter Participation Count]![YearCombo]
a = CStr(mon)
b = CStr(day)
c = CStr(yr)
e = a & "/" & b & "/" & c

d = CDate(e)

strSQL = "INSERT INTO transfercount(Area, Program, Count, Date) VALUES([Forms]![Enter Participation Count]![Area], [Forms]![Enter Participation Count]![Program],[Forms]![Enter Participation Count]![Count], '$[d]')"

DoCmd.RunSQL (strSQL)

The query works but it does not insert the date field into the table, it gives an erroe saying the due to some datatype conversion issue, it cannot insert the field.

While creating the date field in the table, I declared it as date/time datatype.

Any help in this regard is appreciated.

Thank You,

Redd

 

[SkipVought]

Hi,

A DATE in MS is a integral number. Year, Month Day must be converted to a DateSerial Value

day = [Forms]![Enter Participation Count]![DayCombo]
mon = [Forms]![Enter Participation Count]![MonthCombo]
yr = [Forms]![Enter Participation Count]![YearCombo]

d = DateSerial(CInt(yr), cInt(mon), CInt(day))

strSQL = "INSERT INTO transfercount(Area, Program, Count, Date) VALUES([Forms]![Enter Participation Count]![Area], [Forms]![Enter Participation Count]![Program],[Forms]![Enter Participation Count]![Count]," & d & ")"

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}