ACCESS FEED: RE: [MS_AccessPros] Help with string in query

Sabtu, 17 Agustus 2013

RE: [MS_AccessPros] Help with string in query

Rod-

You need quotes around the literal:

tmpNotes = "Payment of $'" & Tbox_ActualPayment & "' logged by " &
tmpUser & " on #" & tmpDate & "#"
dbs.Execute "UPDATE tblPayments " _
& " SET [strPaymentNotes] = """ & tmpNotes & """ " _
& " WHERE (([lngPaymentPlanNum] = " & Tbox_PaymentPlanNum & ") AND
([strDateScheduled] = #" & Tbox_DateScheduled & "#))"

There's really no need to put quotes around the payment amount or # around
the date.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of rodbevill
Sent: Saturday, August 17, 2013 5:18 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Help with string in query

I am sure this is simple but I just can't see the issue. The following is a
section of code to add auto generated notes to a table with a record is
added or updated.

tmpDate = Date
tmpNotes = "Payment of $'" & Tbox_ActualPayment & "' logged by " &
tmpUser & " on #" & tmpDate & "#"
dbs.Execute "UPDATE tblPayments " _
& " SET [strPaymentNotes] = " & tmpNotes & " " _
& " WHERE (([lngPaymentPlanNum] = " & Tbox_PaymentPlanNum & ") AND
([strDateScheduled] = #" & Tbox_DateScheduled & "#))"

the problem lies with the tmpNotes string. When the Update query is
activated an error message stating syntac error (missing operator) in query
expression. The quoted expression is

"Payment of $'1645' logged by Robert on #8/16/2013#"

Hope that makes sense.
Rod

------------------------------------

Yahoo! Groups Links