Syntax Error in Query Expression

[jcruzAME]

Here's the code I have:

Dim FullName As String
Dim Initials As String
Dim UserName As String
Dim Email As String
Dim ContactFileAs As String
Dim SplitName() As String
Dim InsertAdvisor As String
Dim FirstName As String
Dim LastName As String
Dim db As DAO.Database
Set db = CurrentDb()


FullName = txtFullName.Value
SplitName() = Split(FullName)
FirstName = SplitName(0)
LastName = SplitName(1)

ContactFileAs = SplitName(1) & ", " & SplitName(0)

InsertAdvisor = "INSERT INTO dbo_tblContact(FullName, ContactFileAs, FirstName, LastName, IsEmployee) VALUES(" & FullName & ", " & ContactFileAs & ", " & FirstName & ", " & LastName & ", -1)"

db.Execute (InsertAdvisor)


The text it's pulling from the txtFullName will be something like "John Smith". When it gives me this error, it will say Syntax error in query expression 'John Smith'.

Can anyone help me out here?

 

[vbaInet]

Just wondering why you're not using a bound form?

Plus your Split() will fail for names like:

Michael Peter Fox

 

[jzwp22]

Text data must be enclosed by single quotes, so your code should look like this


InsertAdvisor = "INSERT INTO dbo_tblContact(FullName, ContactFileAs, FirstName, LastName, IsEmployee) VALUES('" & FullName & "', '" & ContactFileAs & "', '" & FirstName & "', '" & LastName & "', -1)"

 

[pbaldy]

Text values need a delimiter. At it's simplest:

"...VALUES('" & FullName & "', "...

A more complete discussion here:

http://www.baldyweb.com/BuildSQL.htm

 

[pbaldy]

Geez, how slow am I?

 

[vbaInet]

A bit slow on the typing there Paul

 

[pbaldy]

I haven't had breakfast yet!!

 

[jcruzAME]

Thanks for the replies everyone.

 

[jzwp22]

You're welcome. Please let us know if the code runs as expected.

 

[vbaInet]

I haven't had breakfast yet!!

Kellogs Frosties will sort you out. They're grrrrrreat!

 

[jcruzAME]

Thanks again everyone. That worked. I did what pbaldy pointed out.