Print query criteria on the report

[Yu2008]

I have a multiple criteria form, the criteria what I give to the form is the product category and a date range, is there a way that the criteria can be coverted to words and printed on the report the form generates? If can, this report will be easier to read and I bet my boss will be impressed

Any idea will be appreciated. thanks.

 

[boblarson]

If the form remains open you can just put a text box on your report and set its control source to something like:

="Criteria Selected were: Product Category - " [Forms]![YourFormNameHere]![YourProductCategoryControlHere] & " and From " & [Forms]![YourFormNameHere]![YourBeginningDateTextBoxNameHere] & " to " & [Forms]![YourFormNameHere]![YourEndingDateTextBoxNameHere]

 

[Yu2008]

Thanks! Bob.

I did, but Access give me a error message "The expression you entered contains invalid syntax. You may have entered a operand without an operator". There must be something wrong what I did. 8-(

="Criteria Selected were: Select Model - " [Forms]![Inventory Balance Inquiry Form]![cboSelectModel] & "Select Warehouse - "[Forms]![Inventory Balance Inquiry Form]![cboSelectWarehouse] &" and From " & [Forms]![Inventory Balance Inquiry Form]![TxtFrom] & " to " & [Forms]![Inventory Balance Inquiry Form]![TxtTo]

Thanks,
Yu

 

[boblarson]

="Criteria Selected were: Select Model - " & [Forms]![Inventory Balance Inquiry Form]![cboSelectModel] & " Select Warehouse - " & [Forms]![Inventory Balance Inquiry Form]![cboSelectWarehouse] & " and From " & [Forms]![Inventory Balance Inquiry Form]![TxtFrom] & " to " & [Forms]![Inventory Balance Inquiry Form]![TxtTo]

You also needed a space before the word SELECT in Select Warehouse and another space after the & and " in the And FROM.

 

[Yu2008]

It works this time! Thanks Bob.

But got two other questions again, hope you don't mind.

1) In stead of all combined together like a sentence, Is there a way I can seperate the different criteria by paragraph? So it will look like this in the report:

Criteria Selected were:
Select Model - model name
Select Warehouse - warehouse name
From date to Date -

2) In the printed report, "Select Warehouse -" followed with the Warehouse ID# in stead of the warehouse name, how do I amend my syntax to let it show the name but ID?

Thanks,

Yu

 

[boblarson]

Just add a

& vbCrLf &

where you want the breaks to occur.

As for #2, you will need to figure out a way to include the name on the form so you can refer to it.

 

[boblarson]

Actually, you probably already have it in the combo box so you can try this:

[Forms]![Inventory Balance Inquiry Form]![cboSelectWarehouse].Column(1)

or

[Forms]![Inventory Balance Inquiry Form]

And if that doesn't work, you can put a hidden text box on the form and in the combo's after update event you put:

Me.YourHiddenTextBoxName = Me.YourComboName.Column(1)

and then in the report refer to the hidden text box instead of the combo.

 

[Yu2008]

Hi Bob, thank youuuuuu.

Q1 : But vbCrlf doesn't work to me. When I run the report, Access asking me to enter parameter value for the vbCrlf. ?! below is how I code:

="Criteria Selected were: & vbCrLf & Select Model - " &[Forms]![Inventory Balance Inquiry Form]![cboSelectModel] & vbCrLf & " Select Warehouse - " &[Forms]![Inventory Balance Inquiry Form]![cboSelectWarehouse] & vbCrLf & " and From " & [Forms]![Inventory Balance Inquiry Form]![TxtFrom] & " to " & [Forms]![Inventory Balance Inquiry Form]![TxtTo]

Q2: on my form, the warehouse name did include and in the drop down list it shows the name all correct. But how come on the printed report the warehouse criteria becomes to ID# .... hmm.... Here is what I Put on row source of combo control :

SELECT [Warehouse Location].[ID], [Warehouse Location].[WarehouseName] FROM [Warehouse Location];

Yu

 

[Yu2008]

Actually, you probably already have it in the combo box so you can try this:

[Forms]![Inventory Balance Inquiry Form]![cboSelectWarehouse].Column(1)

or

[Forms]![Inventory Balance Inquiry Form]

And if that doesn't work, you can put a hidden text box on the form and in the combo's after update event you put:

Me.YourHiddenTextBoxName = Me.YourComboName.Column(1)

and then in the report refer to the hidden text box instead of the combo.

Got it, thanks! I'll try!

 

[boblarson]

="Criteria Selected were:" & vbCrLf & " Select Model - " & [Forms]![Inventory Balance Inquiry Form]![cboSelectModel] & vbCrLf & " Select Warehouse - " & [Forms]![Inventory Balance Inquiry Form]![cboSelectWarehouse] & vbCrLf & " and From " & [Forms]![Inventory Balance Inquiry Form]![TxtFrom] & " to " & [Forms]![Inventory Balance Inquiry Form]![TxtTo]

 

[Rich]

Isn't vbCrLf a vb function and thus not available in a control source?

 

[boblarson]

Good point Rich-

brain fart time!

Instead of VbCrLf you would need to use

& Chr(13) & Chr(10) &