DLOOKUP() function help!!!!!

[Guest]

Hi there

I basically need to put my query result (single number) in a textbox and eventually in a report. However, Expression Builder just can't copy the query result neither in my textbox on the form nor my report

People asked me to use the DLOOKUP() function to solve this problem. Can anyone please explain that a li'l bit more. Yes, it's right that the results of my queries are single numbers and I want to put them in different textboxes and then eventually in a dynamic report. I shall be very grateful if you could just explain the DLOOKUP() function in detail.

Thanks again

Mike

 

[Steve Schapel]

Mike,

Here's an example... Let's say you have a query called MyQuery which
includes a field called MyColumn. Ok, if you put an unbound textbox on
a form or a report, and in the Control Source of the textbox you put:
=DLookup("[MyColumn]","MyQuery")
.... then the textbox should show the value from the query. If the query
only contains one record, as seems to be the case with yours, this will
be sufficient. If the query contains more than one record, the DLookup
function will return the value from the first record, otherwise you
might need to specify the record you want to use, by using the where
argument, for example:
=DLookup("[MyColumn]","MyQuery","MyID=99")

I think there is good information in Access Help about the domain
aggregate functions.

 

[fredg]

Hi there,

I basically need to put my query result (single number) in a
textbox and eventually in a report. However, Expression Builder
just can't copy the query result neither in my textbox on the form
nor my report.

People asked me to use the DLOOKUP() function to solve this
problem. Can anyone please explain that a li'l bit more. Yes, it's
right that the results of my queries are single numbers and I want
to put them in different textboxes and then eventually in a dynamic
report. I shall be very grateful if you could just explain the
DLOOKUP() function in detail.

Thanks again.

Mike.

You can use the DLookUp function to return the value of one field of
one record, from either a table or a query.
So, if your query is returning just one record, you can use, as
control source of an unbound control:
=DLookUp("[FieldName]","QueryName")

However, if the query returns more than one record, you must use the
where clause argument to limit the data to just the one record.
=DLookUp("FieldName]","QueryName","[SomeFieldID] = " & SomeCriteria)

Note that the where clause is written differently for different types
of datatypes.
See VBA help files for
Restrict + Restrict data to a subset of records

 

[Neil Ginsberg]

You should avoid using DLookup for multiple queries, but just use your query
as the report's recordsource. However, if you can't do that, then you can
use DLookup with the syntax:

=DLookup("FieldName", "QueryName", "Criteria")

where FieldName is the name of the field whose value you want to display,
QueryName would be the name of your query, and criteria is the criteria for
selecting a record (such as "Field1=3"). You can omit Criteria if you want
to just return the first record from the query (or if the query only has one
record). You must include the quotes in the DLookup expression. Place the
entire expression (inluding the "=") in the text box's ControlSource
property.

Neil

Hi there,

I basically need to put my query result (single number) in a textbox and

eventually in a report. However, Expression Builder just can't copy the
query result neither in my textbox on the form nor my report.

People asked me to use the DLOOKUP() function to solve this problem. Can

anyone please explain that a li'l bit more. Yes, it's right that the results
of my queries are single numbers and I want to put them in different
textboxes and then eventually in a dynamic report. I shall be very grateful
if you could just explain the DLOOKUP() function in detail.

 

[Guest]

Hi there

First of all, I would like to thank Steve, fredg and Neil for helping me out with my problem. Needless to say, I've been successful in solving the problem

However, Neil pointed out that I shouldn't use DLOOKUP() function for multiple queries. Why not?? And what should I use, if I don't want to use DLOOKUP()? Is there any other method to solve this problem

Regards
Mike

----- Neil Ginsberg wrote: ----

You should avoid using DLookup for multiple queries, but just use your quer
as the report's recordsource. However, if you can't do that, then you ca
use DLookup with the syntax

