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 = "Week commencing " & 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??

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 = "Week commencing " & me!txtDate.Value
    End If
   

Have you tried just setting the txtWeekTitle Caption property to "Week commencing " & 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("YourTableName")

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 = "Week commencing " & 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
 
 
 

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

    Dim today As Integer
    today = 9    'Why this value (9), why not Now()?
   
    If Weekday(txtDate.Value) < today Then
        txtWeekTitle.Caption = "Week commencing " & txtDate.Value
    'txtDate does not need the ".Value" in Ms. Access
    ' - in fact it shouldn't even have it.
    'Also, if this was placed on the appropiate
    'section of the report OnCurrent event, the value
    'should be available.
    End If
    today = txtEnterDate.Value
    'again, the ".value" isn't necessary.
    'also, here, you appear to be assigning a date
    '(txtEnterDate) to an Integer (today).  I suggest
    'changing the declaration type of today to "Date"
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

I want "Week commencing to... "appears for every week.

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

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
 
 
 

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??!