User Input Date From and Too (1 Viewer)

[TelecomScott]

Hey,

Might be a really simple solution that I have overlooked, but I need a query to allow the user to input a from date and a too date, so the query only shows records with a date in that period.

Obviously without the end-user having access to the full query in design view, and preferably without the need for a form, much like "Enter Date" but with two.

Thanks,

Scott

 

[pr2-eugin]

Hello TelecomScott, Welcome to AWF..

What you want is a Parametrized Query.. In the Query grid design, you would see something called as Parameters.. use that..

 

[TelecomScott]

Whats the parameter code?

Thanks,

Scott

 

[pr2-eugin]

If you open the Query Design Grid, you will have something like...

Then use that in your Query as..

SELECT whateverYouWant FROM theTable 
WHERE someDateField BETWEEN[COLOR=Blue][B] [EnterFromDate:][/B][/COLOR] AND [COLOR=Blue][B][EnterEndDate:][/B][/COLOR]

Although you do not need to Declare them as Parameters and simply use them as shown in the Query above. However, it is best to Declare the parameters to right Data Types..

 

[TelecomScott]

and one more while I'm being stupid and forgetting how to use access, I need to display the data and then display the SUM of the fields shown as well as the fields themselves.

 

[pr2-eugin]

Would you mind if you could show some example?

 

[TelecomScott]

Report From DD/MM/YYYY to DD/MM/YYYY
Sales Person - {Sales Person}

Total In Total Out Sales Out Company Out
100.00 50.00 25.00 25.00
100.00 50.00 25.00 25.00
100.00 50.00 25.00 25.00

300.00 150.00 75.00 75.00 <----- Totals

 

[pr2-eugin]

You either need two Queries, one to obtain the data and the other to sum the results of the first query.. But then you will not be able to see the two together.. So use Reports, based on the First query and in the footer set four text boxes and just use Sum to get the appropriate sum values..

 

[TelecomScott]

Sorted Forgot how powerful reporting was One more thing

I need sometimes Input Sales Persons name and sometimes make it blank, but if I leave it blank it shows none?

 

[pr2-eugin]

Just add Is Null to the condition.. something like..

SELECT whateverYouWant FROM theTable 
WHERE ((someDateField BETWEEN [EnterFromDate:] AND [EnterEndDate:]) AND (agentName = 'Sam'[COLOR=Red][B] OR Is Null[/B][/COLOR]))