IIF Expression returning unexpected results with dates

[User#13347]

Hello everyone,
Want to start off by saying I am total noob, I know someone smarter than myself would have set things up a little differently but what can you do. Thanks for looking please continue below.

I am running access 2016 on win 7

I have a column in my table that has dates (surprise). I was working on a query that would assign a value to a new column/field we will call date catch. If a date from a record fell between my desired range I'd want to assign a value of one to my new field Date Catch, if not then a value of zero would be assigned. Also I'd like blank dates to have a value of zero as well.

Here is what I used:
Date_Catch: IIf([Actual_Insp_Date] Between 10-1-2016 And 11-1-2016 Is Not Null,1,0)

In future I will use something along the lines of

Date_Catch: IIf([Actual_Insp_Date] Between [Start Date?:] And [End Date] Is Not Null ,1,0)


My Query assigns a value of 1 to everything that isn't null and I can't figure out why. I will continue to research this out on my own but some pointers in the right direction would be very helpful. *I should note that I am building this Query in design view*

If I can provide any specific examples/resources or if I need to clarify anything please let me know!

Thanks in advance!
~B

[User#13347]

I don't know if I was clear before. All dates cause a value of "1" to be assigned, not just the dates that fall into my parameters.

Thanks

[OBP]

First some questions.
you say "I have a column in my table that has dates (surprise).", are they actual Date Type fields or text fields that look like dates?
Second I am not sure what the is not null is actually doing, so what is it's purpose?

[SamT]

I think maybe

IIf([Actual_Insp_Date] Between 10-1-2016 And 11-1-2016 Is Not Null,1,0) Returns True (1) or False (0)

IIf([Actual_Insp_Date] Between 10-1-2016 And 11-1-2016 Is Not Null,1,0) Then Date_Catch = Now, or whatever

[User#13347]

First some questions.
you say "I have a column in my table that has dates (surprise).", are they actual Date Type fields or text fields that look like dates?
Second I am not sure what the is not null is actually doing, so what is it's purpose?

*The Column is a Date/Time Field
* The intended purpose of Is Not Null....well It's obviously wrong but let me try to explain my logic.
If a value falls in my date range, and it is not an empty field, please assign the value of 1 to that record, if the record does not fall into these parameters or no date is listed please assign the value 0 to the record.

I' am not a programmer, I write shell and batch scripts for fun sometimes or to automate things but nothing like this.

I wanted to count the number of records that fell into a specified time range. I planned on taking sum of all my "1" records and dividing them by the total number of records returned to get a percentage for some business metrics. Maybe not the smartest way to do this but I thought I could make it work. Assuming you can understand my intentions could you point me in the right direction or help me unstick myself?

[User#13347]

I think maybe

IIf([Actual_Insp_Date] Between 10-1-2016 And 11-1-2016 Is Not Null,1,0) Returns True (1) or False (0)

IIf([Actual_Insp_Date] Between 10-1-2016 And 11-1-2016 Is Not Null,1,0) Then Date_Catch = Now, or whatever

That was my intention yes! I tried several variations of my code above but none of them came as close to working as the one I listed in my original post.
I don't understand why IIf([Actual_Insp_Date] Between 10-1-2016 And 11-1-2016,1,0) Isn't sufficient, do you have any ideas for me? Or something I should look up?

[User#13347]

Scary Thought...... Is this considered SQL? Am I posting in the right place/Site?

[SamT]

You're in the right place. I don't speak SQL myself, but I believe Access can do that counting for you with the right Query.
Give it a day or so, I'm sure one of our Access experts will be along before too long.

[OBP]

What you actually need is a nested iif statement, I have tested this and I think it does what you describe

testdate: IIf(IsNull([Actual_Insp_Date]),0,IIf([Actual_Insp_Date] Between #01/01/2016# And #31/12/2016#,1,0))

[User#13347]

What you actually need is a nested iif statement, I have tested this and I think it does what you describe

testdate: IIf(IsNull([Actual_Insp_Date]),0,IIf([Actual_Insp_Date] Between #01/01/2016# And #31/12/2016#,1,0))

I didn't know I could use iif multiple times in a statement...or that I needed too.
Your code did exactly what I needed so for that thank you very much! I did notice some odd behavior though- If I set the date range from January 1st to December 1st 2016 via (#01/01/2016# And #1/12/2016#) I am given 0 for every record. Any ideas why that may be?

I thought access might have difficulty distinguishing December 1st 2016 from January 12th 2016 from #1/12/2016# so it gives me zeros? Am I in the ball park?

Fortunately the date ranges I need to examine haven't given me any issues so far but if I had a range that gave me a similar issue in future I'd be SOL.


Again thanks for your help and I really appreciate you getting me unstuck!

[User#13347]

I'll be marking this solved very soon, I am just curious about why the code is functioning the way it does.

Everyone who replied, thank you very much for taking time to help me with my issue. I know it wasn't a complex problem but it means something to me that you at least stopped and tried to help.


Gratefully,
~B

[OBP]

It may be that is caused by the # symbol, which in Access SQL turns the Date in to US format of MM/DD/YYYY.
So in your case it may be looking dates at between 1st and 12 of January.
You could try it without them and see if that works better for you.

You can of course use a form to set the start and end dates if required.