Report Filter

[eavan]

hey,

i am trying to print a report (from a form) which is using a textbox value as a query.However, it doesn't seem to be picking up the filter value. It keeps on prompting me for the query element which i have set out in Where Condition in the macro. Any ideas?

 

[Remou]

Please post the Where statement.

 

[eavan]

[Test scripts]![Tester]=[Forms]![Assigned test list]![Text29]

[Test scripts]![Tester]being the table and field i have used in a query to bring up values. I have tried inserting the query with field as well.

Forms]![Assigned test list]![Text29] being the form which the print button is on with the text box value i need as a query source.

 

[Remou]

Try
[Tester]=[Forms]![Assigned test list]![Text29]

PS You may regret later that you did not use meaningful names for your controls, for example, txtTester rather than Text29.

 

[eavan]

probably regrettin ever taking on this db )

[Tester]=[Forms]![Assigned test list]![Text29] didn't work probably cos [Tester] is a field on a table in the table Test scripts. Which is then used in a query. Once i enter the Tester value manually in the prompt then the report works. It only needs to get the value passed in. Quite frustating.

 

[Remou]

What happens if you enter:
[Tester] = 1
If Tester is numeric, or
[Tester] = 'abc'
If it is text?
If this does not work, you must find the name of the field (exactly as it appears, including any typos) from the query that the report is based on.

 

[eavan]

i created a new report to try and get around this. I am using a table as the control source rather than the query which was giving me a proplem. I need to create a filter to use Tester.Would you know what piece of code i could drop in.

effectively i want to add where condition = tester chosen in a textbox (example text29 from earlier)...what do you think?

Private Sub Command31_Click()
On Error GoTo Err_Command31_Click

Dim stDocName As String

stDocName = "John"
DoCmd.OpenReport stDocName, acNormal

Exit_Command31_Click:
Exit Sub

Err_Command31_Click:
MsgBox Err.Description
Resume Exit_Command31_Click

End Sub

 

[Remou]

No, because I still do not know for sure the name of the field which is to be filtered on.

I need to find out why "[Tester]=[Forms]![Assigned test list]![Text29] didn't work" This is not to do with "cos [Tester] is a field on a table in the table Test scripts." Unless it is not "then used in a query".

That was the trend of the questions I was asking. If my suggestions did not work, it would imply one path, if they did, another path.

 

[eavan]

when i put [tester] =1 in the where condition of the openReport macro it still appeared with the [tester] prompt. This in my opinion was because i was using a query for the report i was using.But now that i have changed the report to use the table fields it doesn't prompt me for a tester. But now i need to filter the table results.

 

[Remou]

The Where statement is exactly the same, which is why it is important to get it working in the macro. If it does not work there, it will not work in code.

Private Sub Command31_Click()
On Error GoTo Err_Command31_Click

    Dim stDocName As String

    stDocName = "John"
    'DoCmd.OpenReport stDocName, acNormal
    'Preview is more suitable for testing etc
    DoCmd.OpenReport "John",acViewPreview,,"[Tester]=" &[Forms]![Assigned test list]![Text29]   

Exit_Command31_Click:
    Exit Sub

Err_Command31_Click:
    MsgBox Err.Description
    Resume Exit_Command31_Click
    
End Sub

If Tester=1 works for the table, it should work for the query unless Tester has not been included in the query.

 

[eavan]

hey...

stDocName = "John"
DoCmd.OpenReport "John",acViewPreview,,"[Tester]=" &[Forms]![Assigned test list]![Text29]

causes a syntax error (operator missing)...it picks up the value in text 29 but the syntax is wrong.Looks ok to me.

 

[eavan]

hey..thanks a mill for your help.

i just changed the code around a small bit.Synntax error is gone now. Thanks

Dim stDocName As String
Dim stWhere As String
Dim stFilter As String

stWhere = "[Tester]="
stFilter = "[Forms]![Assigned test list]![Text29]"

stDocName = "John"
DoCmd.OpenReport "John", acViewPreview, , stWhere & stFilter

 

[MTBChik]

I am having a similar situation with this problem. I can get the macro working, but when I actually convert it to VB I get this error:

Syntax Error (missing operator) in query expression ('SM Cat 4').

Here's my code:
'------------------------------------------------------------
' cmdPrint_Click
' This will select which class to print results from
'------------------------------------------------------------
Private Sub cmdPrint_Click()
Dim strTest As String

strTest = Me.cboCategory.Text

'This is just a test Msg
'05/03/2006 And is displaying the correct value...
MsgBox ("strTest = " & strTest)

On Error GoTo cmdPrint_Click_Err

DoCmd.OpenReport "Results", acViewPreview, "qryResults2", strTest, acDialog


cmdPrint_Click_Exit:
Exit Sub

cmdPrint_Click_Err:
MsgBox Error$
Resume cmdPrint_Click_Exit

End Sub

__~o
`\<,
(_)/(_)
MTBChick
The day you stop riding is the day you die.

 

[Remou]

Hello MTBChik
It may be best to use a new thread. You seem to be including several ideas, all at once!
Try something like:
[tt]DoCmd.OpenReport "Results", acViewPreview, , "AField=" & strTest[/tt]
If which ever field you choose for AField is numeric. Or
[tt]DoCmd.OpenReport "Results", acViewPreview, , "AField='" & strTest & "'"[/tt]
If is is text.

 

[MTBChik]

Well, actually, he's trying to grab a value from a text box, I'm trying to grab the value from a combo box...same principle.

BUT, your suggestion worked. Got rid of the reference to the query (because, duh, the report was referencing it anyway) and voila! It works just fine!

Thank you for your help!

__~o
`\<,
(_)/(_)
MTBChick
The day you stop riding is the day you die.