Dsum with source query in report

[apestaart]

I try to sum all the contract ours of employees in a report. I want to do that per department.
I looked at thread 703-1277036 and tried the following. :

 Dsum("[contrakt]","report query","[DepID]=" & DepID)

.

In "report query" I check for Null values and do not include these.
DepID is in the header of my department detail.
I receive a syntax error.
What is going wrong?
Regards,
Gerard van Beek

 

[dhookom]

I assume you are grouping your report by DepID. You can add a text box in the group header or footer with a control source of:
=Sum(contrakt)

If this doesn't work, you need to tell us more about your data, report, and expectations.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[genomon]

Assuming the parameter is a string, you need to enclose it in single quotes:

Dsum("[contrakt]","report query","[DepID]='" & DepID & "'")

Any help?

10 is company, 11 is a crowd

 

[apestaart]

I will answer you both at the same time.
DepID is a number.
"Report query" is a subset of Employees.
When I take :

Dsum("[contrakt]","Employees","[DepID]=" & DepID)

it works.
Employees is a table. So it does not work with a Query. Do I use the right syntax for the the query?
Hope to hear from you.
Regards
Apestaart

 

[dhookom]

Did you make sure you used "=" in front of DSum()? I never create object names with spaces because they often needed be wrapped in []s. I don't think this is your issue (other than questionable naming conventions).

Where is the text box in the report? Is the query a parameter query?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[apestaart]

Yes, I Used "=" at the start of the expression.
I Had simplefied my question but I now will explain it in detail.
I have a report with Regions info and Departments info.
The Regions have several departments.
Per departments I have several employees having a contract in ours.

I try to make a report with :Region header, Department header, department details, department footer, region footer.

In the department footer I use :

 Dsum("[contrakt]","Employees","[DepID]=" & DepID)

Employees is a table. This gives no ploblems. The result is de sum of conmtrakt ours of all employees per department.

But when I use

 Dsum("[contrakt]","report query","[RegID]='" & RegID & "'")

it does not work.
I get a Null string.
Do I write the query right?? Or Shoud I use []? also with a query??
Regards,
Apestaart

 

[apestaart]

Sorry a mistake should be :

Dsum("[contrakt]","Employees","[RegID]=" & RegID)

because RegID is a number. This does not work also.
Apestaart

 

[dhookom]

I'm confused regarding you RegID or DepID. If you really want to use RegID in the Region footer, what is the data type of RegID?

Why don't I see "=" in your expressions?

Did you try wrap your query name in []s?

Can you answer my question "Is the query a parameter query?"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[apestaart]

Both expressions start with "="
RegID is a long integer
I did try to wrap my query witH [] same problem.
The query is a simple query, no parameter query.
I am desperate.
Apestaart

 

[dhookom]

What is the SQL view of [report query]?

If you are running Access in a non-English version, you may need to replace commas with semi-colons.

Open the debug window (press Ctrl+G) and enter

? Dsum("[contrakt]","[report query]","[RegID]=1234")

Replace 1234 with a known, good RegID value. What are the results?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[apestaart]

The sql is rather complex.
It is using "Station" instead of "department"

PARAMETERS [Periode van] DateTime, [Periode tot] DateTime;
SELECT TabRegios.RegioID, TabRegios.Regio, TabStations.StationID, TabStations.Station, TabStations.Beheerder, TabStations.Telefoon, Verzuim.[Datum ziekmelding], Verzuim.[Datum herstelmelding], Verzuim.[Aantal uren ziek], -[Aantal uren ziek]*([Reden]<>"1111" Or IsNull([Reden])) AS [Ex zwang], [Periode van] AS [Periode van], [Periode tot] AS [Periode tot], [Keuze ziekmelding].Omschrijving, [Keuze oorzaak].Oorzaak1, IIf(([Datum herstelmelding]<[Periode tot]) And ([Datum ziekmelding]<[Periode tot]),[Aantal Uren ziek],1) AS nUrenziek, Verzuim.Reden, Verzuim.Oorzaak, Verzuim.Maandnz, [Naam Query].Naam, IIf([Datum ziekmelding]>0,[Datum ziekmelding],IIf([Datum herstelmelding]>0,[datum herstelmelding],[Maandnz])) AS Sort, Werknemers.[Kontrakt soort]
FROM (TabRegios INNER JOIN (TabStations INNER JOIN (Werknemers INNER JOIN [Naam Query] ON Werknemers.RelID = [Naam Query].RelID) ON TabStations.StationID = Werknemers.StationID) ON TabRegios.RegioID = TabStations.RegioID) INNER JOIN ([Keuze ziekmelding] RIGHT JOIN ([Keuze oorzaak] RIGHT JOIN Verzuim ON [Keuze oorzaak].[Oorzaak code] = Verzuim.Oorzaak) ON [Keuze ziekmelding].Ziekmeldingscode = Verzuim.Reden) ON Werknemers.RelID = Verzuim.RelID
WHERE (((Verzuim.[Datum ziekmelding])>=[Periode van]) AND ((Verzuim.[Datum herstelmelding])<=[Periode tot])) OR (((Verzuim.Maandnz)>=[Periode van] And (Verzuim.Maandnz)<=[Periode tot])) OR (((Verzuim.[Datum herstelmelding])>=[periode van] And (Verzuim.[Datum herstelmelding])<=[Periode tot]));

Table tabRegion has a one to many relationship to TabStation. TabSation has a one to many relation to "werknemers"
This is also the main query for my report.

In the Dsum function I use Semicolons.

I am not familiar with your suggestion to test in in the debugger. Control + G gives me the intermidiate window.
When I put in the dsum test I get compile error.Expected list seperator or ")" (I used "1" for RegID)

I have tested also Dsum("[contrakt]","[report query]")
without the where part. Still I get no value.

 

[dhookom]

What are [Periode van] and [Periode tot]?

If the query [report query] is used in the DSum() and as the record source of the report, then why can't you use just Sum() instead of DSum()? Did you ever try my suggestion of:
=Sum([contrakt])

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[apestaart]

Periode van =Period from
Period tot = Period to

I tried =Sum([contrakt]) once in the Station footer and once in the Regio footer.
That did the job. Hoeraaa!!!

I was mistaken to use the Dsum.

Thanks for your effort.

Regards apestaart.

 

[dhookom]

apestaart,
Glad to hear you got this working.

One minor point to clear up. I asked twice "Is the query a parameter query?" You stated "The query is a simple query, no parameter query" but I believe your [Periode van] is a parameter prompt used to filter the query. You can't use a query like this in DSum() or other domain aggregate functions.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[apestaart]

Thank you dhookom, that really helps me to remove a lot of questionmarks about the Dsum function. When I think about it I can understand it also.
Best regards,
Apestaart