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

 

[THWatson]

Thanks. I have code on the PA form so that if the user plugs in a month earlier than the earliest date in tblArchive a message pops up saying that the earliest date for which we have data is October 6, 2005.

Tom

 

[THWatson]

MajP
I am changing the Archive period. I had it set so that, in November or December of a year, the Archive process could be run and it would archive off Attendance data older than 2 years back from that October. I have changed this to older than 5 years.

I figured that...
1. With almost 4 years complete data in there now, the Back End isn't that large, so one more year isn't going to make a big difference
2. It will be a frosty Friday before the user will want to check a perfect attendance streak for a period greater than 5 years...and there won't be anyone in there anyway
3. This will avoid the need to grab attendance data from the archive file and conjoin it with current attendance data, and thus those couple of queries will run very fast.

Tom

 

[THWatson]

Just put new files on 4Shared. Made a # of Changes.

Here are the links.

http://www.4shared.com/file/122414097/7011701c/Guelph_Kiwanis.html

http://www.4shared.com/file/122414324/8aa4634/Guelph_Kiwanis_be.html

http://www.4shared.com/file/122414433/8a9af453/Archive_Guelph_Kiwanis.html

Tom