DLookUp Problem

[kousotsu]

I'm probably just missing something simple, but I'm having a problem using DLookUp.

I have a report which is bound to a particular query returning counted values by three criteria: Vendor, Area, and Month. From a seperate query (or pair of, depending on which way is quicker for the system), I need to extract a pair of summed Mileages (one total, one YTD), once for each Vendor, once for each Area, and a Total overall.

Here is how I have the report laid out:

Page Header: [headVendor]
[Vendor](labels for counted fields)[MileagebyVendor]*[MileageYTDbyVendor]*

Area Header: [headArea]
[Area].....[MileagebyArea]*[MileageYTDbyArea]*

Detail: ...
[Month] (txtBoxes for counted fields)

Report Footer: ...
(sums for all types) [TotalMileage(system)]*[TotalMileageYTD(system)]*

The *ed items are my DLookUps. This is the Syntax I've used for the first item in the header:

DLookUp("[SumOftrim2004]","qryTTotV","'[currentVendor]=' & Reports![rptY14_Totals]![currentVendor]")

-=-
So, my dilemma is: how do I do these "DLookUp"s so that they operate properly? I am at a loss as to why (when used with proper syntax, lifted both from the forums here and MS Help) they return "#Error"s. Is it because it's located in the section header and not "Detail"?

Any help is appreciated, folks. Thanks in advance!

 

[KenHigg]

Try this:

DLookUp("[SumOftrim2004]","qryTTotV","[currentVendor]='" & Reports![rptY14_Totals]![currentVendor] & "'")

Work?

 

[kousotsu]

Ken's attempt

Syntax Error, but thanks for trying ^_^

 

[KenHigg]

Try:

DLookup("[SumOftrim2004]", "qryTTotV", "[currentVendor]= ' " & Reports![rptY14_Totals]![currentVendor] & " ' ")

 

[kousotsu]

Example File

The file contains the basic basics of the database on which I'm working. I can't believe I just cut it down from over 4MB to less than 1MB! ^_^

Anyhow, the report is in there. If any of you can get the damned thing to work, just let me know here or shoot me an email to keg@mechadeluxe.com. Either way, the results will get posted to the forum when we find something that works.

Thanks again!

 

[KenHigg]

This query is broke: QRY2004TTotV

It can't find fldOHMi in:

SumOffldOHMi: Sum(qryMasterSelector_experiment.fldOHMi)

 

[kousotsu]

Broken Queries

Just go into the queries and delete that particular element (fldOHMi) as it's unused in the scope of my problems; I just forgot to delete it myself.

 

[KenHigg]

Try this, it worked on my side:

=DLookUp("[SumOftrim2004]","[qry2004TTotV]","[currentVendor]='" & [Reports]![qryY14_Totals_Crosstab]![currentVendor] & "' ")

 

[kousotsu]

Huzzah!

Boy did that suck. The forums went down (at least on my end), and it was just today that I was able to get to Ken's response. THREE CHEERS FOR KEN!

Man, what a bitch that was. I don't know how many iterations i tried of that godforsaken thing, but I must have missed that one (the only one that worked). At least now I know the required Microslothese to use. Many many thanks, Ken. You are a King among Princes.

Now that the report's working, let's see if my numbers are accurate. ^_~

(oh, and huzzah!)

- Z

 

[KenHigg]

Cool - I kept watching this thread to see if you got it working....

(I couldn't get to forum for a couple days either)