Lookup value in table from value in report query

H

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
 
D

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] & """")
 
H

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?

Dale Fye said:
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 said:
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
Click to expand...
Click to expand...
 
H

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 said:
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 said:
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
Click to expand...
Click to expand...
 
D

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.

----
HTH
Dale



Hugh self taught said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
H

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 said:
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.

----
HTH
Dale



Hugh self taught said:
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 said:
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



:

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
Click to expand...
Click to expand...
Click to expand...
 
D

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

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 said:
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 said:
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.

----
HTH
Dale



Hugh self taught said:
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?

:

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



:

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
Click to expand...
Click to expand...
Click to expand...
 
H

Hugh self taught

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

Dale Fye said:
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 said:
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 said:
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.

----
HTH
Dale



:

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?

:

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



:

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
Click to expand...
Click to expand...


.
Click to expand...
 
H

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 said:
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 said:
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 said:
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.

----
HTH
Dale



:

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?

:

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



:

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
Click to expand...
Click to expand...


.
Click to expand...
 
D

Dale Fye

You have an extra ")" in the mix.

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

Dale

Hugh self taught said:
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 said:
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 said:
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.



:

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.

----
HTH
Dale



:

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?

:

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



:

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
Click to expand...


.
Click to expand...
Click to expand...
 
H

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 said:
You have an extra ")" in the mix.

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

Dale

Hugh self taught said:
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 said:
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


message 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.



:

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.

----
HTH
Dale



:

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?

:

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



:

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



.
Click to expand...
Click to expand...


.
Click to expand...
 
D

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 said:
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 said:
You have an extra ")" in the mix.

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

Dale

Hugh self taught said:
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?

:

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


message 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.



:

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.

----
HTH
Dale



:

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?

:

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



:

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



.
Click to expand...


.
Click to expand...
Click to expand...
 
H

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 said:
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 said:
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 said:
You have an extra ")" in the mix.

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

Dale

message 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?

:

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


message 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.



:

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.

----
HTH
Dale



:

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?

:

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



:

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



.



.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error in syntax 2
IIF in a report Footer 3
How to count # of Male/Female Entries on Report? 2
Filtering subreport based on main report 3
Percentages, eg. Male v Female 1
How do I create table data based on a query? 22
Grouping data in unequal sized groupd 2
Counting within a field 4

Top