Display DateSerial value in text box on form 4

[THWatson]

The form is called (currently in design stage) Form3.

1. There is a text box called "txtYear" in which the user enters a 4 digit year.

2. There is a list box called "lstMonths" based on a table. It simply lists the month names January through December.

3. There is a text box called "txtMonth" which will display the month selected in lstMonths.

4. There is a list box called "lstMonthYear" the row source for which is a Select Query which is fed from a table called "tblMonths."
There are actually only 2 fields in the table, MonthYear and Required.
The SQL for the query however extracts the Year, the Month, and uses a DateSerial function to get the first and last days of the month. The SQL is

SELECT tblMonths.MonthYear, Right([MonthYear],4) AS [Year], Format([MonthYear],"mmmm") AS [Month], DateSerial(Year([MonthYear]),Month([MonthYear]),1) AS FirstOfMonth, DateSerial(Year([MonthYear]),Month([MonthYear])+1,0) AS LastOfMonth
FROM tblMonths
ORDER BY tblMonths.MonthYear;

What ends up getting displayed in lstMonthYear is the MonthYear column, column 0, from the table. The user selects nothing here. The value gets fed from a command button which has the following code

Dim sql As String
Dim strWHERE As String
strWHERE = "WHERE (Right([MonthYear], 4) = [txtYear]) And (Format([MonthYear], 'mmmm') = [txtMonth]) "
sql = "SELECT tblMonths.MonthYear, Right([MonthYear],4) AS [Year], Format([MonthYear],'mmmm') AS [Month] " _
& "FROM tblMonths " _
& strWHERE & "ORDER BY tblMonths.MonthYear;"
Me.lstMonthYear.RowSource = sql

I have put on the form 2 additional text boxes, "txtStartDate" and "txtEndDate."
In txtStartDate I want to display the first day of the month selected in the process (e.g. March 1, 2009).
In txtEndDate I want to display the last day of the month selected (e.g. March 31, 2009).

I have tried putting the DateSerial serial function as the control source for the respective txtStartDate and txtEndDate text boxes. I have tried adding code to the command button to populate those text boxes. I have also tried putting code on the AfterUpdate event for lstMonthYear but since it itself is populated by code this doesn't work.

The actual end purpose for this form is to display those members who achieved Perfect Attendance in a club for a given month.

Any ideas would be appreciated.

Tom

 

[MajP]

You seem to prefer writing your sql in code, than storing a query. This is not the most efficient way.

So I simply took your code and made a query

qrySubPerfectStreak

SELECT qryPerfectStreak.MemberID, qryPerfectStreak.FullName, qryPerfectStreak.StreakStartMonth, qryPerfectStreak.StreakLength, qryPerfectStreak.EndMonth, qryPerfectStreak.LastName, qryPerfectStreak.PreferredName, qryPerfectStreak.Status, LastPerfectAttendance
FROM qryPerfectStreak
WHERE (((qryPerfectStreak.Status) = 'Active' Or (qryPerfectStreak.Status) = 'Senior' Or (qryPerfectStreak.Status) = 'Leave of Absence'))
ORDER BY qryPerfectStreak.LastName, qryPerfectStreak.PreferredName;

Then simplified the code

Private Sub cmdFindPerfect_Click()
Me.lstMembers = Null

If Me.txtEndDate < (DateSerial(Year(txtEndDate), Month(txtEndDate) + 1, 0)) Then
Me.txtEndDate = DateSerial(Year(txtEndDate), Month(txtEndDate), 0)
Else
Me.txtEndDate = Date
End If

Me!fsubPerfectStreak.Form.RecordSource = "qrySubPerfectStreak"
Me.fsubPerfectStreak.Form.Requery

If IsNull(Me!fsubPerfectStreak.Form!FullName) Then
Call MsgBox("There is no data for the dates selected.", vbExclamation, "No data")
End If

End Sub

Now if I need to modify the query I do not have to change code. Plus the query is optimized by the JET Sql optimizer.

 

[THWatson]

MajP
Know what...it's the piece of code

If Me.txtEndDate < (DateSerial(Year(txtEndDate), Month(txtEndDate) + 1, 0)) Then
Me.txtEndDate = DateSerial(Year(txtEndDate), Month(txtEndDate), 0)
Else
Me.txtEndDate = Date
End If

that's getting in the way. It's doing some nasty things in there. So if I remove that everything works fine.

Thanks.

By the way, I think I may take the "bring up Calendar approach" away. Too many clicks to get the dates right.

Tom

Tom

 

[MajP]

I went back and looked at your code and I think your real problem is here

If Me.txtEndDate < (DateSerial(Year(txtEndDate), Month(txtEndDate) + 1, 0)) Then
Me.txtEndDate = DateSerial(Year(txtEndDate), Month(txtEndDate), 0)
Else
Me.txtEndDate = Date
End If

