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

 

[PHV]

Anyway, I'd replace this:
strWHERE = "WHERE (Right([MonthYear], 4) = [txtYear]) And (Format([MonthYear], 'mmmm') = [txtMonth]) "
with something like this:

strWHERE = "WHERE Right(MonthYear,4)=" & Me!txtYear & " AND Format(MonthYear,'mmmm')='" & Me!txtMonth & "'"

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

 

[THWatson]

Thanks, PHV

I gather you don't have a suggestion for the txtStartDate and txtEndDate text boxes?

Tom

 

[MajP]

Not sure if I understand what part is not working. But I would set it up like this I think. I added a second column to lstMonths which has the integer value of the month (hidden)

Private Sub lstMonths_AfterUpdate()
  updateStartEnd
End Sub

Public Sub updateStartEnd()
  Dim yr As Long
  Dim mth As Integer
  mth = Nz(Me.lstMonths.Column(1))
  If IsNumeric(Me.txtYear) And Not mth = 0 Then
    yr = Me.txtYear
    Me.txtStartDate = DateSerial(yr, mth, 1)
    Me.txtEndDate = DateSerial(yr, mth + 1, 0)
  End If
End Sub

Private Sub txtYear_AfterUpdate()
  updateStartEnd
End Sub

If I change the year or select a month it shows new values

 

[THWatson]

MajP
You got it. PERFECT!

Works like a charm.

Thanks.

Tom

 

[THWatson]

There's another wrinkle in this.

I have placed on the form a subform called "fsubPerfectAttendance" because I want to display those members who achieved the required # of meetings attended in the month chosen.

The parameters could be fed from the value in the lstMonthYear, which is Month & Year (e.g. March 2009) ...
or the value could be "Between txtStartDate AND txtEndDate"

The subform is based on a qry called "yyyPerfectForMonthSelected." That query is the end result of 4 other queries, and the sql is

SELECT yyyTotalREGULARandActivity.MemberID, yyyTotalREGULARandActivity.FullName, yyyTotalREGULARandActivity.LastName, yyyTotalREGULARandActivity.PreferredName, yyyTotalREGULARandActivity.OKAY, yyyTotalREGULARandActivity.MonthYear
FROM yyyTotalREGULARandActivity
WHERE (((yyyTotalREGULARandActivity.OKAY)="Yes"))
ORDER BY yyyTotalREGULARandActivity.LastName, yyyTotalREGULARandActivity.PreferredName;

I know that starting a query with yyy is not normal. However, I have a large number of queries in this database, and since the query in question was based on 4 others, I wanted to be able to differentiate without hunting.

On the form, I have linked the form and subform thus:
Link Child fields: MonthYear
Link Master fields: Forms!frmPerfectAttendance!lstMonthYear

Then behind the command button I have included the following piece of code

sql = "SELECT MemberID, FullName, LastName, PreferredName, OKAY, MonthYear " _
& "FROM yyyPerfectForMonthSelected " _
& "WHERE ([StartDate] = Forms!frmPerfectAttendance!txtStartDate) AND ([EndDate] = Forms!frmPerfectAttendance!txtEndDate)"

Me!fsubPerfectForMonthSelected.Form.RecordSource = sql

I have also tried using this as the WHERE clause:

[MonthYear] Between Forms!frmPerfectAttendance!txtStartDate AND Forms!frmPerfectAttendance!txtEndDate

What happens is that as soon as the form opens the parameters "StartDate" and "EndDate" are called for. Then when the command button is pressed the parameters are called for again. And even then the subform isn't populated.

A push in the right direction would really be appreciated.

Thanks.

Tom

 

[THWatson]

I can stop the StartDate and EndDate parameters from showing by removing the record source value from the subform, so that it's not called for until the command button is pressed.

This however doesn't solve the rest of the problem.

Tom

 

[THWatson]

I think I need to go back and retrace my steps through the queries and get them to align with the subform.

Tom

 

[THWatson]

I have gone back into the query and changed it so the sql is as follows:

