# Query statement

#### paul29berks

##### Active Member
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

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

Hi Paul

Try changing
=*B
to
Like "*B"

HTH, Andrew

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

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

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.

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

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

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

Replies
1
Views
326
Replies
4
Views
302
Replies
5
Views
554
Replies
3
Views
360
Replies
6
Views
718

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.

### Which adblocker are you using?

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

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