Combo Box frustration

[Lisa Reber]

Hi - sorry if this is a dumb question, but I'm still
new at this.

Have a report that I want to filter with a pop-up form
like the sample in RptSmp97. I've gotten most of the
commands to work, except the most important - the filters.
Each of the combo boxes shows numeric data when it should
show text.

Source is table Products.
Filter1 = SELECT DISTINCT Products.SupplierID FROM
Products ORDER BY Products.SupplierID;
Filter2 = SELECT DISTINCT Products.CategoryID FROM
Products;
Filter3 = SELECT DISTINCT Products.[Tagged?] FROM
Products;

SupplierID and CategoryID are both look-up fields in
Products (is this a foreign key?)
Tagged is a check box - the combo box shows a drop-down
selection of Yes/No, then displays -1 or 0 respectively.

So, as usual, trying to explain the problem helps me
come up with a possible answer. If I make the report
source a query showing Supplier Name from tblSuppliers;
likewise CategoryID from tblCategories - this should
give me text fields rather than numeric ID's?

Second, when I try filtering only on Tagged = Yes, I get
error message: Syntax error (missing operator) in query
expression '([Tagged?] ="-1" A)'.
The OlClick [Event Procedure] is as follows (lifted almost
entirely from the RptSmp97 code):

Private Sub Set_Filter_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 3
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) - 3))
'Set the Filter property
Reports![rptInventoryItems].Filter = strSQL
Reports![rptInventoryItems].FilterOn = True
End If

End Sub

So I don't know if the problem is 'not in our stars, but
in ourselves' or something - the code doesn't directly
copy, or that the combo box tagged is yes/no not -1/0.

Any help is greatly appreciated - thanks for your patience!
Regards, Lisa

 

[Lisa Reber]

From Lisa - got the Filter1 & Filter2 to show supplier &
category name, still having trouble with the Filter3:
tagged. don't know if it's because it's a check box or not?

 

[Paul Johnson]

I'm looking past all the SQL stuff, and guessing that the problem is in the
properties for your combo box. If the combo box is displaying the bound
column only; you will need to apply the following settings in your
properties window for the combobox:

Column Count: 2
Column Widths: 0";1" (Your choice, but 0" is necessary to hide the
first column)
Row Source Type: Value List
Row Source: 0;"No";-1;"Yes"
Bound Column: 1 (I am guessing that your bound column is set
to 2)

I hope this helps.
Paul Johnson

From Lisa - got the Filter1 & Filter2 to show supplier &
category name, still having trouble with the Filter3:
tagged. don't know if it's because it's a check box or not?

-----Original Message-----
Hi - sorry if this is a dumb question, but I'm still
new at this.

Have a report that I want to filter with a pop-up form
like the sample in RptSmp97. I've gotten most of the
commands to work, except the most important - the filters.
Each of the combo boxes shows numeric data when it should
show text.

Source is table Products.
Filter1 = SELECT DISTINCT Products.SupplierID FROM
Products ORDER BY Products.SupplierID;
Filter2 = SELECT DISTINCT Products.CategoryID FROM
Products;
Filter3 = SELECT DISTINCT Products.[Tagged?] FROM
Products;

SupplierID and CategoryID are both look-up fields in
Products (is this a foreign key?)
Tagged is a check box - the combo box shows a drop-down
selection of Yes/No, then displays -1 or 0 respectively.

So, as usual, trying to explain the problem helps me
come up with a possible answer. If I make the report
source a query showing Supplier Name from tblSuppliers;
likewise CategoryID from tblCategories - this should
give me text fields rather than numeric ID's?

Second, when I try filtering only on Tagged = Yes, I get
error message: Syntax error (missing operator) in query
expression '([Tagged?] ="-1" A)'.
The OlClick [Event Procedure] is as follows (lifted almost
entirely from the RptSmp97 code):

Private Sub Set_Filter_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 3
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) - 3))
'Set the Filter property
Reports![rptInventoryItems].Filter = strSQL
Reports![rptInventoryItems].FilterOn = True
End If

End Sub

So I don't know if the problem is 'not in our stars, but
in ourselves' or something - the code doesn't directly
copy, or that the combo box tagged is yes/no not -1/0.

Any help is greatly appreciated - thanks for your patience!
Regards, Lisa
.

 

[John Vinson]

Each of the combo boxes shows numeric data when it should
show text.

Microsoft's misdesigned, misleading, obnoxious, and all but useless
Lookup Wizard I bet...

Source is table Products.
Filter1 = SELECT DISTINCT Products.SupplierID FROM
Products ORDER BY Products.SupplierID;
Filter2 = SELECT DISTINCT Products.CategoryID FROM
Products;
Filter3 = SELECT DISTINCT Products.[Tagged?] FROM
Products;

