Error when I set filter

[Guest]

I am using article KB208529, which has worked in other databases of mine, to
set up a report filter from a pop-up form. When I choose a value from a
combo box, then click "set filter", I get the following error:

Syntax error (missing operator) in query expression '([Provider
Type]="Physician" A).'

Originally, the source for the report was a crosstab query, but I have since
changed it to a select query (built from the crosstab) to see if that made a
difference, but it didn't.

Please help - I can't find any solutions to this!

Thanks,
Susan
slegg @ earthlink.net

 

[SusanV]

Why is Physician enclosed in quotations? What is the A and why is it not
also in the quotes? If you posted the entire code section rather than simply
this piece it would be easier to figure out what's causing the problem.

SusanV

 

[Guest]

I wondered the same thing - I have no idea why it is in quotes or why there
is an "A". That is just the error message. Here is the code for the "Set
Filter" command:

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer

' Build SQL String.
For intCounter = 1 To 4
If Me("Filter" & intCounter) <> "" Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & _
" And "
End If
Next

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 4))

' Set the Filter property.
Reports![Report 1A - Adjusted Utilization].Filter = strSQL
Reports![Report 1A - Adjusted Utilization].FilterOn = True
End If
End Sub

Thanks for looking at this.

Why is Physician enclosed in quotations? What is the A and why is it not
also in the quotes? If you posted the entire code section rather than simply
this piece it would be easier to figure out what's causing the problem.

SusanV

I am using article KB208529, which has worked in other databases of mine,
to
set up a report filter from a pop-up form. When I choose a value from a
combo box, then click "set filter", I get the following error:

Syntax error (missing operator) in query expression '([Provider
Type]="Physician" A).'

Originally, the source for the report was a crosstab query, but I have
since
changed it to a select query (built from the crosstab) to see if that made
a
difference, but it didn't.

Please help - I can't find any solutions to this!

Thanks,
Susan
slegg @ earthlink.net

 

[SusanV]

Hi Susan,
Sorry I haven't gotten back sooner - haven't been in the office. Have you
resolved this issue?

If not, try using Debug.Print strSQL at various points to determine what is
happening to the string strSQL during its modifications - something
unexpected is happening, and this will narrow own which section of code
needs correction.
--
hth,
SusanV

I wondered the same thing - I have no idea why it is in quotes or why there
is an "A". That is just the error message. Here is the code for the "Set
Filter" command:

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer

' Build SQL String.
For intCounter = 1 To 4
If Me("Filter" & intCounter) <> "" Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & _
" And "
End If
Next

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 4))

' Set the Filter property.
Reports![Report 1A - Adjusted Utilization].Filter = strSQL
Reports![Report 1A - Adjusted Utilization].FilterOn = True
End If
End Sub

Thanks for looking at this.

Why is Physician enclosed in quotations? What is the A and why is it not
also in the quotes? If you posted the entire code section rather than
simply
this piece it would be easier to figure out what's causing the problem.

SusanV

message

I am using article KB208529, which has worked in other databases of
mine,
to
set up a report filter from a pop-up form. When I choose a value from
a
combo box, then click "set filter", I get the following error:

Syntax error (missing operator) in query expression '([Provider
Type]="Physician" A).'

Originally, the source for the report was a crosstab query, but I have
since
changed it to a select query (built from the crosstab) to see if that
made
a
difference, but it didn't.

Please help - I can't find any solutions to this!

Thanks,
Susan
slegg @ earthlink.net

 

[Guest]

Sorry to sound so naive... but how exactly do I do that?

Thanks!
Susan

Hi Susan,
Sorry I haven't gotten back sooner - haven't been in the office. Have you
resolved this issue?

If not, try using Debug.Print strSQL at various points to determine what is
happening to the string strSQL during its modifications - something
unexpected is happening, and this will narrow own which section of code
needs correction.
--
hth,
SusanV

I wondered the same thing - I have no idea why it is in quotes or why there
is an "A". That is just the error message. Here is the code for the "Set
Filter" command:

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer

