SQL Woes

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

I have an Update statement that refuses to work!

CurrentDb.Execute "UPDATE SalesDetails SET SalesDetails.Quantity =
Forms!Quantity!Pat" & _
"WHERE (((SalesDetails.LineID)=Forms!Buttons!Text98)" & _
"AND ((SalesDetails.SalesID)=Forms!Buttons!SalesID)" & _
"AND ((SalesDetails.ItemID)=Forms!Buttons!Text100)"

I get no error message, it just doesn't work. SalesDetails is the table
name.
Thanks
DS
 
DS said:
I have an Update statement that refuses to work!

CurrentDb.Execute "UPDATE SalesDetails SET SalesDetails.Quantity =
Forms!Quantity!Pat" & _
"WHERE (((SalesDetails.LineID)=Forms!Buttons!Text98)" & _
"AND ((SalesDetails.SalesID)=Forms!Buttons!SalesID)" & _
"AND ((SalesDetails.ItemID)=Forms!Buttons!Text100)"

I get no error message, it just doesn't work. SalesDetails is the
table name.
Thanks
DS
Click to expand...

You have no spaces between the text snippets that you are concatenating
together. Add a single space as the last character of every snippet (except the
last).
 
I have an Update statement that refuses to work!

CurrentDb.Execute "UPDATE SalesDetails SET SalesDetails.Quantity =
Forms!Quantity!Pat" & _
"WHERE (((SalesDetails.LineID)=Forms!Buttons!Text98)" & _
"AND ((SalesDetails.SalesID)=Forms!Buttons!SalesID)" & _
"AND ((SalesDetails.ItemID)=Forms!Buttons!Text100)"

I get no error message, it just doesn't work. SalesDetails is the table
name.
Thanks
DS
Click to expand...

Ok, let's play computer. When you concatenate these strings you'll get

"UPDATE SalesDetails SET SalesDetails.Quantity =
Forms!Quantity!PatWHERE
(((SalesDetails.LineID)=Forms!Buttons!Text98)AND
((SalesDetails.SalesID)=Forms!Buttons!SalesID)AND
((SalesDetails.ItemID)=Forms!Buttons!Text100)"

Access probably can't find a form control named PatWHERE for one
thing, and can't parse the SQL for another!

Blanks aren't optional, sometimes! Try

"UPDATE SalesDetails SET SalesDetails.Quantity =
Forms!Quantity!Pat " & _
"WHERE (((SalesDetails.LineID)=Forms!Buttons!Text98) " & _
"AND ((SalesDetails.SalesID)=Forms!Buttons!SalesID) " & _
"AND ((SalesDetails.ItemID)=Forms!Buttons!Text100)"


John W. Vinson[MVP]
 
One technique I use in contructing SQL String is to in clude a space at the
end of the last component String on the line of code AND another space at
the beginning of the first component String on the next continued line of
code.

This way, I always have 2 spaces in between the concatenated components and
JET will ignore the extra space. The advantage is that if I forget the
space in one of the 2 places, the other space will work just fine.

In addition, you didn't get any error message because you didn't use the
"dbFailOnError" option. Check Access VB Help on the Execute Method for this
option.
 
Rick said:
You have no spaces between the text snippets that you are concatenating
together. Add a single space as the last character of every snippet (except the
last).
Click to expand...
Thanks Rick,
It works!
DS
 
John said:
Ok, let's play computer. When you concatenate these strings you'll get

"UPDATE SalesDetails SET SalesDetails.Quantity =
Forms!Quantity!PatWHERE
(((SalesDetails.LineID)=Forms!Buttons!Text98)AND
((SalesDetails.SalesID)=Forms!Buttons!SalesID)AND
((SalesDetails.ItemID)=Forms!Buttons!Text100)"

Access probably can't find a form control named PatWHERE for one
thing, and can't parse the SQL for another!

Blanks aren't optional, sometimes! Try

"UPDATE SalesDetails SET SalesDetails.Quantity =
Forms!Quantity!Pat " & _
"WHERE (((SalesDetails.LineID)=Forms!Buttons!Text98) " & _
"AND ((SalesDetails.SalesID)=Forms!Buttons!SalesID) " & _
"AND ((SalesDetails.ItemID)=Forms!Buttons!Text100)"


John W. Vinson[MVP]
Click to expand...
Thanks, John....It works. This SQL is sure tricky!
DS
 
Van said:
One technique I use in contructing SQL String is to in clude a space at the
end of the last component String on the line of code AND another space at
the beginning of the first component String on the next continued line of
code.

This way, I always have 2 spaces in between the concatenated components and
JET will ignore the extra space. The advantage is that if I forget the
space in one of the 2 places, the other space will work just fine.

In addition, you didn't get any error message because you didn't use the
"dbFailOnError" option. Check Access VB Help on the Execute Method for this
option.
Click to expand...
Thanks, I'll try that. This SQL thing is a tuff nut to crack!
DS
 
Another trick I use is as follows:

dim mysql as String;

mysql = "Select * from "
mysql = mysql & " temptable "
mysql = mysql & " Where "
mysql = mysql & " (1 = 1) and " // this is a neat trick if your where
clause is conditional
if something = true then
mysql = mysql & "a = b and "
end if
if something else = true then
mysql = mysql & "c = d and "
end if

currentDb.Execute mysql

if you get a problem you can set a breakpoint before the excute statement,
to get the value of mysql, copy it.

Then create a new query don't pick any tables. Change the view to SQL paste
the text and try to change the view back to the design view .... if you have
any syntax errors, you will get much more meaningful error message and then
you correct the sql and cut and paste it back into your code.

I use this a a lot, it also helps you to see how Access creates the sql
based on the design view.


DJ
 
Back
Top