CODEWITHSUNDEEP
sqlexcelvba
Ricky Bid
Ricky Bid

Reputation: 13

SQL Date issue in string

I have a question. I am pretty new to sql. bit more experienced in VBA (excel). So I have created a userform with textboxes (with dates) and I need to transfer these to my database. So I am using an instert into statement. it works for all other values, execept when I want to add the date textbox into my string. I get an syntax error. Hope someone can help. 

strQuery = "INSERT INTO[Database$] (date,allocation,hours,reason,team)" & _
vbCrLf & "VALUES (" & "#" & Chr$(39) & Firstdate & Chr$(39) & "#" & "," & 
Chr$(39) & Name_Abs & Chr$(39) & "," & Chr$(39) & Hours_t & Chr$(39) & "," & 
Chr$(39) & Reason & Chr$(39) & "," & Chr$(39) & Team & Chr$(39) & ");"

If someone can help me that would be great..

Upvotes: 1

Views: 76

Answers (1)

A.S.H
A.S.H

Reputation: 29332

As in the comments, You asked too many (unrelated) questions. I will answer only your "main" question.

  • Enclose your dates with # but without the additional chr(39).

  • Correct your syntax errors; there are missing _ to concatenate you statements.

Applying this, and assuming all else is fine, you query should be:

strQuery = "INSERT INTO [Database$] (date,allocation,hours,reason,team)" & _
vbCrLf & "VALUES (" & "#" & firstDate & "#," & _
Chr$(39) & Name_Abs & Chr$(39) & "," & Chr$(39) & Hours_t & Chr$(39) & "," & _
Chr$(39) & Reason & Chr$(39) & "," & Chr$(39) & Team & Chr$(39) & ");"

EDIT

As it appeared, where some field names have spaces in them (i.e "start date" instead of "date"), enclose them with "[]". It is good practice to apply this systematically to all fields.

strQuery = "INSERT INTO [Database$] ([start date], [allocation], [hours],[reason], [team])" & _
vbCrLf & "VALUES (" & "#" & firstDate & "#," & _
Chr$(39) & Name_Abs & Chr$(39) & "," & Chr$(39) & Hours_t & Chr$(39) & "," & _
Chr$(39) & Reason & Chr$(39) & "," & Chr$(39) & Team & Chr$(39) & ");"

Upvotes: 1

Related Questions