Date Query

[asafb]

Hello. I would like to create a very simple form where the user enters the month in a pull-down and then you hit a button and it performs a query based on a table under a field called "date entered" (let's assume)

So,

february, i hit retrieve records and it will show me all the records who were entered in february.

any help?
thanks!!!!!!
asaf

 

[LittleJess]

in your query you will need to create a field that looks up the entry in the form.
This is how i have done it in the past,

Field - Expr1:[Forms]![frmDateform]![cboMonthselected]
Table -
Sort -
Show - Check box to show
Criteria - [tblYourtable]![Themonth]

this will allow whichever value you selected in the combo box (cbomonthselected) to give that value to the query and it will run for only that month.

Hope that helps

 

[asafb]

hello thank you for your post.

however in my table, the dateentered field is in a short date format like this

NAME: JOE BLOW
DATE ENTERED: 05/21/2003

NAME: JOHN WOOSTER
DATE ENTERED: 05/20/2003

will your query still work since the field is in short date format?
asaf

 

[raskew]

There's no need to have a month field in your table. Here's sample query-SQL applied to the Orders table in Northwind. When prompted enter a mm/yyyy between 08/1984 and 06/1996 (the limitations of the table).

PARAMETERS [enter mm/yyyy] Text;
SELECT Orders.OrderID, Orders.OrderDate
FROM Orders
WHERE (((Orders.OrderDate) Between DateValue([enter mm/yyyy]) And DateAdd("m",1,DateValue([enter mm/yyyy]))-1));

 

[asafb]

this is good but i need the *day* as well....

let's make it more simple

i want a form with "Beginning date" and "end date" and the short date as the default value, then a button that says "show records"

I.E.

04/01/2003
to
05/02/2003

query will produce all records between those dates as well as including those dates.

 

[LittleJess]

You could just put in a parameter value on your query.
Field - Orderdate
Table - Orders
Sort -
Sum - Where
Show - Check box to show
Criteria - Between [start date] And [End Date]

Then right click in the grey in the top pannel of the query and go to parameters, in this type start date and change the value to Date/Time then put End date in and also put to Date/time.
This will create a parameter value so everytime you run the query a msg box will appear asking you for the start date and end date. then all resultsa will be given between this time.

Hope that helps