pop-up form to filter report

  • Thread starter Thread starter Lisa Reber
  • Start date Start date
L

Lisa Reber

Hi - Have downloaded the sample database "RptSmp97.mdb".
It includes a nifty sample "Setting Filter/Filter On
from a pop-up form"
1) I've copied this with partial success.
2) I know basically nothing about SQL.
3) The fields I want to select by are combo boxes.

Had posted this on 4/21 under the Forms group and got a
good answer from John Vinson, but he was unable to reply
to my latest post.

The report source query is:
SELECT DISTINCTROW Products.ProductName,
Suppliers.SupplierCode, Products.VendorPart,
Categories.CategoryName, Products.UnitPrice,
Products.SellPrice, Products.[Tagged?]
FROM Suppliers INNER JOIN (Categories RIGHT JOIN Products
ON Categories.CategoryID = Products.CategoryID) ON
Suppliers.SupplierID = Products.SupplierID
ORDER BY Products.ProductName;

I want to be able to filter by "supplier" and/or
"category" and/or "tagged?" but because(?) they are combo
boxes, I get the error message

Syntax error (missing operator) in query
expression '([Tagged?] ="-1" A)'.

or some variation of it.
JOhn's reply was "Lose the quote marks. They work when the
field is a String but are prohibited when the field is
numeric." I"ve been trying, but wonder which quote marks?
I'm now getting the same message no matter which filter
I try to use. "Tagged?" is a yes/no field, so Access
considers it 0,-1. I eliminated that option, and I get
Syntax error (missing operator) in query
expression '([VendorName] ="XXXX" A)'. Is this because the
vendor name combobox shows my alpha vendor ID, it is still
askingfor numeric data, i.e. vendor ID autonumber?

Can anybody out there please help? Thanks in advance.

Regards, Lisa
 
First try this, when writing sql code use double quotes to
denote the sql query text and single quotes for string
variables. John is right about the need to lose the
quotes around numeric variables. Another problem may be
the question mark in the field name ([Tagged?]). Symbols
are not recommended for use in field names as they are
often reserved. Put them in the label text on the form or
report only. In the case of the question mark it is a wild
card for a single character. So, having covered all that
the sql code should look something like:

"([Tagged?] =-1)"
"([VendorName] = 'XXXX')" or
"([VendorName] = " & 'XXXX' & ")"

with spaces whereever necessary to fulfill the
requirements of the syntax.

Good luck.
 
I meant to remove the question mark:
"([Tagged] =-1)"
Click to expand...
-----Original Message-----
First try this, when writing sql code use double quotes to
denote the sql query text and single quotes for string
variables. John is right about the need to lose the
quotes around numeric variables. Another problem may be
the question mark in the field name ([Tagged?]). Symbols
are not recommended for use in field names as they are
often reserved. Put them in the label text on the form or
report only. In the case of the question mark it is a wild
card for a single character. So, having covered all that
the sql code should look something like:

"([Tagged] =-1)"
"([VendorName] = 'XXXX')" or
"([VendorName] = " & 'XXXX' & ")"

with spaces whereever necessary to fulfill the
requirements of the syntax.

Good luck.


-----Original Message-----
Hi - Have downloaded the sample database "RptSmp97.mdb".
It includes a nifty sample "Setting Filter/Filter On
from a pop-up form"
1) I've copied this with partial success.
2) I know basically nothing about SQL.
3) The fields I want to select by are combo boxes.

Had posted this on 4/21 under the Forms group and got a
good answer from John Vinson, but he was unable to reply
to my latest post.

The report source query is:
SELECT DISTINCTROW Products.ProductName,
Suppliers.SupplierCode, Products.VendorPart,
Categories.CategoryName, Products.UnitPrice,
Products.SellPrice, Products.[Tagged?]
FROM Suppliers INNER JOIN (Categories RIGHT JOIN Products
ON Categories.CategoryID = Products.CategoryID) ON
Suppliers.SupplierID = Products.SupplierID
ORDER BY Products.ProductName;

I want to be able to filter by "supplier" and/or
"category" and/or "tagged?" but because(?) they are combo
boxes, I get the error message

Syntax error (missing operator) in query
expression '([Tagged?] ="-1" A)'.

or some variation of it.
JOhn's reply was "Lose the quote marks. They work when the
field is a String but are prohibited when the field is
numeric." I"ve been trying, but wonder which quote marks?
I'm now getting the same message no matter which filter
I try to use. "Tagged?" is a yes/no field, so Access
considers it 0,-1. I eliminated that option, and I get
Syntax error (missing operator) in query
expression '([VendorName] ="XXXX" A)'. Is this because the
vendor name combobox shows my alpha vendor ID, it is still
askingfor numeric data, i.e. vendor ID autonumber?

