Query...iif problem

[headintheclouds]

Hi there hope you can help again


I am try to write the following multiple iif statement (Badly!!) that will be in the [Date] Criteria of the query. I hoping that one of you could be kind enough to re-write it for me and be able to finish off the FALSE part of the statment as I don't know what to insert into it so access ignores the [Date] field.

iif([Date]="**/11/2005",([Date]),iif([Date]="**/10/05"AND([Date Taken])="**/11/05",Select ([Date]),

Another question I've got is there away of the user to change the months in the iif statement using something like a parameter box? So all the the user has to do is enter months and the iif statements change accordingly.

Hope you know what I am taking about


Headintheclouds

 

[Newman]

I am not sure that I fully understood, but here is what I think you're looking for:
Create a field called MonthWanted and add [Which month do you want? 1 to 12] in its criteria field.
Then add this line instead of yours.
IIf(Month([Date])=MonthWanted Or (Month([Date])=MonthWanted-1 And Month([Date Taken])=MonthWanted),Date,"")

You might need to work it a bit so that it gives you what you are really looking for, but I hope that it is close to it.

Come back for more support with it or with more info if I am way off.

 

[headintheclouds]

Cheers for the reply but maybe am doing this all wrong is there a way of doing the following:

The query that am trying to design is to pull records in 2 ways as follows:

The first is to pull records from the [Date] field for the month of November.

The second is to pull records from the [Date Taken] field that are also for the month of November.

The problem is that the [Date Taken] field has a lot of null values which I want included and I also want to include records that have an October date in the [Date] Field but have a November Date in the [Date Taken] field.

example of a couple of records below;

Name--------Policy Type----------Date-----------Date Taken

Terry----------Car---------------1/11/05-----------2/11/05---This records is perfect!
John----------Car---------------25/10/05-----------2/11/05---The [Date] field has an October date but [Date Taken] has a November Date
Bob------------Car---------------2/11/05--------------This record has a Null value in the [Date Taken Field]

All I want pull is any records were a November Date is Displayed

Cheers

 

[Newman]

OK! I think I see where you are going to...

First, you can't use a [Date] field as it is a reserved word in VB that means «Today's date». At least replace by [MyDate]. Even better, replace by something that you would understand if you go back to that database in 5 years. (ie: Start_Date, PolicyStartingDate, DateOfAccident or something like that.)

To use a criteria that would select only the records you want, use the criteria fields at the bottom. As you may have notice, there is many criteria rows. Underneath the [MyDate] field, in the first criteria row, add this:
Month([MyDate])=[Which month do you want? (1 to 12)]
And underneath the [Date Taken] field, in the second criteria row, add this:
Month([Date Taken])=[Which month do you want? (1 to 12)]

Another way to do it if you want to add the year in the criteria would be:
Month([MyDate])=[Which month do you want? (1 to 12)] AND Year([MyDate])=[Which year do you want? (4 digits please!)]

Remember that if you put a criteria somewhere else in that query, you'll have to write it in both the first and second rows.

If you need so I'll post a sample for you tomorrow.