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

Sabtu, 17 Agustus 2013

[MS_AccessPros] Re: Help with string in query

Thanks Clive, I am still learning and always forget the Debug command.

Thanks again
Rod

--- In MS_Access_Professionals@yahoogroups.com, "Clive" <zctek@...> wrote:
>
> Hi Rod,
>
> You can narrow it down by adding a line
> before the Execute statement,
> Debug.Print TypeName(tmpNotes)
>
> If there is a syntax error in tmpNotes then you
> will get an error message if the syntax is wrong.
>
> It would also be better to creete the whole of the
> Executable string before issuing the Execute Command
> so you use, dbs.Execute MyString, and if there is an
> error then you can do a sanity check on MyString after
> each stage as you add to it.
>
> Hope that helps.
>
> Regards, Clive.
>
> --- In MS_Access_Professionals@yahoogroups.com, "rodbevill" <desertscroller@> wrote:
> >
> > 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
> >
>