SQL String 
Author Message
 SQL String

This is the code that I have for searching for parts.  I want to be able to
narrow down the list.

I have 2 combo boxes on the form and I want to search from them.

The problem that I am having it is searching from the cboComponent fine and
show them all.  But as soon as I select something from the cboDenom and
search it still shows me all the items that go with the cboComponent.

TIA

Private Sub cmdSearch_Click()

    If cboComponent.Text = "" Then Exit Sub
    If cboDenom.Text = "25" Then Denom = "25"
    If cboDenom.Text = "$1.00" Then Denom = "100"
    If cboDenom.Text = "Both" Then Denom = "Both"
    If cboDenom.Text = "" Then Denom = "Both"

    strSQL = "SELECT [Vendor No],[Description],[Quantity Component] FROM
tblParts WHERE Location=" & _
     Chr$(34) & cboComponent.Text & Chr$(34) & " AND " & _
     Chr$(34) & Denom & Chr$(34) & "=True ORDER BY [Vendor No]"

    Data1.DatabaseName = strPath
    Data1.RecordSource = strSQL
    Data1.Refresh
End Sub



Tue, 08 Oct 2002 03:00:00 GMT  
 SQL String
Quote:

> This is the code that I have for searching for parts.  I want to be able to
> narrow down the list.

> I have 2 combo boxes on the form and I want to search from them.

> The problem that I am having it is searching from the cboComponent fine and
> show them all.  But as soon as I select something from the cboDenom and
> search it still shows me all the items that go with the cboComponent.

<snip>

   Have you used Debug.print to view your strSQL?  Your code produces:

SELECT [Vendor No],[Description],[Quantity Component] FROM tblParts
WHERE Location="foo" AND "100"=True ORDER BY [Vendor No]

  The way you use Denom in your AND clause indicates that you have three
fields in tblParts named "25", "100", and "Both" - since you append
Chr$(34) to Denom, these field names would have to include the quotation
marks in your table's design - kinda unconventional naming.  If that's
really how your fields are named, the punctuation marks require that you
add brackets to the field name as you do with [Vendor No] - which
requires them due to the blank space in field name.  Your code would
have to build strSQL to be:

SELECT [Vendor No],[Description],[Quantity Component] FROM tblParts
WHERE Location="foo" AND ["100"]=True ORDER BY [Vendor No]

  If your field names don't really include the quotation marks, then
you'll need to remove Chr$(34) from around Denom.
  If the above is missing the mark, please provide more info on your
table's field names and data types for the AND clause.
--
Jim in Cleveland
If you're writing to me, in my address
change "REAL_Address.see.below" to "worldnet.att.net"
 ((("What's so funny 'bout peace, love & understanding?" - Nick Lowe)))



Wed, 09 Oct 2002 03:00:00 GMT  
 SQL String
Thanks Jim

The fields 25,100,Both do not have quotes around the names.  Those fields
are checkboxes.  I have used the debug.print and it shows correctly but it
displays incorrectly in the msflexgrid form.  I am pretty sure I took the
chr$(34) away from the denom.

25, 100, both fields are setup as true/False
Vendor No, Description as text
Quantity is a number.



Quote:

> > This is the code that I have for searching for parts.  I want to be able
to
> > narrow down the list.

> > I have 2 combo boxes on the form and I want to search from them.

> > The problem that I am having it is searching from the cboComponent fine
and
> > show them all.  But as soon as I select something from the cboDenom and
> > search it still shows me all the items that go with the cboComponent.
> <snip>

>    Have you used Debug.print to view your strSQL?  Your code produces:

> SELECT [Vendor No],[Description],[Quantity Component] FROM tblParts
> WHERE Location="foo" AND "100"=True ORDER BY [Vendor No]

>   The way you use Denom in your AND clause indicates that you have three
> fields in tblParts named "25", "100", and "Both" - since you append
> Chr$(34) to Denom, these field names would have to include the quotation
> marks in your table's design - kinda unconventional naming.  If that's
> really how your fields are named, the punctuation marks require that you
> add brackets to the field name as you do with [Vendor No] - which
> requires them due to the blank space in field name.  Your code would
> have to build strSQL to be:

> SELECT [Vendor No],[Description],[Quantity Component] FROM tblParts
> WHERE Location="foo" AND ["100"]=True ORDER BY [Vendor No]

>   If your field names don't really include the quotation marks, then
> you'll need to remove Chr$(34) from around Denom.
>   If the above is missing the mark, please provide more info on your
> table's field names and data types for the AND clause.
> --
> Jim in Cleveland
> If you're writing to me, in my address
> change "REAL_Address.see.below" to "worldnet.att.net"
>  ((("What's so funny 'bout peace, love & understanding?" - Nick Lowe)))



