Getting Parameters into queries

B

Bell

I'm having the following expression in a query.

O0503: Sum(IIf([Category]="Outcome" And [Period]="200503";[Amount]))

Now, instead of having "200501" "....02" "....03" written in each column I
want to base the year on a parameter value. Hence I would like to have
something looking more like below

O0503: Sum(IIf([Category]="Outcome" And [Period]="Parameter Value" &
03";[Amount]))

where Parameter value would look somthing like [Enter year yyyy]

Any suggestions of how to make this work?

Thanks in advance / Bell
 
B

Brian

You can have the user enter the parameter on the fly as in your example:
[Enter date], but it is much easier to check for the correct format, etc. by
placing this on a form where you can use the format and input mask of the
control as well as VBA to ensure that the parameter is input in the correct
format.

So...have the user enter/select the parameters in text/combo boxes on a
form, then click a button to run the query. Then, you can refer to the
parameters in your query as:

[Forms]![Form1]![txtBox1]
[Forms]![Form1]![txtBox2]
[Forms]![Form1]![cmbBox1]

In your query, you might replace the "200503" with [Forms]![Form1]![txtBox1]
(no quotes).
 
M

Mirek

You was very near. Type the expression like this:
O0503: Sum(IIf([Category]="Outcome" And [Period]=[Give me the year yyyy:] &
"03";[Amount]))

Mirek.
 
B

Bell

I thank you both, been looking more closesly into working with forms instead
and it indeed look much more professional.

Best regards Bell

"Brian" skrev:
You can have the user enter the parameter on the fly as in your example:
[Enter date], but it is much easier to check for the correct format, etc. by
placing this on a form where you can use the format and input mask of the
control as well as VBA to ensure that the parameter is input in the correct
format.

So...have the user enter/select the parameters in text/combo boxes on a
form, then click a button to run the query. Then, you can refer to the
parameters in your query as:

[Forms]![Form1]![txtBox1]
[Forms]![Form1]![txtBox2]
[Forms]![Form1]![cmbBox1]

In your query, you might replace the "200503" with [Forms]![Form1]![txtBox1]
(no quotes).

Bell said:
I'm having the following expression in a query.

O0503: Sum(IIf([Category]="Outcome" And [Period]="200503";[Amount]))

Now, instead of having "200501" "....02" "....03" written in each column I
want to base the year on a parameter value. Hence I would like to have
something looking more like below

O0503: Sum(IIf([Category]="Outcome" And [Period]="Parameter Value" &
03";[Amount]))

where Parameter value would look somthing like [Enter year yyyy]

Any suggestions of how to make this work?

Thanks in advance / Bell
Click to expand...
Click to expand...
 
Top