Modify Date

[Duane8889]

Hello
I am using Access 2k and what I'm trying to do is create an expression that will change a Date label on a report. It is a two column report and each column is for a different day, generally consecutive days. But, if the current day is Friday it will ask the user if they are working the next day, Saturday. If so then the 2nd column will have the Saturday as the label. No problems there with this code...

=Format(DateAdd("d",1,Date()),"dddd")

It is the expression for the label on the report. I just want the day of the week to show.

But, if they are not working Saturday then the 2nd column label needs to display Monday instead. I tried this bit...

Private Sub cmdReportTwoDay_Click()
on Error GoTo Err_cmdReportTwoDay_Click
    Dim stLinkCriteria as String
    Dim myDate As Date
    Dim response as String

    myDate = Weekday(Date)
    If myDate = 6 Then
    MsgBox "It's Friday! Are you working tomorrow?", vbYesNo
       If response = vbNo Then
      Reports!rptRotationTowDay.Controls!txtDate2 = Format(DateAdd("d",3,Date()),"dddd")
    End If
   
    stDocName = "rptRotationTwoDay"
    DoCmd.OpenReport stDocName, acPreview

Exit_cmdReportTwoDay_Click:
    MsgBox Err.Description
    Resume Exit_cmdReportTwoDay_Click
End Sub

But I am getting the error "Type mismatch."
I have this code within a button on a form calling the report. I wonder if there is a better location for it since earlier I was getting the error "The report name 'rptRotationTwoDay' you entered is misspelled or refers to a report that isn't open or doesn't exist."

thanks for any help!
Duane

 

[jsteph]

If response = vbNo Then

Response is dim'd as a string, vbNO is a long integer.

Plus, to use Response as a return from msgbox, use this syntax:

dim response as long
response = MsgBox("It's Friday! Are you working tomorrow?", vbYesNo)
if response = vbNo ...

--Jim

 

[Duane8889]

Thanks jsteph
I made the changes and the report comes up but the textbox for the 2nd column day is blank. I changed things around a bit I don't think I destroyed anything...

Private Sub cmdReportTwoDay_Click()
on Error GoTo Err_cmdReportTwoDay_Click
    Dim stLinkCriteria as String
    Dim myDate As Date
    Dim response as Long

    myDate = Weekday(Date)
    If myDate = 6 Then
    response = MsgBox("It's Friday! Are you working tomorrow?", vbYesNo)
       If response = vbYes Then
          stDocName = "rptRotationTwoDay"
          DoCmd.OpenReport stDocName, acPreview
          'txtDate2 = Format(DateAdd("d",1,Date()),"dddd")
          Reports!rptRotationTowDay.Controls!txtDate2 = Format(DateAdd("d",1,Date()),"dddd")
       Else
          Reports!rptRotationTowDay.Controls!txtDate2 = Format(DateAdd("d",3,Date()),"dddd")
    End If
   
    Exit_cmdReportTwoDay_Click:
    MsgBox Err.Description
    Resume Exit_cmdReportTwoDay_Click
End Sub

 

[dhookom]

You mis-spelled the report name. I would not attempt to push a value into a report from code in a form. I would set some value in the form (hidden text box) and pull the value from within the report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]