Need help with Date/Time Question 1

[rookiedev]

I was hoping maybe someone could help me with a date time problem I am having....I have 2 unbound text boxes that are populated by this code:
Private Sub Form_Open(Cancel As Integer)
Me![Beginning Date] = Date - WeekDay(Date, 4) + 1
Me![Ending Date] = Me![Beginning Date] + 6

End Sub

This returns a beginning date of 2/19/03 and an Ending Date of 2/25/03.(These dates signify our pay week from Wednesday thru Tuesday) My payroll report is based on these dates...which is fine except for one thing...I enter the time worked the next day. (We work 24 hrs a day.)

Here is the problem..on Wednesday mornings I enter the time for Tuesday but my date range has change to reflect the current week. This is the day that I send in the report and need the previous weeks date range. My question is this....
How do I delay the change in the date range until Thursday?
I was hoping there would be an easy way to do this but I am at a loss....

Thanks!!
RookieDev

 

[PaulBricker]

About the only thing I can think that might work would be to change the EndDate to BeginDate + 7
Then you would see Wednesday to Wednesday. You would only input Wednesday thru Tuesday but as far as I know there isn't any way to have the the week switch on a day that isn't part of the calculation.

Paul

 

[rookiedev]

IS THERE A WAY TO SOMEHOW ADD AN IF STATEMENT? I'M NOT TO GOOD WITH STRUCTURING THEM BUT I WAS THINKING MAYBE IF DATE()=DATE,4 THEN.....YOU SEE WHAT I MEAN? I'M JUST NOT SURE IF IT IS POSSIBLE TO CONSTRUCT SOMETHING LIKE THAT IN AN EVENT PROCEDURE. WHAT DO YOU THINK?
ROOKIEDEV

 

[PaulBricker]

Well your idea isn't bad but you don't give me any indication what you want to happen on the other side of the THEN statement. What are we modifying? You could look at the results from this expression and let me know what you think.

Me.[Ending Date] = IIf(Weekday(Date())=4, Me![Beginning Date] + 7, Me![Beginning Date] + 6)

This says If the date is Wednesday then Ending Date = Wednesday otherwise Ending Date = Tuesday.

????

Paul

 

[rookiedev]

Paul-
After looking at your expression, I think it comes down to this:
The only field that should need to be altered is the [BeginningDate]

