I have a little problem. I am running a query on twelved fields that contain a date. Basically, I want my new field, AY2009-2010, to indicate if people are within this date range or outside of it. My small nested IIF workds great up to the 10th field in the table:

AY2009-2010: IIf([t1r0] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t1r1] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t1r2] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t1r3] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t2r0] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t2r1] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t2r2] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t2r3] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t3r0] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t3r1] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t3r2] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t3r3] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t4r0] Between #7/1/2009# And #7/1/2010#,"AY2009-2010","OUT")))))))))))))

But it completely fails when I try to add the remaining fields for my test. Access 2010 says that it is too complex. In other words, the below fails.

AY2009-2010: IIf([t1r0] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t1r1] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t1r2] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t1r3] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t2r0] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t2r1] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t2r2] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t2r3] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t3r0] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t3r1] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t3r2] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t3r3] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t4r0] Between #7/1/2009# And #7/1/2010#,"AY2009-2010",IIf([t4r1] Between #7/1/2009# And #7/1/2010#,"AY2009-2010","OUT"))))))))))))))

There must be some way to do this using VBA. All I want to do is check each of my 12 fields for a data range between #7/1/2009# And #7/1/2010#, and display the text, AY2009-2010 if the record is in the range or “Out” if they are not in the range.

1. I have no idea where to begin with VBA
2. I persume that I would have to write what I don't know in visual student express (I have it)
3. I presume that I would have to call the VBA code in the query builder, but I don't know how to do that either.

Can anyone help-a-noob? I would really like to make this work, and it would go a long way toward helping me understand how to make VBA work. I would be very grateful.

Thanks in advance.

---

Reply from Richard Rost:

Yes, your statement is too long. It's probably due to the number of overall characters. You could TRY cheating and shorten your text value from "AY2009-2010" to something like "X" and that MIGHT work. If not, you would need to make a custom function in VBA to do the trick. It's not terribly hard. I would recommend you watch my Access 300-series which covers VBA programming, but IN A NUTSHELL, here's what you do:

First, create a module (Home > Create > Module). Now you can make your custom function. From looking at your IIF statements, it would look something like this:

Public Function DateCheck(D1 As Date, D2 As Date, D3 As Date) As Boolean

    DateCheck = False
    
    If D1 >= #7/1/2009# And D1 <= #7/1/2010# Then DateCheck = True
    If D2 >= #7/1/2009# And D2 <= #7/1/2010# Then DateCheck = True
    If D3 >= #7/1/2009# And D3 <= #7/1/2010# Then DateCheck = True

End Function


Now I only built it for 3 date fields, but you can add as many as you like. This function is going to look at your date fields, one after the other, and if any of them are within those dates, it will set the value of the function to TRUE.

Now from inside your query, you can say:

MyValue: DateCheck(T1R0, T1R1, T1R2)

Again, you can add as many date fields here as you need to. This "MyValue" field in your query will then have either a TRUE or FALSE value as to whether or not any of those dates fall in that range.

See? Not too hard once you know how to do it. Now go watch my Access 300-level classes. :)