SQL and DateAdd

[WillM]

Hello everyone,
I am trying to add 30 or 45 days to a certain date and insert that date in a table.

The field is declared as datetime on the SQLServer and has been tried with all date formats, and none, on the access side.

The problem I am encountering is that the date is being inserted as 12:00:00 in the table, no date is being stored.

When I print out the SQL statement to the immediate window, the date in question shows the correct date.

Dim a As Date
Dim b As Date

 a = DateAdd("d", 45, Me.DateOfExit) 
 b = DateAdd("d", 30, Me.DateOfExit) 


  strSQL = "INSERT INTO MyTable ( No, Name,DueDate, OriginatedBy, ID, Status) " & vbCrLf & _
            "VALUES (" & Me.No & ",'" & Me.Name & "', " & b & ", '" & Me.Facilitator & "', " & Me.ID & ", '" & "Required" & "');"

I feel like I am missing something simple, but I cannot put my finger on it.

I have tried adding the dateadd code to the SQL string, but that produced the same result.

Any help is appreciated!
Will

 

[WillM]

Fixed it!

"', [COLOR=Red]#[/COLOR]" & b & "[COLOR=Red]#[/COLOR], '"

By adding the pounds/hashtag in the code string it forced a date recognition.

 

[pbaldy]

For starters, you need to wrap the date values in #, just as you wrapped the text values with '.

 

[WillM]

I must have been reading your mind Paul, thanks!!!

 

[pbaldy]

That's a scary place, I'm not sure you want to do that!

Glad you found the answer.