report options via query options (1 Viewer)

hi ,
I have a query that has 2 date fields ( target date) format "23-May-18"
and an expression field "May 2018" not sure if I need that or not for what I am tying to do

is it possible to run the same query that can give me any 1 of 2 results depending on which one selected

1. report on the month I run the query in .

2. let the query just run and report on all the months

thanks
steve

I too use 2 formats of 1 date field in the query, for sorting purposes.

I am going to jump to conclusions (but it might be confusions) here. My ambiguity is that I had to read the question twice before I caught a second way to understand what you are asking.

Here is the difference between your #1 and #2 questions. The difference is that #1 has a WHERE clause to restrict selection of dates. #2 does not. So a static query could be made to do both of those things, but it would be tricky.

My thought is to make TWO queries. Make the first one that gives you every month. It is easier to write. Now COPY that query to make a new query with a different name. Add the WHERE clause to the copy. You then run whichever query you need - the "get it all" version or the "get recent" version.

The "get recent" version MIGHT look vaguely like this.


This takes the current date and subtracts the number of days represented by today's date from the full date. So on the 10th of the month, it subtracts 10 from the date. This gives you the last day of the previous month, so you use ">" rather than ">=" to select the dates.

Or you can use a single query with a parameter input:
Select Month (or leave blank to select all)

Either method will work. Use whichever you find easier to understand.

A start and end date is the most flexible. If you want to ensure that the range is full months, check the dates before applying the criteria.

Where SomeDate Between Forms!yourform!StartDate and Forms!Yourform!EndDate

PS - formatting a date turns it into a string which makes it act like a string rather than a date. NEVER format dates except for display to avoid issues.

hi,

if I have 2 queries would I not need 2 reports?

I was hoping that I was able to use one report and if I ignored the prompt in the query all the results where shown if I used the prompt then just that month was shown

if that is not possible or does it have to be done with a macro

thanks
steve

In this situation, I also use start date and end date textboxes together with an All Dates button
However if preferred you could use a combo for month & year together with the button.

In either case the idea is that the selection made is used to build a WHERE clause for your report record source SQL. Only one report is needed

If you use my suggestion, you would have ONE query.

hi
I have been travelling to china so had no time to look at the response you all sent me of which I appreciate

Pat

I am new to this so just learning
are you telling me to put this in my query

Where SomeDate Between Forms!yourform!StartDate and Forms!Yourform!EndDate

Click to expand...

my form name is "issue details " my table name in "Issues"


so how do I write that

thanks for all your help


steve

Yes - but of course you would need to change all the column and control names.