using variable between dates in a query then report 2

[Tarnish]

Hi all,

I have a query that produces the mass of records I need to start with, but I need to filter that on a date range. I know how to do that in a query but only if I hard-code the date ranges into the query (using 'Between' in the criteria of the date field in question). What I'd like to know is how to set it up so I'll be prompted for the dates (beginDate and endDate) so I can run the query and input whatever dates I need to use for the range at the time I need to results.

Thanks, in advance, for any direction.
T

 

[straybullet]

Between [Enter Start Date] And [Enter End Date]

will prompt you to fill in the dates each time you run the query

Let them hate - so long as they fear... Lucius Accius

 

[Tarnish]

So easy I feel dirty now.

I am, nonetheless, grateful!

T

 

[dhookom]

As long as you are feeling dirty, kick your application development up a notch and never use parameter prompts. Use controls on forms for all filtering. Prompts are so quick and dirty. They easily forget their values and can't be trusted in longer term relationships. Values in controls can be much more flexible, they persist until you close the form (relationship), you can test for integrity, and provide a much better appearance.

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]

 

[Tarnish]

Thanks for the reply, Duane.

I understand what you're saying but I can't quite think how I'd execute it in this situation.

I could make a form and have two places for dates which would constitute the date range, but what effect would that have on the type of query I used. I'd think my query would be different then the parameter one I've set up.

Assumming you answer that question, how would it change if I wanted to add another 'variable' piece of data to input in the form.....for example I wanted to limit the report to both a date range and a particular employeee (just as an example). Does that change the query?

I'm always looking to step it up a notch. Thanks for any replies.

T

 

[straybullet]

Rather than "Between [Enter Start Date] And [Enter End Date]", what DH is suggesting would appear more like
Between Forms!frmDateRange.[txtStartDate] And Forms!frmDateRange.[txtEndDate] where txtStartDate and txtEndDate are controls on an unbound form. Then a button on that same form could be used to run the report or query. The query would automatically find the values needed for the criteria and filter accordingly.
Adding Forms!frmDateRange.[txtEmployeeName] to the criteria in the query and creating the corresponding control on your form would further filter.
Like Forms!frmDateRange.[txtEmployeeName] &"*" would accommodate txtEmployeeName being left blank, returning all records in the desired date range.
Basically, so long as the form is not closed, you can refer to the controls on it and use their values repeatedly (ex: display the date range used to filter the query as part of a report's title)

Let them hate - so long as they fear... Lucius Accius

 

[Tarnish]

Stray!

Thanks again. Between you and Duane here, and another related post I found on a different site, I'm in business. The report has an "on open" event that pops up the form, where you enter the parameters, then hit the "calculate report" button which really just hides the form and allows the report to continue using the newly added parameters.

Then, on the report close event there's a docmd.close acform, "frmParameters" that closes the hidden form.

The "like" and "*" was very helpful in your last post. I remember seeing something about that kind of stuff but dont' remember where. Need to brush up on what kind of words/phrases can be used in the criteria row in querys I guess....

Again, thanks!

T

 

[dhookom]

I actually never use the method that opens a form in Windows Dialog mode when the report opens. I always start from an open form with the controls for filtering. I also try to not use
[green]Between Forms!frmDateRange.[txtStartDate] And Forms!frmDateRange.[txtEndDate][/green]

Assuming you have a command button on frmDateRange, my typical method with have this code in the On Click event of the command button:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStartdate) Then
    strWhere = strWhere & " And [datefield]>=#" & _
        Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEnddate) Then
    strWhere = strWhere & " And [datefield]<=#" & _
        Me.txtEndDate & "# "
End If
DoCmd.OpenReport "rptYourRpt", acPreview, , strWhere

This solution allows me to leave control blank if I don't want to use them. It also keeps the report and its record source separate from the form.

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]

 

[Tarnish]

Thanks Duane,

I'll have to look at that a little more closely, but I get the idea and that may be a better solution. I was going to ask one more question and I think the above might be a different method that answers the question, but I'm still interested in an answer if either of you (or anyone else) is around and willing.

The "Like" ...... & "*" works to allow me to NOT use a dropdown box I have that fiilters the report by an employee's name (no selection gives me all the results). So the question is this: is there an alternative to the Between forms!myform.mycontrol and forms!myform.myothercontrol in the query criterion that will accept NO values appearing in those date controls? I tried Like/"*" again just to eliminate it as a possibility and, of course, it didn't work.

I'm thinking, after your (Duane's) last post, there is NOT an alternative and that's why you use that last technique you posted.

Regardless if anyone wants to respond to othis question (I know I'm being greedy!), I really have learned a bit of valuable info and thanks to you both.

T

 

[Tarnish]

Duane,

That code is a good alternative. It doesn't require two iterations of the query (1 i use in other areas of my app and has no criteria filtering, the second I would need to add the criteria if I went with the other way).

Assumming I can use similar code for adding different parameters to the form, all will be well.

I don't really get the need for the strWhere = "1=1 ", though.

T

 

[dhookom]

You could allow the controls to be null and use something like:

Between Nz(Forms!frmDateRange.[txtStartDate], [dateField]) And Nz(Forms!frmDateRange.[txtEndDate],[DateField])

This would substitute the value of the date field for the criteria if it was left blank. Null date field records would still be excluded using this method.


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]

 

[dhookom]

[green]I don't really get the need for the strWhere = "1=1 ", though.[/green]
This allows me to add conditions to strWhere always beginning with

   strWhere = strWhere & " [b][red]AND[/red][/b]..."

If I didn't begin with "1=1 " then I would have to test to determine if the [red]AND[/red] is required or not.

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]

 

[Tarnish]

Duane,

Thanks again. This thread has been extremely helpful. I knew there was something "placeholder-ish" about that 1=1, but I just didn't see it until you responded.

T