AfterUpdate() calculation

[di.miller]

Hi All,

I am having trouble with a report calculation.

My report is grouped by YEARMONTH and then by JOBTYPE.
YEARMONTH is numeric and JOBTYPE is text. Details are hidden as they number in the hundreds.

I have a query that is to run after the user chooses a date from a combo box. The combo box is called SelectYearMonth and is populated by YEARMONTH in HBELP_FINAL_Q. This part works great.

Once the YEARMONTH is selected, I need to calculate the total Pages for each JOBTYPE for the chosen YEARMONTH. There are four JOBTYPES and the types appear on the report correctly.

Here is where I run into problems, how to calculate the page totals for the YEARMONTH selected by the user by JOBTYPE. The total is to be displayed in a text box called TotalPagesTextbox that I have in the JOBTYPE footer. The AfterUpdate() code I am trying to use is:

Me.TotalPagesTextbox = DSum("Pages", "HBELP_FINAL_Q", "YEARMONTH = " & [SelectYearMonth] & "' AND JOBTYPE = '" & Me.[JOBTYPE] & "'")

I know there is something I am missing in the syntax here.

I have a general report that uses the code:

Me.TotalPagesTextbox = DSum("Pages", "HBELP_FINAL_Q", "YEARMONTH = " & [SelectYearMonth])

This code works great for the general report and it gives me an accurate total of all pages for the month. I want to break the information down in detail of how many pages for each JOBTYPE.


Can you please help?

Thanks,
Diana"

 

[Trevor G]

Hi Diana and welcome to the forum,

Microsoft supply some sample databases for Queries, Forms and Reports in the reports database they show you how to have totals on page by page scenario.

I believe it will help you. Here is the link to download the samples

http://office.microsoft.com/en-us/a...t-you-can-download-and-adapt-HA001120138.aspx

 

[di.miller]

Hi Trevor,

I am sorry to say that there was nothing there that helped me with my problem.

Thanks anyhow,
Diana

 

[Trevor G]

Thats a pity,

Can you upload a sample of the database then.

 

[rainman89]

Is job type a text value or a number?

 

[di.miller]

JOBTYPE is a text value.

 

[rainman89]

So do you get any values if you just do

Me.TotalPagesTextbox = DSum("Pages", "HBELP_FINAL_Q", "JOBTYPE = '" & Me.[JOBTYPE] & "'")

 

[di.miller]

Hi Rainman,

I am getting close here, I am now trying:

Me.TotalPagesTextbox = DSum("Pages", "HBELP_FINAL_Q", "YEARMONTH = " & [SelectYearMonth] & " AND JOBTYPE = '" & Me.[JOBTYPE] & "'")

The result is correct for the first JOBTYPE, but it is placing the same value for the first one into all four JOBTYPES on the report....

 

[rainman89]

Which it will because it is creating the jobtype of the first one on the report creation. What I think you need to do is distinguish the jobtype for each section. Do you have grouping on for the job types?

 

[di.miller]

Hi Rainman,

Yes, I have the report grouped by YEARMONTH and then by JOBTYPE.

 

[rainman89]

Could you turn group footer on and create that textbox in there for each group. I cant see why that wouldn't work. I haven't tried it though.

 

[di.miller]

Hi Rainman,

How did you know that that is exactly where I have this textbox! I thought it was suppose to work that way too! Stranger and stranger....

 

[rainman89]

Can you post up a sample database? Id like to see some data and give it a try

 

[di.miller]

Hi Ray,

I am sorry to say that I can not post a sample due to security reasons.

 

[di.miller]

Hi Ray,

I am over trying to get this thing working by groups it is just too frustrating! I have coded the individual JOBTYPE boxes, since there are only 4 JOBTYPES as:

Me.ExactReprintTextbox = DSum("Pages", "HBELP_FINAL_Q", "YEARMONTH = " & [SelectYearMonth] & " AND JOBTYPE = 'E'")

Me.KeylineUpdateTextbox = DSum("Pages", "HBELP_FINAL_Q", "YEARMONTH = " & [SelectYearMonth] & " AND JOBTYPE = 'K'")

Me.OriginalsTextbox = DSum("Pages", "HBELP_FINAL_Q", "YEARMONTH = " & [SelectYearMonth] & " AND JOBTYPE = 'O'")

Me.ReprintsTextbox = DSum("Pages", "HBELP_FINAL_Q", "YEARMONTH = " & [SelectYearMonth] & " AND JOBTYPE = 'R'")

This is working fine for the report. I have added a total of pages at the bottom of the report as:

Me.GrandTotalTextbox = DSum("Pages", "HBELP_FINAL_Q", "YEARMONTH = " & [SelectYearMonth])

This is also working great.

I would like to add a job count to each type and I am trying to use:

Me.ExtactJobsTextbox = Count("JOBTYPE", "HBELP_FINAL_Q", "YEARMONTH = " & [SelectYearMonth] & " AND JOBTYPE = 'E'")

But something is not quite right with this calculation. Do you see where I am going wrong on this one?

You have really been a great help and I truly appreciate it.

Thanks,
Diana

 

[di.miller]

OMG! I used Count and not DCount in my code! It works great as:

DCount("JOBTYPE", "HBELP_FINAL_Q", "YEARMONTH = " & [SelectYearMonth] & " AND JOBTYPE = 'E'")