dlookup in query criteria not working.. but calc is correct!

[gangel]

Hi everyone,

So I have a basic issue but just failing to achieve results!!

I have a field (number) with vales 1,2,3 and 4 in it for different Customers. (it represents how many weeks of medication they get at a time.)

Anyway, when I open up the form which has the query connected, I want it to lookup a setting I have and then only show certain results.

Pretty much
[Settings]
[SettingID] = 2
Field = [Monthly] (which is a yes/no field)

IF monthly is TICKED then I want field packsToMake = 1,2,3,4 (so = anything)
If monthly is NOT ticked I want field PacksToMake <> 4

So I have this criteria underneath PacksToMake

iif(dlookup("Monthly","Settings","[SettingID] = 2") = true, "<>4",">0")

This comes up with an error "data type mismatch in expression"

Now I can understand that as maybe its like im trying to put a string as criteria for an integer field?

But if I just put

iif(dlookup("Monthly","Settings","[SettingID] = 2") = true, <>4,>0)

it doesn't work either. The query runs but the actual <>4 and >0 results don't show.

Finally I tried just using

iif(dlookup("Monthly","Settings","[SettingID] = 2") = true, 4,2)



just to see if my calc was working and it worked... but obviously I don't need those results.
Therefore its the <> and > that is screwing stuff up!

How do I get <> and > into my dlookup true/false return values!!!??

Im stumped.!

[ranman256]

DONT use Dlookup in queries.
the query IS the dlookup. Join tables in the query you need to do the lookup.

[gangel]

thanks for the fast response.

I defer to your knowledge and not using the dlookup function in the query, but how can I make a settings table with a true/false field give the kind of results I want by making the [PackToMake] field <>4?