Can anybody out there please help? Thanks in advance.

Regards, Lisa

.
Click to expand...
.
Click to expand...
 
Hi Leasha -

Thanks for the advice regarding the ? mark. I know I've
read that, but never gave it a thought when setting up my
table. However, I'm pretty stumped. I think I know what's
going on, but I don't know if I'm expressing it correctly.
There are three levels to look at here, right?
1 - the query which underlies the report (see original
post). This seems to work OK.
2 - the row source for the combo boxes, filter1, filter2
and filter3. Here's the code for one of them:
SELECT DISTINCT qryInvItems.SupplierCode_
FROM qryInvItems ORDER BY qryInvItems.SupplierCode;
These seems to work OK - the combo boxes show lists of
suppliers, categories and yes/no for 'tagged'.
3 - the code for the On Click event for the Set Filter
button. The code is pasted below, with my *interpretation*
of the code. (I really, really don't know much.)

Private Sub Set_Filter_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
*a comment line, right?*
For intCounter = 1 To 3
*my filter combo boxes are filter1, filter2
and filter3*
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" &
intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" &
intCounter) & Chr(34) & " And "
*This is where the filter is read? John
*V. wrote that Chr(34) = ". Beyond that it
*makes no sense, so I don't know which quote
*marks to lose.
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = left(strSQL, (Len(strSQL) - 3))
*This should run the report without filters?
'Set the Filter property
Reports![rptInventoryItems].Filter = strSQL
Reports![rptInventoryItems].FilterOn = True
End If
*OK, this is where it's applied, the first part
*is how to interpret the filter?
End Sub

Sorry to have such a long-winded post; this seemed like
such a useful, easy (HAH!) project, and I AM learning
a lot as I go . . . It's just aggrevating to paste code
that doesn't make sense (or doesn't work), but it's also
aggrevating to take hours and hours to create something
that seems so simple! Thanks very, very much!
-----Original Message-----
I meant to remove the question mark:
"([Tagged] =-1)"
Click to expand...
-----Original Message-----
First try this, when writing sql code use double quotes to
denote the sql query text and single quotes for string
variables. John is right about the need to lose the
quotes around numeric variables. Another problem may be
the question mark in the field name ([Tagged?]). Symbols
are not recommended for use in field names as they are
often reserved. Put them in the label text on the form or
report only. In the case of the question mark it is a wild
card for a single character. So, having covered all that
the sql code should look something like:

"([Tagged] =-1)"
"([VendorName] = 'XXXX')" or
"([VendorName] = " & 'XXXX' & ")"

with spaces whereever necessary to fulfill the
requirements of the syntax.

Good luck.


-----Original Message-----
Hi - Have downloaded the sample database "RptSmp97.mdb".
It includes a nifty sample "Setting Filter/Filter On
from a pop-up form"
1) I've copied this with partial success.
2) I know basically nothing about SQL.
3) The fields I want to select by are combo boxes.

Had posted this on 4/21 under the Forms group and got a
good answer from John Vinson, but he was unable to reply
to my latest post.

The report source query is:
SELECT DISTINCTROW Products.ProductName,
Suppliers.SupplierCode, Products.VendorPart,
Categories.CategoryName, Products.UnitPrice,
Products.SellPrice, Products.[Tagged?]
FROM Suppliers INNER JOIN (Categories RIGHT JOIN Products
ON Categories.CategoryID = Products.CategoryID) ON
Suppliers.SupplierID = Products.SupplierID
ORDER BY Products.ProductName;

I want to be able to filter by "supplier" and/or
"category" and/or "tagged?" but because(?) they are combo
boxes, I get the error message

Syntax error (missing operator) in query
expression '([Tagged?] ="-1" A)'.

or some variation of it.
JOhn's reply was "Lose the quote marks. They work when the
field is a String but are prohibited when the field is
numeric." I"ve been trying, but wonder which quote marks?
I'm now getting the same message no matter which filter
I try to use. "Tagged?" is a yes/no field, so Access
considers it 0,-1. I eliminated that option, and I get
Syntax error (missing operator) in query
expression '([VendorName] ="XXXX" A)'. Is this because the
vendor name combobox shows my alpha vendor ID, it is still
askingfor numeric data, i.e. vendor ID autonumber?

Can anybody out there please help? Thanks in advance.

