Display some search criteria in reports

[Sharky II]

Hi

I've created a nice report, which pulls its fields off a stored query (called TransactionQuery) which is created dynamically after a user searches (called Dynamic_Query). One simple example could be:

SELECT *
FROM TransactionQuery
WHERE (((TransactionQuery.[EntryDate]) Between #1/8/2011# And #8/31/2011#));

Although it'll usually be a little more complex than this. The results of this are displayed on a form, and they can then click a button and print out the results as a report.

The only 'problem' is that i would like to be able to display on the report - somewhere at the top - that this report is for all records "between 1/8/2011 and 8/31/2011".

How can i 'access' this date info and display it on my report?

Thank you!

 

[pbaldy]

Generally you'd get the criteria from a form, and put this in a textbox:

="From " & Forms!FormName.FromTextboxName & " to " & Forms!FormName.ToTextboxName

As I recall, your form is more complicated, but presuming you want all the criteria listed you could build a string in a similar manner used to build your SQL (perhaps in the same code).

 

[Sharky II]

Hey Paul - sorry, i'm a bit confused by that (so nothing new there)!

I don't display the criteria anywhere after the search is done. You search and create the query, the search form closes, and a new form comes up with the results.

On that results form is a button which can open up the report. So the criteria/SQL only really exists in the stored/temp 'Dynamic_Query'.

It's just the date criteria (EntryDate from/to) that i want. Even if i wanted to put the search criteria on the results Form (i could make it hidden, for example) - that is what i'm struggling with.

And i think i need to reverse the date too, because it will be in mm/dd/yyyy format!

 

[pbaldy]

You're building this query from that form with all the various optional criteria, right? If that form remains open, you can do like the above to show the dates on the report. I thought you might want to display all the selected criteria.

 

[Sharky II]

Hi,

I see what you're saying! I could leave the search form open, and 'steal' the dates that are entered. That certainly works! Thanks

However, what if the user closes the search form before generating the report? The guys using this aren't going to be very computer literate.

It could get a little window heavy - the user would have the search form, the results form, and then a report preview on top of that, so i could imagine that they could close the search form.

I thought there might be a way of getting it from the Dynamic_Query query?

If not, at least the way you suggested works - i can tell them to make sure to keep the search window open.

Thanks

Eddie

 

[pbaldy]

I can't think of a way to get it from the query when it's hard-coded like that. Don't leave it on them to leave it open. You're the developer; you control their environment.

Take the close "X" away so they can't close it that way. If you want, behind the button that they would use to "close" it you can hide it instead (set Visible to False). Or hide it behind your search button and close it behind the close button. If/when you hide it, you can make it visible again in the close event of your report or results form, as appropriate to your situation.

 

[Sharky II]

You're a genius!

I've set it to become invisible when you search (and the results pop up).

Then when you close the results page, the search comes back up.

Great! Now i can access those fields with no problem.

Thank you so much - i owe you!! And it even comes up in the correct date format because it's taking it straight from the search box field (and so hasn't gone through VBA translation)!

Cheers

Eddie

 

[pbaldy]

Great! Glad we got it sorted out.