Using IIF Aand Between in Querry 1

[cneill]

Hi Everyone,

I know I am missing something with this, so could do with a pointer

Field in Query
within the query there is another field that filter the Tax Year Selected on FrmMain
I then just need to check that the date entered on the Form is between the start and the end dates for that Tax Year Selected
At the moment this just returns Yes no matter what date I enter.

TaxDate: IIf([Forms]![FrmMain]![NewDate] Between [StartTaxYear] And [EndTaxYear],"Yes","No")

Can Any one help?

Thanks

CNEILL

 

[SkipVought]

There is no BETWEEN in the IFF syntax as oppesed to SQL...
[tt]
TaxDate: IIf([Forms]![FrmMain]![NewDate] >= [StartTaxYear] And [Forms]![FrmMain]![NewDate] <= [EndTaxYear],"Yes","No")

[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cneill]

Hi Skip

Thanks for this, but I am now getting the result as No, but the date is between the two date and is it is outside the dates I also get No, any thoughts?

Thanks

Neill

 

[SkipVought]

What is the value of [Forms]![FrmMain]![NewDate]?

What is the value of [StartTaxYear]?

What is the value of [EndTaxYear]?

You may have a STRING as NewDate -- TILT!

You may indeed have a DateValue in NewDate but simply a year (integer value) in start & end. -- TILT!

Apples and oranges.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cneill]

Hi Skip

My Fault, one of the fields was not a date/time field, all sorted now

many thanks

Neill