If today is a Wednesday (2/26/03)[BeginningDate]=2/19/03 Or (I'm not sure this is the correct way to return the date of the 19th)
Me![BeginningDate]= Date-Weekday(Date,4)-7 (If this is 19th)
Me![EndingDate]=[BeginningDate]+6 (This will be 25th)

If today is any other day Then
Me![BeginningDate]=Date-WeekDay(Date,4)+1
Me![EndingDate]=Me![BeginningDate]+6

Basically I need it to do what it is doing now if the day is not a Wednesday.The Beginning date needs to return the previous Wednedays date if it is a Wedneday.

Do you think we can get that to happen? I have to include the value of the [EndingDate] in this Event because the form has to populate for my date range form to process the report correctly.

Let me know what you think!

Much thanks,
RookieDev

 

[PaulBricker]

Me!BeginningDate = IIf(Weekday(Date()) = 4,Date()-7,Date() - WeekDay(Date,4)+1

Something like that?

Paul

 

[rookiedev]

Yes!
That's what I was thinking...Now the question is if this type of function will actually work on an Open event. Like I said I don't really know what I'm doing when writting an If statement except for the fact that you have to deal with the evaluation of true first and then if it evaluates to false.If I were to put this in a Modual how would I call it from the form? I just can't seem to wrap my mind around how that is actually done either.
At any rate if we approach it this way...Would the correct code be:

Me!BeginningDate = IIf(Weekday(Date()) = 4,Then Date()-7,Date() -WeekDay(Date,4)+1
Me![EndingDate]=Me![BeginningDate]+6

Right now my Date Range form displays Last weeks Range with these calculated controls
LBeginning Date=[Beginning Date]-7
LEnding Date=[LBeginning Date]+6

I do have this If statement on the Preview Cmd button

Private Sub Preview_Click()
If IsNull([Beginning Date]) Or IsNull([Ending Date]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "Beginning Date"
Else
If [Beginning Date] > [Ending Date] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "Beginning Date"
Else
Me.Visible = False
End If
End If
End Sub

This almost makes me think that we need to let the Beginning date be evaluated and then take action based on that value.I don't know though because the action would be based on the current date.
I guess it goes to the "More than one way to skin a Cat" mentality but I'm working with a very important existing database and need to do this the correct way....the first time.
I just may be over thinking this whole thing.....
RookieDev

 

[Norris68]

Code:

Private Sub Command1_Click()
Const TestUsingSpecificDate = True
Dim QueryDate As Date
Dim StartDate As Date
Dim EndDate As Date
Dim DaysSinceTuesday As Long

If TestUsingSpecificDate Then
    QueryDate = DateSerial(2003, 2, 19) 'Last Wednesday
Else
    QueryDate = Date 'Normal Use - current date
End If

DaysSinceTuesday = Weekday(QueryDate) - vbTuesday
If DaysSinceTuesday < 0 Then DaysSinceTuesday = DaysSinceTuesday + 7
EndDate = DateAdd(&quot;d&quot;, -1 * DaysSinceTuesday, QueryDate)
StartDate = DateAdd(&quot;d&quot;, -6, EndDate)

MsgBox StartDate & vbCr & EndDate

End Sub

This code will give you the start & ending dates for the previous *complete* Wed-Tues week.

 

[rookiedev]

That's impressive!! However if I use this where do I use it? I need the beginning date and ending date to calculate as soon as the form opens. I don't always need the previous weeks dates either. I only need them to hold their value until I can enter Tuesday's time in on Wednesday morning.
My name is true to form here Rookie!!!!

 

[PaulBricker]

How is the information set up in your Form. You have a BeginningDate and an EndingDate but what else are you doing. If you have seven textboxes that you enter the hours into for each day is one thing. I guess my question is how do you use BeginningDate and EndingDate on your form.
A form I use has 7 unbound textboxes and the default date value for each one is set using this type of expression
Date()-(Weekday(Date())+6) 'last Sunday
Date()-(Weekday(Date())+5) 'last Monday
Date()-(Weekday(Date())+4) 'last Tuesday
So we do our payroll/timesheets on Monday and I see all the dates from last week.
You don't need to change to my system, but it might spark something for you. A little info about how the form works would help.
Paul

 

[rookiedev]

Our payroll is set up for two companies. I run the time detail for the Prime contractor that only gives them the information on their employees and e-mail it to their office up north and then run the same report for the Subcontractor and e-mail it to them. They then cut the checks for the people on their payroll and courier them to our satellite office.

The jobs and pay rates vary from day to day depending on what a craftsperson does that day so it is not uncommon to rerun the report several times and make changes before it is final. The beginning and ending date being calculated on the form feeds the Date Range Form so that the dates don't have to be re-entered every time the report is run.Outside of simply displaying the current weeks dates the only thing the fields do is act as a source for the Date Range Form that the time sheet report is based on.

RookieDev

 

[raskew]

It would seem that you need a mechanism in both your input form and output report that prompts for:
1. A date within the target week
2. The first day of the week (in your example Wednesday (4)

Given this information, you can tailor your query to return records from the first day of the target week through the first day of the target week + 6-a one-week span..

The following function will return the starting date for you. It has the benefit of being generic, i.e., it adapts to any First Day of Week without hard-coding any particular day. To test, copy/paste it to a new module in Northwind.

Function GetStartDate(dteMyDate As Date, pDay As Integer) As Date
'*******************************************
'Name:      GetStartDate (Function)
'Purpose:   Return the first day of a week
'          	    based on a date input by user
'                 and a starting weekday (pDay)
'Parameters:    dteMyDate: the target date
'               pDay: Day of week of starting day [Sun(1) - Sat(7)]
'Inputs:   from the debug window:  GetStartDate(#22-Feb-03#, 4)
'Output:   2/19/03
'*******************************************

Dim StartDate As Date
If WeekDay(dteMyDate) = pDay Then
   StartDate = dteMyDate
Else
   StartDate = dteMyDate - (WeekDay(dteMyDate) + IIf(WeekDay(dteMyDate) <= pDay, 7, 0) - pDay)
End If
GetStartDate = StartDate
End Function

Now, copy/paste the following query to Northwind and run the query. When prompted for a date, enter dates between 4-Aug-94 and 5-Jun-96, the range of the Orders table. Specify your First Day of Week as 1 for Sunday through 6 for Saturday.

PARAMETERS [Enter any day in week] DateTime, [Enter first day of week] Short;
SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, Format([OrderDate],"ddd") AS Expr1
FROM Orders
WHERE (((Orders.OrderDate) Between getStartDate([Enter any day in week],[Enter first day of week]) And getStartDate([Enter any day in week],[Enter first day of week])+6));

If this looks like what you're after, it should be a simple matter to adapt the query to your specific application.

 

[rookiedev]

raskew-
I pasted the function into a module and named it GetStartDate Then I pasted the query in. When I try to run the query I get this message:Undefined function 'getStartDate'in expression. I tried to change the query so that it looked like GetStartDate but that didn't do it. What did I do wrong?
RookieDev

 

[raskew]

Try renaming the module to something other than the name of the function.

Before moving on to the query, test the function from the Debug window (Ctrl+G) by typing: ? GetStartDate(#22-Feb-03#, 4)<Enter>

If that works, then move on to the query, else examine the code you pasted into the module. Look for any strange characters which can sometimes get picked up when copying from a web-site.

Please post back as to your success.

Bob

 

[rookiedev]

raskew-
No sucess yet but I'm still working on it. I must be missing something in the code or like you said there is a stray character I haven't identified yet. I'll let you know what or if I find anything.

Thanks for your help!
Rookie Dev