Can't Isolate Syntax Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've been trying to isolate a syntax error, and am having no luck. Hoping
some fresh eyes could help me out.

Thanks in advance.


Here is the SQL statement:

insert into tblscsalesorderdetails (ordernumber, itemid, locationid,
qtyordered, qtyshipped, listprice, discount, extended, stagedpart) values
([forms]![frmscsalesinvoice]![txtordernumber],
[forms]![frmviewstagedparts]![itemid],
[forms]![frmviewstagedparts]![truckid],
[forms]![frmviewstagedparts]![quantity],
[forms]![frmviewstagedparts]![quantity], '" & gblListPrice & "', '" &
gblDiscount & "', ([forms]![frmviewstagedparts]![quantity] * ('" &
gblListPrice & "' * (1 - '" & gblDiscount & "'))), -1;

I have also tried it as a String statement, as suggested in a previous post:

gblStrSQL = "insert into tblscsalesorderdetails (ordernumber, itemid,
locationid, qtyordered, qtyshipped, listprice, discount, extended,
stagedpart) values ('" & [Forms]![frmscsalesinvoice]![txtOrderNumber] & "',
'" & [Forms]![frmviewstagedparts]![ItemID] & "', '" &
[Forms]![frmviewstagedparts]![TruckID] & "', '" &
[Forms]![frmviewstagedparts]![Quantity] & "', '" &
[Forms]![frmviewstagedparts]![Quantity] & "', '" & gblListPrice & "', '" &
gblDiscount & "', ('" & [Forms]![frmviewstagedparts]![Quantity] & "' * ('" &
gblListPrice & "' * (1 - '" & gblDiscount & "'))), -1;"


So....any brilliant suggestions? =)
 
Hi Sharkbyte,
So....any brilliant suggestions? =)
Click to expand...

Why, of course! Add a Debug.Print gblStrSQL
statement. Then examine the results in the Immediate window (Ctrl G). Is
your statement picking up the correct values referenced in the two forms?
Are these values from the forms text or numeric? If text, you must wrap them
in quotes. I prefer to use Chr(34) myself.

The value that you showed as gblStrSQL is not correct, unless it is all on
one long line. If it is on several lines, then you need to add the line
continuation character, which is a space plus an underscore. You'll also need
to terminate each line in double quotes. For example:

gblStrSQL = "insert into tblscsalesorderdetails (ordernumber, itemid, " _
& "locationid, qtyordered, qtyshipped, listprice, discount, extended, " _

etc.

If your form references appear to be inserted correctly, then copy the
resulting SQL statement from the Immediate window and paste it into the SQL
window of a new query. When you attempt to run the query, you will likely get
an error that provides a meaningful clue.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


Sharkbyte said:
I've been trying to isolate a syntax error, and am having no luck. Hoping
some fresh eyes could help me out.

Thanks in advance.


Here is the SQL statement:

insert into tblscsalesorderdetails (ordernumber, itemid, locationid,
qtyordered, qtyshipped, listprice, discount, extended, stagedpart) values
([forms]![frmscsalesinvoice]![txtordernumber],
[forms]![frmviewstagedparts]![itemid],
[forms]![frmviewstagedparts]![truckid],
[forms]![frmviewstagedparts]![quantity],
[forms]![frmviewstagedparts]![quantity], '" & gblListPrice & "', '" &
gblDiscount & "', ([forms]![frmviewstagedparts]![quantity] * ('" &
gblListPrice & "' * (1 - '" & gblDiscount & "'))), -1;

I have also tried it as a String statement, as suggested in a previous post:

gblStrSQL = "insert into tblscsalesorderdetails (ordernumber, itemid,
locationid, qtyordered, qtyshipped, listprice, discount, extended,
stagedpart) values ('" & [Forms]![frmscsalesinvoice]![txtOrderNumber] & "',
'" & [Forms]![frmviewstagedparts]![ItemID] & "', '" &
[Forms]![frmviewstagedparts]![TruckID] & "', '" &
[Forms]![frmviewstagedparts]![Quantity] & "', '" &
[Forms]![frmviewstagedparts]![Quantity] & "', '" & gblListPrice & "', '" &
gblDiscount & "', ('" & [Forms]![frmviewstagedparts]![Quantity] & "' * ('" &
gblListPrice & "' * (1 - '" & gblDiscount & "'))), -1;"


So....any brilliant suggestions? =)
Click to expand...
 
Thanks for the suggestion(s), Tom. And while it didn't actually solve my
problem ( I happened upon it just through sheer determination...), it did
show me another way to troubleshoot, and actually pointed me to another
problem that would have surfaced once my first was resolved. (Did you follow
all that?)

The problem was so amazingly simple....

If you look at the SQL statement - both iterations - you'll see that I
forgot the closing ')' for the VALUES statement. DUH!

So, with that, I must say that I am done fighting with this, for the night.

Thanks, again.

Sharkbyte




Tom Wickerath said:
Hi Sharkbyte,
So....any brilliant suggestions? =)
Click to expand...

Why, of course! Add a Debug.Print gblStrSQL
statement. Then examine the results in the Immediate window (Ctrl G). Is
your statement picking up the correct values referenced in the two forms?
Are these values from the forms text or numeric? If text, you must wrap them
in quotes. I prefer to use Chr(34) myself.

The value that you showed as gblStrSQL is not correct, unless it is all on
one long line. If it is on several lines, then you need to add the line
continuation character, which is a space plus an underscore. You'll also need
to terminate each line in double quotes. For example:

gblStrSQL = "insert into tblscsalesorderdetails (ordernumber, itemid, " _
& "locationid, qtyordered, qtyshipped, listprice, discount, extended, " _

