Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

dSum problem - SQL expression in access. syntax error

Posted on 2007-12-05
17
Medium Priority
?
267 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:squidlings
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 5
17 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20412464
you forgot the { & }

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;
0
 

Author Comment

by:squidlings
ID: 20412540
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 '&' )
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20412591
your query is not complete. you have to include the other table
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:squidlings
ID: 20412668

I added the other table and it loads, but the chqsum column is still blank?

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 RIGHT JOIN SS07_payment_plan_chq_info ON SS07_payment_plan.JBA_code = SS07_payment_plan_chq_info.Acc_num;
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20412720
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20412726
try replacing the RIGHT JOIN with INNER JOIN or LEFT JOIN
0
 
LVL 44

Expert Comment

by:GRayL
ID: 20412782
I notice you are using " ' " 
----------------------------^^--remove the spaces and re-try.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 20412813
ie:


DSum("[Chq_amm]","SS07_payment_plan"," [JBA_code] = '" & SS07_payment_plan_chq_info.Acc_num "'" )
0
 

Author Comment

by:squidlings
ID: 20412846
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.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 20412858
Don't forget to copy the URL of the uploaded file and paste it in a post back in this thread.
0
 

Author Comment

by:squidlings
ID: 20412887
Hi,

The file has been uploaded, under question I put 20412720

It's only 100k, feel free to have a peek.  Cheers!
0
 

Author Comment

by:squidlings
ID: 20412904
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 :)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20413083
you have the wrong table in the dsum,

try this

SELECT SS07_payment_plan_chq_info.*, DSum("[Chq_amm]","SS07_payment_plan_chq_info","[Acc_num]='" & [SS07_payment_plan].[jba_code] & "'") 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_payment_plan_chq_info","[Acc_num]='" & [SS07_payment_plan].[jba_code] & "'") 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;

0
 
LVL 44

Expert Comment

by:GRayL
ID: 20413106
There is no field in the DSum field named chq_amm.  I subsititued [Order Value(vat Inc)] and it ran fine.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 20413193
the query  i posted above will give some result but i don't think that values returned is the one you are after.

maybe this is what you are after

SELECT SS07_payment_plan_chq_info.Acc_num, Sum(SS07_payment_plan_chq_info.Chq_amm) AS SumOfChq_amm
FROM SS07_payment_plan INNER JOIN SS07_payment_plan_chq_info ON SS07_payment_plan.JBA_code = SS07_payment_plan_chq_info.Acc_num
GROUP BY SS07_payment_plan_chq_info.Acc_num;
0
 
LVL 44

Expert Comment

by:GRayL
ID: 20413195
Bah!  'substituted'
0
 

Author Comment

by:squidlings
ID: 20413230
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!!!!!!!!!!!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question