This check makes no sense. I beleive you want to be ensuring that the end date is greater than the start date and probably also checking that it is not greater than the current date. But this checks to see if the end date is less than the last day of the previous month. That Can never be true. So it sets the end date to the current date.

 

[THWatson]

Well, I originally put it there for this reason...

A single click in the End Date box brought up today's date. However, if that date is less than the last day of the current month (e.g. April 16) there would be less than a full month's data to work with so the perfect in streak could never, ever work out.

The bug in that is that whenever the date chosen is less than the end of the current month, it ALWAYS resets, and I don't want that to happen.

In any event, I am ditching the Calendar control approach. It works very well in some situations, and saves typing dates, but in this case it's more a nuisance than a help.

(best laid plans of mice and men, et al)

Tom

 

[MajP]

Actually the way the code works it really only cares about the month and year. When it reads in an end date it does this

currentMonth = DateSerial(Year(dtmEndDate), Month(dtmEndDate), 1)

So if you pass in 3/1/2009 or 3/15/2009 or 3/31/2009 it will convert it to the last day of the month. The code is only designed to see if they have credit for the whole month. So what you can not do is check if they are good for half of a month. If it is the second week in April and someone has made 2 meetings, they still will not have perfect attendance for April because April has 5 required meetings. So defaulting it to todays date is probably not such a good idea. Probably the month prior is better.

The bug in that is that whenever the date chosen is less than the end of the current month, it ALWAYS resets, and I don't want that to happen