' Build SQL String.
For intCounter = 1 To 4
If Me("Filter" & intCounter) <> "" Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & _
" And "
End If
Next

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 4))

' Set the Filter property.
Reports![Report 1A - Adjusted Utilization].Filter = strSQL
Reports![Report 1A - Adjusted Utilization].FilterOn = True
End If
End Sub

Thanks for looking at this.

Why is Physician enclosed in quotations? What is the A and why is it not
also in the quotes? If you posted the entire code section rather than
simply
this piece it would be easier to figure out what's causing the problem.

SusanV

message
I am using article KB208529, which has worked in other databases of
mine,
to
set up a report filter from a pop-up form. When I choose a value from
a
combo box, then click "set filter", I get the following error:

Syntax error (missing operator) in query expression '([Provider
Type]="Physician" A).'

Originally, the source for the report was a crosstab query, but I have
since
changed it to a select query (built from the crosstab) to see if that
made
a
difference, but it didn't.

Please help - I can't find any solutions to this!

Thanks,
Susan
slegg @ earthlink.net

 

[SusanV]

Insert that as a new line in between processing lines, and when you run the
code check the immediate window to see the results (View menu in the VBE):

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer

' Build SQL String.
For intCounter = 1 To 4
If Me("Filter" & intCounter) <> "" Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & _
" And "

Debug.Print strSQL

End If
Next

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 4))

Debug.Print strSQL

' Set the Filter property.
Reports![Report 1A - Adjusted Utilization].Filter = strSQL
Reports![Report 1A - Adjusted Utilization].FilterOn = True
End If
End Sub

Sorry to sound so naive... but how exactly do I do that?

Thanks!
Susan

Hi Susan,
Sorry I haven't gotten back sooner - haven't been in the office. Have you
resolved this issue?

If not, try using Debug.Print strSQL at various points to determine what
is
happening to the string strSQL during its modifications - something
unexpected is happening, and this will narrow own which section of code
needs correction.
--
hth,
SusanV


message

I wondered the same thing - I have no idea why it is in quotes or why
there
is an "A". That is just the error message. Here is the code for the
"Set
Filter" command:

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer

' Build SQL String.
For intCounter = 1 To 4
If Me("Filter" & intCounter) <> "" Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & _
" And "
End If
Next

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 4))

' Set the Filter property.
Reports![Report 1A - Adjusted Utilization].Filter = strSQL
Reports![Report 1A - Adjusted Utilization].FilterOn = True
End If
End Sub

Thanks for looking at this.



:

Why is Physician enclosed in quotations? What is the A and why is it
not
also in the quotes? If you posted the entire code section rather than
simply
this piece it would be easier to figure out what's causing the
problem.

SusanV

message
I am using article KB208529, which has worked in other databases of
mine,
to
set up a report filter from a pop-up form. When I choose a value
from
a
combo box, then click "set filter", I get the following error:

Syntax error (missing operator) in query expression '([Provider
Type]="Physician" A).'

Originally, the source for the report was a crosstab query, but I
have
since
changed it to a select query (built from the crosstab) to see if
that
made
a
difference, but it didn't.

Please help - I can't find any solutions to this!

Thanks,
Susan
slegg @ earthlink.net

 

[Guest]

I added those lines, and when I click on the Set Filter button (to run the
macro in my form) I get the exact same message. Help!

Thanks so much,
Susan

Insert that as a new line in between processing lines, and when you run the
code check the immediate window to see the results (View menu in the VBE):

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer

' Build SQL String.
For intCounter = 1 To 4
If Me("Filter" & intCounter) <> "" Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & _
" And "

Debug.Print strSQL

End If
Next

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 4))

Debug.Print strSQL

' Set the Filter property.
Reports![Report 1A - Adjusted Utilization].Filter = strSQL
Reports![Report 1A - Adjusted Utilization].FilterOn = True
End If
End Sub

Sorry to sound so naive... but how exactly do I do that?

Thanks!
Susan

