Run Query from a Form Help with field filter

[dj_mix]

I am trying filter my query from a form to print a report.

The orignal query includes all the counties field = [County]

I would like to click on a certain command button on my form and only show
counties that equal ESSEX ...

I have this code so far but it will still show all counties in my report..

most likey the error is in here ?? stWhere = "[County] = ESSEX"

Private Sub ActiveEssex_Click()
On Error GoTo Err_ActiveEssex_Click

    Dim stDocName As String
    Dim stWhere As String

    stDocName = "001 - Status Report 1 - General - Active"
    stWhere = "[County] = ESSEX"
    DoCmd.OpenReport stDocName, acPreview, stWhere
    DoCmd.Maximize
    
Exit_ActiveEssex_Click:
    Exit Sub

Err_ActiveEssex_Click:
    MsgBox Err.Description
    Resume Exit_ActiveEssex_Click
End Sub

 

[lightray]

That's not really your stDocName is it?

 

[gemma-the-husky]

try this

"[County] = " & chr(34) & "ESSEX" & chr(34)

 

[dj_mix]

That's not really your stDocName is it?

001 - Status Report 1 - General - Active is the name of the report

I know it's long but that how it was setup at work..
I will try what was mention when I go back to work on monday...

 

[dj_mix]

try this

"[County] = " & chr(34) & "ESSEX" & chr(34)

will there be a problem with this part of the code?

DoCmd.OpenReport stDocName, acPreview, stWhere

I seen somehwere where the condition should be the 4th arugment?

like

DoCmd.OpenReport stDocName, acPreview, , stWhere

 

[gemma-the-husky]

when you type it in, the access syntax checker should guide you, or look at help (click on the word openreport and press F1)

 

[dj_mix]

Ok I got it working thanks again..

the syntax for print preview did require a space.. stwhere needs to be the 4th argument ..

DoCmd.OpenReport stDocName, acPreview, , stWhere

Private Sub ActiveEssex_Click()
On Error GoTo Err_ActiveEssex_Click

Dim stDocName As String
Dim stWhere As String

stDocName = "001 - Status Report 1 - General - Active"
stWhere = "[County] = ""ESSEX"""
DoCmd.OpenReport stDocName, acPreview, , stWhere
DoCmd.Maximize

Exit_ActiveEssex_Click:
Exit Sub

Err_ActiveEssex_Click:
MsgBox Err.Description
Resume Exit_ActiveEssex_Click
End Sub

 

[gemma-the-husky]

yeah, - out of interest using the chr(34) makes it easier to check you have it right

stWhere = "[County] = " & chr(34) and "ESSEX" & chr(34)

is generally easier to get right than

stWhere = "[County] = ""ESSEX""",

especially when the strings get even more complex.

 

[dj_mix]

yeah, - out of interest using the chr(34) makes it easier to check you have it right

stWhere = "[County] = " & chr(34) and "ESSEX" & chr(34)

is generally easier to get right than

stWhere = "[County] = ""ESSEX""",

especially when the strings get even more complex.

I'm glad you used the chr trick I never knew about it and now when I get into complex code I will keep it in mind.