Process Report Data Dynamically

[drkestrel]

how could I 'process' values in an Access report dynamically?

What I have is a report with "Date" as a column (and hence a textbox txtDate whose data source is linked to the corresponding field in the DB Table). What I want is for each week to have a title 'Week commencing....'. I attempted to do something like the following-

Private Sub Report_Open(Cancel As Integer)
    Dim today As Integer
    today = 9
   
    If Weekday(txtDate.Value) < today Then
        txtWeekTitle.Caption = &quot;Week commencing &quot; & txtDate.Value
    End If
    today = txtEnterDate.Value
End Sub

Unfortunately,

txtDate.Value

is NOT allowed (error saying it doesn't have a value).

Any ideas??

 

[kathryn]

This may have to do with the fact that the text box isn't populated when the Open event occurs, but I am not sure. Have you tried using the Me keyword?


If Weekday(me!txtDate.Value) < today Then
me!txtWeekTitle.Caption = &quot;Week commencing &quot; & me!txtDate.Value
End If


Have you tried just setting the txtWeekTitle Caption property to &quot;Week commencing &quot; & txtDate.Value in the txtWeekTitle properties box???


An other way to do this is to open a recordset based on the table (or maybe a query) and get the value from there.

Something like

dim rst as recordset
dim datWeek as Date

set rst=currentdb.openrecordset(&quot;YourTableName&quot

rst.movefirst 'I am assuming the table with date only has one record?????

set datWeek = rst!YourDateFieldName


If Weekday(datweek) < today Then
me!txtWeekTitle.Caption = &quot;Week commencing &quot; & datWeek.Value
End If

I am not sure why you are testing that Weekday is less than 9; as I read the help file, Weekday only returns values from 1 to 7.

Anyway, I hope this helps.

Kathryn

 

[MichaelRed]

Private Sub Report_Open(Cancel As Integer)
[tab]'Since this is in the Open, it will occur only once
[tab]'therefore you would only get a single date for all
[tab]'occurances of the field. It should probably be in
[tab]'a section on current event

Dim today As Integer
today = 9[tab]'Why this value (9), why not Now()?

If Weekday(txtDate.Value) < today Then
txtWeekTitle.Caption = &quot;Week commencing &quot; & txtDate.Value
[tab]'txtDate does not need the &quot;.Value&quot; in Ms. Access
[tab]' - in fact it shouldn't even have it.
[tab]'Also, if this was placed on the appropiate
[tab]'section of the report OnCurrent event, the value
[tab]'should be available.
End If
today = txtEnterDate.Value
[tab]'again, the &quot;.value&quot; isn't necessary.
[tab]'also, here, you appear to be assigning a date
[tab]'(txtEnterDate) to an Integer (today). I suggest
[tab]'changing the declaration type of today to &quot;Date&quot;
End Sub

Unfortunately, txtDate.Value is NOT allowed (error saying it doesn't have a value).



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[drkestrel]

I want &quot;Week commencing to... &quot;appears for every week.

I set today=9 initially cos I want the Week commencing to appears for the FIRST week, no matter what..

 

[kathryn]

OK, are you saying that you have a report that is grouped on weeks? In other words will one report have multiple weeks or will it cover only one week?

Kathryn

 

[drkestrel]

I need a report that is Grouped by week

I created a function that return the monday before a date as follows-

Public Function findMonday(theDate As Date)
    Dim theDay, theMonth, theyear As Integer
    Dim result As String
    Dim monthString As String
    theDay = Day(theDate)
    theMonth = Month(theDate)
    theyear = Year(theDate)
    Select Case Weekday(theDate)
           
        Case Is = 3
            theDay = theDay - 1
        Case Is = 4
            theDay = theDay - 2
        Case Is = 5
            theDay = theDay - 3
        Case Is = 6
            theDay = theDay - 4
        Case Is = 7
            theDay = theDay - 5
    End Select
    If theDay = 0 Then
        Dim tmpYear, tmpMonth As Integer
        tmpYear = theyear
        tmpMonth = theMonth - 1
        If tmpMonth = 0 Then
            tmpMonth = 12
            tmpYear = theyear - 1
        End If
        theDay = DateDiff("d", DateSerial(tmpYear, tmpMonth, 1), theDate, vbSunday)
        theMonth = theMonth - 1
    End If
    If theMonth = 0 Then
        theMonth = 12
        theyear = theyear - 1
    End If

    findMonday = DateSerial(theyear, theMonth, theDay)
End Function

Main query is something like

SELECT myDate,others,findMonday(myDate) as [theMonday] FROM myTable

Then I create subquery

SELECT DISTINCT theMonday from mainQuery

I then have sub report with the main bits linked to mainQuery, and MAIN report linked to SUBquery.

Unfortunately, it fails to sort the Monday Groups by Date format, even if I use

SELECT DISTINCT theMonday from mainQuery ORDER BY theMonday

How could I group the Mondays properly??!