Hi I have a small problem. I have a form which allows the user to see what months have not been recorded. Each month a return is submitted covering the full month.
The user when they add a record(Month) cicks on a button and a small form with a list box (called Me!calendar_months) comes up. It essentially does a loop to go back 12 months. The SQL Statement works it is simply the display in the list box of February. All other months work fine.

The problem is that when it comes to February it states it is March. The message box states that Month(Today_date) - I is 2 which is Feb.
I cant see where I am going wrong.
Any help appreciated

cheers
Tony
-------------------------------------------------------------------------

The code looks like
If Me!calendar_months.ListCount > 0 Then Me!calendar_months.RowSource = "" 'clear the list box
For I = 1 To backmonth ' backmonth is the number of months to go back default is 12
'problem here with february comes up as march - TO FIX
If (IsNull(DLookup("[MonthEnding]", "[TblEmployeeHoursWorked]", "[OperatorID] =" & Forms!FrmInjury!subFrm_Employment.Form!OperatorID & _
" AND [SiteCode] LIKE '" & Forms!FrmInjury!subFrm_Employment.Form!SiteCode & _
"' AND [monthending] = #" & _
check_date(Format(DateSerial(Year(Today_date), (Month(Today_date) - I), (Day(Today_date) - 1)), "yyyy - mmmm")) & "#"))) Then

'MsgBox (Month(Today_date) & vbCrLf & _
Month(Today_date) - I & vbCrLf & _
DateSerial(Year(Today_date), (Month(Today_date) - I), (Day(Today_date) - 1)) & vbCrLf & _
Format(DateSerial(Year(Today_date), (Month(Today_date) - I), Day(Today_date) - 1), "yyyy - mmmm"))

Me!calendar_months.AddItem Format(DateSerial(Year(Today_date), (Month(Today_date) - I), (Day(Today_date) - 1)), "yyyy - mmmm")
End If
Next I
'IIf(Month(Today_date) = 2, 0, 0)
Me!calendar_months.AddItem "****** - *******"
Me!calendar_months.AddItem "Other - Use Cal."
End Function

Private Function check_date(indate) As Date
Dim f1, f2, f3, newdate
f1 = Split(indate, " - ")(0)
f2 = Split(indate, " - ")(1)
'must convert months to numbers
If f2 = "January" Then check_date = DateSerial(Year("15/01/" & f1), (Month("15/01/" & f1) + 1), 0)
If f2 = "February" Then check_date = DateSerial(Year("15/02/" & f1), (Month("15/02/" & f1) + 1), 0)
If f2 = "March" Then check_date = DateSerial(Year("15/03/" & f1), (Month("15/03/" & f1) + 1), 0)
If f2 = "April" Then check_date = DateSerial(Year("15/04/" & f1), (Month("15/04/" & f1) + 1), 0)
If f2 = "May" Then check_date = DateSerial(Year("15/05/" & f1), (Month("15/05/" & f1) + 1), 0)
If f2 = "June" Then check_date = DateSerial(Year("15/06/" & f1), (Month("15/06/" & f1) + 1), 0)
If f2 = "July" Then check_date = DateSerial(Year("15/07/" & f1), (Month("15/07/" & f1) + 1), 0)
If f2 = "August" Then check_date = DateSerial(Year("15/08/" & f1), (Month("15/08/" & f1) + 1), 0)
If f2 = "September" Then check_date = DateSerial(Year("15/09/" & f1), (Month("15/09/" & f1) + 1), 0)
If f2 = "October" Then check_date = DateSerial(Year("15/10/" & f1), (Month("15/10/" & f1) + 1), 0)
If f2 = "November" Then check_date = DateSerial(Year("15/11/" & f1), (Month("15/11/" & f1) + 1), 0)
If f2 = "December" Then check_date = DateSerial(Year("15/12/" & f1), (Month("15/12/" & f1) + 1), 0)

End Function


This post has been edited by tonez90: Aug 30 2010, 10:15 PM

Hi Tony,

There is a much simpler, faster and more reliable way to do this, but I need some more information.

1. What is the datatype of tblEmployeeHoursWorked.MonthEnding, and what sort of format is it in (sample please)?
2. What is the datatype of tblEmployeeHoursWorked.SiteCode, and what sort of format is it in (sample please)?
3. What is the datatype of tblEmployeeHoursWorked.OperatorID, and what sort of format is it in (sample please)?
4. What can I expect to see in Forms!FrmInjury!subFrm_Employment.Form!OperatorID?
5. What can I expect to see in Forms!FrmInjury!subFrm_Employment.Form!SiteCode?
6. Isn't the function check_date() just returning the last day of the month?
7. Given you're in NT, don't you use UK date format?

In answer to your questions
What is the datatype of tblEmployeeHoursWorked.MonthEnding, and what sort of format is it in (sample please)?
the format is a number format. I store the actual data for the end of each month (date format) with month (number) and Year (number). Yes it is sloppy but this is an inherited database which only used to strore month and year and not the actual date so I had to continue with it.

What is the datatype of tblEmployeeHoursWorked.SiteCode, and what sort of format is it in (sample please)?
The site ocde is string format as it is just an identifyer (a company may have multiple sites each submitting its own monthly report).

What is the datatype of tblEmployeeHoursWorked.OperatorID, and what sort of format is it in (sample please)?
The OperatorID is a number allocated to each unique operator (i.e. one operator multiple sites)

What can I expect to see in Forms!FrmInjury!subFrm_Employment.Form!OperatorID?
Simply the ID number of the operator.

What can I expect to see in Forms!FrmInjury!subFrm_Employment.Form!SiteCode?
A code for the site. Note each site has a unique code (say OGP or 123 or ABC13 etc a uniue identifier which is again from the old database)

Isn't the function check_date() just returning the last day of the month?
Yes as the submitted form is a summary of the preceding months data (which is from 1st to last day of each month). The last day is used when we interrogate the data for reports.

Given you're in NT, don't you use UK date format?
In Australia we use the date format DD/MM/YYYY.


In essence the process is - a monthly report comes in, the user opens the storage form and selects add new. This opens another list box form which is populated with the data (dates) for that company and site that no data has been entered for. It states the dates where no data is recorded. an example is if company xyz and site abc is selected the list box will look at the table for those records belonging to the company and site. It will then check the dates to see if a record exists for it (using the end of each month). It works for every month except for February each year as it states it is March rather thaan February.

Hope that helps
Tony

This post has been edited by tonez90: Aug 30 2010, 11:22 PM

Hi Tony,

That's still not enought. I did ask for examples.

I have to make a few assumptions here. For example, I'm assuming monthending is stored in yyyymm format. I'm also assuming Today_date is an actual date. Lastly, I'm assuming you really want to filter on an exact match for SiteCode, rather than a LIKE.

Thanks for the reply but unfortunately the same thing happens with February (see screen shot). This one is strange as the months are recognised etc.

OK, then you have duplicates. Try this:

Worked much better but had to change Month(Today_Date) +1 to Month(Today_Date) - i or else it simply displayed all the same months.

Thanks for your help