Wed, 09 Oct 2002 03:00:00 GMT  
 SQL String

Quote:

> Thanks Jim

> The fields 25,100,Both do not have quotes around the names.  Those fields
> are checkboxes.  I have used the debug.print and it shows correctly but it
> displays incorrectly in the msflexgrid form.  I am pretty sure I took the
> chr$(34) away from the denom.

> 25, 100, both fields are setup as true/False
> Vendor No, Description as text
> Quantity is a number.

  When you build your strSQL, you really need to put the brackets around
the field names for fields named 25 and 100.  One way to achieve this is
to revise your If...Thens to:

    If cboDenom.Text = "25" Then Denom = "[25]"
    If cboDenom.Text = "$1.00" Then Denom = "[100]"

  I'm not being critical, but I don't think it's standard practice to
name a field with an integer.  If you really want to use 25 and 100 as
names, your code must always refer to those field names by surrounding
them with brackets.  Here's why your SQL statement doesn't limit the
records:  your AND clause is applying a condition to the SELECT which
tells Jet to get records "WHERE 25 = True" (or "WHERE 100 = True").  In
boolean, every number = True except zero, so your AND clause can never
rule out any records.  For every record, the phrase "WHERE 25 = True"
will always return True.
  When you add the brackets, the AND clause becomes "WHERE [25] =
True".  Jet recognizes that [25] is a field name because of the
brackets, and will limit records to those where the Value in that field
is True.
  Although I don't know much about your db, you might consider switching
these three checkbox fields to a single text field for "Denomination",
where each record would get a value of "25", "100", or "Both" - this
way, if you ever want to permit other denominations, you won't need to
keep adding fields to the table.
  If this still don't git it, please e me directly - see addy info in my
sig, below.
--
Jim in Cleveland
If you're writing to me, in my address
change "REAL_Address.see.below" to "worldnet.att.net"
 ((("What's so funny 'bout peace, love & understanding?" - Nick Lowe)))



Thu, 10 Oct 2002 03:00:00 GMT  
 SQL String
That worked.  Thank you very much maybe I will change those field names.

Ed



Quote:

> > Thanks Jim

> > The fields 25,100,Both do not have quotes around the names.  Those
fields
> > are checkboxes.  I have used the debug.print and it shows correctly but
it
> > displays incorrectly in the msflexgrid form.  I am pretty sure I took
the
> > chr$(34) away from the denom.

> > 25, 100, both fields are setup as true/False
> > Vendor No, Description as text
> > Quantity is a number.

>   When you build your strSQL, you really need to put the brackets around
> the field names for fields named 25 and 100.  One way to achieve this is
> to revise your If...Thens to:

>     If cboDenom.Text = "25" Then Denom = "[25]"
>     If cboDenom.Text = "$1.00" Then Denom = "[100]"

>   I'm not being critical, but I don't think it's standard practice to
> name a field with an integer.  If you really want to use 25 and 100 as
> names, your code must always refer to those field names by surrounding
> them with brackets.  Here's why your SQL statement doesn't limit the
> records:  your AND clause is applying a condition to the SELECT which
> tells Jet to get records "WHERE 25 = True" (or "WHERE 100 = True").  In
> boolean, every number = True except zero, so your AND clause can never
> rule out any records.  For every record, the phrase "WHERE 25 = True"
> will always return True.
>   When you add the brackets, the AND clause becomes "WHERE [25] =
> True".  Jet recognizes that [25] is a field name because of the
> brackets, and will limit records to those where the Value in that field
> is True.
>   Although I don't know much about your db, you might consider switching
> these three checkbox fields to a single text field for "Denomination",
> where each record would get a value of "25", "100", or "Both" - this
> way, if you ever want to permit other denominations, you won't need to
> keep adding fields to the table.
>   If this still don't git it, please e me directly - see addy info in my
> sig, below.
> --
> Jim in Cleveland
> If you're writing to me, in my address
> change "REAL_Address.see.below" to "worldnet.att.net"
>  ((("What's so funny 'bout peace, love & understanding?" - Nick Lowe)))



Thu, 10 Oct 2002 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. a SQL string question

2. Sql string to Saved Query

3. Recordset = SQL String

4. Recordset = SQL string

5. converting QBE Sql string to Where

6. SQL string being truncated

7. SQL String check

8. Concatenating Fields in sql-string in vba

9. SQL String in Access 2K

10. SQL String - Please help!!!

11. SQL String limited to 256 characters

12. Fault in SQL-string

 

 
Powered by phpBB® Forum Software