Solved Update query gives different errors (1 Viewer)

[hhag]

Hi,
I use the query tool to create SQL strings that I then add in a VBA module.
I've created the one below, and I get different errors. Does anyone see some odd things? I've tried out different soultions now for an hour....
The mistake is somewhere in the two last lines, that for sure


strMsg = "Anbud nr: " & ChoosedOffer & " är noterat"

db.Execute "UPDATE (tblAM INNER JOIN tblAnb ON tblAM.AMID = tblAnb.AMID)" _
& " INNER JOIN tblAnb_Uppf ON tblAnb.AnbID = tblAnb_Uppf.AnbID" _
& " SET tblAnb_Uppf.UppfDatum = Date(), tblAnb_Uppf.Kommentar = strMsg" _
& " WHERE (((tblAnb.AMID)=" & BOpp & ") AND ((tblAnb_Uppf.AnbID)<>" & ChOff & ") AND ((tblAnb_Uppf.NyttUppfDatum)<>0))"

BOpp and HhOff are constants that have correct values - I've checked when I debuged
I guess I'm missing some odd sign...

Thanks in advance!

 

[theDBguy]

Hi. What were the errors?

 

[hhag]

Hi,

Now, The error message says: (It has been different messages during my journey ...)
Error 3061.
Too few parameters . 1 was expected.

Thank you for your attention and support!

 

[theDBguy]

Hi,

Now, The error message says: (It has been different messages during my journey ...)
Error 3061.
Too few parameters . 1 was expected.

Thank you for your attention and support!

Okay, quick way to find out what parameter is missing is to assign your SQL statement to a String variable and then print it to the Immediate Window. Then, copy and paste it into the query designer. Access should prompt you for the missing parameter.

 

[Gasman]

I would have thought you would need to concatenate strMsg? as you have done for the other variables.?

 

[hhag]

This is a screenshot:
1. I just want to include the date of today
2. Now I get an error message "Syntax Error". An operator is missing ??

 

[theDBguy]

This is a screenshot:
1. I just want to include the date of today
2. Now I get an error message "Syntax Error". An operator is missing ??

View attachment 80375

Hi. If you're concatenating a date value, you'll have to delimit it with #s (and format it using U.S. or ISO). For example: #3/31/2020#

 

[hhag]

Ok. I've tried to change without any success...
I'll reframe the whole issue.
If you where about to assign, using SQL, a field the actual date of today and another field a concatenated text string as strMsg above. How would you write this in VBA?

 

[theDBguy]

Ok. I've tried to change without any success...
I'll reframe the whole issue.
If you where about to assign, using SQL, a field the actual date of today and another field a concatenated text string as strMsg above. How would you write this in VBA?

Hi. Not in front of a computer now, but see if this helps.

How to build strings in VBA

I promised a member at UtterAcess that I will write a blog about how to build a string in VBA, particularly when constructing a SQL statemen...

thedbguy.blogspot.com

 

[Gasman]

Try

& " SET tblAnb_Uppf.UppfDatum = #" & Format(Date,"mm/dd/yyyy") & "#, tblAnb_Uppf.Kommentar = '" & strMsg & "'"

You can do the date in one pass with another format string,which I use as a constant.

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

HTH

 

[hhag]

Thanks for your support. I'll check out this later tonight and post a reply

 

[hhag]

Finally! Thanks a lot! You're my heroes as well as all healthcare professionals in these covid-19 times.

 

[theDBguy]

Finally! Thanks a lot! You're my heroes as well as all healthcare professionals in these covid-19 times.

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

 

[The_Doc_Man]

I'm going to toss in another comment just for future reference. When you start getting intermittant errors of the "missing operator" type but you don't get that ALL of the time, you might be fighting a battle with nulls somewhere in your data. It is the intermittancy that would point to this problem. If it happened for every record, it is probably syntax. If it happens only for selected records, there is something wrong with the record and nulls are the most common culprits.

 

[Galaxiom]

And I'll add another potential problem. The SQL would fail if ChoosedOffer were to include a double quote character because it would close the string prematurely.

If necessary, to avoid this you can escape the double quotes by doubling them like this:

strMsg = "Anbud nr: " & Replace(ChoosedOffer, """", """""") & " är noterat"

You will properly understand the issue once you comprehend why there are so many double quote marks in that expression.

Paste the following into the VBA Immediate Window and hit Enter to see the value of str1 before and after the Replace.

str1 = "xx""yy": debug.print str1: str1 = Replace(str1, """", """"""): debug.print str1

 

[hhag]

Once more;