Parameter Question

[delaikhi]

I have a query and I want to make a certain range of dates for the query to run. For example: from 10/6 to 10/7. Can some one please tell me what would be a parameter for this range of date? Should I put it on a query? Thanks

 

[pbaldy]

Typically I'd create a form for the user to enter the dates into, and in the query criteria:

Between Forms!FormName.FirstTextbox And Forms!FormName.SecondTextbox

 

[delaikhi]

Thanks, Paul.
Not sure if I described it clearly!
But situation is this: I have a query, say, Order Query, with Order_Date, Order_ID, Customer_Name etc. I want to enter a range of dates into the Order_Date, i.e 10/7-10/8, as a matter of course, I follow the MS instruction, put in a kind of exprestion in the criteria box of Order_Date, say [From Date?]. Now, my question is, is there any way that I can limit the 2 dates in this kind of query? If yes, what would be the criteria?

 

[pbaldy]

You can have

Between [From date] And [End date]

But like I said, I'd use a form.

 

[namliam]

But like I said, I'd use a form.

I just want to re-itterate this... go with a form instead of parameters

 

[delaikhi]

Paul,
Here is what I've done:
1- I created a form as you suggested, named it DatePicker. And I put 2 Textbox: Date1 and Date2 in the form (both have control source with Order_Date)
2- Then on the criteria of the query I put Between Forms!DatePicker.Date1 And Forms!DatePicker.Date2 (under the field Order_)Date
The result is: MS Access keeps putting the word Forms into bracket: Between [Forms]![DatePicker].[Order_date] And [Forms]![DatePicker].[Order_date]
So please tell me what wrong with this? Thanks

 

[delaikhi]

Paul, thanks a lot for your advice!
One more question: It's OK with the form and query now, but how can I do with report? I have a report based on the Invoice Query and I want to print out this report with the date range in the DatePicker. Thanks again!

 

[namliam]

since the report will run of the query, it should just work fine.

 

[delaikhi]

Thanks guys, any way that I can put in the query criteria a Part of the field? like all customer started with P? Moreover, I also want to put some thing like: if the user does not fill the name of the customer on the form, then by default ALL customers would be sorted out in the query

 

[namliam]

You are probably better off building a filter form that is going to (re) build your where clause ... instead of trying to work with parameters like this.... Parameters are somewhat unflexible where as in coding you can add and remove entire parts of the where clause making it most flexible.

 

[delaikhi]

Yeah namliam, I am trying to do just that. Problem is not sure, how to go around with Null value on the form.

 

[namliam]

well if there is a null value on your form you simply do not add the where clause...

If you search the forum for filter form, search form or something simular I am sure you can find samples of how to do it....

pseudo:
whereclause = ""
If somefieldonyourform is not null then
whereclause = whereclause & "YourField = "& somefieldonyourform
endif
if otherfieldonyourform is not null then
...
endif

Then simply put the whereclause into the query and run your report based on the query/whereclause as per created on your form.