Combine a date with text and calculating a date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

Does anyone know if this is possible?

At the moment on my form I have a label reading "Today's date is" and a
separate unbound text box fomatted as Date().

I would like to combine the whole thing into one unbound text box.

Secondly I guess this is possible but I am fairly new to VBA but learning
all the time thanks to forums like this....anyway this is what I would like
to do.

On my form I have a bound text box named txtHireDate the date of which can
be any day of the week from Monday to Friday. I would like to have a second
calculated text box which would show the Monday day for the week in the first
text box. Eg txtHireDate is any date from 09 May 2005 to 13 May inclusive
second box would show 09 May 2005.(Note hire date will never be Sat or Sun).

Any help would be most welcome.

Many thanks.

Chris
 
To combine your two text boxes:
"Today's Date Is " & Cstr(date())

To get the monday of the week of any date

Function WeekStartDate(BaseDate As Date) As Date
'Dave Hargis 9/04
'Returns the first date of the accounting week for the date entered)

WeekStartDate = DateAdd("d", vbMonday - DatePart("w", BaseDate), BaseDate)
End Function
 
Hi

Does anyone know if this is possible?

At the moment on my form I have a label reading "Today's date is" and a
separate unbound text box fomatted as Date().

I would like to combine the whole thing into one unbound text box.
Click to expand...

Something like:
="Today's date is " & Format(Date(), "dddd, d mmmm yyyy")
Secondly I guess this is possible but I am fairly new to VBA but learning
all the time thanks to forums like this....anyway this is what I would like
to do.

On my form I have a bound text box named txtHireDate the date of which can
be any day of the week from Monday to Friday. I would like to have a second
calculated text box which would show the Monday day for the week in the first
text box. Eg txtHireDate is any date from 09 May 2005 to 13 May inclusive
second box would show 09 May 2005.(Note hire date will never be Sat or Sun).
Click to expand...

Public Function MondayOfWeek(TheDate As Date) As Date
MondayOfWeek = CDate(TheDate - Weekday(TheDate, vbMonday) + 1)
End Function
 
John

Thank you for your reply I have now combined the label and text box and that
is just what I want.

I am struggling with the second part as vba and functions are very new to
me. I have pasted your code into an existing module as a public function
exactly as you worded it.

Can you walk me through with an idiots guide as to how I actually put it to
use?
On my form I have a bound text box txtHireDate which receives a keyed in
date value. I have now added a second unbound text box which I have called
txtWeekStart in which I want to show the calculated Monday date for the week
that refers to the txtHireDate.

I assume the code goes in the afterupdate event of txtHireDate but I am not
sure of the correct syntax.

Forgive my ignorance but as I say this is all new to me.

Thanks again

Chris
 
Chris,

The function needs to go in an ordinary (as opposed to form or class)
module whose name must be different from the function's name.

In txtHireDate's after update event procedure, put something like this:

Me.txtWeekStart.Value = MondayOfWeek(Me.txtHireDate.Value)

You may also want to put this in the Form's Current event procedure.

If you get "Invalid Use of Null" or similar errors with this, it means
that MondayOfWeek is getting a null instead of a date, because
txtHireDate is blank. You can avoid this and some other problems by
testing before you call MondayOfWeek:

With Me.txtHireDate
If IsDate(.Value) Then
Me.txtWeekStart.Value = MondayOfWeek(.Value)
Else
Me.txtWeekStart.Value = ""
End If
End WIth


TeIn the after update event,
 
Back
Top