That is not what the bug is
Me.txtEndDate < (DateSerial(Year(txtEndDate), Month(txtEndDate) + 1, 0)

You would get that bug if your code says this
Me.txtEndDate < (DateSerial(Year(Date()), Month(Date()) + 1, 0)
That is probably what you meant to check

 

[THWatson]

Right on! That

If Me.txtEndDate < (DateSerial(Year(Date), Month(Date) + 1, 0)) Then
Me.txtEndDate = DateSerial(Year(txtEndDate), Month(txtEndDate), 0)
Else
Me.txtEndDate = Date
End If

does it.

Works for both the subform and the report.

THANKS!

Tom

 

[TheAceMan1]

Just for reference ... MajP's post for 13 Apr 09 9:1 ... Ya can't be lazy when you use [ignore][tt][/tt][/ignore]

[tt][blue]MemberID MonthYear Required Attended Makeup MakeUpCredit Credited
-------- ----------- -------- -------- ------ ----------- ---------[/blue]
1 01/01/2008 5 4 0 0 NoCredit
1 02/01/2008 4 3 0 0 NoCredit
1 03/01/2008 4 4 3 3 Credit
1 04/01/2008 4 4 0 0 Credit
1 05/01/2008 5 5 1 1 Credit
1 06/01/2008 4 3 0 0 NoCredit
1 07/01/2008 5 4 1 1 Credit
1 08/01/2008 4 2 0 0 NoCredit
1 09/01/2008 4 3 1 1 Credit
1 10/01/2008 4 4 6 4 Credit
1 11/01/2008 4 4 1 1 Credit
1 12/01/2008 3 2 0 0 NoCredit
1 01/01/2009 4 3 2 2 Credit[/tt]

Everyones report card for last month

[tt][blue]MemberID MonthYear Required Attended Makeup MakeUpCredit Credited
-------- --------- -------- -------- ------ ------------ --------[/blue]
1 3/1/2009 4 4 0 0 Credit
2 3/1/2009 4 0 0 NoCredit
3 3/1/2009 4 2 0 0 NoCredit
4 3/1/2009 4 0 1 1 NoCredit
5 3/1/2009 4 3 0 0 NoCredit
6 3/1/2009 4 0 0 0 NoCredit
7 3/1/2009 4 0 0 0 NoCredit
8 3/1/2009 4 0 0 0 NoCredit
9 3/1/2009 4 0 0 0 NoCredit
10 3/1/2009 4 0 0 0 NoCredit
11 3/1/2009 4 2 0 0 NoCredit
12 3/1/2009 4 4 1 1 Credit
13 3/1/2009 4 3 0 0 NoCredit
14 3/1/2009 4 0 0 0 NoCredit
15 3/1/2009 4 0 0 0 NoCredit
16 3/1/2009 4 0 0 0 NoCredit
17 3/1/2009 4 1 0 0 NoCredit
18 3/1/2009 4 3 1 1 Credit[/tt]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[THWatson]

I'm getting squirrelly results using

If Me.txtEndDate < (DateSerial(Year(Date), Month(Date) + 1, 0)) Then
Me.txtEndDate = DateSerial(Year(txtEndDate), Month(txtEndDate), 0)
Else
Me.txtEndDate = Date
End If

Say I enter the Start Date of 10/1/07, and the End Date of 9/30/08...the code changes the End Date to 8/31/08

This doesn't make sense to me.

Tom

 

[THWatson]

Maybe this is what I want

If Month(Me.txtEndDate) = Month(Date) And Day(Me.txtEndDate) < (DateSerial(Year(Date), Month(Date) + 1, 0)) Then
MsgBox "some error message"
Me.txtEndDate.SetFocus
Exit Sub
End If

because I only need to worry about it if the End Date selected is prior to the last day of the current month and we're not at the end of the month yet, so there won't be a full month's data.

Tom

 

[PHV]

I'd try this:

If Me!txtEndDate = Date And Date < (DateSerial(Year(Date), Month(Date) + 1, 0)) Then
  Me!txtEndDate = DateSerial(Year(Date), Month(Date), 0)
  Me!txtEndDate.SetFocus
  Exit Sub
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[THWatson]

PHV
That seems to work too.

Is there an advantage of this formulation over the one I posted?

Tom

 

[PHV]

Your is wrong as you test a Day against a DateSerial ...

 

[THWatson]

Thanks, PHV

I have a list box called "lstMonths". (That will, of course, only work for the current year.)

The AfterUpdate event on lstMonths has the following code

Dim yr As Long
Dim mth As Integer

    mth = Nz(Me.lstMonths.Column(1))
    yr = Year(Date)
    Me.txtStartDate = DateSerial(yr, mth, 1)
    Me.txtEndDate = DateSerial(yr, mth + 1, 0)

We're not finished with April yet and therefore don't have complete data for April...so if the user clicks a month that is April or later than April, I want a message to indicate that a month earlier in the year than April needs to be clicked.

How do I change the following to ensure that? It works for months after April, but not for April.

If Me!txtEndDate = Date And Date < (DateSerial(Year(Date), Month(Date) + 1, 0)) Then
  Me!txtEndDate = DateSerial(Year(Date), Month(Date), 0)
  Call MsgBox("The current month is not yet complete" _
            & vbCrLf & "so a full month's data is not yet available." _
            & vbCrLf & "      - - - - - - - -" _
            & vbCrLf & "Please select an End Date prior to the current month." _
            , vbExclamation, "Data check")
  Me!txtEndDate.SetFocus
  Exit Sub
End If

Tom

 

[PHV]

I'd try this:

If Me!txtEndDate >= Date And Date < (DateSerial(Year(Date), Month(Date) + 1, 0)) Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[THWatson]

PHV
I'm assuming you mean this formulation on the AfterUpdate

Dim yr As Long
Dim mth As Integer
If Me!txtEndDate >= Date And Date < (DateSerial(Year(Date), Month(Date) + 1, 0)) Then
mth = Nz(Me.lstMonths.Column(1))
yr = Year(Date)
    Me.txtStartDate = DateSerial(yr, mth, 1)
    Me.txtEndDate = DateSerial(yr, mth + 1, 0)
End If

But nothing happens now when I select a Month.

(unless I'm misunderstanding something)

Tom

 

[PHV]

I meant something like this:

mth = Nz(Me.lstMonths.Column(1))
yr = Year(Date)
Me!txtStartDate = DateSerial(yr, mth, 1)
Me!txtEndDate = DateSerial(yr, mth + 1, 0)
If Me!txtEndDate >= Date And Date < (DateSerial(yr, Month(Date) + 1, 0)) Then
  Me!txtStartDate = DateSerial(yr, Month(Date) - 1, 1)
  Me!txtEndDate = DateSerial(yr, Month(Date), 0)
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[THWatson]

Ah! I see what that does. It reverts to the last month in which there is complete data.

Thanks!

Tom

 

[MajP]

http://www.4shared.com/file/99461606/9aaed5d9/DatePicker.html

Tom,
Here is a concept for setting the start and end period. I thought what you had was a little clunky.

I am just going to show the user month and year, and provide them a month year picker. The day is irrelevant because the check only needs the month and year. This kind of bullet proofs everything and makes it much easier to pick a start and stop period. Then I check the following.
1) There are valid dates
2) The start is not greater than the end
3) neither the end or start are in the future

 

[THWatson]

Thanks, MajP

Looks great! I will incorporate it into my frmPerfectAttendance.

Tom (aka 'ol clunky)

 

[MajP]

This demonstrates a nice trick of using a dialog pop up form to return a value. If you hit OK on the pop up it sets the form to invisible, if you hit cancel it closes the form. At that point the code in the calling form continues to execute. You check to see if the pop up is still loaded (invisible) and grab the values off of it then you close it. If it is not loaded that means it was canceled.

Most techniques like the calendar pass in an openargs to the pop up of the control to get the value. The problem with that approach is enforcing error checking.