passing multiple records to a query

[Guest]

How can I pass a series of user defined records to a query.
IE ID 1, 5 and 13.
I have tried typing 1 OR 5 OR 13 in the parameter box when prompted but this
does not work.
Ultimately, I want the user to pass these values by selecting records on a
sub form but at the moment I can't get the query to work.
Any ideas?
I am using A2003

 

[Steve Schapel]

Simon,

If the user will select the records in a subform, consider adding a
Yes/No field to the table that the subform will be based on, and
represent this by a checkbox on the subform. That way, your query won't
need to relate directly to the ID value of the selected records, just
apply the Criteria -1 to the checkbox field.

 

[banem2]

How can I pass a series of user defined records to a query.
IE ID 1, 5 and 13.
I have tried typing 1 OR 5 OR 13 in the parameter box when prompted but this
does not work.
Ultimately, I want the user to pass these values by selecting records on a
sub form but at the moment I can't get the query to work.
Any ideas?
I am using A2003

You cannot do this as Access will use that as (simplified)

FieldName = "1 OR 5 OR 13"

What you need is:

FieldName = "1" OR FieldName = "5" OR FieldName = "13"

Therefore you cannot use such a parameters that way and, basically,
please use Steve advice.

Regards,
Branislav Mihaljev

 

[Guest]

Hi Steve,
Thx for the swift response.
As this is just a way of temporarily selecting specific records, if I do
that I would need to set all the tick boxes back to false before closing the
form. The data source is a union query based on 4 different tables. Do you
think this is still the best way?

 

[Guest]

As Steve indicated, you cannot do this with a parameter query. Two questions?

1. How do plan on selecting the records you want to pass? Since you are
using a union query rather than a table, I don't think Steve's suggestion of
the Yes/No field will help much. Since you have indicated that you want to
be able to select multiple items, my assumption is that you will be
displaying the information in a multi-select listbox.

2. What do you want to do with this second recordset that you are
selecting? Are you going to use it as the data source for another form or
report? If so, you can use WhereCondition parameter in the OpenForm or
OpenReport methods to restrict the recordset. If this is the case, I assume
you have a button on your form to open the form or report based on the items
selected in your listbox. If this is the case, then you would define your
where clause using code similar to the following.

Private sub cmd_OpenForm_Click

Dim varList as variant, varItem as Variant

varList = NULL
For each varItem in me.lst_SelectRecords.ItemsSelected
'this next line assumes that the bound column in your list is 1
varList = (varList + ", ") & me.lst_SelectedRecords(0, varItem)

Next
varList = "[ID] IN (" + varList + ")"
docmd.openform "frmNextForm" , , , varList

End Sub

HTH
Dale

 

[Guest]

Hi Dale,
Your option 2 is pretty much where I'm going which is why (as you say)
Steve's answer doesn't help. I was using a command button on the sub form to
copy the record ID into an unbound text box on the Parent form then using
that text box as the criteria in the query which runs the report. This is
fine if I only select 1 record but falls over on multiples. I was using this
simple code

If IsNull(Parent!Text37) Then
Parent!Text37 = Me!UID
Else
Parent!Text37 = Parent!Text37.OldValue & " Or " & Me!UID
End If

I've not used list boxes before. Should this be an unbound list box with its
not in list property set to ok? or something?

Thanks very much for your help
Simon

As Steve indicated, you cannot do this with a parameter query. Two questions?

1. How do plan on selecting the records you want to pass? Since you are
using a union query rather than a table, I don't think Steve's suggestion of
the Yes/No field will help much. Since you have indicated that you want to
be able to select multiple items, my assumption is that you will be
displaying the information in a multi-select listbox.

2. What do you want to do with this second recordset that you are
selecting? Are you going to use it as the data source for another form or
report? If so, you can use WhereCondition parameter in the OpenForm or
OpenReport methods to restrict the recordset. If this is the case, I assume
you have a button on your form to open the form or report based on the items
selected in your listbox. If this is the case, then you would define your
where clause using code similar to the following.

Private sub cmd_OpenForm_Click

