Need to get result of a select query in code

[Robert]

I have a select statement that I need to run in code. The select statement
needs to reference 2 variables which are in the code. I know that the usual
way to run a select in code is with DLookup. This if fine because I only
need 1 column of 1 record even though the select can return more than 1
record. But, the query needs to reference those 2 variables in the code.
How can I run a select in code which references 2 fields in the code and get
the result of 1 record (only)? If necessary I could put those 2 values in
textboxes on forms as I have several queries which reference textboxes on
forms but is there a way to do it directly in the code?

Robert

 

[John W. Vinson]

I have a select statement that I need to run in code. The select statement
needs to reference 2 variables which are in the code. I know that the usual
way to run a select in code is with DLookup. This if fine because I only
need 1 column of 1 record even though the select can return more than 1
record. But, the query needs to reference those 2 variables in the code.
How can I run a select in code which references 2 fields in the code and get
the result of 1 record (only)? If necessary I could put those 2 values in
textboxes on forms as I have several queries which reference textboxes on
forms but is there a way to do it directly in the code?

Robert

A DLookUp can reference two - or twenty - values. The third argument to
DLookUp is a criteria string, which just needs to be a valid SQL WHERE clause
(without the word WHERE). It can include multiple arguments, multiple AND and
OR clauses, subqueries, etc. If you're assuming you can only use one field as
criteria - revise your assumption!

You can also use a Query (again with multiple critera); these criteria can be
of the form

=[Forms]![YourFormName]![SomeControlName]

to select values based on entries on the form, and you can use DLookUp to
retrieve one record from this query.

John W. Vinson [MVP]

 

[Robert]

The problem is that it's a nested query with 2 where clauses. I was
thinking along the lines of using a string and concatenating in these 2
variables. But the currentdb.execute or docmd.runsql approach rejects
select queries. So I was wondering if there was any way a select string
could be executed in code without creating a recordset. Maybe not.

I have a select statement that I need to run in code. The select
statement
needs to reference 2 variables which are in the code. I know that the
usual
way to run a select in code is with DLookup. This if fine because I only
need 1 column of 1 record even though the select can return more than 1
record. But, the query needs to reference those 2 variables in the code.
How can I run a select in code which references 2 fields in the code and
get
the result of 1 record (only)? If necessary I could put those 2 values in
textboxes on forms as I have several queries which reference textboxes on
forms but is there a way to do it directly in the code?

Robert

A DLookUp can reference two - or twenty - values. The third argument to
DLookUp is a criteria string, which just needs to be a valid SQL WHERE
clause
(without the word WHERE). It can include multiple arguments, multiple AND
and
OR clauses, subqueries, etc. If you're assuming you can only use one field
as
criteria - revise your assumption!

You can also use a Query (again with multiple critera); these criteria can
be
of the form

=[Forms]![YourFormName]![SomeControlName]

to select values based on entries on the form, and you can use DLookUp to
retrieve one record from this query.

John W. Vinson [MVP]

 

[John W. Vinson]

The problem is that it's a nested query with 2 where clauses. I was
thinking along the lines of using a string and concatenating in these 2
variables. But the currentdb.execute or docmd.runsql approach rejects
select queries. So I was wondering if there was any way a select string
could be executed in code without creating a recordset. Maybe not.

If you can save the query, you can use DLookUp to look up a value in the
query.

You do not need to, nor should you, "run" or "open" the query to do so. Just

xyz = DLookUp("[fieldname]", "[queryname]")

will do, if the query returns only one record.

A Recordset may well be better, especially if your query returns more than one
record or if you want to retrieve more than one field from the record.

John W. Vinson [MVP]

 

[Robert]

Here is the actual query:

SELECT qryItemsmodels.Itemid, qryItemsmodels.Modelid, qryItemsmodels.Name1,
qryItemsmodels.Manufacturerid, qryItemsmodels.Iteminout
FROM qryItemsmodels
WHERE (((qryItemsmodels.Itemid) Not In (SELECT tblRentaldetails.Itemid
FROM tblRentaldetails INNER JOIN tblRentals ON tblRentaldetails.Rentalid =
tblRentals.Rentalid
WHERE ( tblRentals.Startdateandtime<=
[Forms]![frmreservations]![txtstartdateandtime] AND
tblrentals.enddateandtime >=
[Forms]![frmreservations]![txtstartdateandtime] AND (
([tblRentaldetails].[returndateandtime]) Is Null) ) OR
( tblRentals.Startdateandtime<=[Forms]![frmreservations]![txtenddateandtime]
AND
tblrentals.enddateandtime >= [Forms]![frmreservations]![txtenddateandtime]
AND ( ([tblRentaldetails].[returndateandtime]) Is Null) ) OR
(
tblRentals.Startdateandtime>=[Forms]![frmreservations]![txtstartdateandtime]
AND
tblrentals.enddateandtime <= [Forms]![frmreservations]![txtenddateandtime]
AND ( ([tblRentaldetails].[returndateandtime]) Is Null) ))) AND
((qryItemsmodels.Modelid)=[Forms]![frmmodelspopupforres]![lstModels]));