Hi Susan,
Sorry I haven't gotten back sooner - haven't been in the office. Have you
resolved this issue?

If not, try using Debug.Print strSQL at various points to determine what
is
happening to the string strSQL during its modifications - something
unexpected is happening, and this will narrow own which section of code
needs correction.
--
hth,
SusanV


message
I wondered the same thing - I have no idea why it is in quotes or why
there
is an "A". That is just the error message. Here is the code for the
"Set
Filter" command:

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer

' Build SQL String.
For intCounter = 1 To 4
If Me("Filter" & intCounter) <> "" Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & _
" And "
End If
Next

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 4))

' Set the Filter property.
Reports![Report 1A - Adjusted Utilization].Filter = strSQL
Reports![Report 1A - Adjusted Utilization].FilterOn = True
End If
End Sub

Thanks for looking at this.



:

Why is Physician enclosed in quotations? What is the A and why is it
not
also in the quotes? If you posted the entire code section rather than
simply
this piece it would be easier to figure out what's causing the
problem.

SusanV

message
I am using article KB208529, which has worked in other databases of
mine,
to
set up a report filter from a pop-up form. When I choose a value
from
a
combo box, then click "set filter", I get the following error:

Syntax error (missing operator) in query expression '([Provider
Type]="Physician" A).'

Originally, the source for the report was a crosstab query, but I
have
since
changed it to a select query (built from the crosstab) to see if
that
made
a
difference, but it didn't.

Please help - I can't find any solutions to this!

Thanks,
Susan
slegg @ earthlink.net

 

[SusanV]

Yes, those lines are not going to SOLVE the problem, they're going to show
you where the error is. Look at the immediate window in the Visual Basic
editor after clicking the button to see where the filter string is getting
flubbed.

I added those lines, and when I click on the Set Filter button (to run the
macro in my form) I get the exact same message. Help!

Thanks so much,
Susan

Insert that as a new line in between processing lines, and when you run
the
code check the immediate window to see the results (View menu in the
VBE):

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer

' Build SQL String.
For intCounter = 1 To 4
If Me("Filter" & intCounter) <> "" Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & _
" And "

Debug.Print strSQL

End If
Next

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 4))

Debug.Print strSQL

' Set the Filter property.
Reports![Report 1A - Adjusted Utilization].Filter = strSQL
Reports![Report 1A - Adjusted Utilization].FilterOn = True
End If
End Sub



message

Sorry to sound so naive... but how exactly do I do that?

Thanks!
Susan

:

Hi Susan,
Sorry I haven't gotten back sooner - haven't been in the office. Have
you
resolved this issue?

If not, try using Debug.Print strSQL at various points to determine
what
is
happening to the string strSQL during its modifications - something
unexpected is happening, and this will narrow own which section of
code
needs correction.
--
hth,
SusanV


message
I wondered the same thing - I have no idea why it is in quotes or why
there
is an "A". That is just the error message. Here is the code for
the
"Set
Filter" command:

Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer

' Build SQL String.
For intCounter = 1 To 4
If Me("Filter" & intCounter) <> "" Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] "
_
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34)
& _
" And "
End If
Next

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 4))

' Set the Filter property.
Reports![Report 1A - Adjusted Utilization].Filter = strSQL
Reports![Report 1A - Adjusted Utilization].FilterOn = True
End If
End Sub

Thanks for looking at this.



:

Why is Physician enclosed in quotations? What is the A and why is
it
not
also in the quotes? If you posted the entire code section rather
than
simply
this piece it would be easier to figure out what's causing the
problem.

SusanV

message
I am using article KB208529, which has worked in other databases
of
mine,
to
set up a report filter from a pop-up form. When I choose a value
from
a
combo box, then click "set filter", I get the following error:

Syntax error (missing operator) in query expression '([Provider
Type]="Physician" A).'

Originally, the source for the report was a crosstab query, but I
have
since
changed it to a select query (built from the crosstab) to see if
that
made
a
difference, but it didn't.

Please help - I can't find any solutions to this!

Thanks,
Susan
slegg @ earthlink.net