Solved Controls source is =[OpenArgs] From Form

[oxicottin]

Hello, I have a form with a few option buttons that use OpenArgs to change the dates in a report. If the date is a Friday #7 then highlight the textbox on the report. I tried using Weekday([OpenArgs])=7 under conditional formatting but it never changes the color. How should I approach this?

Date text box on report I need to change background color if it's a Friday.

Code on the form:

'Print Friday sheet if ticked.
    If Me.optFri = True Then
        If Me.Dirty Then Me.Dirty = False
        DoCmd.OpenReport "rpt_WeeklyAttendanceSheet", , , , , Format(Me.txtWeekEnding, "Long Date")
    End If

 

[GaP42]

Could it be openargs is a string and your vba wants a date?

 

[MajP]

I would get ride of = [openargs]. Does that actually work?

in the forms report's load event

if not (me.Openargs & "") = "" then
    me.txtDate = cdate(me.openargs)
end if

 

[oxicottin]

I would get ride of = [openargs]. Does that actually work?

in the forms load event

if not (me.Openargs & "") = "" then
    me.txtDate = cdate(me.openargs)
end if

@MajP yes it works well and its in the reports text box that uses the Openargs from the VBA in the forms option. You wrote to use your code in the forms load event, I'm not understanding and I think maybe I didn't explain what I'm trying to do very well. I attached a copy of the DB, what I want to do is if I open frm_WeeklyAttendanceSheet and tick the 5 options and select print then it will print those days based on the date in the forms txtWeekEnding text box. What I want it to do is if Friday is ticked (optFri) then I want the report rpt_WeeklyAttendanceSheet text box txtDate to have a highlighted background color.

 

[MajP]

I will take a look I meant the Reports on load.

 

[MajP]

This works for me in the report on load

Private Sub Report_Load()
  If Not (Me.OpenArgs & "") = "" Then
     Me.txtDate = CDate(Me.OpenArgs)
    If Weekday(Me.txtDate) = vbFriday Then Me.txtDate.BackColor = vbYellow
  End If
End Sub

However you cannot (or need to) pass a long date. Change the code to an easier to read date format
DoCmd.OpenReport "rpt_WeeklyAttendanceSheet", acPreview, , , , Format(Me.txtWeekEnding - 4, "mm/dd/yyyy")


Open args passes a string, and if you push that string to an unbound text box that has a date format applied it might figure out it is a date, but maybe not. So convert it to a date format in the load event.

 

[theDBguy]

I tried using Weekday([OpenArgs])=7 under conditional formatting but it never changes the color.

What happens if you try?

Weekday([txtDate])=7

Edit: Based on what @MajP just said, maybe also try:

Weekday(CDate([txtDate]))=7

 

[MajP]

If in your form if you would have simply passed a date in open args without formatting to long date it probably would work fine even without the mod I did. VB is really good at implicitly casting anything that looks like a date, but that long date format is probably too complicated

Here is a good demo showing how it casts a string to a date when the string is less ambigous.

Public Sub DateConvert()
  Dim x As Variant
  Dim y As Date

  x = "1/19/2024"
  y = x
  Debug.Print y

  x = "Friday, January 19, 2024"
  y = x
  Debug.Print y
End Sub

The above works fine when I set a date variable to a string of form "1/19/2024"
It fails with a type mismatch when I try the same thing with "Friday, January 19, 2024"

Your conditional formatting never worked because it could not figure out it was a date.

 

[oxicottin]

This works for me in the report on load

Private Sub Report_Load()
  If Not (Me.OpenArgs & "") = "" Then
     Me.txtDate = CDate(Me.OpenArgs)
    If Weekday(Me.txtDate) = vbFriday Then Me.txtDate.BackColor = vbYellow
  End If
End Sub

However you cannot (or need to) pass a long date. Change the code to an easier to read date format
DoCmd.OpenReport "rpt_WeeklyAttendanceSheet", acPreview, , , , Format(Me.txtWeekEnding - 4, "mm/dd/yyyy")


Open args passes a string, and if you push that string to an unbound text box that has a date format applied it might figure out it is a date, but maybe not. So convert it to a date format in the load event.

@MajP I'm sorry I tried your example in my reports load and when printing, it's still prints normal with no yellow background? I see what your saying with your X and Y example I get the error 13 also as you stated BUT I changed the long date to mm/dd/yyyy like you suggested and its outcome is the same.

The reason for the long date is so it shows what day of the week which is needed on the printouts...

 

[arnelgp]

here is another.
I remove the Formating on your OpenArgs parameter when you open the report.
next I added a dummy (hidden) textbox, txtOpenArg and set its ControlSource to:

=[OpenArgs]

txtDate textbox controlsource, I changed to:

=Format([OpenArgs], "Long Date")

next i changed the Conditional Format to:

Format([txtOpenArg], "ddd") = "Fri"

 

[oxicottin]

@arnelgp your example worked on printouts thanks!