Default Date for a Query

[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?

 

[KARL DEWEY]

IIF([Your prompt] Is Null, Year(Date()), [Your prompt])

 

[John W. Vinson]

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?

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.

 

[Frank The Novice]

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

IIF([Your prompt] Is Null, Year(Date()), [Your prompt])


--
KARL DEWEY
Build a little - Test a little

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?

 

[Lord Kelvan]

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

 

[Frank The Novice]

That did the trick. Thanks so much for your assistance.

 

[Frank The Novice]

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

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?

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.

 

[John W. Vinson]

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

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.

 

[Lord Kelvan]

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