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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hi Paul
Can you explain what you want to do? What are you wanting to do with the *B?
Andrew
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Hi Paul
Try changing the AND to a comma, like so :
Backouts: IIf([type] Like "*B", IIf([startdate]>Date(), "EBNIBKOT", "BACKOUT"), "")
Andrew
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,758
Messages
6,138,439
Members
450,137
Latest member
HANHAN

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
Back
Top