Date ranges 1

[jer007]

I am running a report based on a query which requires the user to input a date range (Criteria: Between [start]AND [END]). This report displays the information within that range and sums the financial information. I am having trouble with two items.

1. How can I display the date range selected by the user on the report?
2. Along with the totals for the slected range I would like to display a year to date total. In a perfect world the YTD total should calculate from January 1st of the year in the selected range to the end date selected by the user. If that does not work it would be acceptable to have it calculate from January 1st of current year to the current day the report is run.

If this is unclear please let me know and I will try to be more specific.

Thank you for any help you can provide.

 

[PaulBricker]

The first part is easy. To display a date range on your report that mimics the values entered in a parameter box, you simply add a textbox and set it's control source to the criteria you have in your query. So if your query says
Between [Start] And [End]
your textbox would have a control source set to
=Between [Start] And [End]

Your second question would be easier to accomplish if you had the Start and End values entered into textboxes on a form. Then you could create queries that did the totaling based on the values entered in the form. For the current year YTD the criteria in your query would be
Between #1/1/2004# And Forms!myForm!End

You could create a Totals query and use that criteria and just use DLookUp to get the value for your YTD or you could just create a Select query and use the DSum() function to sum up the values you need.

To get more specific, you would need to have decided on how you wanted to approach the situation. Also, if you want data from previous years, you would have to modify the Betwen criteria to something like
Between "1/1" & Year(Forms!myForm!Start) And Forms!myForm!End

Let me know if you have specific questions.

Paul

 

[jer007]

Thanks for the help.
I've now set it up so that the user enters the dates in a form using an unbound text box. The date range now displays properly.

I'm sorry but I'm lost on the second part. I've set up a query to total the fields I need. I am unable to have query select the YTD totals. The query is set up as follows:
Field: ValueUponCompletion
Table: tblPermit
Total: Sum

Field: TotalFees
Table: tblPermit
Total: Sum

Field: Date
Table: tblPermit
Total: Where
Criteria: Between #2004-01-01# And [Forms]![RunPermitReport]![txtEnd]


The Format for our dates is entered as yyyy-mm-dd. I liked your sugestion of being able to pull the YTD information for past years. I am unfamilliar with the DLookup or DSum functions. What kind of syntax would I enter and would it just be used in the criteria field?

Thanks for the help.

 

[PaulBricker]

Do you get an error when you run the query or you just don't get any records? The structure looks OK. One thing you need to make sure when using an unbound form is you have to move the focus off the textbox, in this case txtEnd, in your form before the query will see the value. So if you have a button you are clicking to open the Report, that will do it.
Then to use the DLookUp, you would put this expression in the Control Source for a textbox on your Report.

=DLookup("Fieldname", "queryName")

in your case, you don't tell us what the query name is but I'll call it Qry1. I'll assume that Access has named you two fields, SumOfValueUponCompletion and SumOfTotalFees
so the syntax would be
=DLookup("SumOfValueUponCompletion","Qry1")

and

=DLookUp("SumOfTotalFees","Qry1")

This will give you your YTD value in the Report.

Give a go and post back with problems.

Paul

 

[jer007]

It looks like it is working except for formatting.
When I run the query on it's own the numerical formatting is correct. When I use the DLookUp string in the report txt box the displayed value is only numerical. It looses the commas, dollar sign, and the decimal places. How would I get it back?

 

[PaulBricker]

Make sure you format you textbox in the report for Currency. If that doesn't help, post back.

Paul

 

[jer007]

Thanks Paul,

That formatting works.

I was just advised I need to add one more thing to my report. I have the report grouped by WorkType and SubWorkType. I need a YTD total for each group. When I modify my query qryYTD and add the fields WorkType and SubWorkType using "Group By" in the Total row the amounts are properly calculated. When I add a text box using =DLookUp("SumOfValueUponCompletion","qryYTD") as the Control source the display is the YTD total for the first group in the query for all groups in the report. How would I set up the text box to pull the information from the query for the correct group in the report?

Thanks again,

-Jeremy

 

[PaulBricker]

You can add a Where type clause to the function like this.

=DLookup("SumOfValueUponCompletion","qryYTD","WorkType = '" & [WorkTypeTextBoxOnReport] & "'")

The last argument is the Where clause without the word Where. The syntax for the example I posted is for a String datatype. If WorkType is a number then the clause would look like this

"WorkType = " & [WorkTypeTextboxOnReport])

WorkTypeTextboxOnReport is the name of the textbox on your report that contains the WorkType.


Paul

 

[jer007]

Sorry, I'm lost here. I just get an error when I try that string. I'll try to break down the format a bit more. This DB is for different permits that are issued. There is a WorkType Field (ie: New Construction, Renovation, etc.) and a SubworkType Field (ie: Dwelling, Commercial, etc.) The form grouping is set with SubworkType at the top and the WorkType below it. For reporting purposes we need to display the montly total for each WorkType (ie. $$ value of new housing) we also need the YTD total for the same grouping. I don't need a YTD total for the entire SubWorkType. In qryYTD the GroupBy splits the numbers apart quite well. I'm just completely lost when it comes to displaying the information in the report.

When I think it through logically it would be something like Where PermitReport!SubworkType = qryYTD!SubWorkType AND PermitReport!Worktype = qryYTD!Worktype Then display the value associated with WorkType and SubWorkType.

I just don't know how to go about coding that to work properly. I hope my explination makes sense and I appricate all the help.

Thanks

 

[PaulBricker]

Are both fields Text datatypes? If so it would look like this.

=DLookup("SumOfValueUponCompletion","qryYTD","SubWorkType = '" & [SubworkType] & "' And WorkType = '" & [Worktype] & "'")

If they are another datatype like number let me know and I'll rewrite it.
Anyway, try it out and let me know how it goes.

Paul

 

[jer007]

Thanks Paul,

That appears to be working. I'll test it out a while and I'll post back if I run into any more problems.

Once again, thank you.

-Jeremy

 

[jer007]

Paul

I just noticed one problem. I have one WorkType where there is no SubWorkType attached to it. The code you provided does not work with that condition. Is there a way that it can match up the WorkType and the SubWorkType of null?

Thx

 

[PaulBricker]

You can wrap the whole expression in an IIF statement and then test for nulls.

=IIf(IsNull([SubWorktype]),DLookup("SumOfValueUponCompletion","qryYTD","IsNull(SubWorkType) = True  And WorkType = '" & [Worktype] & "'"),DLookup("SumOfValueUponCompletion","qryYTD","SubWorkType = '" & [SubWorkType] & "' And WorkType = '" & [Worktype] & "'"))

This should do it.

Paul

 

[jer007]

Thanks for the help Paul,

It works Great!

 

[HeidiE]

Paul,

I'm a beginner in Access and have the same question as jer007 "I am running a report based on a query which requires the user to input a date range (Criteria: Between [start]AND [END]). This report displays the information within that range and sums the financial information. I am having trouble with two items.

1. How can I display the date range selected by the user on the report?"

Only my query's criteria is as follows (Between #7/1/2004# and 12/31/2004#) Can I display this type of date range in a report?

 

[PaulBricker]

To get a date range into your report you would use something like this in the Control Source for your textbox
="Between " & [start] & " and " & [end]

The values inside the "'s are text that will show in the textbox, and then [start] and [end] will pick up the values entered in the query parameter boxes.

Hope this helps.

Paul