stWhere = Syntax error help needed

[dj_mix]

How do I combine these 2 aruguments into one Line ?

stWhere = "[FPAID] Is Null"
stWhere = "[County] = ""ESSEX"""

I tried

stWhere = """[FPAID] Is Null" & "[County] = ""ESSEX"""

i get a syntax error (missing op) in query expression '("[FPAID] is Null[CCOUNTY]= "ESSEX")'

BTW Code works fine if I one use 1 arugment..

 

[KeithG]

Try below

stWhere = "[County] = 'ESSEX'" & " And [FPAID] Is Null"

 

[dj_mix]

Thankyou that worked great !!

 

[dj_mix]

I trying to add a additional arugument.. and hope someone can help me
these syntax errors are killing me

stWhere = "[County] = 'ESSEX'" & " And [FPAID] Is Null" & " And [LOCATION] =" & "[Please Enter Municipality Name:]"

I get the popup to enter municipality works fine but I'm trying to add

Municipility* <= wild card to end

I tried

stWhere = "[County] = 'ESSEX'" & " And [FPAID] Is Null" & " And [LOCATION] =" & "[Please Enter Municipality Name:]" & "*""

i get a syntax error

I had this in my query that i would like to get rid off

Like "" & [Please Enter Municipality Name:] & "*"

 

[KeithG]

[Please Enter Municipality Name:]"

Is this a parameter value?

 

[dj_mix]

[Please Enter Municipality Name:]"

Is this a parameter value?

Yes it's a Parameter Box

[Please Enter Municipality Name:]" is a popup box where you enter the name of the municipality

this part works fine:

stWhere = "[County] = 'ESSEX'" & " And [FPAID] Is Null" & " And [LOCATION] =" & "[Please Enter Municipality Name:]"

I want to add a * to the end

[LOCATION] =" & "[Please Enter Municipality Name:]" & "*""

and it doesn't work syntax error

 

[boblarson]

If you have your heart set on using the parameter for enter municipality name and having the user type it in, instead of selecting from a combo or listbox on a form, then you should set a variable to the input box BEFORE building the string and pass that variable into the string.

 

[dj_mix]

If you have your heart set on using the parameter for enter municipality name and having the user type it in, instead of selecting from a combo or listbox on a form, then you should set a variable to the input box BEFORE building the string and pass that variable into the string.

In this situation the popup box works much faster then a list, combobox
since the list of municipalities large, easier to type the 1st few letters and print a report

it works fine in my query

Like "" & [Please Enter Municipality Name:] & "*"

but i trying to remove the query and add code to the command box and clean up my query list...

 

[boblarson]

Yes, as noted it works in your query, but if you want to use it in a SQL WHERE clause that is built as a string, you will need to convert it to a variable to use the parameter.

 

[dj_mix]

Yes, as noted it works in your query, but if you want to use it in a SQL WHERE clause that is built as a string, you will need to convert it to a variable to use the parameter.

how do I convert to variable ? I'm new to access any links that can help me ?

 

[boblarson]

Where are you building your query string? Is it in a standard or form module?

 

[dj_mix]

Where are you building your query string? Is it in a standard or form module?

It's based from a from run from a command button..

Private Sub ActiveEssexM_Click()
On Error GoTo Err_ActiveEssexM_Click

Dim stDocName As String
Dim stWhere As String

stDocName = "01 - Status Report 1 - General"
stWhere = "[County] = 'ESSEX'" & " And [FPAID] Is Null" & " And [LOCATION] =" & "[Please Enter Municipality Name:]" & "*""

DoCmd.OpenReport stDocName, acPreview, , stWhere
DoCmd.Maximize

Exit_ActiveEssexM_Click:
Exit Sub

Err_ActiveEssexM_Click:
MsgBox Err.Description
Resume Exit_ActiveEssexM_Click
End Sub

 

[boblarson]

change the code to this:

Private Sub ActiveEssexM_Click()
On Error GoTo Err_ActiveEssexM_Click

Dim stDocName As String
Dim stWhere As String
Dim strMunicipality As String

strMunicipality = InputBox("Please Enter Municipality Name:","Municipality Name")
stDocName = "01 - Status Report 1 - General"
stWhere = "[County] = 'ESSEX'" & " And [FPAID] Is Null" & " And [LOCATION] =" & strMunicipality & "*""

DoCmd.OpenReport stDocName, acPreview, , stWhere
DoCmd.Maximize

Exit_ActiveEssexM_Click:
Exit Sub

Err_ActiveEssexM_Click:
MsgBox Err.Description
Resume Exit_ActiveEssexM_Click
End Sub

 

[dj_mix]

I will give this a try tomorrow, I appreciate everyone's help.

I basiclly learned everything from this forum.. 6mths ago I had no idea about macros and vb code was... hopefully some day I can help out others as well.

 

[boblarson]

I'm sure you will. We've all started from the same place.

 

[dj_mix]

Just tried it and got a syntax error:

syntax error in string in query expression '([COUNTY]='ESSEX' And [FPAID] Is Null And [LOCATION] =bloom*")'

 

[boblarson]

If you manually typed in bloom, it needs quotes as it is text (single quotes should work). You might have to use

...And [LOCATION] Like 'bloom*'

 

[dj_mix]

Finally got it to work with the following

stWhere = "[County] = 'ESSEX'" & " And [FPAID] Is Null" & " And [LOCATION] like '" & strMunicipality & "*'"

 

[boblarson]

Great to hear! Thanks for posting back. Those quotation marks can be a major pain in the...