Dim varList as variant, varItem as Variant

varList = NULL
For each varItem in me.lst_SelectRecords.ItemsSelected
'this next line assumes that the bound column in your list is 1
varList = (varList + ", ") & me.lst_SelectedRecords(0, varItem)

Next
varList = "[ID] IN (" + varList + ")"
docmd.openform "frmNextForm" , , , varList

End Sub

HTH
Dale


--
Email address is not valid.
Please reply to newsgroup only.

How can I pass a series of user defined records to a query.
IE ID 1, 5 and 13.
I have tried typing 1 OR 5 OR 13 in the parameter box when prompted but this
does not work.
Ultimately, I want the user to pass these values by selecting records on a
sub form but at the moment I can't get the query to work.
Any ideas?
I am using A2003

 

[Steve Schapel]

Simon,

Yes, I do still think this is the best way. It is easy to run an Update
Query on the Close event of the form to set the value of the Yes/No
field back to 0.

 

[Guest]

As Steve indicated, you cannot do this with a parameter query.

Why do you say that? Consider this example using Northwind, with the
addition of a standard issue Sequence table of unique integers (seq), to
parse a delimited string of CustomerIDs to a (derived) table of individual
parameter values to use in a JOIN to return the respective rows from the
Customers table (ANSI-92 Query Mode syntax i.e. 'parameter query' =
PROCEDURE):

CREATE PROCEDURE GetCustomersFromList
(
delimted_text MEMO,
delimiter VARCHAR(4) = ','
)
AS
SELECT C1.CustomerID, C1.CompanyName, C1.ContactName
FROM Customers AS C1
INNER JOIN
(
SELECT MID(I1.input_string, S1.seq, MIN(S2.seq) - S1.seq -
LEN(delimiter)) AS param
FROM
(
SELECT DISTINCT delimted_text AS input_string
FROM SEQUENCE AS S3
) AS I1, SEQUENCE AS S1, SEQUENCE AS S2
WHERE MID(delimiter & I1.input_string & delimiter, S1.seq,
LEN(delimiter)) = delimiter
AND MID(delimiter & I1.input_string & delimiter, S2.seq, LEN(delimiter))
= delimiter
AND S1.seq < S2.seq
AND S1.seq BETWEEN 1 AND LEN(delimiter) + LEN(delimted_text) +
LEN(delimiter)
AND S2.seq BETWEEN 1 AND LEN(delimiter) + LEN(delimted_text) +
LEN(delimiter)
GROUP BY I1.input_string, S1.seq
HAVING LEN(MID(I1.input_string, S1.seq, MAX(S2.seq) - S1.seq -
LEN(delimiter))) > 0
) AS LoadTable
ON C1.CustomerID = LoadTable.param;

Example usage:

EXECUTE GetCustomersFromList 'ALFKI,ANTON,BERGS'

Jamie.

--

 

[Guest]

How can I pass a series of user defined records to a query.
IE ID 1, 5 and 13.
I have tried typing 1 OR 5 OR 13 in the parameter box when prompted but this
does not work.
Ultimately, I want the user to pass these values by selecting records on a
sub form but at the moment I can't get the query to work.
Any ideas?

Considering the implicit question in the title of of your post, "passing
multiple records to a SQL query" the answer is "Use a JOIN".

Take the delimited input string, parse it using the delimiting character(s)
where the delimiter is either hard-coded or (preferrably IMO) another
optional parameter, put each value into a load table (make sure you've
cleared it down first) then invoke a query/view that creates a JOIN between
your load table and your target table(s).

While you can do all this in one hit in pure SQL using a parameter query
(PROCEDURE), an example of which I've posted elsewhere in this thread, you
could do the same procedurally using VBA code if more comfortable.

Jamie.

--

 

[Guest]

Jamie,

Why do you insist on confusing the issue.

The OP indicated that the he is typing text into an inputbox (Parameter)
when his query is run.

You cannot implement the code you posted in the other thread, or the VBA, in
a simple ACCESS query!

 

[Jamie Collins]

You cannot implement the [CREATE PROCEDURE] code you posted in the other thread ... in
a simple ACCESS query!