SELECT yyyTotalREGULARandActivity.MemberID, yyyTotalREGULARandActivity.FullName, yyyTotalREGULARandActivity.LastName, yyyTotalREGULARandActivity.PreferredName, yyyTotalREGULARandActivity.OKAY, yyyTotalREGULARandActivity.MonthYear, DateSerial(Year([MonthYear]),Month([MonthYear]),1) AS StartDate, DateSerial(Year([MonthYear]),Month([MonthYear])+1,0) AS EndDate
FROM yyyTotalREGULARandActivity
WHERE (((yyyTotalREGULARandActivity.OKAY)="Yes"))
ORDER BY yyyTotalREGULARandActivity.LastName, yyyTotalREGULARandActivity.PreferredName;

Now behind the command button on frmPerfectAttendance includes this sql for the subform "fsubPerfectForMonthSelected"

sql = "SELECT MemberID, FullName, LastName, PreferredName, OKAY, MonthYear, StartDate, EndDate " _
& "FROM yyyTotalREGULARandActivity " _
& "WHERE (OKAY = 'Yes') AND ([StartDate]=Forms!frmPerfectAttendance!txtStartDate) AND ([EndDate]=Forms!frmPerfectAttendance!txtEndDate) " _
& "ORDER BY LastName, PreferredName;"

Me!fsubPerfectForMonthSelected.Form.RecordSource = sql

When I press the command button I am still asked for "StartDate" and "EndDate"

Tom

 

[MajP]

why not define the subform query as a saved query, instead of building it in code?

so you code is simply
Me!fsubPerfectForMonthSelected.Form.RecordSource = "subFrmQry"

Makes it a lot easier to trouble shoot. I see a problem, but do not think it is your current problem.

This line
AND ([StartDate]=Forms!frmPerfectAttendance!txtStartDate) AND ([EndDate]=Forms!frmPerfectAttendance!txtEndDate) "

will resolve to that exact text string since your variables are included inside the quotes. You will not get something like

