Query statement

[paul29berks]

I have written the statement below:

IIF([type]=*B and [startdate]>Date(), "X", "Y")

My results however return all types with a start date > todays date with X and not restricted to those like B.

Can anyone suggest something in my statement I have missed?

Thanks

 

[Andrew Fergus]

Hi Paul
Can you explain what you want to do? What are you wanting to do with the *B?
Andrew

 

[paul29berks]

I have a number of document types in my database that represent invoices, I need to distinguish:

1) between types in this case those document types that end with b i.e 1tb or 1sb etc

2) of those document types ending with b I need to distinguish whether the start date of these is greater or less than todays date. So:

If greater than todays date = Mark column with X
If less than todays date = Mark column with a Y

Hope this makes sense

Thanks

Paul

 

[Andrew Fergus]

Hi Paul

Try changing
=*B
to
Like "*B"

HTH, Andrew

 

[Andrew Fergus]

On second thoughts, your existing formula will show the value 'Y' for all items that do not end with a 'b'. If you want to give these items a value other than 'Y' then use the following :

IIF([type] Like "*B", IIF([startdate]>Date(), "X", "Y"), "Z")

Change "Z" to suit.

Andrew

 

[paul29berks]

Thanks for your reply.

My statement: Backouts: IIf([type] Like "*B" And [startdate]>Date(),"X","Y")

now only returns value X regardless of start date > or < of todays date.

Thanks

 

[paul29berks]

Beat me to it

my statement looks like this:

Backouts: IIf([type] Like "*B" And IIf([startdate]>Date(),"EBNIBKOT","BACKOUT"),"")

However now all returns are Null.

 

[Andrew Fergus]

Hi Paul
Try changing the AND to a comma, like so :
Backouts: IIf([type] Like "*B", IIf([startdate]>Date(), "EBNIBKOT", "BACKOUT"), "")
Andrew

 

[paul29berks]

Apologies I tried with , beforehand which returned same result as AND i.e Null for all.

 

[Andrew Fergus]

Hi Paul

No worries. I'm guessing that if you change the "" to "Z" you will see Z for all? This means the query is not finding any records where the 'type' field ends with a b. Whether you use an uppercase B or a lowercase b won't make any difference. So, without being rude and I hope you don't mind my asking, is there data that matches the *b criteria?

Do you have the formula exactly as I typed it in my last reply? I don't think this is the issue but what is the format of the 'startdate' field? Lastly, you haven't excluded the records ending in b within another fields criteria in your query? If you aren't sure do you want to post the full SQL for your query?

Andrew