Report/Query Help

[barrymungall1]

Hi,

I have a simple table with various values, one of which is a date/time stamp.

I have a query, that references data from this table. My query Sum's a total dollar value of a field, and also counts the # of unique entries where a particular criteria is met (just a true/false criteria).

This works, and populates appropriately. Next, I am trying to add a date picker so to speak. I drag my query into my report, and still works, except no matter what I try to do to make a simple way in my report to get these values IF between certain dates...I just can't figure it out. Any ideas?

I've tried in this same query: Between [Table1].[ReportStartDate] and [Table1].[ReportEndDate] and selecting 'Where' as the total function. Obviously I made a 'Table1' with two fields named as such, with the value I'd like.

I'm probably going about this all wrong. I'm a super-amateur!

 

[DavidAtWork]

I'm a little confused at where the criteria is coming from, are you trying to use the date picker(s) to set your date parameters? If so then using the WHERE in your Total row is correct but in the Criteria line for your date field you need to use the wizard to locate the controls on your form and should read something like:
Between [Forms]![myForm]![myDatePicker1] And [Forms]![myForm]![myDatePicker2]

David

 

[ConnorGiles]

Im sure a Parameter Query may solve your problem if a date picker is what i'm thinking it is - (i'm thinking you already have a date next to a record)

To solve this there are many samples of parameter queries for dates on google

 

[barrymungall1]

I'll re-explain a bit better and hope there's an easy answer..

In my query, the first column is a dollar figure - I have the totals toggled, and have it summing the value. The next column is a unique primary key number. I have it counting these in the total. Next is a column with a true or false statement, and I have it set to 'where' it's true. Finally, the last column and the one that isn't working is a date completed column. I have dragged this whole query into a report, and all the values populate except when this date column is setup, mostly because I don't know what 'code' to put into the criteria.

All I want is a dynamic/simple way to update the filter of this date completed column in my query. For e.g., when I open the report with this query attached, I'd like to basically choose a date range within a text box or something, and have it retroactively update the criteria in the date completed query column. This way, I can report on the counts, sums and so on of the other fields, for whichever date range I see fit 'on the fly'.

Hope this makes more sense?

 

[ConnorGiles]

As said before if you wish to pick a date through your own input - Parameter Queries are for you (They prompt the user for input of your choosing in the query).

 

[DavidAtWork]

"I'd like to basically choose a date range within a text box or something"
Yes Barry this is as I thought, whatever contains your textbox or date picker needs to be open when the query runs and look at my ealier reply to see how you set the criteria line in your query, are you familiar with using the Wizard?

David

 

[vbaInet]

"I'd like to basically choose a date range within a text box or something"
Yes Barry this is as I thought, whatever contains your textbox or date picker needs to be open when the query runs and look at my ealier reply to see how you set the criteria line in your query, are you familiar with using the Wizard?

David

... in addition to David's suggestion, remember to add those textboxes as parameters in your query - selecting the Date/Time data type. And on the form Format them as any Date type of your choice so that the date picker becomes visible.

 

[barrymungall1]

I think I've got it figured out. I used [ ] in my query, and it's working perfect now - it just prompts when I open the report, which is just fine with me.

Is there a way to, based off of my inputs of the date when I open the report, to have a label somewhere on my report that auto populates with these inputs?

This way the report is 'done' and i dont have to manually list what the date range is.

 

[ConnorGiles]

[] is a parameter query

and by " i dont have to manually list what the date range is"

do you mean you dont want to enter dates everytime you open the report?

It may also help if you have a screenshot showing us what you have

 

[barrymungall1]

I'm OK entering it when I open the report.

Once it's open, I'd like a label defining what the report is. If I chose a date range of Sep 1 - Sep 8, I'd like the label to autopopulate with that date range.

So the prompt is OK, just figuring out a way to input the prompt values into a label without any additional manual work.

 

[ConnorGiles]

Ahh - Unfortunately i do not know how this is achieved as i am a novice i shall leave you in the capable hands of Vba and David - Hope ive helped you in any way i can

 

[vbaInet]

Once it's open, I'd like a label defining what the report is. If I chose a date range of Sep 1 - Sep 8, I'd like the label to autopopulate with that date range.

You can carry the values across with variables and function(s) but you're better off using a form as DavidAtWork already advised.

That way you can simply set the Control Source of the textboxes on the report to the same references you used in the query. There are also other advantages of using a form.