It returns more than one records but I only actually need one of them.
qryItemsmodels is also a query.

The problem is that it's a nested query with 2 where clauses. I was
thinking along the lines of using a string and concatenating in these 2
variables. But the currentdb.execute or docmd.runsql approach rejects
select queries. So I was wondering if there was any way a select string
could be executed in code without creating a recordset. Maybe not.

If you can save the query, you can use DLookUp to look up a value in the
query.

You do not need to, nor should you, "run" or "open" the query to do so.
Just

xyz = DLookUp("[fieldname]", "[queryname]")

will do, if the query returns only one record.

A Recordset may well be better, especially if your query returns more than
one
record or if you want to retrieve more than one field from the record.

John W. Vinson [MVP]

 

[John W. Vinson]

Here is the actual query:

SELECT qryItemsmodels.Itemid, qryItemsmodels.Modelid, qryItemsmodels.Name1,
qryItemsmodels.Manufacturerid, qryItemsmodels.Iteminout
FROM qryItemsmodels
WHERE (((qryItemsmodels.Itemid) Not In (SELECT tblRentaldetails.Itemid
FROM tblRentaldetails INNER JOIN tblRentals ON tblRentaldetails.Rentalid =
tblRentals.Rentalid
WHERE ( tblRentals.Startdateandtime<=
[Forms]![frmreservations]![txtstartdateandtime] AND
tblrentals.enddateandtime >=
[Forms]![frmreservations]![txtstartdateandtime] AND (
([tblRentaldetails].[returndateandtime]) Is Null) ) OR
( tblRentals.Startdateandtime<=[Forms]![frmreservations]![txtenddateandtime]
AND
tblrentals.enddateandtime >= [Forms]![frmreservations]![txtenddateandtime]
AND ( ([tblRentaldetails].[returndateandtime]) Is Null) ) OR
(
tblRentals.Startdateandtime>=[Forms]![frmreservations]![txtstartdateandtime]
AND
tblrentals.enddateandtime <= [Forms]![frmreservations]![txtenddateandtime]
AND ( ([tblRentaldetails].[returndateandtime]) Is Null) ))) AND
((qryItemsmodels.Modelid)=[Forms]![frmmodelspopupforres]![lstModels]));

It returns more than one records but I only actually need one of them.

Which record? Does it matter? Would ny arbitrary record do or is there some
preference (most recent rental, currently out, ...?)

Which field or fields do you want to retrieve? ItemID? All of the fields?

What are you going to do with the value or values once you retrieve them?


John W. Vinson [MVP]

 

[Robert]

Well, I've already got it working with a recordset. All I need is the field
Itemid from any one of the records.

Here is the actual query:

SELECT qryItemsmodels.Itemid, qryItemsmodels.Modelid,
qryItemsmodels.Name1,
qryItemsmodels.Manufacturerid, qryItemsmodels.Iteminout
FROM qryItemsmodels
WHERE (((qryItemsmodels.Itemid) Not In (SELECT tblRentaldetails.Itemid
FROM tblRentaldetails INNER JOIN tblRentals ON tblRentaldetails.Rentalid =
tblRentals.Rentalid
WHERE ( tblRentals.Startdateandtime<=
[Forms]![frmreservations]![txtstartdateandtime] AND
tblrentals.enddateandtime >=
[Forms]![frmreservations]![txtstartdateandtime] AND (
([tblRentaldetails].[returndateandtime]) Is Null) ) OR
(
tblRentals.Startdateandtime<=[Forms]![frmreservations]![txtenddateandtime]
AND
tblrentals.enddateandtime >=
[Forms]![frmreservations]![txtenddateandtime]
AND ( ([tblRentaldetails].[returndateandtime]) Is Null) ) OR
(
tblRentals.Startdateandtime>=[Forms]![frmreservations]![txtstartdateandtime]
AND
tblrentals.enddateandtime <=
[Forms]![frmreservations]![txtenddateandtime]
AND ( ([tblRentaldetails].[returndateandtime]) Is Null) ))) AND
((qryItemsmodels.Modelid)=[Forms]![frmmodelspopupforres]![lstModels]));

It returns more than one records but I only actually need one of them.

Which record? Does it matter? Would ny arbitrary record do or is there
some
preference (most recent rental, currently out, ...?)

Which field or fields do you want to retrieve? ItemID? All of the fields?

What are you going to do with the value or values once you retrieve them?


John W. Vinson [MVP]

 

[John W. Vinson]

Well, I've already got it working with a recordset. All I need is the field
Itemid from any one of the records.

I'm confused then.

With that requirement,

xyz = DLookUp("[ItemID]", "queryname")

will do the job, with no recordset and no additional code.

I presume it does not do so? What DOES it do?

John W. Vinson [MVP]

 

[Robert]

