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

Sabtu, 17 Agustus 2013

Re: [MS_AccessPros] Help with string in query

Thanks John, I will remember that for future usage.
Thanks Again
Rod

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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
>