Select records based on Multi Entries 2

[ruru9292]

Hello,

I am trying to run a query that pull all the records base on an entry a user make on a form. for example. Consider selecting all records where multi OrderNo entered by the user on a form seperating them by ; .

I am using the select statement below but getting no result. if I enter one order then I get result. Please advice.

Select * from Orders WHERE OrderNo In ([Forms]![myform]![UserEntry])

Note: where User's Entry could look like 1235;2345;3456

thank you in advance.

 

[dhookom]

Won't work that way. You probably don't have a single OrderNo with a value of "1235;2345;3456". There are some FAQs in either this forum or in the Reports forum that provide code on multiselect list boxes.

Depending on your OrderNo data type, you could use something like:
WHERE Instr(";" & [Forms]![myform]![UserEntry] & ";", ";" & [OrderNo] & ";") > 0

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ruru9292]

Thanks for the tips

 

[ruru9292]

Hello,

I am trying to pull mutiple order from MyTbl and make new table Called NewTbl based on entries a user can type on a continious form. This form is bound to table called MySelectTbl. The user types on each row one order #. I am using the code below to read all the entries the user enter and make NewTbl with records that match his entries. However; I am getting Debug error at the Where Clause. Please Advice.

thanks.

Private Sub Command0_Click()
Dim cn As DAO.Database
Dim rs, tst As DAO.Recordset
Dim StCr, Crt As String
Set db = CurrentDb
Set rs = db.OpenRecordset("MySelectTbl")

rs.MoveFirst
StCr = ""
Crt = ""
Do Until rs.EOF
StCr = rs!UserEntry
If Len(Crt) > 0 Then
Crt = Crt & " Or OrdId = '" & StCr & "'"
Else
Crt = "OrdID = '" & StCr & "'"
End If
rs.MoveNext
Loop
Set tst = db.OpenRecordset("SELECT x,y,z INTO NewTbl " & _
"FROM MyTbl" & _
"WHERE " & Crt)
End Sub

 

[dhookom]

Not sure why you are doing this however to debug, I would use:

Private Sub Command0_Click()
      Dim cn As DAO.Database
      Dim rs As DAO.Recordset, tst As DAO.Recordset
      Dim StCr, Crt As String
      Dim strSQL as String
      Set db = CurrentDb
      Set rs = db.OpenRecordset("MySelectTbl")
      
rs.MoveFirst
StCr = ""
Crt = ""
Do Until rs.EOF
    StCr = rs!UserEntry
    If Len(Crt) > 0 Then
        Crt = Crt & " Or OrdId = '" & StCr & "'"
    Else
        Crt = "OrdID = '" & StCr & "'"
    End If
    rs.MoveNext
Loop
strSQL = "SELECT x,y,z INTO NewTbl " & _
      "FROM MyTbl" & _
      "WHERE " & Crt
Debug.Print strSQL
'Set tst = db.OpenRecordset(strSQL)
End Sub

Then open the debug window to view the sql that you are attempting to run.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ruru9292]

I am using the method above because I tried to use this statement below and did not work. every time I add it on my query criteria line it automatically changes the 0 to "0". I welcome any other method that will do multi selection.

WHERE Instr(";" & [Forms]![myform]![UserEntry] & ";", ";" & [OrderNo] & ";") > 0

Thanks.

 

[dhookom]

I'm not sure
-what you added to your criteria line since we can't see your full sql view
-what was in you debug window that I asked you to test

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ruru9292]

Okay Here the actual code and debug result for two entries only on the form.

Private Sub Command3_Click()
Dim cn As DAO.Database
Dim rs As DAO.Recordset, tst As DAO.Recordset
Dim StCr, Crt As String
Dim strSQL As String
Set db = CurrentDb
Set rs = db.OpenRecordset("UserEntry")

rs.MoveFirst
StCr = ""
Crt = ""
Do Until rs.EOF
StCr = rs!OrdID
If Len(Crt) > 0 Then
Crt = Crt & " Or dbo_SOShipLine.ShipperID = '" & StCr & "'"
Else
Crt = "dbo_SOShipLine.ShipperID = '" & StCr & "'"
End If
rs.MoveNext
Loop
strSQL = "SELECT dbo_SOShipHeader.OrdNbr, dbo_SOShipLine.ShipperID, dbo_SOShipLine.InvtID INTO NewTbl " & _
"FROM dbo_SOShipHeader INNER JOIN dbo_SOShipLine ON dbo_SOShipHeader.ShipperID = dbo_SOShipLine.ShipperID " & _
"WHERE " & Crt
Debug.Print strSQL
Set tst = db.OpenRecordset(strSQL)
End Sub

Debug:
SELECT dbo_SOShipHeader.OrdNbr, dbo_SOShipLine.ShipperID, dbo_SOShipLine.InvtID INTO NewTbl FROM dbo_SOShipHeader INNER JOIN dbo_SOShipLine ON dbo_SOShipHeader.ShipperID = dbo_SOShipLine.ShipperID WHERE dbo_SOShipLine.ShipperID = 's0086859' Or dbo_SOShipLine.ShipperID = 's0086524'

After simplifying the select statement I got the debug result with no errors, but now my table is not generated. if I run my query and put physically the above value in the debug I get the NewTbl with records matched.

Any Idea why not generating my NewTbl now?

Thanks

 

[PHV]

What about changing this:
Set tst = db.OpenRecordset(strSQL)
By this ?
db.Execute strSQL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ruru9292]

I did change it to command below and still not making new table. again if I open my query and run it it generates this table with no problem.

db.Execute strSQL

 

[PHV]

And this ?
DoCmd.RunSQL strSQL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ruru9292]

This statement works and generate my new table. thanks a million for all who help out.