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_C de] =""" & me.[Prov] & """")
----
HTH
Dale
"Hugh self taught" wrote:...
Thread: Lookup value in table from value in report query in microsoft.public.access.reports  |
|||||||||||||
|
Started 1 month, 2 weeks ago by 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] = -...
|
|
||||||||||||
|
|||||||||||||
Other posts in this thread:
Dale Fye replied 1 month, 2 weeks ago
Size: 2,528 bytes
Customize: 
Hugh self taught replied 1 month, 2 weeks ago
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" wrote:
> 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. ...
Size: 2,967 bytes
Customize:
Hugh self taught replied 1 month, 2 weeks ago
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" wrote:
> Hugh,
>
> My guess is that the [Province_Cde] ...
Size: 3,114 bytes
Customize:
Dale Fye replied 1 month, 1 week ago
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...
Size: 4,953 bytes
Customize:
Hugh self taught replied 1 month, 1 week ago
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...
Size: 6,003 bytes
Customize:
Dale Fye replied 1 month, 1 week ago
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:...
Size: 5,076 bytes
Customize:
Dale Fye replied 1 month, 1 week ago
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 ...
Size: 7,833 bytes
Customize:
Hugh self taught replied 1 month, 1 week ago
I learn something new all the time....Tks Dale
"Dale Fye" wrote:
> 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
>...
Size: 8,701 bytes
Customize:
Hugh self taught replied 1 month, 1 week ago
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 ...
Size: 9,574 bytes
Customize:
|
Top contributing authorsRelated threads on "Discussions in microsoft.public.access.reports":Related threads on other sites: |
||||||||||||
Thread profile page for "Lookup value in table from value in report query in
microsoft.public.access.reports" on http://www.microsoft.com/communities/newsgroups....
This report page is a snippet summary view from a single thread "Lookup value in table from value in report query in
microsoft.public.access.reports", located on the Message Board at http://www.microsoft.com/communities/newsgroups....
This thread profile page shows the thread statistics for: Total Authors, Total Thread Posts, and Thread Activity
Home |
About Us |
Submit Your Site |
Update Your Site |
Get Search For Your Site |
Partners |
Contact |
Search Plugin |
Blog |
 |
Last Searches | Top Searches |
Designated brands and trademarks are property of their respective owners.
Use of this website constitutes acceptance of BoardReader's User Agreement and Privacy Policy.