Easier way than filling a recordset (for just 1 value)?

[What-a-Tool]

Need to get a record from a field in table A, depending on a match to a
joined field in table B.
I'm filling a recordset with a SQL string, and then loading the first value
into a label. (there will only ever be 1 value returned.)
Filling a recordset seems like the long way to return a single value. Is
there an easier way? Can't get a DLookup to work because of the join
involved.(so I'm assuming)

Heres my query:

SELECT tblShellVersions.Shell FROM (tblShellVersions INNER JOIN
tblShellStats
ON[tblShellVersions].[ID]=[tblShellStats].[tblShellVersions_ID]) WHERE
(((tblShellStats.Alias)='10')) GROUP BY tblShellVersions.Shell ORDER BY
tblShellVersions.Shell;


--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

 

[Ron Weiner]

Untested! But I think this works. Basically what we are doing is to put the
Join In the Where clause

YourValue = Dlookup("Shell","tblShellVersions","ID="&
DlookUp("tblShellVersions_ID","tblShellStats","Alias='10"))

You should wrap the dlookups with NZ()'s to avoid errors when the result of
the Dlookup is Null

I do not understand the GroupBy and the Order By in your query example.
Looks to me like you could rewrite the Sql as

SELECT Distinct tblShellVersions.Shell
FROM (tblShellVersions INNER JOIN tblShellStats
ON[tblShellVersions].[ID]=[tblShellStats].[tblShellVersions_ID])
WHERE tblShellStats.Alias ='10'

Ron W

 

[What-a-Tool]

Thanks - makes sense. Never thought of wrapping a DLookup within a DLookup.

Yeah, I need to rewrite without the group by and order by - no need with 1
record.
Originally did query by example for that string, and hadn't really thought
about it till I re-read my post.

Thanks Again

--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

Untested! But I think this works. Basically what we are doing is to put the
Join In the Where clause

YourValue = Dlookup("Shell","tblShellVersions","ID="&
DlookUp("tblShellVersions_ID","tblShellStats","Alias='10"))

You should wrap the dlookups with NZ()'s to avoid errors when the result of
the Dlookup is Null

I do not understand the GroupBy and the Order By in your query example.
Looks to me like you could rewrite the Sql as

SELECT Distinct tblShellVersions.Shell
FROM (tblShellVersions INNER JOIN tblShellStats
ON[tblShellVersions].[ID]=[tblShellStats].[tblShellVersions_ID])
WHERE tblShellStats.Alias ='10'

Ron W

Need to get a record from a field in table A, depending on a match to a
joined field in table B.
I'm filling a recordset with a SQL string, and then loading the first value
into a label. (there will only ever be 1 value returned.)
Filling a recordset seems like the long way to return a single value. Is
there an easier way? Can't get a DLookup to work because of the join
involved.(so I'm assuming)

Heres my query:

SELECT tblShellVersions.Shell FROM (tblShellVersions INNER JOIN
tblShellStats
ON[tblShellVersions].[ID]=[tblShellStats].[tblShellVersions_ID]) WHERE
(((tblShellStats.Alias)='10')) GROUP BY tblShellVersions.Shell ORDER BY
tblShellVersions.Shell;


--

/ Sean the Mc /


"I have not failed. I've just found 10,000 ways that won't work."
- Thomas Alva Edison (1847-1931)

 

[david epsom dot com dot au]

DLookup will run against a stored query as well as against a table.
There are also DSUM and DCOUNT functions.
I don't know (haven't tested) how the criteria clause is applied
against an aggregate query. I expect that if you have a saved
query, the criteria will be applied as a HAVING clause (applied
as a where clause to the result of the saved query). If you
use DSUM/DCOUNT against a non-grouped select query, I would expect
the criteria to be applied as a WHERE clause.

(david)

 

[Bryan Hughes]

Hello,

Use this http://members.iinet.net.au/~allenbrowne/ser-42.html from Allen
Brown's website it peforms much better then DLookup.

To replace other Domain Aggregate Functions(DCount, DMax, etc.) you can use
http://www.mvps.org/access/modules/mdl0012.htm.

Hope this helps.
Bryan

 

[What-a-Tool]

Huh?
This was my post from a long time ago!
How'd it get here again and under the name (Sofia)?

--

/ Sean the Mc /

"Opinions are like flatulence - everyone loves the sound of their own, but
anyone else's usually just stinks !"
-anonymous