Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Dynamically building Where Condition

Posted on 2006-05-23
8
Medium Priority
?
188 Views
Last Modified: 2011-09-20
In my VB6.0 app, based on user selections from combo boxes I am trying to dynaically build a 'where' clause conditionally based on what is selected in the combo boxes (there are two of them).

I have verified that the fields 'cboCustomer.ItemData(cboCustomer.ListIndex)' and 'cboItem.ItemData(cboItem.ListIndex)' both contain numeric values if selected by the user.  The problem is that as I build the where clause, a double quote is appended at the end, which is incorrect.  I am not purposely putting it there but when I show the contents the where clause looks like:

"Where ClientID = 16"

based on my statements

    wkVal = cboCustomer.ItemData(cboCustomer.ListIndex)
    whereCond = whereCond & " ClientID = " & wkVal

I want it to look like:  "Where ClientID = " 16

How do I keep the double quote from moving where I put it (prior to the 16) to where the system puts it (after the 16)?

My full logic looks like this:


anyCond = False
whereCond = "WHERE"
myFilter = ""

  If cboCustomer.ListIndex > -1 Then
    If anyCond Then
      whereCond = whereCond & " AND "
      myFilter = myFilter & ", "
    End If
    wkVal = cboCustomer.ItemData(cboCustomer.ListIndex)
    whereCond = whereCond & " ClientID = " & wkVal
    myFilter = myFilter & "Customer: " & cboCustomer.Text
    anyCond = True
  End If

  If cboItem.ListIndex > -1 Then
    If anyCond Then
      whereCond = whereCond & " AND "
      myFilter = myFilter & ", "
    End If
    whereCond = whereCond & " ItemID = " & cboItem.ItemData(cboItem.ListIndex)
    myFilter = myFilter & "Item: " & cboItem.Text
    anyCond = True
  End If

  rs.Open "SELECT * FROM qryPriceSheet" & whereCond, cn
0
Comment
Question by:mlcktmguy
8 Comments
 
LVL 2

Expert Comment

by:AlThePal7
ID: 16746454
If you want quotes to appear inside a string, you have to enter the quotes twice e.g.

textbox1.text = "This string includes "" quotes"

Textbox1 will displayed the string

This string includes " quotes

Can you give an example of the complete string you want to end up with, along with the contents of your listbox?
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 16746688
As I mentioned above, this is what I am ending up with:

"Where ClientID = 16"

This is what I want to end up with:

"Where ClientID = " 16

"I have verified that the fields 'cboCustomer.ItemData(cboCustomer.ListIndex)' and 'cboItem.ItemData(cboItem.ListIndex)' both contain numeric values if selected by the user".

I also include the logic I am using to build the string:

    wkVal = cboCustomer.ItemData(cboCustomer.ListIndex)
    whereCond = whereCond & " ClientID = " & wkVal

I have verified that wkVal contains the numeric value 16.

What I odn't understand is based on the statement:

    whereCond = whereCond & " ClientID = " & wkVal

there shold be no double quote at the end but there is.
0
 
LVL 2

Expert Comment

by:AlThePal7
ID: 16746793
What you are doing is adding a value to the end of a string.  VB will convert the number to the string and join the 2 strings together.  Whenever it shows a string it will surround it in quotes.  If you really want a string with double quotes before and after the 'Where ClientID = ' part, you will have to use the following:

    whereCond = """Where"
    wkVal = cboCustomer.ItemData(cboCustomer.ListIndex)
    whereCond = whereCond & " ClientID = """ & wkVal
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Assisted Solution

by:Brownhead
Brownhead earned 200 total points
ID: 16747554
Let me elaborate a bit more on that...

    wkVal = cboCustomer.ItemData(cboCustomer.ListIndex)
    whereCond = """" & whereCond & " ClientID = """ & wkVal

That will be the code that you wanted. When you display it in the debugger, it will appear as:

""Where ClientID = " Bla"
(Bla being some number)

But the string ACTUALLY equals:

"Where ClientID = " Bla

The VB Debugger simply adds quotes around a string to tell teh programmer that he/she is dealing with a string. Anyways, hope that helped a little bit
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 16747645
I appreciate the responses but I am still getting 'Error in the From Clause' on the statement:

rs.Open "SELECT * FROM qryPriceSheet" & whereCond, cn

I tried:

   whereCond = """Where"
    wkVal = cboCustomer.ItemData(cboCustomer.ListIndex)
    whereCond = whereCond & " ClientID = """ & wkVal

and I tried

    whereCond = """" & whereCond & " ClientID = """ & wkVal

no luck yet.

Any other ideas?

0
 
LVL 2

Assisted Solution

by:AlThePal7
AlThePal7 earned 200 total points
ID: 16749228
mlcktmguy, looking at the string you are trying to build I cannot understand why you feel you need quotes there at all.  I would have thought the final query should look like

SELECT * FROM qryPriceSheet WHERE ClientID = 16

The string "WHERE ClientID = 16" is therefore correct - you are just missing a space between it and the preceding part of the string.

If this doesn't solve things, would you please let me know the contents of your comboboxes and tell me the final string you want to achieve.
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 600 total points
ID: 16750303
your problem may be that you are missing a 'blank' after your table name

rs.Open "SELECT * FROM qryPriceSheet" & whereCond, cn

try this change:

'++++++++++++++++++++++++++++
Dim strSQL as string

strSQL = "SELECT * FROM qryPriceSheet" & whereCond

MsgBox "SQL = " & strSQL, vbOkOnly

rs.Open strSQL, cn
'++++++++++++++++++++++++++++

I will bet that the Message Box WILL show

SELECT * from qryPriceSheetWHERE ClientID = 16

it SHOULD show:

SELECT * from qryPriceSheet WHERE ClientID = 16

see the difference?

change the code to:

'++++++++++++++++++++++++++++
Dim strSQL as string

strSQL = "SELECT * FROM qryPriceSheet " & whereCond

MsgBox "SQL = " & strSQL, vbOkOnly

rs.Open strSQL, cn
'+++++++++++++++++++++++++++


AW


0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 16765666
I'm going to split the point because you all gave me an education even though the last comment was the clincher.

It turns out that after trying all of the suggestions, I ended up with my orginal logic with one small change.

Instead this one statement:

rs.Open "SELECT * FROM qryPriceSheet " & whereCond, cn

I loaded my sql statement into a separate 'sqlString' string variable, built the Wherecond string just like I started out and used the following two statement without changing anything else:

  sqlString = sqlString & whereCond
   rs.Open sqlString, cn

It ran the first time I tried it, no moe misplaced quotes etc...

Thanks again for the input and helping me through this frustrating situation.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question