Calculating a number to be put into a field

[t3nchi]

I am trying to calculate something to me put into a field called 'finalRptCycleTime_BeforeUpdate'. I want to take the 'auditEndDate' box to be subtracted from the current date and the answer to be put into the 'finalRptCycleTime' field.

Here's what I have in the finalRptCycleTime before update:

Private Sub finalRptCycleTime_BeforeUpdate(Cancel As Integer)

Me![finalRptCycleTime].Value = Date - [auditEndDate]

End Sub​

 

[reclusivemonkey]

Try using

DateAdd("d",(DateDiff("d",Date(),[auditEndDate])),Date())

You might need to juggle those around if it doesn't give you the right date; I've been to the pub at lunchtime ;-) Basically, DateDiff gets the number of days between now and auditEndDate and DateAdd subtracts them.

 

[t3nchi]

Try using

DateAdd("d",(DateDiff("d",Date(),[auditEndDate])),Date())

You might need to juggle those around if it doesn't give you the right date; I've been to the pub at lunchtime ;-) Basically, DateDiff gets the number of days between now and auditEndDate and DateAdd subtracts them.

and where does this code go? BTw it shows up in red in the VBA builder.

 

[reclusivemonkey]

It replaces Date - [auditEndDate] so,

Me![finalRptCycleTime].Value = DateAdd("d", (DateDiff("d", Date, [auditEndDate])), Date)

My VBA builder doesn't have a problem with it.

 

[t3nchi]

btw what is 'd' in the code?

 

[reclusivemonkey]

Place your cursor over the function with 'd' in it. Press F1. You'll learn allsorts ;-)