No, we've got the startdateandtime and the enddateandtime and the modelid
which must be used to filter. The startdateandtime and enddateandtime go in
the inner query.

Well, I've already got it working with a recordset. All I need is the
field
Itemid from any one of the records.

I'm confused then.

With that requirement,

xyz = DLookUp("[ItemID]", "queryname")

will do the job, with no recordset and no additional code.

I presume it does not do so? What DOES it do?

John W. Vinson [MVP]

 

[John W. Vinson]

No, we've got the startdateandtime and the enddateandtime and the modelid
which must be used to filter. The startdateandtime and enddateandtime go in
the inner query.

Yes. I understand that.

If you use DLookUp on the outer query...

the outer query will call the inner query...

the inner query will evaluate its paramters...

and DLookUp will return the appropriate value.

Did you try it? Trust me, it will work. It doesn't matter how complex the
query is; if you can open a recordset on the query, then you can call DLookUp
on the query.

John W. Vinson [MVP]

 

[Robert]

No, I didn't try it with the whole query. What would be the where
condition?

 

[John W. Vinson]

No, I didn't try it with the whole query. What would be the where
condition?

The where condition IS IN THE QUERY ALREADY.

It would not need to be referenced in the DLookUp.

The query is

SELECT qryItemsmodels.Itemid, qryItemsmodels.Modelid, qryItemsmodels.Name1,
qryItemsmodels.Manufacturerid, qryItemsmodels.Iteminout
FROM qryItemsmodels
WHERE (((qryItemsmodels.Itemid) Not In (SELECT tblRentaldetails.Itemid
FROM tblRentaldetails INNER JOIN tblRentals ON tblRentaldetails.Rentalid =
tblRentals.Rentalid
WHERE ( tblRentals.Startdateandtime<=
[Forms]![frmreservations]![txtstartdateandtime] AND
tblrentals.enddateandtime >=
[Forms]![frmreservations]![txtstartdateandtime] AND (
([tblRentaldetails].[returndateandtime]) Is Null) ) OR
( tblRentals.Startdateandtime<=[Forms]![frmreservations]![txtenddateandtime]
AND
tblrentals.enddateandtime >= [Forms]![frmreservations]![txtenddateandtime]
AND ( ([tblRentaldetails].[returndateandtime]) Is Null) ) OR
(
tblRentals.Startdateandtime>=[Forms]![frmreservations]![txtstartdateandtime]
AND
tblrentals.enddateandtime <= [Forms]![frmreservations]![txtenddateandtime]
AND ( ([tblRentaldetails].[returndateandtime]) Is Null) ))) AND
((qryItemsmodels.Modelid)=[Forms]![frmmodelspopupforres]![lstModels]));


If frmmodelspopupforres and frmreservations are open, they will be used when
DLookUp connects to the query.

Robert, you're borrowing trouble, and the interest rates are ruinous. It is
simply not as difficult as you're trying to make it!!!


John W. Vinson [MVP]

 

[Robert]

You're right (and I had already done a similar dLookup with form references
in it). But when I tried this one, it didn't work, probably form some other
error. Anyway it's working now as a recordset and I'll just leave it at
that. Thanks for you help.


That was my whole

No, I didn't try it with the whole query. What would be the where
condition?

The where condition IS IN THE QUERY ALREADY.

It would not need to be referenced in the DLookUp.

The query is

SELECT qryItemsmodels.Itemid, qryItemsmodels.Modelid,
qryItemsmodels.Name1,
qryItemsmodels.Manufacturerid, qryItemsmodels.Iteminout
FROM qryItemsmodels
WHERE (((qryItemsmodels.Itemid) Not In (SELECT tblRentaldetails.Itemid
FROM tblRentaldetails INNER JOIN tblRentals ON tblRentaldetails.Rentalid =
tblRentals.Rentalid
WHERE ( tblRentals.Startdateandtime<=
[Forms]![frmreservations]![txtstartdateandtime] AND
tblrentals.enddateandtime >=
[Forms]![frmreservations]![txtstartdateandtime] AND (
([tblRentaldetails].[returndateandtime]) Is Null) ) OR
(
tblRentals.Startdateandtime<=[Forms]![frmreservations]![txtenddateandtime]
AND
tblrentals.enddateandtime >= [Forms]![frmreservations]![txtenddateandtime]
AND ( ([tblRentaldetails].[returndateandtime]) Is Null) ) OR
(
tblRentals.Startdateandtime>=[Forms]![frmreservations]![txtstartdateandtime]
AND
tblrentals.enddateandtime <= [Forms]![frmreservations]![txtenddateandtime]
AND ( ([tblRentaldetails].[returndateandtime]) Is Null) ))) AND
((qryItemsmodels.Modelid)=[Forms]![frmmodelspopupforres]![lstModels]));


If frmmodelspopupforres and frmreservations are open, they will be used
when
DLookUp connects to the query.

Robert, you're borrowing trouble, and the interest rates are ruinous. It
is
simply not as difficult as you're trying to make it!!!


John W. Vinson [MVP]