Advanced Query Criteria

[jamiesuperman420]

Hey!! I hope I can explain this well (I also hope I picked a good subject)... I'm working on a database for a company that does landscaping, sweeping, janitorial, etc, etc. I'm trying to make a query to summarize ALL the service information.

SO - in some records (sorted by property) there's a fee for sweeping and landscaping and janitorial. HOWEVER, on SOME record's there's a fee entered even though those services are inactive (good to keep the old numbers in there in case they're bid again, etc, etc).

LSActive=Yes/No, SwpActive=Yes/No, LSFee=Currency, SwpFee=Currency, LSCrew=Text, SwpCrew=Text etc, etc...

I'm TRYING to say if the Landscaping isn't active but the Sweeping is, that the Landscaping fields be replaced with blank and the Sweeping fields be displayed properly.

I've tried iif statements in the "Field" and different things with [LSActive] in the Criteria, but no beans.

I hope I explained it well enough!! Any ideas?? Thank you.

 

[John Big Booty]

In your query grid in the field section of a blank column put something like;

IIf([LSActive]="No","",[LSActive])

This will give you a column in the query results that is either blank or hold the value for [LSActive]

 

[jamiesuperman420]

Didn't work I also tried iif([LSActive]=True,[LS Fee],"") and that didn't work either. Silly Query!!

 

[jamiesuperman420]

Okay, wow. You know you're tired when: you had [LSActive] on the WHOLE time and wondered why the numbers kept showing up under iif([LSActive]=True,[LSFee],""). GREEEEAT. Thanks for your help - I thought it musta been an amazingly difficult problem since I couldn't figure it out, but really I'm just blind.

I want to delete this thread 'cause I'm embarassed - hehe

 

[John Big Booty]

Does [LSActive] have a check box as a control source? If so "checked" will test as -1 whilst "unchecked" will test as 0

 

[jamiesuperman420]

Yep!! I've been using that - just switched to "True" because I thought that was the problem. Ahhhh yes. Ha!! I'm basically mocking myself with the subject line. In any case - again, thank you very much for the help!!

 

[Simon_MT]

You could treat these types of income differently but having a Table containing the Invoice Details.

Type of income:

LS = Landscape
SP = Sweep

Then you would only have one field for:

Currency
Fee
Crew etc..

Simon