Count between Date issue

[Sam Summers]

Hi Guys,

I have a final issue to resolve which shouldn't be too hard but is beyond my current skill level.

I have a form who's control source is a query which displays dates that personnel and equipment are mobilized.

For invoicing on a weekly basis i need to display the number of days within the week that these are on hire.

The mobilization and demobilization dates may be well outwith the weeks dates or may be within the week though.

For example MobDate 20/03/2014, DeMobDate 13/06/2014 but the week is this week.
or Mobdate 27/05/2014, DeMobDate Today 30/05/2014

I need to display the actual dates if within the current week or display the first day of the current week and the last day of the current week if the mobilization and demobilization dates are outwith the current weeks dates?

I have created two unbound textboxes MobTextBox and DeMobTextBox and am trying to use the code below to insert what i described above.

Once i have the dates inserted i have a third textbox with the ControlSource set to =DateDiff("d",[MobTextBox],[DeMobTextBox])
______________________________________________________________

Dim MobDate As Date
Dim DeMobDate As Date

Me.MobDate.SetFocus
If Me.MobDate.Text > (Date - 7) Then
Me.MobTextBox.Text = Me.MobDate.Text

Else

If Me.MobDate.Text < (Date - 7) And Me.DeMobDate > Date Then
Me.MobTextBox.Text = (Date - 7) And Me.DeMobTextBox.Text = Date

End If
End If
_____________________________________________________________

I hope this is clearly explained enough?

Many thanks in advance

 

[Cotty42]

Hi Sam

Something like below will work, you will just need to replace the 'vbMonday' in the DatePart function with what ever the day of the first invoice period is.

It then shouldn't matter which day you run the query on.

' *** Check which day of the week today is and set first and last invoice dates for this period
TodayIs = DatePart("w", Date, vbMonday)
FirstDatetoInvoice = Date - (TodayIs - 1)

If TodayIs = 7 Then
LastDatetoInvoice = Date
Else
LastDatetoInvoice = Date + (7 - TodayIs)
End If

' *** Checks if dates fall within the current invoice period
If Me.Mobdate > LastDatetoInvoice Or Me.DeMobDate < FirstDatetoInvoice Then
Me.firstInvoicedate.Visible = False
Me.LastInvoiceDate.Visible = False
Me.DaystoInvoice.Visible = False
Me.NothingtoInvoice.Visible = True
Exit Sub
Else
Me.firstInvoicedate.Visible = True
Me.LastInvoiceDate.Visible = True
Me.DaystoInvoice.Visible = True
Me.NothingtoInvoice.Visible = False
End If

' *** Allocates first & last invoice dates for this period
If Me.Mobdate < FirstDatetoInvoice Then
Me.firstInvoicedate = FirstDatetoInvoice
Else
Me.firstInvoicedate = Me.Mobdate
End If
If Me.DeMobDate > LastDatetoInvoice Then
Me.LastInvoiceDate = LastDatetoInvoice
Else
Me.LastInvoiceDate = Me.DeMobDate
End If

' *** calculate number of days to invoice
Me.DaystoInvoice = DateDiff("d", Me.firstInvoicedate, Me.LastInvoiceDate) + 1

Hope this helps

Dave

 

[Sam Summers]

Thanks Dave,
I replaced vbMonday with Date() as today will always be 'today' whenever they want to view the data.

There is something strange going on? I can see the data in MobDate and DeMobDate and then the NOD textbox (number of days) but ever since i started down this route never once has anything been displayed in the other unbound textboxes i have created?

 

[Cotty42]

Check the names of your text boxes against calculated values etc. You may have a conflict.

 

[Sam Summers]

Thanks i will check again but i think they are okay? Just strange that nothing shows up?

 

[Cotty42]

I think the .Text expression is only valid when the textbox has the focus.

Try replacing
Me.MobTextBox.Text = Me.MobDate.Text
with
Me.MobTextBox = Me.MobDate