Query Field Criteria

[cturner]

I am not sure how to search for this so if there is another thread with this example please direct me in that direction. What I am trying to accomplish in my query is the following:
I am bringing data in from another source in the following table

ID Date1 Date 2

I want a query to do the following based on dates entered on the switchboard in a beginningDate field and EndingDate field

ID Date1 Date2 Final1 FInal2

If Date1 is between beginning and ending date I want it to put a yes in Final1 if Date2 is between beginning and ending date I want it to put a yes in Final2. Both can have yes'.

I tried doing an if statement in my expression as follows: IIF([Date1] between [Forms]![FrmSwitch]![BeginningDate] and [Forms]![FrmSwitch]![EndingDate], "Yes", "") but nothing comes up. My switchboard is always open with the dates in an unbound field.

Any suggestions???

 

[Murli]

The expression which you are trying is correct and works fine on my system.

Did you check if the formats is set to Date in your tables and the unbound fields?

 

[cturner]

Mine still is not working

I do have them as a date field but mine query still is not working. Can you send me a sample of your query. I must have something typed in wrong.

 

[Murli]

Here you go!

 

[cturner]

Ahah.

I think I know why I thought mine wasn't working. I have the start date as 9/1/05 and the ending date as 10/1/05. Most of my date1's are 9/1/05 and my between statement is not picking up 9/1/05 but on yours it is. Is there an internal setup that would cause this?

 

[Murli]

I dont think so. Not sure why its not picking up on yours.