Default Date for a Query

  • Thread starter Thread starter Frank The Novice
  • Start date Start date
F

Frank The Novice

I have a table with several years worth of data. I have a query that will
retrieve the data for a particular year or month. 90% of the time the data I
need to retrieve is in the current month and year, but not always. Is there
any way to have a default value of the current month and year so when the
[Enter Month] or [Enter Year] function runs, I can just hit the enter key and
have it default to the current month and year?
 
I have a table with several years worth of data. I have a query that will
retrieve the data for a particular year or month. 90% of the time the data I
need to retrieve is in the current month and year, but not always. Is there
any way to have a default value of the current month and year so when the
[Enter Month] or [Enter Year] function runs, I can just hit the enter key and
have it default to the current month and year?
Click to expand...

Your best bet may be to have a little form named frmCrit, open when you run
the query (or the report based on the query). It would have two textboxes
txtYear, default value =Year(Date()), and txtMonth, default value
=Month(Date()). For criteria use

=Forms!frmCrit!txtYear
=Forms!frmCrit!txtMonth

It's handy to put a button on frmCrit to launch the desired report.
 
That doesn't seem to work, Karl. My expression looks like this "IIF([Enter
Month] Is Null, Month(Date()), [Enter Month])". When I run the querry, the
Enter Month prompt comes up twice, and it doesn't due any filtering.

Frank

KARL DEWEY said:
IIF([Your prompt] Is Null, Year(Date()), [Your prompt])


--
KARL DEWEY
Build a little - Test a little


Frank The Novice said:
I have a table with several years worth of data. I have a query that will
retrieve the data for a particular year or month. 90% of the time the data I
need to retrieve is in the current month and year, but not always. Is there
any way to have a default value of the current month and year so when the
[Enter Month] or [Enter Year] function runs, I can just hit the enter key and
have it default to the current month and year?
Click to expand...
Click to expand...
 
cough the reason why it aint working is because you dont use it as an
expression you use it as a criteria

your expression should be

month of date field: month([date field])

and the criteria should be

IIf(IsNull([enter month]),Month(Date()),[enter month])

and for the year

year of date field: Year([date field])

and the criteria

IIf(IsNull([enter year]),Year(Date()),[enter year])

if you translated it to sql it would be

SELECT [date field], Month([date field]) AS [month of date field],
Year([date field]) AS [year of date field]
FROM yourtable
WHERE (((Month([date field]))=IIf(IsNull([enter month]),Month(Date()),
[enter month])) AND ((Year([date field]))=IIf(IsNull([enter
year]),Year(Date()),[enter year])));

so basically you can just press enter twice and it will return the
current data you could combine it into one


year and month of date field: month([date field]) & "/" & year([date
field])

and the criteria

IIf(IsNull([enter month and year in mm/yyyy format]),month([date()]) &
"/" & year([date()]),[enter month and year in mm/yyyy format])
this will allow you to enter 08/2008 to get the information for the
month of 08 in the year of 2008

hope this helps

Regards
Kelvan
 
Thank you. This also worked, but Kelvan's solution was simpler.



John W. Vinson said:
I have a table with several years worth of data. I have a query that will
retrieve the data for a particular year or month. 90% of the time the data I
need to retrieve is in the current month and year, but not always. Is there
any way to have a default value of the current month and year so when the
[Enter Month] or [Enter Year] function runs, I can just hit the enter key and
have it default to the current month and year?
Click to expand...

Your best bet may be to have a little form named frmCrit, open when you run
the query (or the report based on the query). It would have two textboxes
txtYear, default value =Year(Date()), and txtMonth, default value
=Month(Date()). For criteria use

=Forms!frmCrit!txtYear
=Forms!frmCrit!txtMonth

It's handy to put a button on frmCrit to launch the desired report.
Click to expand...
 
Thank you. This also worked, but Kelvan's solution was simpler.
Click to expand...

Simpler to implement... but more timeconsuming for the user in the long run.
Both techniques have their place, but I'll usually use a form rather than
prompts.
 
i do agree with john you should never allow the end users to access
the database directally only through a form but of course if this is
for you as the database admin (and i use that term for you loosly no
offence) to access it only then i see no problem i personally as a DBA
hate using forms i find them more time consuming.

Regards
Kelvan
 
Back
Top