No 31st of January!?

[XelaIrodavlas]

I had to double check too... for some reason my form wont pick up the 31st of any month in my input form (jan, mar, may, july, aug, oct, dec).

I have a simple search form (no control source) that runs a query which is based on the records between two input dates (text boxes). These could be any days, but could also be by month, so for the users convenience I wanted an option to just choose a month and year, which would automatically fill out the input boxes.

Maybe there's an easy way to do this? This is how I tried...

The start date box was easy, i just set the control source to:

="01/" & [Month] & "/" & [Year]

which produces normal dates like 01/02/2013 or 01/11/2010.

But because the months have different number of days, the ending date was a bit more complex, I used iif statements in the control source and have this:

=IIf([Month]="02" And [Year]/4=Int([Year]/4),"29/" & [Month] & "/" & [Year],
IIf([Month]="02","28/" & [Month] & "/" & [Year],
IIf([Month]="04" Or "06" Or "09" Or "11","30/" & [Month] & "/" & [Year],
IIf([Month]="01" Or "03" Or "05" Or "07" Or "08" Or "10" Or "12","31/" & [Month] & "/" & [Year],"FALSE"))))

The "01" bits refer to the bound column reference in each month, february is 02, december is 12 etc.
This successfully sets the date to the 28th if February (29th on leap years, see first line), and the 30th for Apr, June, Sep and Nov. But for some reason the last IIf statement doesn't work right, it just keeps setting it to the 30th, and i can't change it manually.. so if i run the query it then works only until the 30th of these months even when there's 31 days.

Have I made a really simple mistake and just can't see it? it should be working... but it isn't...

Any ideas? Thanks

 

[XelaIrodavlas]

thinking on it, could the problem be the 'Or' in my function? that looks to be where I'm getting stuck... is there a better way to say if x = Y Or Z other than just writing each IIf individually?

Good brainstorming

 

[pr2-eugin]

Hello Alex, I am sure that what you are trying to do could be easily achieved in a simple VBA function, but I am finding it a bit hard to understand..

Do you wish to just get the Last date of a month, for any given date? If so this might be what you are looking for..

? DateSerial(Year(Date),Month(Date)+1,0)
28/02/2013 
? DateSerial(Year(#15/06/2013#),Month(#15/06/2013#)+1,0)
30/06/2013

 

[XelaIrodavlas]

Thanks (as usual!) that is a much easier way to do it.

 

[pr2-eugin]

Glad to help..

 

[Pat Hartman]

Paul's solution is better (assuming it translates month 13 to month 1 of the following year) but the problem with your code is you need to repeat the field name for each condition.

IIf([Month]="04" Or [month] = "06" Or [month] = "09" Or [month] = "11","30/" & [Month] & "/" & [Year],

An alternative would be to use the IN() clause

IIf([Month] IN ("04", "06", "09", "11)","30/" & [Month] & "/" & [Year],

PS - if you are in a position to change your column names, I would suggest doing it ASAP. Month and Year are the names of functions and if you ever make a slip in VBA, you will get the current month or year rather than the one from your record/form. Best practice is to use only letters, numbers, and the underscore and to avoid VBA and SQL Reserved words. Since no one wants to memorize long lists of reserved words, the best solution is to create compound words separated with underscores or by case - SalesMonth, BirthYear, PartDesc, CustName, etc.