Handling dates in sql and Access (1 Viewer)

[ppullag1]

Hello Everybody.....

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 having trouble forming the date and inserting the date field into the table. 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 = CInt(mon)
b = CInt(day)
c = CInt(yr)

d = DateSerial(a, b, c)

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 & ") "

While debugging, I can see that a, b, c are getting correct values from the form in the Integer format. But when I assign the d to DateSerial(a,b,c), its not getting the correct date (Its getting some other date). I don't know whats wrog here.

After that, the query works fine but it does not insert the that wrong date value in d, instead it inserts some time value like 12.00.25AM.....


Any help in this regard is appreciated.

Thanks,
Redd

 

[Rich]

Why would you want to store the date as separate components?

 

[lcline]

I did not insert the data into a table but I outputted the strSQL in a message box and it looked okay.

Try
Dim thaDate As Date
Dim strSQL as string
thaDate = [Forms]![Enter Participation Count]![MonthCombo] & "/" _
& [Forms]![Enter Participation Count]![DayCombo] & "/" _
& [Forms]![Enter Participation Count]![YearCombo]


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

 

[ppullag1]

re:Handling dates in SQL

Hi Rich,

I am not storing date separately but I am getting parts of the dates from a form and combining them to form a date and insert the date into the table

Why would you want to store the date as separate components?

 

[Rich]

Sorry I misread it
Why not just use the CalControl

 

[ppullag1]

re:Handling dates in SQL

Hi Lee,

Thanks for your response. I tried your code but its giving an error. While debugging I can see that 'thaDate' is getting the correct date value but the query is not executing, its giving an error ( Syntax error in INSERT INTO..).

Probably, this info might help...The table used here is independent and not related to any tables. The date field is declared as date/time while building the table.

I did not insert the data into a table but I outputted the strSQL in a message box and it looked okay.

Try
Dim thaDate As Date
Dim strSQL as string
thaDate = [Forms]![Enter Participation Count]![MonthCombo] & "/" _
& [Forms]![Enter Participation Count]![DayCombo] & "/" _
& [Forms]![Enter Participation Count]![YearCombo]


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

 

[lcline]

Okay, thaDate is still not properly formated to allow you to use the variable in the sql statement. However, if you create a text box on the form and format it as Short Date you can pass this to the statment and it has worked for me. If you don't want to display the text box, set the Visible property to No, however it can serve as a display of the date that was just entered.

Here is the modified script that I have tested; you will need to change the name of the table, form, and combo boxes.


Dim thaDate As Date

thaDate = Me.cmbMon & "/" & Me.cmbDay & "/" & Me.cmbYr
Me.TextDateHdr = thaDate

If MsgBox("You have seleted " & thaDate & " as the date you want to enter! Continue?", vbOKCancel) = vbOK Then

strSQl = "INSERT INTO Table1(thaMon, thaDay, thaYr, thaDate) VALUES([Forms]![Form1]![cmbMon], [Forms]![Form1]![cmbDay],[Forms]![Form1]![cmbYr], textDateHdr ) "

DoCmd.RunSQL (strSQl)

Else

End If
End Sub

 

[Jon K]

a = CInt(mon)
b = CInt(day)
c = CInt(yr)

d = DateSerial(a, b, c)

But when I assign the d to DateSerial(a,b,c), its not getting the correct date (Its getting some other date). I don't know whats wrog here.

The syntax of DateSerial is

DateSerial(year, month, day)

.

 

[ppullag1]

re:Handling dates in SQL

Thanks a lot!!! It works great....Appreciate all your help.

Okay, thaDate is still not properly formated to allow you to use the variable in the sql statement. However, if you create a text box on the form and format it as Short Date you can pass this to the statment and it has worked for me. If you don't want to display the text box, set the Visible property to No, however it can serve as a display of the date that was just entered.

Here is the modified script that I have tested; you will need to change the name of the table, form, and combo boxes.


Dim thaDate As Date

thaDate = Me.cmbMon & "/" & Me.cmbDay & "/" & Me.cmbYr
Me.TextDateHdr = thaDate

If MsgBox("You have seleted " & thaDate & " as the date you want to enter! Continue?", vbOKCancel) = vbOK Then

strSQl = "INSERT INTO Table1(thaMon, thaDay, thaYr, thaDate) VALUES([Forms]![Form1]![cmbMon], [Forms]![Form1]![cmbDay],[Forms]![Form1]![cmbYr], textDateHdr ) "

DoCmd.RunSQL (strSQl)

Else

End If
End Sub