etc.

If your form references appear to be inserted correctly, then copy the
resulting SQL statement from the Immediate window and paste it into the SQL
window of a new query. When you attempt to run the query, you will likely get
an error that provides a meaningful clue.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


Sharkbyte said:
I've been trying to isolate a syntax error, and am having no luck. Hoping
some fresh eyes could help me out.

Thanks in advance.


Here is the SQL statement:

insert into tblscsalesorderdetails (ordernumber, itemid, locationid,
qtyordered, qtyshipped, listprice, discount, extended, stagedpart) values
([forms]![frmscsalesinvoice]![txtordernumber],
[forms]![frmviewstagedparts]![itemid],
[forms]![frmviewstagedparts]![truckid],
[forms]![frmviewstagedparts]![quantity],
[forms]![frmviewstagedparts]![quantity], '" & gblListPrice & "', '" &
gblDiscount & "', ([forms]![frmviewstagedparts]![quantity] * ('" &
gblListPrice & "' * (1 - '" & gblDiscount & "'))), -1;

I have also tried it as a String statement, as suggested in a previous post:

gblStrSQL = "insert into tblscsalesorderdetails (ordernumber, itemid,
locationid, qtyordered, qtyshipped, listprice, discount, extended,
stagedpart) values ('" & [Forms]![frmscsalesinvoice]![txtOrderNumber] & "',
'" & [Forms]![frmviewstagedparts]![ItemID] & "', '" &
[Forms]![frmviewstagedparts]![TruckID] & "', '" &
[Forms]![frmviewstagedparts]![Quantity] & "', '" &
[Forms]![frmviewstagedparts]![Quantity] & "', '" & gblListPrice & "', '" &
gblDiscount & "', ('" & [Forms]![frmviewstagedparts]![Quantity] & "' * ('" &
gblListPrice & "' * (1 - '" & gblDiscount & "'))), -1;"


So....any brilliant suggestions? =)
Click to expand...
Click to expand...
 
Hi Sharkbyte,

Glad to hear that you got it solved!

I find using Debug.Print strSQL (or gblStrSQL in your case) to be a very
useful troubleshooting technique.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


Sharkbyte said:
Thanks for the suggestion(s), Tom. And while it didn't actually solve my
problem ( I happened upon it just through sheer determination...), it did
show me another way to troubleshoot, and actually pointed me to another
problem that would have surfaced once my first was resolved. (Did you follow
all that?)

The problem was so amazingly simple....

If you look at the SQL statement - both iterations - you'll see that I
forgot the closing ')' for the VALUES statement. DUH!

So, with that, I must say that I am done fighting with this, for the night.

Thanks, again.

Sharkbyte




Tom Wickerath said:
Hi Sharkbyte,
So....any brilliant suggestions? =)
Click to expand...

Why, of course! Add a Debug.Print gblStrSQL
statement. Then examine the results in the Immediate window (Ctrl G). Is
your statement picking up the correct values referenced in the two forms?
Are these values from the forms text or numeric? If text, you must wrap them
in quotes. I prefer to use Chr(34) myself.

The value that you showed as gblStrSQL is not correct, unless it is all on
one long line. If it is on several lines, then you need to add the line
continuation character, which is a space plus an underscore. You'll also need
to terminate each line in double quotes. For example:

gblStrSQL = "insert into tblscsalesorderdetails (ordernumber, itemid, " _
& "locationid, qtyordered, qtyshipped, listprice, discount, extended, " _

etc.

If your form references appear to be inserted correctly, then copy the
resulting SQL statement from the Immediate window and paste it into the SQL
window of a new query. When you attempt to run the query, you will likely get
an error that provides a meaningful clue.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


Sharkbyte said:
I've been trying to isolate a syntax error, and am having no luck. Hoping
some fresh eyes could help me out.

Thanks in advance.


Here is the SQL statement:

insert into tblscsalesorderdetails (ordernumber, itemid, locationid,
qtyordered, qtyshipped, listprice, discount, extended, stagedpart) values
([forms]![frmscsalesinvoice]![txtordernumber],
[forms]![frmviewstagedparts]![itemid],
[forms]![frmviewstagedparts]![truckid],
[forms]![frmviewstagedparts]![quantity],
[forms]![frmviewstagedparts]![quantity], '" & gblListPrice & "', '" &
gblDiscount & "', ([forms]![frmviewstagedparts]![quantity] * ('" &
gblListPrice & "' * (1 - '" & gblDiscount & "'))), -1;

I have also tried it as a String statement, as suggested in a previous post:

gblStrSQL = "insert into tblscsalesorderdetails (ordernumber, itemid,
locationid, qtyordered, qtyshipped, listprice, discount, extended,
stagedpart) values ('" & [Forms]![frmscsalesinvoice]![txtOrderNumber] & "',
'" & [Forms]![frmviewstagedparts]![ItemID] & "', '" &
[Forms]![frmviewstagedparts]![TruckID] & "', '" &
[Forms]![frmviewstagedparts]![Quantity] & "', '" &
[Forms]![frmviewstagedparts]![Quantity] & "', '" & gblListPrice & "', '" &
gblDiscount & "', ('" & [Forms]![frmviewstagedparts]![Quantity] & "' * ('" &
gblListPrice & "' * (1 - '" & gblDiscount & "'))), -1;"


So....any brilliant suggestions? =)
Click to expand...
Click to expand...
Click to expand...
 
So....any brilliant suggestions? =)

.... not that it was relevant, but yes. Never use a global
variable (gblStrSQL) when a local variable will do.

Use global variables when global scope is required,
not just to save typing. Use local variables when
only local scope is required.

(david)
 
Back
Top