combo box control

[gwog]

Using Access 97.

I have a table that represents our fiscal year in 4 fields.

FISCAL YEAR
Day
Week
Period

Here is an example of the data:
Day Week Period Fiscal Year
12/27/03 26 6 2004
12/28/03 27 7 2004
12/29/03 27 7 2004
12/30/03 27 7 2004
12/31/03 27 7 2004
1/01/04 27 7 2004
1/02/04 27 7 2004
1/03/04 27 7 2004
1/04/04 28 7 2004

As you can see the weeks run from Sunday to Saturday. Our Fiscal Year (2004) runs from June 29,2003 - July 3, 2004. But the period crosses months (Period 7 contains December & January days).

Now here is my dilema...

Combo box on form for the Period. I want to limit it to the previous Period or earlier of whatever the current date is when the user is opening database.

So for 1/07/04 I only want the combo box to show Periods 1-6. NOT P7 (which is the current period for 1/07/04).

I've tried to come up with a criteria that makes this happen but can't seem to make it work for all situations.

For example - 1 thing I tried to to have the criteria be
=Date()-30
This works ok if Date() is not early in the month. But for Date() that fall in the first few days of the Period - going back 30 days sometimes skips the last Period and only shows 2 Periods back and earlier.

If any one could help me figure a way to do this I would be must gratefull!

Thanks

Lisa.

 

[MakeItSo]

Why can't you have the criteria be the maximum Period -1 ?

Example: Period=cint(DMax("[Period]", "[Fiscal Year]&quot)-1

Does that apply?

Greetings,
Andy

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[TonyJollans]

Hi gwog,

I presume you have future dates on your table and, if so, DMax is the wrong function. Try, instead, DLookup.

WHERE [Period] < DLookup(&quot;[Period]&quot;,&quot;[Fiscal Year]&quot;,&quot;[Day] = #&quot; & Date() & &quot;#&quot

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[gwog]

Thanks to both of you.

Tony your suggestion worked perfectly!
You are correct that the table contains forward dates also, so the DMax would be Period 12.

I really appreciate both your inputs!

Lisa.

 

[MakeItSo]

Thanks Tony! Hadn't considered that problem...