SupplierID and CategoryID are both look-up fields in
Products (is this a foreign key?)

Exactly. The Lookup feature CONCEALS the actual contents of your table
from view. It *LOOKS* like you have a supplier name in the table; you
don't! What you have in the table is a numeric ID. And when you do
more than the very simplest things with the table (such as sorting, or
creating a query, or a filter) this will trip you up.

Tagged is a check box - the combo box shows a drop-down
selection of Yes/No, then displays -1 or 0 respectively.

That's because YES is stored as -1, and NO as 0. Why not just use a
checkbox on the criteria form?

So, as usual, trying to explain the problem helps me
come up with a possible answer. If I make the report
source a query showing Supplier Name from tblSuppliers;
likewise CategoryID from tblCategories - this should
give me text fields rather than numeric ID's?

Exactly. Join tblSuppliers to your table by SupplierID.

Second, when I try filtering only on Tagged = Yes, I get
error message: Syntax error (missing operator) in query
expression '([Tagged?] ="-1" A)'.

Lose the quote marks. They work when the field is a String but are
prohibited when the field is numeric.

The OlClick [Event Procedure] is as follows (lifted almost
entirely from the RptSmp97 code):

Private Sub Set_Filter_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 3
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) - 3))
'Set the Filter property
Reports![rptInventoryItems].Filter = strSQL
Reports![rptInventoryItems].FilterOn = True
End If

End Sub

So I don't know if the problem is 'not in our stars, but
in ourselves' or something - the code doesn't directly
copy, or that the combo box tagged is yes/no not -1/0.

You'll need some more complex code to determine the datatype of the
field you're searching. Text fields should have either " (which is
Chr(34) ) or ' as a delimiter; Numeric and Yes/No fields should have
no delimiter at all; Date fields should have # as a delimiter.

 

[Lisa Reber]

John - thanks! You're always a big help. Not sure about
one part of your answer, and will have to dig in to the
other part. anyhow:

That's because YES is stored as -1, and NO as 0. Why not just use a
checkbox on the criteria form?

Sorry to be ignorant, but what do you mean by "Why not
just use a checkbox on the criteria form?" I suspect if I
change how I'm using this field, the problem in SQL below
might be made to disappear (?) Plus I know pretty much
zip about SQL. Anyhow, the way I'm using "Tagged" is as a
checkbox on product entry form, but it can be a text field
elsewhere. If it's a text field in the source for this
report, then none of the fields will be numeric. If it can
be done, it sounds like a good way around the problem?
Let me know what you think, and thanks a bunch.

So, as usual, trying to explain the problem helps me
come up with a possible answer. If I make the report
source a query showing Supplier Name from tblSuppliers;
likewise CategoryID from tblCategories - this should
give me text fields rather than numeric ID's?

Exactly. Join tblSuppliers to your table by SupplierID.

Second, when I try filtering only on Tagged = Yes, I get
error message: Syntax error (missing operator) in query
expression '([Tagged?] ="-1" A)'.

Lose the quote marks. They work when the field is a String but are
prohibited when the field is numeric.

The OlClick [Event Procedure] is as follows (lifted almost
entirely from the RptSmp97 code):

Private Sub Set_Filter_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 3
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) - 3))
'Set the Filter property
Reports![rptInventoryItems].Filter = strSQL
Reports![rptInventoryItems].FilterOn = True
End If

End Sub

So I don't know if the problem is 'not in our stars, but
in ourselves' or something - the code doesn't directly
copy, or that the combo box tagged is yes/no not -1/0.

You'll need some more complex code to determine the datatype of the
field you're searching. Text fields should have either " (which is
Chr(34) ) or ' as a delimiter; Numeric and Yes/No fields should have
no delimiter at all; Date fields should have # as a delimiter.



.

 

[Lisa Reber]

Have been working on this - see below. Thanks!

Second, when I try filtering only on Tagged = Yes, I get
error message: Syntax error (missing operator) in query
expression '([Tagged?] ="-1" A)'.

Lose the quote marks. They work when the field is a String but are
prohibited when the field is numeric.

Tried to simplify the process, and made a new pop-up filter
form with only VendorCode and Category. I still get the
same Syntax error message, except the "-1" is filled with
the text I selected. So, am I right in thinking that even
though the query that is the source for the report and
for RowSource, it is still looking for a numeric entry,
namely the key ID#? It seems like this will !never! work,
unless the source data is a simple table with no look-ups.

 

[John Vinson]

John - thanks! You're always a big help. Not sure about
one part of your answer, and will have to dig in to the
other part. anyhow:

Lisa, I just came upon this unanswered message - did you get your
combo box problem resolved?