Error running SQL in Access VBA

I am having two issues running an SQL script in VBA.  The first is the date that I am passing is showing up in my table incorrectly.  I think this is because I am passing it as a string in the SQL statement.  How can I tell SQL it is a date?  Do I use a # somewhere in the string.

The second is the very last variable I am passing "UniformNotes" and " & strUniformNotes & ". This is the only field that generates an error.  If I remove this, all of the other values insert to the table.  Here is the code:

Function WriteUniformChargeToTransactionLog()

Dim db As DAO.Database
Dim sSql As String

Dim intUniformID As Long
Dim intMemberID As Long
Dim strUniformYear As String
Dim strUniformDate As Date
Dim strUniformSize As String
Dim strUniformType As String
Dim strUniformNote As String
Dim strUniformQty As Integer
Dim strUniformPayTrigger As String


intUniformID = [Form_Uniform SubForm_Add].txtUniformID_Trans
intMemberID = [Form_Uniform SubForm_Add].txtMemberID_Uniform
strUniformYear = [Form_Uniform SubForm_Add].txtUniformYear
strUniformDate = [Form_Uniform SubForm_Add].txtUniformDate
strUniformSize = [Form_Uniform SubForm_Add].txtUniformSize
strUniformType = [Form_Uniform SubForm_Add].txtUniformType
strUniformNote = [Form_Uniform SubForm_Add].txtUniformNote
strUniformQty = [Form_Uniform SubForm_Add].txtUniformApparelQty
strUniformPayTrigger = [Form_Uniform SubForm_Add].UniformOrderPartOfMemberFee




Set db = CurrentDb()


sSql = "INSERT INTO tblUniformOrderStaging2Payment (MemberID, UniformID, UniformYear, UniformDate, UniformApparelType, UniformApparelQty, UniformOrderPartOfMemberFee, UniformNotes) VALUES (" & intMemberID & " , " & intUniformID & ", " & strUniformYear & ", " & strUniformDate & " , " & strUniformType & ", " & strUniformQty & ", " & strUniformPayTrigger & " , " & strUniformNote & ")"


db.Execute sSql, dbFailOnError


End Function

Open in new window

marku24Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
Try this:


sSql = "INSERT INTO tblUniformOrderStaging2Payment (MemberID, UniformID, UniformYear, UniformDate, UniformApparelType, UniformApparelQty, UniformOrderPartOfMemberFee, UniformNotes) VALUES (" & intMemberID & " , " & intUniformID & ", " & strUniformYear & ", #" & strUniformDate & "# , " & strUniformType & ", " & strUniformQty & ", " & strUniformPayTrigger & " , '" & strUniformNote & "')"

Open in new window



You're right about # for dates, and text/strings need to be delimited with quotes.
0
Rey Obrero (Capricorn1)Commented:
try this revision

sSql = "INSERT INTO tblUniformOrderStaging2Payment (MemberID, UniformID, UniformYear, UniformDate, UniformApparelType, UniformApparelQty, UniformOrderPartOfMemberFee, UniformNotes) VALUES (" & intMemberID & " , " & intUniformID & ", " & strUniformYear & ", #" & strUniformDate & "# , " & strUniformType & ", " & strUniformQty & ", " & strUniformPayTrigger & " , " & strUniformNote & ")"


for Text Data type use, as an example

" & strUniformNote & "  should be  '" & strUniformNote & "'
0
Gustav BrockCIOCommented:
As noted, apply the correct quotation in the SQL, including ## for dates.

You also need these revisions:

Dim datUniformDate As Date
Dim strUniformDate As String
...
datUniformDate = [Form_Uniform SubForm_Add].txtUniformDate
strUniformDate = Format(datUniformDate, "yyyy\/mm\/dd")

If not, your expression will fail if the user's Windows settings are non-US.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slubekCommented:
You should also apply user input validation (",", "'", """" etc.) to prevent SQL Injection.
0
marku24Author Commented:
Thank you all - very, very helpful
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.