AND ([StartDate]=#1/1/2009#) AND ([EndDate]=#1/31/2009#) "

you would need something like

AND ([StartDate]= #" & Forms!frmPerfectAttendance!txtStartDate) & "# AND ([EndDate]= #" & Forms!frmPerfectAttendance!txtEndDate) & "# ..

 

[MajP]

Here are some techniques that I use to make this a lot easier. If I have a form value that I need to use in a query within a function I build a user defined function. Also when dealing with sql dates I always use a function to get the correct format.
It makes trouble shooting easier and increases flexibility.

To get the correct sql string for a date value.

Function getSQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            getSQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            getSQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

Now wrap that function

Public Function getStartDate() As String
  'your form name here
  Const frmName = "frmAttendance"
  Dim frm As Access.Form
  
  If CurrentProject.AllForms(frmName).IsLoaded Then
    Set frm = Forms(frmName)
    If IsDate(frm.txtStartDate) Then
      getStartDate = getSQLDate(frm.txtStartDate)
    End If
  End If
  'If the form is not loaded or no txtStartDate then return a default
  If getStartDate = "" Then
    getStartDate = getSQLDate(Int(Now()))
  End If

End Function

now if the form is not loaded or the startDate is not yet selected it simply returns todays date and your query will not crash telling you the form is not loaded.

So if the form is loaded I get something like
#02/01/2009#
or something like this if not loaded or no start date
#04/05/2009#

so in a query I now simply put.

"WHERE (OKAY = 'Yes') AND ([StartDate]= " & getStartDate & " AND ([EndDate]= " getEndDate & "...

 

[THWatson]

MajP
Thanks for your input.

It's clear to me that I'm not understanding the process behind your question why not define the subform query as a saved query, instead of building it in code?

I thought perhaps you meant to create the result of the query that is supposed to populate the subform, using a Make Table query and save the result of that as subFrmQry as a Query. So I did that, but then Access can't find that when the code runs.

So I tried to just save the result of the query as a Select query, naming it subFrmQry. But when the command button is pressed on frmPerfectAttendance that results in an error "too complex to evaluate."

So I tried to use the subFrmQry as the record source for the subform but that also results in a "too complex to evaluate" error.

(Feels as if I'm treading water here.)

I'll try the other techniques as well...haven't been able to get to them yet.

Tom

 

[MajP]

Sorry. I was just trying to say that instead of writing the sql string in code, you could make it one of your stored queries. Then set the record source to your stored query. The advantage is that you can then error check it by opening the query to see if it populates correctly.

So you can use the query builder and name your query. If you named this query "qryAttendanceSubFrm". Then your code would be simply

Me!fsubPerfectForMonthSelected.Form.RecordSource = "qryAttendaceSubFrm"

There would be no need to build the sql string, but more importantly you could error check the query.

 

[THWatson]

MajP
Well, this doesn't work. Either that, or it's possible it's Sunday night and I'm just not gettin' it.

The sql in code is exactly the same as query yyyPerfectForMonthSelected which is already a stored query. But when I use that as the record source it doesn't work in the form.

If I just run the subform by itself, I can use "yyyPerfectForMonthSelected" as the record source, and it functions perfectly.

So the problem must still lie in the interconnection between the form and the subform. That's, at least for me, the puzzle.

Tom

 

[THWatson]

You probably already deduced this, but the main form is Unbound, simply a display form.

Tom

 

[THWatson]

What is a good free online storage and sharing site where I could post a zipped, minimal, copy of the database?

Tom

 

[THWatson]

I have uploaded a zipped file to

http://www.myotherdrive.com/dyn/prof/2Nesd1eigXE0r6a65US

If the query called "yyyPerfectForMonthSelected" is run it produces the proper results.

When frmPerfectAttendance is run, and the month and year selected, the results are supposed to show in the subform.

Tom

 

[TheAceMan1]

Howdy THWatson . . .

I get a login screen. try

http://www.4shared.com

See Ya! . . . . . .

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

 

[THWatson]

Hi TheAceMan1
I uploaded the file to 4Share, as you suggested.

I think (hope!) this should get it...

http://www.4shared.com/account/dir/14311876/13ea6a2d/sharing.html?rnd=58

Tom

 

[THWatson]

I thought I would take a moment and explain what is at the root of all of this.

The database is for a Kiwanis club. It keeps track not only of members and their personal data, but also of their attendance.

A member can be granted an award for perfect attendance. This is where the process has more than enough wrinkles to make it appear old and decrepit.
1. Perfect Attendance is defined as any 12 continuous months in which the member has attended the required # of meetings.
2. The club's fiscal year runs from October 1 through September 30. Too bad the attendance award doesn't match this fiscal year but it doesn't. That means that every member in the club could be operating on a different "perfect attendance" cycle.
3. The Regular meetings are always on Thursdays. But they are not held on Christmas or New Years days. Therefore, there can be 3, 4 or 5 required Thursdays in a month, and also 50, 51, 52 or 53 possible meetings in a year.
3. The member can also do Makeup Activities, to make up for Regular meetings not attended. There are over 20 possible makeup activities.
5. The member can use no more Makeups than there are required Regular Thursdays in a month. In other words, if there are 3 Thursdays in a month and he misses all 3 Regular meetings, he needs to attend 3 Makeups in order to get perfect attendance for that month. Likewise, if there are 4 Regular Meetings Thursdays in a month and he misses all 4 of those, he needs 4 Makeups in order to achieve perfect attendance for that month.
The caveat to this requirement is that in months in which there are 5 Regular Meeting Thursdays, the member can still only use 4 Makeups, so would have to attend at least 1 Regular Meeting in order to get perfect attendance credit.
6. You cannot bank ahead or behind. So even if a member had 11 months in a row of perfect attendance, miss the 12th and you start a 12 month cycle all over again.

I have tried a few different ways of tackling this, but some queries I built took so long to run...and that's only with a little over a year and a half of data...that in the end they will choke.

So, this is what I have been working on...
1. Calculate the members who had perfect attendance for a chosen month. That uses the 5 queries built in order.
2. When I get those to show in a form, I can use an update sql to put the results into a table. The table will have a counter field. The counter field will either augment or reset to 0 according to the result in the month chosen.
For example, if the member's counter field is currently "1" and he gets perfect for the next ensuing month, his counter goes to 2...but if that member is not in the next month's list of perfect attendees his counter resets to 0.
If the member's counter says 11, and he doesn't get perfect in the 12 month, his counter resets to 0.

Tom