How to return dates that are on Saturday and Sunday

[Poobear1929]

Here is my question; I hope I am making it clear. I have a report, which I want to insert a date for the up coming weekend. Here is the scenario,

Every week I pick a group of individuals who have to come into work on the weekend. I have a report that has the individuals who I pick from a form whose names appear on the report. I have a field that I want to have the dates of the weekend duty. So lets say I run the report on a Wednesday, 19 May 04, I want it to return 22 & 23 May 04. Since those dates are Saturday and Sunday. So each week, no matter what day I run it, prior to Saturday, it will give me the weekend dates. If anyone can help me, I will be so grateful.

 

[scriverb]

Try this expression:

Saturday:
DateAdd("d",7 - Switch(DatePart("W",Date()), Date())

Sunday:
DateAdd("d",7 - Switch(DatePart("W",Date()), Date()) + 1

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[SgtJarrow]

Place the following in the OnOpen event of the report...

Dim dteCurrent As Date

dteCurrent = Date
Do Until Weekday(dteCurrent) = 7
    dteCurrent = DateAdd("d", 1, dteCurrent)
Loop

Me![FieldNameInReport] = CStr(Day(dteCurrent)) & " & " & CStr(Format(DateAdd("d", 1, dteCurrent), "dd mmm yy"))

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[scriverb]

Sorry about the code. I started using a Switch statement and changed in mid-stream and left the function in. Here is the updated change:

Saturday:
DateAdd("d",7 - DatePart("W",Date()), Date())

Sunday:
DateAdd("d",7 - DatePart("W",Date()), Date()) + 1

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[Poobear1929]

Thank you both for your quick reply. One more question, how do I make it work? I put a text box on my report in design view and named it date1. Then I went into the properties of the Report to write the event. However nothing happens? I pick names from on the form, I hit report and the report is supposed to open with the dates of the weekend. I think I missed something. You either of you knows what I did wrong, that would be great.

 

[SgtJarrow]

OPen the report in design view. find the OnOpen property in the properties window. use the combo box to select "Event Procedure". Click the elipse (3 dots) to the right of the box and the code window will appear. You will see something like:

Report_OnOpen()

End Sub

Paste the code I supplied before between the Report_OnOpen and the End Sub. Then change the Me![FieldNameInReport] to Me![date1], save the report and you should be set.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[scriverb]

With the expressions that I have provided you can use them as the control source for controls in your report. Just copy and paste them each into the appropriate controls control source.

Or, if you are using a query as the RecordSource for the report just add two columns to the query. Then the values will be made part of the recordset and you can just pick them from the dropdown list in the controls Controlsource. Just copy them completely including the preceeding Name and paste into individual columns in the query.

Or you can assign them to variables in the OnOpen event procedure of your report.

I would put them in the Query myself.

Good luck.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[Poobear1929]

One again thank you both, I got it working. I used some aspects of both of yours coding. I made a text box using scriverb's code, but add the formating code from mstrmage1768's code. Now I have another question, when the report is ran, it is sent as an e-mail attachment which is opened in word. How do I get it to open up in a word templet. Our company had letter head, and I want to use it. Here is the event code for it currently. Any help would be great.

Private Sub Command76_Click()
On Error GoTo Err_Command76_Click

Dim stDocName As String

stDocName = "StdByLtr"
DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , "A/C Flight After Hours Standby", "See attachment for A/C Flight Weekly After Hours Standby Coverage."



Exit_Command76_Click:
Exit Sub

Err_Command76_Click:
MsgBox Err.Description
Resume Exit_Command76_Click

End Sub

Poobear1929

 

[scriverb]

I think the easiest way would be to scan your companies letterhead and save it as a .jpg picture file. Create a Picture control in the PageHeader section and embed the picture in the report. Now you can just send it out as is and the letterhead is at the top.

Good luck.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]