Query Criteria based on an input date

[webharvest]

I have a database with the following information:

FTA # Date Attended Last name First name assigned person......

Each week I need to generate a report that shows the people that attended for that week, grouped by the assigned person. I only want to show the data for a specific "date attended" (i.e. that days date). I figured I first need to generate a query that only returns the data for that specific date in the "date attended" column. I did that but it is manuel by using the criteria field.

Is there an easy way to type in the date that I want the query to use as the criteria?

Thanks

 

[Brianwarnock]

You can put in the criteria =[Enter date] and a pop up will appear for the date to be entered, or you can drive the query from a form.

Brian

 

[webharvest]

Thanks Brian that worked great.

I know this is the query forum but if I wanted to drive it from a form how do you link the form to the criteria input of the query?

Thanks again

 

[Brianwarnock]

Basically create an unbound form that is one without a record source by not selecting a query or table, then create a text box , lets call it txtEnterdate, you can format this, incuding having input masks thus ensuing correct input,
also on the form create a command button which when clicked runs the query, this is easy todo with the wizard, run query comes under the miscellaneous category. Save the form as frmInputDate.

In the criteria in the query put =Forms!frmInputdate!txtEnterdate having no blanks saves using[] round the names and leeds to less syntax problems prefixing your names means that you can use the same generic name if you wish for the text box and the form.

To run the query Open the form, enter the date click the command button.
This approach is useful for queries more complex than yours with multiple criteria.

Hope this helps.

Brian

 

[shaz123]

How would you do this for two date fields, a from and too date.

 

[Michael J Ross]

for an input box put in your criteria as Between [Enter From Date] And [Enter End Date]

For forms use crieria Between [Forms]![YourFormName]![YourTextBoxFrom] And [Forms]![YourFormName]![YourTextBoxTo]

 

[shaz123]

One more question, when doing this, the input box displays, Enter parameter value how can you change this text?

 

[Michael J Ross]

I don't think you can.

 

[shaz123]

Hi
Thanxs alot for your help, is there any other way i could get around it like coding the inputbox in events prcedure in a form, which when clicked would bring both input boxs, and run the query!!!!!!!!.

 

[Michael J Ross]

You can create a form with two text boxes for the two dates you are searching between, and add a button to run the query using its on click event (form referenced to the query as above).

 

[shaz123]

I tired wat you said, put this in the Query criteria "[Forms]![WorksOrder Import Date Selection From]![Enter From Date] And [Forms]![WorksOrder Import Date Selection To]![ Enter End Date]"

I also created to forms one for the from date and the other with the To date, i also creasted a command button to open the query. However i get he followng result if you look at the doucment I have atthaced. Once i put the date into my form, and click ok to run the query the second inputbox appears, which does not make sense, it should open the date TO form. Can you see what i have done worng

 

[Brianwarnock]

You should create one form with both parameter boxes and the command button on.

Brian

 

[Michael J Ross]

As well as what Brian said you also need to amend your criteria to

Between [Forms]![*WorksOrder Import Date Selection]![Enter From Date] And [Forms]![WorksOrder Import Date Selection]![ Enter End Date]

*WorksOrder Import Date Selection or whatever you call your new form with the two boxes on.

 

[shaz123]

Thnaxs for that have made the approiate amendments however after i have inputed both dates and selected ok the Query runs, but does not bring up any records between the dates selected. I have tested to see if this works when i have typed "Between [Please Enter Document Date From yyyymmdd] And [Please Enter Document Too Date yyyymmdd]" into the criteria, with the same dates and it works. Any Ideas

 

[Michael J Ross]

Can you attach a copy of your mdb and I'll have a look?

 

[shaz123]

Thnxs for quick response the query is running of a linked table which is connected to one of our db2 databases, as it ha some sensitive inofmration i cannot send the linked table, even if i did i dnt think it the query would run on ur end. However I will send u a copy of the query and the form to see if u can notice anything wrong with it, thanx in advance!!!

Hope this is enough

Just in case your wondering the query brings up the following information form the linked table once the two dates have been inputted :

kdnr= customer number
Beldat = document date
BELNR = advice note number

 

[Michael J Ross]

Sorry, I can't see anything obvious as to why it won't bring back any results.

Just a thought do you leave the form open whilst the query runs? You need to.

Can you show me your query design when you use the form to get the dates?

 

[shaz123]

Hey,
I do leave the form open whilst the query runs, when you say show the query desing do you mean show you what happens when I input the dates into the form, If yes i have a atthaced some screen shots in Word. Thanku again

 

[Brianwarnock]

The criteria statement shows this

Between [Please Enter Document Date From yyyymmdd] And [Please Enter Document Too Date yyyymmdd]

which I presume was what was coded for your second test, was the original code for using the form

Between forms![Works Order Import Date Selection]![Enter From Date] And forms![Works Order Import Date Selection]![Enter End Date]

Brian

 

[shaz123]

Yes my original code was:

[Forms]![Works Order Import Date Selection]![Enter From Date] And [Forms]![Works Order Import Date Selection]![ Enter End Date]