1

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..

Improve this question
5
  • Welcome to stackoverflow. Please, check these links: stackoverflow.com/help/mcve and stackoverflow.com/help/how-to-ask. Dont ask twe different questions in the same post. It is hard to reply following the structure of the stackoverflow. Edit your question for asking one of them, and ask the other one in a different post. Commented May 1, 2017 at 9:27
  • @GileadKenzo checked the links, thanks. And also I won't be asking two questions again. Sorry for the mixed up structure! Commented May 1, 2017 at 11:50
  • my pleassure :). I recommend you edit this question for asking just one thing. And move on another question your second problem... :) Cheers mate. Commented May 1, 2017 at 12:29
  • @GileadKenzo yess did it bro.. Commented May 1, 2017 at 12:54
  • You are Welcome... :) Commented May 9, 2017 at 11:54

1 Answer 1

Reset to default
1

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) & ");"
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Hi @A.S.H thanks for your reply. But it still won't work. Still get the syntax error.
@RickyBid Do you really have a field in the table called date?? If that's the case, urgently rename it. And what is the name of your table??
@A.S.H yess DID have a name called date. renamed it. I don't have a table. It is just an excel sheet named "database "
If it's an Excel sheet try inserting the dat as a normal number. vbCrLf & "VALUES (" & CLng(firstDate) & _ ...
Figured it out, needed [ ] for my start date field. it works now. Thanks guys! so: INSERT INTO [Database$] ([start date],allocation,hours,reason,team) VALUES (" & "#" & Format(Firstdate, "dd/mm/yyyy") & "#,"
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.