Regards, Lisa

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Is it possible (if the database is small enough) to send a
zipped copy to me? If that doesn't work then how about
just the objects that are giving you the grief? I think
it would be easier and quicker to solve if I can see it
all put together. If this works for you send it to me at
(e-mail address removed) and be sure to include your return email.

Leasha
-----Original Message-----
Hi Leasha -

Thanks for the advice regarding the ? mark. I know I've
read that, but never gave it a thought when setting up my
table. However, I'm pretty stumped. I think I know what's
going on, but I don't know if I'm expressing it correctly.
There are three levels to look at here, right?
1 - the query which underlies the report (see original
post). This seems to work OK.
2 - the row source for the combo boxes, filter1, filter2
and filter3. Here's the code for one of them:
SELECT DISTINCT qryInvItems.SupplierCode_
FROM qryInvItems ORDER BY qryInvItems.SupplierCode;
These seems to work OK - the combo boxes show lists of
suppliers, categories and yes/no for 'tagged'.
3 - the code for the On Click event for the Set Filter
button. The code is pasted below, with my *interpretation*
of the code. (I really, really don't know much.)

Private Sub Set_Filter_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
*a comment line, right?*
For intCounter = 1 To 3
*my filter combo boxes are filter1, filter2
and filter3*
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" &
intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" &
intCounter) & Chr(34) & " And "
*This is where the filter is read? John
*V. wrote that Chr(34) = ". Beyond that it
*makes no sense, so I don't know which quote
*marks to lose.
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = left(strSQL, (Len(strSQL) - 3))
*This should run the report without filters?
'Set the Filter property
Reports![rptInventoryItems].Filter = strSQL
Reports![rptInventoryItems].FilterOn = True
End If
*OK, this is where it's applied, the first part
*is how to interpret the filter?
End Sub

Sorry to have such a long-winded post; this seemed like
such a useful, easy (HAH!) project, and I AM learning
a lot as I go . . . It's just aggrevating to paste code
that doesn't make sense (or doesn't work), but it's also
aggrevating to take hours and hours to create something
that seems so simple! Thanks very, very much!
-----Original Message-----
I meant to remove the question mark:
"([Tagged] =-1)"
Click to expand...
-----Original Message-----
First try this, when writing sql code use double quotes to
denote the sql query text and single quotes for string
variables. John is right about the need to lose the
quotes around numeric variables. Another problem may be
the question mark in the field name ([Tagged?]). Symbols
are not recommended for use in field names as they are
often reserved. Put them in the label text on the form or
report only. In the case of the question mark it is a wild
card for a single character. So, having covered all that
the sql code should look something like:

"([Tagged] =-1)"
"([VendorName] = 'XXXX')" or
"([VendorName] = " & 'XXXX' & ")"

with spaces whereever necessary to fulfill the
requirements of the syntax.

Good luck.



-----Original Message-----
Hi - Have downloaded the sample database "RptSmp97.mdb".
It includes a nifty sample "Setting Filter/Filter On
from a pop-up form"
1) I've copied this with partial success.
2) I know basically nothing about SQL.
3) The fields I want to select by are combo boxes.

Had posted this on 4/21 under the Forms group and got a
good answer from John Vinson, but he was unable to reply
to my latest post.

The report source query is:
SELECT DISTINCTROW Products.ProductName,
Suppliers.SupplierCode, Products.VendorPart,
Categories.CategoryName, Products.UnitPrice,
Products.SellPrice, Products.[Tagged?]
FROM Suppliers INNER JOIN (Categories RIGHT JOIN Products
ON Categories.CategoryID = Products.CategoryID) ON
Suppliers.SupplierID = Products.SupplierID
ORDER BY Products.ProductName;

I want to be able to filter by "supplier" and/or
"category" and/or "tagged?" but because(?) they are combo
boxes, I get the error message

Syntax error (missing operator) in query
expression '([Tagged?] ="-1" A)'.

or some variation of it.
JOhn's reply was "Lose the quote marks. They work when the
field is a String but are prohibited when the field is
numeric." I"ve been trying, but wonder which quote marks?
I'm now getting the same message no matter which filter
I try to use. "Tagged?" is a yes/no field, so Access
considers it 0,-1. I eliminated that option, and I get
Syntax error (missing operator) in query
expression '([VendorName] ="XXXX" A)'. Is this because
the
vendor name combobox shows my alpha vendor ID, it is
still
askingfor numeric data, i.e. vendor ID autonumber?

Can anybody out there please help? Thanks in advance.

Regards, Lisa

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top