Query statement

paul29berks

Active Member
Joined
Mar 15, 2004
Messages
293
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi Paul
Can you explain what you want to do? What are you wanting to do with the *B?
Andrew
 

paul29berks

Active Member
Joined
Mar 15, 2004
Messages
293
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

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432

ADVERTISEMENT

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

Active Member
Joined
Mar 15, 2004
Messages
293
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

Active Member
Joined
Mar 15, 2004
Messages
293

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi Paul
Try changing the AND to a comma, like so :
Backouts: IIf([type] Like "*B", IIf([startdate]>Date(), "EBNIBKOT", "BACKOUT"), "")
Andrew
 

paul29berks

Active Member
Joined
Mar 15, 2004
Messages
293
Apologies I tried with , beforehand which returned same result as AND i.e Null for all.
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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
 

Forum statistics

Threads
1,137,204
Messages
5,680,160
Members
419,887
Latest member
Vasokir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top