DLookUp Problem in Query

[mikeTTI]

I have a DLookUp Expression

=DLookUp("Rate","tblLevyRates","[LevyYear]= " & [LevyYear] & " And [Species]= '" & [Species] & "'")

I use this expression in a form and it works fine.

I it as 'Rate:' in select query "qryLevyPayAll" and it works fine: -

qryLevyPayAll
SELECT tblLevyReceiptsDetail.AutoID, tblLevyReceiptsDetail.Grower, tblLevyReceiptsDetail.Species, tblLevyReceiptsDetail.LevyPaid, tblLevyReceiptsDetail.Tonnes, tblLevyReceiptsDetail.HeaderID, tblLevyReceiptsDetail.LevyYear, tblLevyReceiptsDetail.LevyDate, DLookUp("Rate","tblLevyRates","[LevyYear]= " & [LevyYear] & " And [Species]= '" & [Species] & "'") AS Rate
FROM tblLevyReceiptsDetail
WHERE (((tblLevyReceiptsDetail.LevyDate) Between [Forms]![LevyReportDialog]![DateFrom] And [Forms]![LevyReportDialog]![DateTo]));

Things are going great. BUT!!! When I base select queryB on qryLevyPayAll : -

queryB

SELECT qryLevyProcXSpp.Name, qryLevyProcXSpp.DateAdded, qryLevyPayAll.Species, qryLevyPayAll.LevyYear, qryLevyPayAll.LevyDate, qryLevyPayAll.Tonnes, [COLOR="Red"]qryLevyPayAll.Rate[/COLOR]
FROM qryLevyProcXSpp LEFT JOIN qryLevyPayAll ON qryLevyProcXSpp.AutoID = qryLevyPayAll.HeaderID
GROUP BY qryLevyProcXSpp.Name, qryLevyProcXSpp.DateAdded, qryLevyPayAll.Species, qryLevyPayAll.LevyYear, qryLevyPayAll.LevyDate, qryLevyPayAll.Tonnes, qryLevyPayAll.Rate;

I get two error messages.

1. There is a syntatx error (missing operatro) in the DLookUp in qryLevyPayAll.
2. The expression is typed incorrectly or too complex to evaluate.

Now this has me completely stuck.

If the DLookUp is valid on my form and in qryLevyPayAll, why does it misbehave when I refer to the field containing it in queryB?

 

[ajetrumpet]

qryLevyPayAll
SELECT tblLevyReceiptsDetail.AutoID, tblLevyReceiptsDetail.Grower, tblLevyReceiptsDetail.Species, tblLevyReceiptsDetail.LevyPaid, tblLevyReceiptsDetail.Tonnes, tblLevyReceiptsDetail.HeaderID, tblLevyReceiptsDetail.LevyYear, tblLevyReceiptsDetail.LevyDate, DLookUp("Rate","tblLevyRates","[LevyYear]= " & [LevyYear] & " And [Species]= '" & [Species] & "'") AS Rate

To me, the red is not written correctly. What are you trying to say with it? "WHERE [LevyYear] = the [Species] value?" If that's all you're trying to say, you only need this...

"[LevyYear] = [Species]"

Things are going great. BUT!!! When I base select queryB on qryLevyPayAll : -
[*]There is a syntatx error (missing operatro) in the DLookUp in qryLevyPayAll.
[*]The expression is typed incorrectly or too complex to evaluate.
[/LIST]

The expression, to me, does look too complicated to be evaluated really. And, I wouldn't be surprised if there was a syntax error present. There are too many quotes floating around. Could you say what the purpose of the two queries are (IN WORDS) perhaps? An explanation this way would certainly give a better picture of this.

 

[pbaldy]

Mike, I'd look at joining the 2 tables on those 2 fields to get the value, rather than using a DLookup, which will be less efficient.

Adam, there's no problem with the syntax in red. It would evaluate to something like

[LevyYear]= 2007 And [Species]= 'Rhino'

which is perfectly valid.

 

[ajetrumpet]

Adam, there's no problem with the syntax in red. It would evaluate to something like

[LevyYear]= 2007 And [Species]= 'Rhino'

Well, I have no idea where you get the 'Rhino' from, but that's OK. I can read the syntax, and yes, it is perfectly fine, but a little too redundant if you ask me. Thank you for pointing that out to me though, are you gunning for me? I think a few of you might be... I figured so since I've stolen some peoples' thunder a few times. I'm trying to minimize my mistakes, so you guys can't catch me anymore.

I'll tell you something though, from all these posts I see with the quotation marks everywhere, I start to think sometimes that people are just copying samples from websites to get things done. There really should be a FAQ on this issue, because there seems to be as much confusion over these quote marks that there is over cascading combo boxes...

 

[Simon_MT]

I agree with pbaldy, you could create a join from tblLevyReceiptsDetail. Access, is a relational database so explore its capabilities and use its functionality.

When we give advice we give it freely. As far as I know its not a competition.

Simon

 

[pbaldy]

Well, I have no idea where you get the 'Rhino' from, but that's OK. I can read the syntax, and yes, it is perfectly fine, but a little too redundant if you ask me.

I made up both the year and species values as an example, as they might have been supplied by the program. I'm not sure what you see as redundant, as that's the only syntax that could be used for a 2-criteria lookup, with one numeric and one text.

And no, I'm not gunning for you, but if I see something incorrect I will mention it. I'll try to refrain if it bothers you, but I didn't want the OP going down the wrong road.

 

[mikeTTI]

Hi All,

Thank you all for your posts. I originally created the DLookUp to display a value in a calculated text box on a form, which it works really well for.

The table it looks up is a "list" of Levy Rates by Species and LevyYear. Currently it has one year and three species (sadly, all aquaculture species, so no Rhinos, as they are a terrestrial beast, I might be able to squeeze in hippos though!).

So for one row in tblLevyRates there will be many rows in tblLevyReceipts.

None of the fields in tblLevyReceiptsDetail Look up tblLevyRates fields. The Species and LevyYear fields in tblLevyRates and tblLevyReceiptsDetail look up separate "list" tables.

Under these circumstances is it still OK to make the two field join people have recommended?

I will have a try in the meantime.

And agree an FAQ on quote marks and speech marks would be good. I copy and paste from the web or rely on trial and error to try and get my functions to work, as I can't find any firm guidelines to follow. An FAQ would be real useful.

 

[pbaldy]

My wife loves rhino's which is probably why that jumped into my head as an example.

I'm having trouble visualizing the whole package, so a sample db might be a big help. Generally, it looks like you get a value based on 2 fields that exist in both tables, so a join should work.

 

[mikeTTI]

Hi Pbaldy,

Thanks for the advice just tried the join and it works nicely, and as you say will be much more efficient than a DLookUp.

I guess I should have tried this to begin with but because I had the DLookUp on the form I just copied and pasted!

 

[ajetrumpet]

And no, I'm not gunning for you, but if I see something incorrect I will mention it. I'll try to refrain if it bothers you

Very few things bother me Paul. But I will say that we live in a world of competition, and not everything, or everybody is always nice. I don't mean anything to you by that, but it's a great thing to keep in mind when you start to get too nice to someone, especially in business.

Haven't you noticed that I like to make jokes every now and then? Doing this stuff day in and day out, you need a break, don't you? I have to give up my serious attitude somtimes, and I have to be competitive at other times. Doing one or the other will tell you what kind of a day I've had.