squidlings
asked on
dSum problem - SQL expression in access. syntax error
Hi,
Please can somebody help me debug the following statement within access.
Explanation.
I have a database which has a list of all the customers orders and their values, once we receive the cheques for their orders, I add them up, if they equal the order value I release the order.
If I use a sum query to add up all the cheques for 1 customer, once you link the query to the table and run it in a form and all the data becomes read only. Hence I'm unable to release customers.
I have been suggested to use dSum to add all the customers cheques together rather than a sum query.
This is a layout of the 2 tables involved.
TBL name : SS07_payment_plan
Cliente Fatturazione Text
JBA_code (key) (Linked to other table) Text
Ragione Sociale Text
Order Value(inc vat) Currency
Currency Text
controller (linked to another table not in this example) Text
Insurance in currency Currency
Memo notes Memo
release_acc (yes/no)
TBL2 : SS07_payment_plan_chq_info
ID (key) autonumber
Acc_num (Linked to other table) (text)
Chq_amm Currency
date date/time
The expression a very kind fella helped me with is...
SELECT SS07_payment_plan_chq_info .*,
DSum("[Chq_amm]",
"SS07_payment_plan",
" [JBA_code] = ' " & SS07_payment_plan_chq_info .Acc_num " ' " )
AS chqsum
FROM SS07_payment_plan_chq_info ;
It keeps coming back with syntax error, it highlights the " ' " at the end of
" [JBA_code] = ' " & SS07_payment_plan_chq_info .Acc_num " ' " )
Please can somebody help me correct this expression. Thanks.
Thank you.
Please can somebody help me debug the following statement within access.
Explanation.
I have a database which has a list of all the customers orders and their values, once we receive the cheques for their orders, I add them up, if they equal the order value I release the order.
If I use a sum query to add up all the cheques for 1 customer, once you link the query to the table and run it in a form and all the data becomes read only. Hence I'm unable to release customers.
I have been suggested to use dSum to add all the customers cheques together rather than a sum query.
This is a layout of the 2 tables involved.
TBL name : SS07_payment_plan
Cliente Fatturazione Text
JBA_code (key) (Linked to other table) Text
Ragione Sociale Text
Order Value(inc vat) Currency
Currency Text
controller (linked to another table not in this example) Text
Insurance in currency Currency
Memo notes Memo
release_acc (yes/no)
TBL2 : SS07_payment_plan_chq_info
ID (key) autonumber
Acc_num (Linked to other table) (text)
Chq_amm Currency
date date/time
The expression a very kind fella helped me with is...
SELECT SS07_payment_plan_chq_info
DSum("[Chq_amm]",
"SS07_payment_plan",
" [JBA_code] = ' " & SS07_payment_plan_chq_info
AS chqsum
FROM SS07_payment_plan_chq_info
It keeps coming back with syntax error, it highlights the " ' " at the end of
" [JBA_code] = ' " & SS07_payment_plan_chq_info
Please can somebody help me correct this expression. Thanks.
Thank you.
ASKER
OMG, no errors, no nothing. It loads the datasheet and there is nothing in the dSum box.
I'm going to chew my own fingers off.
excuse me, I've counted to 10, I understand this is my own fault for not studying so hard when I was at school. :)
You guys are amazing, anything you can see which might be causing it to not show any result?
(It's amazing the power of an '&' )
I'm going to chew my own fingers off.
excuse me, I've counted to 10, I understand this is my own fault for not studying so hard when I was at school. :)
You guys are amazing, anything you can see which might be causing it to not show any result?
(It's amazing the power of an '&' )
your query is not complete. you have to include the other table
ASKER
I added the other table and it loads, but the chqsum column is still blank?
SELECT SS07_payment_plan_chq_info
FROM SS07_payment_plan RIGHT JOIN SS07_payment_plan_chq_info
try replacing the JOIN with INNER or LEFT
can you post sample data from both tables
or better if you can zip the db and upload here www.ee-stuff.com
can you post sample data from both tables
or better if you can zip the db and upload here www.ee-stuff.com
try replacing the RIGHT JOIN with INNER JOIN or LEFT JOIN
I notice you are using " ' "
-------------------------- --^^--remo ve the spaces and re-try.
--------------------------
ie:
DSum("[Chq_amm]","SS07_pay ment_plan" ," [JBA_code] = '" & SS07_payment_plan_chq_info .Acc_num "'" )
DSum("[Chq_amm]","SS07_pay
ASKER
Taken all the spaces out, it works, but doesn't display anything in the chqsum field we've created.
Cheers for this, I'm uploading the file now, I'd love it if you could have a look.
Cheers, there shouldn't be any VB code so you can disable anything if it asks.
Thanks.
Cheers for this, I'm uploading the file now, I'd love it if you could have a look.
Cheers, there shouldn't be any VB code so you can disable anything if it asks.
Thanks.
Don't forget to copy the URL of the uploaded file and paste it in a post back in this thread.
ASKER
Hi,
The file has been uploaded, under question I put 20412720
It's only 100k, feel free to have a peek. Cheers!
The file has been uploaded, under question I put 20412720
It's only 100k, feel free to have a peek. Cheers!
ASKER
Your file has successfully been uploaded!
To download the file, you must be logged into EE-Stuff. Here are two pages that will display your file, if logged in:
View all files for Question ID: 20412720
https://filedb.experts-exchange.com/incoming/ee-stuff/6010-Copy--2--of-Sixty---Letters.zip
Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/6010-Copy--2--of-Sixty---Letters.zip
Glad you mentioned that :)
To download the file, you must be logged into EE-Stuff. Here are two pages that will display your file, if logged in:
View all files for Question ID: 20412720
https://filedb.experts-exchange.com/incoming/ee-stuff/6010-Copy--2--of-Sixty---Letters.zip
Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/6010-Copy--2--of-Sixty---Letters.zip
Glad you mentioned that :)
you have the wrong table in the dsum,
try this
SELECT SS07_payment_plan_chq_info .*, DSum("[Chq_amm]","SS07_pay ment_plan_ chq_info", "[Acc_num] ='" & [SS07_payment_plan].[jba_c ode] & "'") AS chqsum
FROM SS07_payment_plan LEFT JOIN SS07_payment_plan_chq_info ON SS07_payment_plan.JBA_code = SS07_payment_plan_chq_info .Acc_num;
or
SELECT SS07_payment_plan_chq_info .*, DSum("[Chq_amm]","SS07_pay ment_plan_ chq_info", "[Acc_num] ='" & [SS07_payment_plan].[jba_c ode] & "'") AS chqsum
FROM SS07_payment_plan Inner JOIN SS07_payment_plan_chq_info ON SS07_payment_plan.JBA_code = SS07_payment_plan_chq_info .Acc_num;
try this
SELECT SS07_payment_plan_chq_info
FROM SS07_payment_plan LEFT JOIN SS07_payment_plan_chq_info
or
SELECT SS07_payment_plan_chq_info
FROM SS07_payment_plan Inner JOIN SS07_payment_plan_chq_info
There is no field in the DSum field named chq_amm. I subsititued [Order Value(vat Inc)] and it ran fine.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Bah! 'substituted'
ASKER
I could reach throught your monitors and give you all a big sloppy kiss!!!!!!
I'm getting results now. But further problems to be raised tomorrow :)
Love it!!!!!!!!!! THANKS!!!!!!!!!!!
I'm getting results now. But further problems to be raised tomorrow :)
Love it!!!!!!!!!! THANKS!!!!!!!!!!!
SELECT SS07_payment_plan_chq_info
DSum("[Chq_amm]",
"SS07_payment_plan",
" [JBA_code] = ' " & SS07_payment_plan_chq_info
AS chqsum
FROM SS07_payment_plan_chq_info