The SQL to create the PROC can be run in the Access user interface
when in ANSI-92 Query Mode; it can also be run in 'traditional'
ANSI-89 Query Mode by altering from:

CREATE PROCEDURE GetCustomersFromList
(
delimted_text MEMO,
delimiter VARCHAR(4) = ','
)
AS
SELECT ...

to (aircode):

PARAMETERS delimted_text MEMO, delimiter VARCHAR(4);
SELECT...

and manually saving the Query object as 'GetCustomersFromList';
however, I'm not sure whether the revised syntax fully supports
default parameter values.

You cannot implement the ... VBA,

I didn't post any VBA. Do you must mean my description of a possible
'procedural' approach. If so, what do you think is not achievable
using VBA? Parsing a delimited string? Putting values in a table?
Invoking a query?

One problem I have is that "ACCESS query" could mean many things. I
reserve 'query' for a SQL DML SELECT that returns a resultset,
anything else being a SQL *statement*; phrases such as 'delete query'
are contradictory for me. Sometimes 'query' is used here to mean an
object stored in the mdb, that which I'd call either a VIEW or a
PROCEDURE (depending on whether the resultset can be used as a virtual
table); these more specific terms have been around in Access/Jet SQL
syntax for nearly a decade now: OK SQL-92 Query Mode isn't the
'traditional' mode but has been the default since at least Access
2003. 'Access' sometimes means just the user interface, sometimes it
means just the engine. Sometimes 'Access' is used loosely by intent,
other times the originator is being vague because they can't tell the
interface from the engine.

What do *you* mean by "ACCESS query"?

The OP indicated that the he is typing text into an inputbox (Parameter)
when his query is run.

A PROC defined at the engine level using CREATE PROCEDURE or
PARAMETERS syntax can by invoked in that way in the Access user
interface.

Why do you insist on confusing the issue.

A little self doubt can take you a long way; perhaps it is you who is
confused

Here's a suggestion: try getting my example running in Northwind.
Perhaps it will clarify a few things for you. Here's some VBA to
create a new mdb with the tables, data and parameter query for you to
to see it working (and perhaps examine in the Access user interface):

