Joining two select....statement to pull the records needed

[lalee5]

Below is my code. I am trying to select only the records meeting the two criteria and then clone the data to a datagrid. I need helo joining the two select statements. I have try "and"...but it does not work.. it says data mismatch.


there are two text box for user input for the select"

polookup1
itemnolookup1



then a command button for click to execute the following:


strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=J:\purchaseorders.mdb;Persist Security Info=False"
Set CN = New ADODB.Connection
CN.Open strConn


stcnQL1 = "SELECT * FROM tpurchaseorders where pono=" & "'" + Polookup1.Text + "'" And "SELECT * FROM tpurchaseorders where itemno=" & "'" + Itemnolookup1.Text + "'"



Set RS1 = New ADODB.Recordset


With rsclone
.ActiveConnection = CN
.CursorLocation = adUseServer
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Properties("IRowsetIdentity") = True
.Open stcnQL1, , , , adCmdText



End With



Set Adodc1.Recordset = RS1

Set DataGrid1.DataSource = Adodc1.Recordset
Set rsclone = Adodc1.Recordset.Clone



Please help me joined the two select statements


thanks in advance

 

[gmmastros]

Can you please clarify this a little?

polookup1
itemnolookup1

If the user enters data in to both text boxes, what should be returned? Records that match both criteria?

Also...

If the user enters information in to polookup1 but leaves itemnolookup1 blank, what should be returned? Records that match polookup1 regardless of itemnolookup1 or Records that match polookup1 and itemnolookup1 is blank/null?

Generally speaking, to write a query with multiple criteria, the structure would be...

[tt]
SELECT *
FROM tpurchaseorders
where pono=SomeValue
And itemno=SomeOtherValue
[/tt]

Using this structure, you may have a problem when the user leaves one (or both) text boxes blank.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lalee5]

The result should return the records meeting both critria. I have test this on one criteria and it work. what i can do is put a if statements that:

If Polookup1 <>"" then...........

else...

Msg"both criteria must have value"

end if

but that is beside the point....the main problem is that my select statement is giving me a "data type mismatch" error.

I need to combined both statement correct to select the records then clone the selected data to the datagrid.

 

[gmmastros]

Try this....

[tt][blue]
stcnQL1 = "SELECT * FROM tpurchaseorders where pono='" & Polookup1.Text & "' And itemno='" & Itemnolookup1.Text & "'"
[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lalee5]

I replace my code with yours and it is moving paste that lind of code and giving me an error "run-time error 424 object required" at:


ActiveConnection = CN

any idea why this happen.


again it works fine with only one select criteria

 

[gmmastros]

Because the ActiveConnection property is an Object. As such, you need to [!]Set[/!] it. Like this...

   With rsclone
        [!]Set [/!].ActiveConnection = CN
        .CursorLocation = adUseServer
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Properties("IRowsetIdentity") = True
        .Open stcnQL1, , , , adCmdText
        
               
        
    End With

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lalee5]

I did the following and still same error message


With rsclone
Set .ActiveConnection = CN
.CursorLocation = adUseServer
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Properties("IRowsetIdentity") = True
.Open stcnQL1, , , , adCmdText



End With



Any other suggest George....thanks in advance

 

[lalee5]

Anyone here...please help.....

is there any easier way to solve this problem

 

[harebrain]

   stcnQL1 = "SELECT * FROM tpurchaseorders where pono=" & "'" + Polookup1.Text + "'" And "SELECT * FROM tpurchaseorders where itemno=" & "'" + Itemnolookup1.Text + "'"

Ampersand is the concatenation operator in VB6. Change the plus signs: they're for arithmetic, which is why you're getting a type mismatch.

 

[strongm]

>Change the plus signs: they're for arithmetic

The plus operator is overloaded; it will happily concatenate strings (although, unlike the & operator, it won't cast a numeric to a string for concatenation)

>which is why you're getting a type mismatch

No, it isn't. For the statement shown, the type mismatch is because there is an attempt to apply a boolean operation (AND) to some text strings. But boolean operators require numeric parameters (or, at least, parameters that can be cast to numerics), so this results in a type mismatch ...

 

[SBerthold]

The statement would work if the "AND" is embedded into the statement and the second SELECT statement had parenthesis around it.

"SELECT * FROM tpurchaseorders where pono=" & "'" + Polookup1.Text + "' And (SELECT * FROM tpurchaseorders where itemno=" & "'" + Itemnolookup1.Text + "')"


But because the two SELECT statements use the same table, it can be shortened to what gmmastros proposed.

 

[MakeItSo]

Do you really want to use a logical AND, resulting in records that only comply with both criteria?

1:
I thought you wanted to ...errr..concatenate ...both outputs, i.e. get results from SELECT 1 and then also display the output of SELECT 2, which will factually leave you with a logical OR:

stcnQL1 = "SELECT * FROM tpurchaseorders where pono=" & "'" & Polookup1.Text & "' OR itemno=" & "'" & Itemnolookup1.Text & "';"

2:
Are your table fields NUMBER or TEXT fields? You surround them in your query with quotes: a no-no for number fields
==> error

stcnQL1 = "SELECT * FROM tpurchaseorders where pono=" & Polookup1.Text & " OR itemno=" & Itemnolookup1.Text

3:
You can also "concatenate" the output of as many SELECTs as you want (provided the output column names are all the same) with the help of a "UNION ALL":

stcnQL1 ="SELECT * FROM tpurchaseorders where pono=" &  Polookup1.Text & "[b] UNION ALL[/b] SELECT * FROM tpurchaseorders where itemno=" & Itemnolookup1.Text

This would pretty much be the correct SQL-equivalent of your "AND" construct.


Ergo: leave away the single quotes around your number fields to remove the error, and then feel free to apply either variancy.
;-)

Cheers,
Andy

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[SBerthold]

>This would pretty much be the correct SQL-equivalent of your "AND" construct.

It could be "One" of the possible intentions, but not the only one (see gmmastros example, see mine, and that the original statement is pulling data from the same table), and therefore "correct" is an assumtion.

I also assumed a UNION ALL at first look.

 

[MakeItSo]

Any news on this issue?

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell