Lookup value in table from value in report query

[Hugh self taught]

Hi Informed people,

I have been reading the forums but can't find a solution to make my problem
work.

I have a query with the following code

SELECT Left([Nat_Reg],2) AS Prov, IIF([Male] = -1, "Males", "Females") AS
Gender, Count([Male]) AS Quanity
FROM Competitors
GROUP BY Left([Nat_Reg],2), IIF([Male] = -1, "Males", "Females")
UNION ALL SELECT "ZZ", IIF([Male] = -1, "Males", "Females") AS Gender,
Count([Male]) AS Quanity
FROM Competitors
GROUP BY "ZZ", IIF([Male] = -1, "Males", "Females");

I use this as my report source code (May be a problem shortly as I need
other data as well)

I then need to look up a text box value for "Prov" in tbl "Provinces" where
the field is "Province"

The last code I tried is
=DLookUp("[Province]","[Provinces]","[Province_Cde] =" &
[Reports]![rptStatistics]![Prov]) but just get the horrid #error output.

What I need in addition since the last part of the union query is a total,
is something along the lines of IIF the value is not found then "Total"

Any suggestions on how to achieve this. My thinning hair won't last much
longer

[Dale Fye]

Hugh,

My guess is that the [Province_Cde] field is a string, in which case you
have to wrap the value of the reports [Prov] field in quotes, or single
quotes. Try:

=DLookUp("[Province]","[Provinces]","[Province_Cde] =""" & me.[Prov] & """")

----
HTH
Dale

[Hugh self taught]

Thanks for the reply Dale. Problems is now I'm getting the Me parameter box
when I return to report view. Any suggestions?

[Hugh self taught]

Me again dale,

I amended the [Me] to the full address & then it works although it seems a
tiny bit slow in producing the results. I presume that I can still then wrap
this in an IIF statement where this syntax is the result if not true?

[Dale Fye]

Hugh,

Where is this field that has the DLOOKUP( ) as the ControlSource? If it is
in the detail section of the report, then it should use me.[Prov] just fine.
If it is in a header or something like that, then it could cause a problem.

Actually, I was going to recommend, as an alternative, modifying your query
so that it looks like:

SELECT T.Prov, T.Gender, T.Quantity, P.Province
FROM (SELECT Left([Nat_Reg],2) AS Prov,

IIF([Male] = -1, "Males", "Females") AS Gender,
Count([Male]) AS Quanity
FROM Competitors
GROUP BY Left([Nat_Reg],2), IIF([Male] = -1, "Males", "Females")
UNION ALL
SELECT "ZZ",
IIF([Male] = -1, "Males", "Females") AS Gender,
Count([Male]) AS Quanity
FROM Competitors

GROUP BY "ZZ", IIF([Male] = -1, "Males", "Females")) as T
LEFT JOIN Provinces as P
ON T.Prov = P.Province_Cde

Then, you don't need the DLOOKUP. All you need to do is set the control
source of the control to the Province field.

[Hugh self taught]

Hi Dale, Different time zones make us miss each other. Thanks for your efforts

You're correct in that it is in a header section. I'll give your alternative
a go later today however my last issue on this is to make Province = "Total"
if the Province_Cde is not found. An IIF statement I tried in a text box
=IIf([Prov]="ZZ","Total","") which works fine but I have no clue how to
incorporate that all together.

[Dale Fye]

Hugh,

Where is the control that uses the DLOOKUP as it's source? If it is in the
Detail section of the report, it should work just fine using the me!
identifier.

Another option would be to modify your query so that it returns the Provence
name so you don't have to use the DLOOKUP. It would look something like:

Select T.Prov, T.Gender, T.Quantity, P.Province
FROM (

SELECT Left([Nat_Reg],2) AS Prov,
IIF([Male] = -1, "Males", "Females") AS Gender,
Count([Male]) AS Quanity
FROM Competitors
GROUP BY Left([Nat_Reg],2),
IIF([Male] = -1, "Males", "Females")
UNION ALL
SELECT "ZZ",
IIF([Male] = -1, "Males", "Females") AS Gender,
Count([Male]) AS Quanity
FROM Competitors
GROUP BY "ZZ",

IIF([Male] = -1, "Males", "Females")) as T
LEFT JOIN Provinces as P
ON T.Prov = P.Province_Cde

With this, all you need to do is change the control source of the Province
control from the DLOOKUP( ) domain function to the Province field.

HTH
Dale

"Hugh self taught" <Hughsel...@discussions.microsoft.com> wrote in
message news:28390AB7-B3DA-4951...@microsoft.com...

[Dale Fye]

Hugh

Replace: SELECT T.Prov, T.Gender, T.Quantity, P.Province

with: SELECT T.Prov, T.Gender, T.Quantity, NZ(P.Province, "Total")

in the query I provided.

Alternately, you could replace the DLookup() with

=NZ(Dlookup(...), "Total")

The NZ( ) function accepts two parameters. The first is a field or
expression. If that expression or field evaluates to NULL then the function
returns a zero, or whatever is provided as the optional second parameter.

HTH
Dale

"Hugh self taught" <Hughsel...@discussions.microsoft.com> wrote in

message news:D29611DF-C0DB-4DE9...@microsoft.com...

[Hugh self taught]

I learn something new all the time....Tks Dale

"Dale Fye" wrote:

> .
>

[Hugh self taught]

I've amended the original query as you suggested :-

ELECT T.Prov, T.Gender, T.Quantity, NZ(P.Province, "Total")

FROM (SELECT Left([Nat_Reg],2) AS Prov, IIF([Male] = -1, "Males", "Females")

AS Gender, Count([Male]) AS Quanity)

FROM Competitors
GROUP BY Left([Nat_Reg],2), IIF([Male] = -1, "Males", "Females")
UNION ALL SELECT "ZZ", IIF([Male] = -1, "Males", "Females") AS Gender,
Count([Male]) AS Quanity
FROM Competitors

GROUP BY "ZZ", IIF([Male] = -1, "Males", "Females") as T LEFT JOIN Provinces
as P ON T.Prov = P.Province_Cde;

Now I have a "Syntax error in FROM clause" The only thing to my limited
knowledge is perhaps the 2 FROM statements after each other?

"Dale Fye" wrote:

> .
>

[Dale Fye]

You have an extra ")" in the mix.

Remove the ")" immediately following the first "As Quantity)"

Dale

"Hugh self taught" <Hughsel...@discussions.microsoft.com> wrote in

message news:C71F2558-363F-472E...@microsoft.com...

[Hugh self taught]

Hi Dale,

That doesn't seem to be the problem. I count 4 opening & 4 closing
parenthases but tried it any way. In fact it doesn't even complain that there
is one missing as it usually would.

In th interim I've made use of the dlookup but I would really like to get
this working as I see an opportunity to use this same query later in my
reports. Any other observations I can try?

Thanks

"Dale Fye" wrote:

> .
>

[Dale Fye]

The reason the parenthesis match up is that you added one where you
shouldn't have, and left one out where it was needed. You have to wrap the
entire (SELECT ... UNION ALL SELECT ....) subquery in parenthesis. I
focused on the one you inserted, but failed to notice the one that you had
dropped off.

Let's try this again. The query should read:

SELECT T.Prov, T.Gender, T.Quantity, P.Province
FROM
(

SELECT Left([Nat_Reg],2) AS Prov,
IIF([Male] = -1, "Males", "Females") AS Gender,
Count([Male]) AS Quanity
FROM Competitors
GROUP BY Left([Nat_Reg],2),
IIF([Male] = -1, "Males", "Females")
UNION ALL
SELECT "ZZ",
IIF([Male] = -1, "Males", "Females") AS Gender,
Count([Male]) AS Quanity
FROM Competitors
GROUP BY "ZZ",
IIF([Male] = -1, "Males", "Females")

) as T
LEFT JOIN Provinces as P
ON T.Prov = P.Province_Cde

I would just copy and paste this into the SQL of your existing query. Don't
try to edit it, Access will reformat it when it is compiled.

Dale

"Hugh self taught" <Hughsel...@discussions.microsoft.com> wrote in

message news:41ED3035-0D09-4ED0...@microsoft.com...

[Hugh self taught]

Thanks a mil Dale. I changed the first line to read

SELECT T.Prov, T.Gender, T.Quanity, NZ(P.Province,"Total") AS Province

and now I have the result I was looking for.

"Dale Fye" wrote:

> .
>