Problem about parameters in query

[ryantam626]

PARAMETERS [forms]![queryform]![end date] DateTime, [forms]![queryform]![start date] DateTime, [forms]![queryform]![work type] Text ( 255 ), [forms]![queryform]![project id] Text ( 255 ), [forms]![queryform]![work designation] Text ( 255 ), [Forms]![QueryForm]![Machine Designation] Text ( 255 );SELECT [Progess Table].[Date Of Work], [Progess Table].[Work Designation], [Machine Work-Progress Table].[Machine Designation], [Progess Table].[Work Type], [Progess Table].[Project ID], Sum([Progess Table].[End increment]-[start increment]) AS [Increment Difference]
FROM [Machine List] INNER JOIN ([Progess Table] INNER JOIN [Machine Work-Progress Table] ON [Progess Table].[Progess ID] = [Machine Work-Progress Table].[Progess ID]) ON [Machine List].[Machine Designation] = [Machine Work-Progress Table].[Machine Designation]
WHERE ((([Progess Table].[Date Of Work]) Between [Forms]![QueryForm]![Start Date] And [Forms]![QueryForm]![End Date]) AND (([Machine List].[Production Machine])=True) AND (([Machine Work-Progress Table]![Machine Designation]=[Forms]![QueryForm]![Machine Designation] Or [Forms]![QueryForm]![Machine Designation] Is Null)=True));

Now I am using

[Machine Work-Progress Table]![Machine Designation]=[Forms]![QueryForm]![Machine Designation] Or [Forms]![QueryForm]![Machine Designation] Is Null

in a field to use a combobox as a criteria and return all records if combobox is blank. The problem is when I define the parameters, it wont return anything if I dont select something in the combobox, how can I make it work? (I need to define the parameters because it will be changed to a crosstab query.)

[June7]

I have never use PARAMETERS.

Here is one technique for filtering a form http://datapigtechnologies.com/flash...tomfilter.html.

[orange]

That is not how Parameter Query works.
see this for simple example of Parameter Query

http://www.techonthenet.com/access/q...parameter3.php

or this for more details

http://msdn.microsoft.com/en-us/library/office/bb208916%28v=office.12%29.aspx

[June7]

I have seen PARAMETERS clause before. Review http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

I just don't understand it much, not seeing benefit in its use.

[orange]

It is much like using [Enter Name] or whatever in a query.
It prompts for each defined Parameter.
However, if you have to use a parameter value more than once, you only have to enter the value once.

Don't know that there are many benefits, but you can set up some "templates" for users, and declare data type and ensure the SQL is sound, b efore handing it to user.


Here's more from Martin Green's site
http://www.fontstuff.com/access/acctut01.htm

[June7]

My experience with Access query popup input prompts is if the same criteria parameter is identical under multiple fields, prompt will popup only once and the input will apply to all. So still not seeing benefit to PARAMETER clause, except maybe being able to restrict the input type. However, I never use popup input prompts.