=DLookup("FieldName", "QueryName", "Criteria"

where FieldName is the name of the field whose value you want to display
QueryName would be the name of your query, and criteria is the criteria fo
selecting a record (such as "Field1=3"). You can omit Criteria if you wan
to just return the first record from the query (or if the query only has on
record). You must include the quotes in the DLookup expression. Place th
entire expression (inluding the "=") in the text box's ControlSourc
property

Nei

Hi there

eventually in a report. However, Expression Builder just can't copy th
query result neither in my textbox on the form nor my reportanyone please explain that a li'l bit more. Yes, it's right that the result
of my queries are single numbers and I want to put them in differen
textboxes and then eventually in a dynamic report. I shall be very gratefu
if you could just explain the DLOOKUP() function in detail

 

[Steve Schapel]

Mike,

Some purists look down their noses at Domain Aggregate Functions (such
as DLookup) because they have a reputation for being inefficient. In
reality, any performance sluggishness is only discernible to the naked
eye with large sets of data, which does not apply in your situation, as
you are talking single record queries. I am not sure whether this type
of thinking was behind Neil's comments.

Nevertheless, in my experience the kind of thing you are doing is quite
unusual, and I would guess that it is very likely that there is a
simpler way of achieving the same purpose. What that may be, I don't
think we have enough information to be specific.

 

[Guest]

Hi there

Again, I would like to thank Steve for clarifying a point here

I would just to like to give you an idea about what I am really trying to do. On my from I have different combo boxes and in these combo boxes are different criterias for my queries. After selecting all the relevant criteria using the combo-boxes, when a user clicks on the command button, all the 7 queries are run coming up with seven different results (all of them being single numbers). Now I need to put these seven different query results in a dynamic report (and in the textboxes on the form). Et voila. It seems pretty simple, but I've already lost one whole week trying to solve this problem

I would appreciate your comments or observations here. Thanks again

Regards
Mike.

 

[Steve Schapel]

Mike,

Are the 7 queries based on 7 different tables, or are they all based on
the same table but using 7 differerent sets of criteria? Maybe you
could post back with the SQL view of a couple of the queries, to help us
see what is going on. In any case, it may be possible to include the 7
queries within the main query that your form or report is based on, and
therefore you would have those values available to directly bind your
form/report controls to. Or it may be worth setting up a single-record
table with 7 fields to hold these values, which you can then use this
table in your form/report query, and run a set of Update Queries from
your criteria form to adjust the values in this table according to the
criteria entered in the comboboxes. Or, there may be advantages in
replacing your queries with SQL statements which are generated in code,
according to the criteria entered, on the Open event of your form or
report, opening recordsets based on these SQL statements, and then
retrieving the required values from the recordsets and assigning them to
the form/report controls. In other words, it is not a trivial question,
and depends a lot on the specifics of what the data is and where it
comes from. But in the end, on the basis of what we know so far, it
seems to me that the DLookup approach will be adequate.

 

[Guest]

Hi there again

Thank you very much Steve for helping me out

I don't think that I would bother you with some irrelevant details but I shall be VERY grateful if you could help me the last time. I've tried to make this reply very very detailed so please don't get scared of its size. I've included all the relevant info that I thought could be helpful to you

In my table "tblData", I've the following data

Employee_I
Last_Nam
First_Nam
Departmen
Office_Cit

Now, I've made a query that searches for all the employees in a particular city ("Office_City"), i.e., Birmingham, Manchester, Paris, Brussels, Milan, Berlin, New York, LA etc....

This query is working very well for a particular city like NY, Paris etc.. However, now I want to run a query for all the employees (First_Name, Last_Name) working in Euro-zone, Europe, Americas, and globally. For Euro-zone, I want Paris, Brussels, Milan and Berlin; for Europe I want all the european cities and for America, NY and LA.

For this I created another table "tblCity" with the following format
Field Names: Global, Europe, Eurozone, America
After which the column contains the city names; e.g.

Eurozone (Field Name
Pari
Mila
Berli
Brussel
....

After doing this I made a query "qryCity" which displays the name of the American cities where the office is based (NY and LA)

Now this query is based on the table "tblCity" and returns the names of the two cities (NY and LA). I then made another query "qrySearch" based on the previous query "qryCity" and linking it with the original table "tbData"
=DLookUp("[Americas]","qryCity"

Thus this query takes the city names from the result of the query "qryCity" and then searches the employees names in the basic table "tblData"

This works really well, HOWEVER, it finds out the records of only the NY office!!!! and NOT the LA office!!

Actually, the result of "qryCity" i
N
L

And the query based on this (since it uses the DLOOKUP function) only finds the data for NY and not LA!!!

I want the second query to give me the employees list for BOTH NY and LA!! Can anybody PLEASE help me out here??

Another problem is that Paris is in both Euro-zone as well as Europe!!! Can you propose a solution for this?? (Thank you very much for replying to my earlier email)

I shall be very grateful if somebody could help me out here. All other comments would be highly appreciated

Regards
Mike

 

[Steve Schapel]

Mike,

The DLookup() function can only ever return one value. What you are
doing is different from what I understood from your earlier
descriptions... I thought you had queries that only returned one value,
and that you were using the DLookup function in the Control Source of
textboxes on your form or report to display these values from the
queries. This, apparently, is now not the case.

I would do my tblCities like this...
Fields:
City
Zone
.... so the data looks like this...
LA America
NY America
Paris Europe
Manchester Europe
Milan Eurozone
Paris Eurozone
etc...

Then, just make a query including both the tables, joined on the
tblData.Office_City and tblCities.City fields, and apply your criteria
to the Zone field. Base your form and/or report on this query, and you
will be able to see the employees for any zone(s) you like.

--
Steve Schapel, Microsoft Access MVP

Hi there again,

Thank you very much Steve for helping me out.

I don't think that I would bother you with some irrelevant details
but I shall be VERY grateful if you could help me the last time. I've
tried to make this reply very very detailed so please don't get
scared of its size. I've included all the relevant info that I
thought could be helpful to you.

In my table "tblData", I've the following data;

Employee_ID Last_Name First_Name Department Office_City

Now, I've made a query that searches for all the employees in a
particular city ("Office_City"), i.e., Birmingham, Manchester, Paris,
Brussels, Milan, Berlin, New York, LA etc.....

This query is working very well for a particular city like NY, Paris
etc.. However, now I want to run a query for all the employees
(First_Name, Last_Name) working in Euro-zone, Europe, Americas, and
globally. For Euro-zone, I want Paris, Brussels, Milan and Berlin;
for Europe I want all the european cities and for America, NY and LA.


For this I created another table "tblCity" with the following format:
Field Names: Global, Europe, Eurozone, Americas After which the
column contains the city names; e.g.,

Eurozone (Field Name) Paris Milan Berlin Brussels ....

After doing this I made a query "qryCity" which displays the name of
the American cities where the office is based (NY and LA).

Now this query is based on the table "tblCity" and returns the names
of the two cities (NY and LA). I then made another query "qrySearch"
based on the previous query "qryCity" and linking it with the
original table "tbData". =DLookUp("[Americas]","qryCity")

Thus this query takes the city names from the result of the query
"qryCity" and then searches the employees names in the basic table
"tblData".

This works really well, HOWEVER, it finds out the records of only the
NY office!!!! and NOT the LA office!!

Actually, the result of "qryCity" is NY LA

And the query based on this (since it uses the DLOOKUP function) only
finds the data for NY and not LA!!!

I want the second query to give me the employees list for BOTH NY and
LA!! Can anybody PLEASE help me out here???

Another problem is that Paris is in both Euro-zone as well as
Europe!!! Can you propose a solution for this?? (Thank you very much
for replying to my earlier email).

I shall be very grateful if somebody could help me out here. All
other comments would be highly appreciated.

Regards, Mike.

 

[Neil Ginsberg]

Actually, I'm a big fan of domain aggregate functions. I feel they've been
grossly misunderstood, and that they are no slower (and sometimes faster)
than opening a recordset to get a single value. I use them often.

What I meant by "You should avoid using DLookup for multiple queries" (which
actually was supposed to read: "You should avoid using DLookup for multiple
fields") was that, if a report is opened with DLookup in the controlsource,
of say, 20 text boxes, this will be significantly slower than basing the
report (or a subreport) on the query directly. Even if a recordset is opened
in the report Open event and the values are manually placed in the fields,
that would be better than having a slew of DLookups (not to mention the high
maintenance overhead factor).

But if the user is a novice user and isn't comfortable with recordsets; and
if the report or a subreport can't be based on the query; then DLookup is
the way to go.

Neil