Append Query from Form (1 Viewer)

[Design by Sue]

Can anyone please tell me where my syntax error is in this code? The error message states syntax error in date in query expression but I am using a similar code elsewhere and I don't get this error. I am sure it is something simple that I am missing - so any assistance as always greatly appreciated.


Dim strSQL As String
strSQL = "Update [SOP Time Table] Set [DateCompleted] = #" & Me.DateCompleted & _
"' And [Employee Number] = '" & ESC(Me.ComboEmpName) & _
"' And [SOP Number] = '" & ESC(Me.ComboSOPNo) & _
"' And [SOPVersion] = '" & ESC(Me.Version) & _
"' And CompletionTime = " & ESC(Me.CompletionTime)
CurrentDb.Execute strSQL, dbFailOnError


DateCompleted is Date/Time
Employee Number is Text
SOP Number is Text
SOPVersion is Number
CompletionTime is Number

FYI ESC is

Function ESC(str) As String
ESC = Replace(str, "'", "''")
End Function

Thanks!
Sue

 

[GPGeorge]

strSQL = "Update [SOP Time Table] Set [DateCompleted] = #" & Me.DateCompleted & _

 

[Design by Sue]

strSQL = "Update [SOP Time Table] Set [DateCompleted] = #" & Me.DateCompleted & _

That is what I have - so I am confused. What should it be? I don't understand why this does not work when I does when I use it to add to a different table with the same set up

 

[Gasman]

Why not Debug.Print strSQL and see what you get and see if that gives you a clue?
You said similar, not same, so compare one against the other.

I would be formatting the date correctly if I needed to concatenate into a string.

I used to use this in some of my DBs

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

 

[Design by Sue]

strSQL = "Update [SOP Time Table] Set [DateCompleted] = #" & Me.DateCompleted & _

That is what I have -

Why not Debug.Print strSQL and see what you get and see if that gives you a clue?
You said similar, not same, so compare one against the other.

When I say similar - there are additional fields in this code but the first line is exactly the same (DateCompleted) and there are no issues with it for that table. The fields in the tables for DateCompleted are set up exactly the same. I am not on that computer right now and will see what DebugPrint for me, but I thought this was a simple comma or such missing and the experts could see early what I was missing. Thanks

 

[Gasman]

When I say similar - there are additional fields in this code but the first line is exactly the same (DateCompleted) and there are no issues with it for that table.

Sorry, I cannot believe that is true. Access does not make it's mind up to accept code one minute and then not the next.
If you do not want to follow my method, start it off one line at a time, when you get that correct, add the next line.

 

[Design by Sue]

Sorry, I cannot believe that is true. Access does not make it's mind up to accept code one minute and then not the next.
If you do not want to follow my method, start it off one line at a time, when you get that correct, add the next line.

Yes I understand that - that is why this is so strange to me. Yes I will be looking into this next time I am on that computer. Thanks for your help!

I have a feeling it is one of the other lines of the code causing the problem and the message (as often it seems) is not really telling the problem.

 

[The_Doc_Man]

Sue, part of the problem is symmetry.

