Re: DLookup that isn't working

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 01/17/05 

Date: Mon, 17 Jan 2005 11:25:46 +0800

The 3rd argument for DLookup needs to be a string like this:

=DLookUp("[Combined]", "CombinedQuery",
    "[LinkField] = " & [LinkField] & " And [Year]= " & [Year1])

It would probably be much more efficient to base the report on a query that
includes both tables, rather than use the DLookup(). 

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"JWCrosby" <JWCrosby@discussions.microsoft.com> wrote in message
news:C347F742-5F45-4188-8267-2EFCABD4CD3B@microsoft.com...
>I have a once-working Dlookup that has decided to quit.  Here are the
> particulars:
>
> tblTransactions
> LinkField (PK, autonumber)
> StudentName
>
> tblHistory
> LinkField
> Year (ex. 2003)
> Class (ex. "P1")
> Grade (ex. "S")
>
> tblTransactions and tblHistory are related in a one-to-many relationship
> based on LinkField.
>
> QueryCombined (based on tblHistory) with 3 fields:
> LinkField
> Year
> Combined (created by combining Class and Grade, ex. "P1S")
>
> In a report based on tblTransactions, I have in the detail area the 
> following:
> StudentName
> LinkField (though it's not visible)
> A text box representing a year, named "Year1"
> A calculated Control with the following Control Source:
> =DLookUp("[Combined]","CombinedQuery","[LinkField]=[LinkField]" And
> "[Year]=[Year1]")
>
> It is pulling up a Combined field from the query, but I get the same field
> for every record.
>
> What am I missing?

Relevant Pages

  • Re: DLookup that isnt working
    ... The suggested DLookup() expression assumes: ... your report has text boxes named LinkField and Year1, ... If the text boxes are not present on the report, ... without it repeating the one side (student name) on multiple ...
    (microsoft.public.access.reports)
  • RE: Access Opening Balance Expression
    ... I guess at this point I'll go back to my Excel s/s and Crystal report combo. ... "Duane Hookom" wrote: ... cannot seem to get the DLookup to bring in the BegBal. ... you can create a query to get all starting balances with a totals ...
    (microsoft.public.access.reports)
  • Re: Report for single record
    ... If you just need to look up a single value from another table so you can limit the report to the correct value, DLookup() should do that for you. ... After retrieving the correct value from the table, you can then open the report filtered to just that one record by using the WhereCondition of OpenReport. ... Alternatively it may be possible to create a query that uses both your main table and the lookup table, and use this query as the RecordSource for the report. ... the project manager is set ...
    (microsoft.public.access.reports)
  • Re: Report for single record
    ... I used Dlookup as the source for the control on the report. ... Alternatively it may be possible to create a query that uses both your main ... In my new DB, the project manager is ...
    (microsoft.public.access.reports)
  • RE: I have a problem with Dlookup via an unbound control on report
    ... You asked "Why do you need to use DLookup when you might have been able to ... matches that parameter against a complex postcode matching query. ... I needed a way to get the chosen parameter value into the report so that the ... "Duane Hookom" wrote: ...
    (microsoft.public.access.reports)