Expected:line number or label or statement or end of statement

  • Thread starter Thread starter Joel
  • Start date Start date
J

Joel

I am trying to update a table using a command button with the following
UPDATE statement. However, there is something wrong in my coding that
I can't seem to find. I either get a syntax error or the error in the
subject line. Any help in this would be greatly appreciated.

QrySQL = "UPDATE Timesheet"_
& "SET Timesheet.LvType = '" & Forms!frmLvTimeCard!LvTypeCbo &
"'"_
& "Timesheet.LvStart = " & Forms!frmLvTimeCard!TxtLvStart & "_
& "Timesheet.LvHours = " & Forms!frmLvTimeCard!TxtLvHours & "_
& "WHERE Timesheet.UserName = " & Forms!frmLvTimeCard!LvUserName &
"_
& "Timesheet.TDate = " & Forms!frmLvTimeCard!TxtLvDate & ";
 
One problem I see is that the closing ; for the sql is outside the quotes.
Another possible problem is Timesheet.TDate appears to be a date field. In
that case, you need to enclose your dates in #. Try this:
& "Timesheet.TDate = #" & Forms!frmLvTimeCard!TxtLvDate & "#;"
 
At first glance: there's no space between

"UPDATE Timesheet"_

and

& "SET Timesheet.LvType

Is supposed to be that way?
 
Hello, Joel,

You need to be consistent, as well as add spaces, as has been mentioned above.
Try the following:

QrySQL = "UPDATE Timesheet"_
& " SET Timesheet.LvType = '" & Forms!frmLvTimeCard!LvTypeCbo & "'" _
& " Timesheet.LvStart = #" & Forms!frmLvTimeCard!TxtLvStart & "#" _
& " Timesheet.LvHours = " & Forms!frmLvTimeCard!TxtLvHours _
& " WHERE Timesheet.UserName = '" & Forms!frmLvTimeCard!LvUserName & "'"
_
& " Timesheet.TDate = #" & Forms!frmLvTimeCard!TxtLvDate & "#;"

This assumes that LvType, LvTypeCbo, UserName, and LvUserName are string
(text) fields, LvStart, TxtLvStart, TDate, and TxtLvDate are Date/time fields,
and LvHours and TxtLvHours are numeric fields. If that is not the case, you
must make the necessary corrections. I think if you copy and paste this code
it just might do the trick.

Sam
At first glance: there's no space between

"UPDATE Timesheet"_

and

& "SET Timesheet.LvType

Is supposed to be that way?
I am trying to update a table using a command button with the following
UPDATE statement. However, there is something wrong in my coding that
Click to expand...
[quoted text clipped - 9 lines]
"_
& "Timesheet.TDate = " & Forms!frmLvTimeCard!TxtLvDate & ";
Click to expand...
Click to expand...
 
Thanks Sam, I can see where my coding skills need some work. Your
assumptions are correct and I was able to cut, paste and run without
errors. However, the results were posted to the first record in the
table and not the record that the "WHERE" statement should have found.
Got any ideas??
 
Sam does it make a difference if on the form all the boxes are unbound
and the TxLvDate source is DATE()???
 
Let's go slowly. What do you mean the first record in the table? What is/are
the difference(s) between the search string

( " WHERE Timesheet.UserName = '" & Forms!frmLvTimeCard!LvUserName & "'"
_
& " Timesheet.TDate = #" & Forms!frmLvTimeCard!TxtLvDate & "#;")

and the string where the update was actually applied? In other words, does
UserName = LvUserName? Do the two date fields match?

By the way, I just copied and pasted the entire query into SQL, and I think I
found the problem. You need to add the "AND" operator just before the last
part of the WHERE clause, to wit:

" WHERE Timesheet.UserName = '" & Forms!frmLvTimeCard!LvUserName & "'" _
& " AND Timesheet.TDate = #" & Forms!frmLvTimeCard!TxtLvDate & "#;"

When I added the AND operator SQL loved it! Try it, you might like it! It
just might solve your last problem.

To answer your question (below), no, it shouldn't matter what the contents of
the textboxes are.

Sam
 
Sam, Thank you for your patience.
First, I have a table with a few test records in it. Second, I am
passing the LvUserName (unbound text field) from a previous form and I
am capturing the current date (Date()) in a unbound text field. Third,
I am trying to find the record in the table that matches this search
criteria and post the next three (3) fields as an update. The process
is posting the three fields but to the first record in the table and it
should be posting to the third record where the table fields match the
WHERE fields.
 
Back
Top