There is such a thing as a type suffix, which is a one-sided type indicator, but those are used only for the rarest of cases. The symmetry to which I refer is that a quoted string is surrounded by matching quote marks. A single-argument function surrounds its argument with paired parentheses. A table name, query name, and field name are surrounded with paired square brackets. And dates are surrounded by paired octothorpes (#).

When you say "simiilar" code, I'll bet that the difference is pairing of markers.

 

[GPGeorge]

That is what I have - so I am confused. What should it be? I don't understand why this does not work when I does when I use it to add to a different table with the same set up

I was hoping you'd notice there was one # in your code. The symmetry aspect The_Doc_Man described. Look again, please.

 

[Design by Sue]

Thanks again everyone - will post back when I work on this again.

 

[The_Doc_Man]

I was hoping you'd notice there was one # in your code. The symmetry aspect The_Doc_Man described. Look again, please.

Past a certain point, I believe "highway hypnosis" sets in. Folks see the opening marker but miss the closing one because they want to read ahead at a time when slow reading is necessary. It's a "human factors" type of thing. It is why, for example, airline pilots have printed checklists of single, specific steps to follow when preparing for takeoff, landing, or certain types of emergency responses. The checklist is to SLOW THEM DOWN so that they HAVE to look at things in enough detail to assure success.

 

[GPGeorge]

Past a certain point, I believe "highway hypnosis" sets in. Folks see the opening marker but miss the closing one because they want to read ahead at a time when slow reading is necessary. It's a "human factors" type of thing. It is why, for example, airline pilots have printed checklists of single, specific steps to follow when preparing for takeoff, landing, or certain types of emergency responses. The checklist is to SLOW THEM DOWN so that they HAVE to look at things in enough detail to assure success.

True, and it's happened to me. That's why I colored the date delimiter in red and bolded and italicized it.

What you're describing is also one aspect of a trouble-shooting technique we learned about in a presentation to our User Group last night. It's called Rubber Duck debugging. You keep an inanimate object, like a rubber deck, on your desk. When you encounter a problem, you describe it to the inanimate object. Many times formulating that description brings you to the solution. The commonality is the enforced slower pace that helps you avoid rushing past the missing piece.

 

[ebs17]

Anyone who thinks they will only make one error is an optimist. The procedure only gets stuck at the first error.

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

- This is not an append query, but an update query.
- The unfinished formatting of the expression for a date has already been pointed out, see notes from @Gasman
- SOPVersion is Number => then expression without single quotes
- In the SET part, the expressions must be separated by commas, not logically linked using AND.

For numbers, the use of the ESC function is unnecessary or even harmful, as it explicitly returns a string instead of the expected number.

You should refresh your knowledge of data types. SQL is less error-tolerant than VBA in terms of data types and formats offered.
At this point I would like to point out a tool:

Add-In for debug SQL in VBA

Compiling SQL statements or filters in VBA and then using them is quite error-prone. The add-in linked below sets display of the instruction as well as test and error display to a higher level. SqlDebugPrint Eberhard

www.access-programmers.co.uk

 

[Pat Hartman]

When you are unfamiliar with SQL syntax, the best solution is to let the QBE help you to build it. Once the query works, if you insist on using it as embedded SQL, then copy the SQL that the QBE build and format it as a string. You'll need to test again very carefully to ensure that you have constructed the string correctly.

There is no advantage to using embedded SQL and there are disadvantages. People who dislike the way that QBE formats the SQL it builds, tend to use embedded SQL. It is a preference because they prefer neatly formatted strings. If I use the QBE, I don't convert to SQL view for exactly the same reason, the SQL string is impossible to read but then I don't need to read it when the graphical view is generally pretty clear so there we have it.

 

[GPGeorge]

When you are unfamiliar with SQL syntax, the best solution is to let the QBE help you to build it. Once the query works, if you insist on using it as embedded SQL, then copy the SQL that the QBE build and format it as a string. You'll need to test again very carefully to ensure that you have constructed the string correctly.

There is no advantage to using embedded SQL and there are disadvantages. People who dislike the way that QBE formats the SQL it builds, tend to use embedded SQL. It is a preference because they prefer neatly formatted strings. If I use the QBE, I don't convert to SQL view for exactly the same reason, the SQL string is impossible to read but then I don't need to read it when the graphical view is generally pretty clear so there we have it.

The Monaco SQL editor promises to make this less of a problem in the future.....

 

[ebs17]

Doing it completely differently is of course also a way to avoid making certain errors.

There are different ways to pass parameters to a query.

Query to find records not between two dates

In a nutshell, I'm trying to create a report showing who hasn't made a payment between a selected date range, that's it. I don't know what the actual field names are, but the logic is: < Date1 OR > Date2 OR Payment Amount ISNULL (because these people didn't make any payment at all)

www.access-programmers.co.uk

I am one of those who want to read and understand a SQL statement, on the one hand to not be dependent on an assistant and on the other hand to have more flexibility and more options than those offered by the assistant (QBE). If an assistant dictates the options to me, I can't really be the head of development.

SQL statements as such and how they are formatted are two different things. With VBA, you also format for readability if you want to do something good for yourself and others.

 

[Pat Hartman]

The Monaco SQL editor promises to make this less of a problem in the future.....

They have been promising that for 10+ years. I"m not holding my breath. It would be fabulous to just have a search and replace feature let alone support formatting and comments.

They've waited so long they ought to include a feature that copies and formats it for VBA as well to satisfy those who love embedded SQL. I spent 20 years coping with embedded SQL in COBOL/CICS before I came to Access which is probably why I am even willing to put up the really poor QBE. I dreamed about a graphical tool like the QBE long before Access even existed.

I guess when you only work with databases you created yourself and the same ones year after year, you have memorized all your schemas and column names. I simply work with too many databases due to being a consultant and half of them I didn't create so there is no consistency in their naming standards. It is just so much easier for me to point and click and only switch to SQL view when the QBE can't display the structure I need.

 

[Gasman]

@isladogshas a utility to do just that.

 

[GPGeorge]

They have been promising that for 10+ years. I"m not holding my breath. It would be fabulous to just have a search and replace feature let alone support formatting and comments.

They've waited so long they ought to include a feature that copies and formats it for VBA as well to satisfy those who love embedded SQL. I spent 20 years coping with embedded SQL in COBOL/CICS before I came to Access which is probably why I am even willing to put up the really poor QBE. I dreamed about a graphical tool like the QBE long before Access even existed.

I guess when you only work with databases you created yourself and the same ones year after year, you have memorized all your schemas and column names. I simply work with too many databases due to being a consultant and half of them I didn't create so there is no consistency in their naming standards. It is just so much easier for me to point and click and only switch to SQL view when the QBE can't display the structure I need.

The Monaco Editor leaked into a beta channel earlier this week as reported by two or three brave souls. It is far from being ready to release for reals, though. I'm reasonably convinced we'll see it in the wild by the end of this year. And it'll actually work as intended by the end of 2025.

 

[Pat Hartman]

I'm reasonably convinced we'll see it in the wild by the end of this year. And it'll actually work as intended by the end of 2025.

You're such an optimist