Solved

criteria

Posted on 2002-03-27
5
262 Views
Last Modified: 2008-02-01
when creating a query critieror using the following

Expr1: Year([date])

[Enter year:]

this works great but how can i make the popup box that appears to type the year into already have text in ie the currant year i know that

Year(Date())

displays all my records of the currant year. A combination of the 2 would be great. This would mean that i do not have to type in the currant year every time, but if i wanted to view the previous years records i ccould do that esily

cheers migs
0
Comment
Question by:a_migdal
5 Comments
 
LVL 1

Expert Comment

by:TheNextStep
ID: 6899253
Use a pop-up form with an unbound field on it, and set its defalut value to Year(Date()).  Next set the criteria in your query to forms![TheNameOfYourPopupForm]![TheNameOfYourField].

The query will use the value on the form.  You can change it or leave it as is.  Place a button on the form to execute your query.
0
 
LVL 3

Expert Comment

by:Bob Scriver
ID: 6899559
You could go a step further with with the pop-up form and instead of using a text box you could use a combo box that would have as its RowSource something like the following:

SELECT Year([DateInTable]) AS Expr1
FROM tblYourTable
GROUP BY Year([DateInTable])
ORDER BY Year([DateInTable]) DESC;

The default value for the combo could be Year(Date()) and the criteria in your query could then reference the combobox on the pop-up form.

In the OnLoad event procedure of the popup form enter the following:

me![ComboBoxName].setfocus
me![ComboBoxName].dropdown

This way all years that are possibile in your table are in the drop-down of the combobox.  They are sorted Descending so that the top one is the current one and you will only have to pick the appropriate year that you wish to run.  No typing.

Bob Scriver
0
 
LVL 2

Accepted Solution

by:
kiddiec earned 25 total points
ID: 6903126
Create the following Function in a module

Function WhichYear()
WhichYear = InputBox("Which Year", "Enter Year in Two digit mode", Format(Date, "yy"))

End Function


then call the function from the criteria of the query, as follows

whichyear()

This will put up an input box with the current year as the default.  Of cours, by amending the format of the date you can make it match your data
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7079523
for a_migdal

No comment has been added for the last two months.
So it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
 - Answered by: kiddiec  
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7099939
Thanks for your help, Nic;o)
Finalized by Moondancer - EE Moderator
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Password on a button in Access 2013 7 34
Format vertical text in Access 2016 3 31
Unrecognized Database Format 8 90
MS Access Tables Linking 6 40
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now