Sub ParseDelimitedCustomers()
Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
..Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim sql As String
sql = _
"CREATE TABLE Sequence (seq INTEGER" & _
" PRIMARY KEY);"
..Execute sql
sql = _
"INSERT INTO Sequence (seq) VALUES" & _
" (1);"
..Execute sql
sql = _
"INSERT INTO Sequence (seq)" & _
" SELECT Units.nbr + Tens.nbr FROM" & _
" (SELECT nbr FROM (SELECT 0 AS" & _
" nbr FROM Sequence UNION ALL SELECT" & _
" 1 FROM Sequence UNION ALL SELECT" & _
" 2 FROM Sequence UNION ALL SELECT" & _
" 3 FROM Sequence UNION ALL SELECT" & _
" 4 FROM Sequence UNION ALL SELECT" & _
" 5 FROM Sequence UNION ALL SELECT" & _
" 6 FROM Sequence UNION ALL SELECT" & _
" 7 FROM Sequence UNION ALL SELECT" & _
" 8 FROM Sequence UNION ALL SELECT" & _
" 9 FROM Sequence) AS Digits) AS" & _
" Units, (SELECT nbr * 10 AS nbr" & _
" FROM (SELECT 0 AS nbr FROM Sequence" & _
" UNION ALL SELECT 1 FROM Sequence" & _
" UNION ALL SELECT 2 FROM Sequence" & _
" UNION ALL SELECT 3 FROM Sequence" & _
" UNION ALL SELECT 4 FROM Sequence" & _
" UNION ALL SELECT 5 FROM Sequence" & _
" UNION ALL SELECT 6 FROM Sequence"
sql = sql & _
" UNION ALL SELECT 7 FROM Sequence" & _
" UNION ALL SELECT 8 FROM Sequence" & _
" UNION ALL SELECT 9 FROM Sequence)" & _
" AS Digits) AS Tens WHERE Units.nbr" & _
" + Tens.nbr <> 1;"
..Execute sql
sql = _
"CREATE TABLE Customers (CustomerID" & _
" NCHAR(5) PRIMARY KEY, CompanyName" & _
" NVARCHAR(40) NOT NULL, ContactName" & _
" NVARCHAR(30));"
..Execute sql
sql = _
"INSERT INTO Customers (CustomerID," & _
" CompanyName, ContactName) SELECT" & _
" DT1.CustomerID, DT1.CompanyName," & _
" DT1.ContactName FROM ( SELECT" & _
" DISTINCT 'ALFKI' AS CustomerID," & _
" 'Alfreds Futterkiste' AS CompanyName," & _
" 'Maria Anders' AS ContactName" & _
" FROM Sequence UNION ALL SELECT" & _
" DISTINCT 'ANATR', 'Ana Trujillo" & _
" Emparedados y helados', 'Ana" & _
" Trujillo' FROM Sequence UNION" & _
" ALL SELECT DISTINCT 'ANTON'," & _
" 'Antonio Moreno Taquería', 'Antonio" & _
" Moreno' FROM Sequence UNION ALL" & _
" SELECT DISTINCT 'AROUT', 'Around" & _
" the Horn', 'Thomas Hardy' FROM" & _
" Sequence UNION ALL SELECT DISTINCT" & _
" 'BERGS', 'Berglunds snabbköp'," & _
" 'Christina Berglund' FROM Sequence" & _
" UNION ALL SELECT DISTINCT 'BLAUS',"
sql = sql & _
" 'Blauer See Delikatessen', 'Hanna" & _
" Moos' FROM Sequence UNION ALL" & _
" SELECT DISTINCT 'BLONP', 'Blondel" & _
" père et fils', 'Frédérique Citeaux'" & _
" FROM Sequence UNION ALL SELECT" & _
" DISTINCT 'BOLID', 'Bólido Comidas" & _
" preparadas', 'Martín Sommer'" & _
" FROM Sequence UNION ALL SELECT" & _
" DISTINCT 'BONAP', 'Bon app'''," & _
" 'Laurence Lebihan' FROM Sequence)" & _
" AS DT1;"
..Execute sql
sql = _
"CREATE PROCEDURE GetCustomersFromList" & _
" ( delimted_text MEMO, delimiter" & _
" VARCHAR(4) = ',' ) AS SELECT" & _
" C1.CustomerID, C1.CompanyName," & _
" C1.ContactName FROM Customers" & _
" AS C1 INNER JOIN ( SELECT MID(I1.input_string," & _
" S1.seq, MIN(S2.seq) - S1.seq" & _
" - LEN(delimiter)) AS param FROM" & _
" ( SELECT DISTINCT delimted_text" & _
" AS input_string FROM SEQUENCE" & _
" AS S3 ) AS I1, SEQUENCE AS S1," & _
" SEQUENCE AS S2 WHERE MID(delimiter" & _
" & I1.input_string & delimiter," & _
" S1.seq, LEN(delimiter)) = delimiter" & _
" AND MID(delimiter & I1.input_string" & _
" & delimiter, S2.seq, LEN(delimiter))" & _
" = delimiter AND S1.seq < S2.seq" & _
" AND S1.seq BETWEEN 1 AND LEN(delimiter)" & _
" + LEN(delimted_text) + LEN(delimiter)" & _
" AND S2.seq BETWEEN 1 AND LEN(delimiter)"
sql = sql & _
" + LEN(delimted_text) + LEN(delimiter)" & _
" GROUP BY I1.input_string, S1.seq" & _
" HAVING LEN(MID(I1.input_string," & _
" S1.seq, MAX(S2.seq) - S1.seq" & _
" - LEN(delimiter))) > 0 ) AS LoadTable" & _
" ON C1.CustomerID = LoadTable.param;"
..Execute sql
sql = _
"EXECUTE GetCustomersFromList" & _
"'ANTON,BLAUS,BOLID';"
Dim rs
Set rs = .Execute(sql)
MsgBox rs.GetString
End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--