DSum

[jiajian]

Hi

I have a DB which pull through the company ERP system . Later I made a query (so call Qry1)which simplyfy all necessary fields which I need .

One of the field in this query is Customer. There are two major group of the Customer call Internal & External. I do not want to make a query to identify what customer is External or Internal customer as it can be diffrentiate easily with A** & B** & C** is internal whereas others in External.

Also another field of this Qry1 is [Amount] which indicate the sales amount.

Question :

How can I make a report to identify the sales amount of Internal customer only? which means sales by A**,B** & C**.

I had tried this in the report (in header ?)but it don't work.

=Nz((DSum("[Amount]","[Qry1]","[Customer]= 'A**'")))+Nz((DSum("[Amount]","[Qry1]","[Customer]= 'B**'")))+Nz((DSum("[Amount]","[Qry1]","[Customer]= 'C**'")))

Please commend.

Thanks

jiajian

 

[Jon K]

Use the Like operator instead of =

that is:

"[Customer] Like 'A**'" ..., etc.

 

[jiajian]

Dsum

Hi

Thanks.

After changing the = to Like, I still facing the same result e.g "error".

Please commend.

jiajian

 

[Jon K]

I assume the * is a wildcard character that represents any other characters in the Customer field (one * should suffice as a wildcard). I tried the DSum expression in the report header, footer and detail bands. They all worked in my report.


Check the spellings of the field names in Qry1 and in the DSum expression in the report.

Is Qry1 a parameter query? If it is, DSum will not pop up an input box for the parameter value; it simply displays #Error.

 

[jiajian]

Yes. Qry1 is a date parameter query. I have another field as deldate to use as the parameter dialog box once it is been query.I need to have the date parameter to check the result.
So, any suggestion on what shall I do?

Thanks

jiajian

 

[Jon K]

The deldate has to be ready when the report (and therefore the query) is run.

You can place the deldate in a text box on a form, e.g. name the text box as txtDelDate. In the query, change the parameter to Forms![formName]![txtDelDate], using the correct form name. Open the report from a command button on this form. This way, the parameter value will be ready in the text box for the DSum when the report is run.