DateDiff function keeps giving "0" for an answer

[sirnose1]

I have a from with one date and a subform with another date. When I try to calculate the difference in time, I keep getting zero. Here is my code:

Dim frmTemp As New Form_frmTemp

Dim elapsedTime1 As Integer
Dim firstTime As Date
Dim secondTime As Date
firstTime = frmTemp.txtCurrSxTime.Value
secondTime = Me.txtPrevDate.Value

elapsedTime1 = DateDiff("n", firstTime, secondTime)

 

[ettienne]

Change "n" to "s" and see if you get a value in seconds. If so then your time difference is less than a minute.

 

[sirnose1]

I tried using a two different dates earlier that were exactly 24 hours apart. I still get zero. Should Have gotten 1440. Is it because I'm using VBA instead of VB?

 

[xlbo]

how are you checking the result ??

if you don't use the right format, it may appear that 24 hours = 0 (in fact it = 1 day and 0 hours....)

 

[Loomah]

Isn't the syntax the wrong way round?
shouldn't it be datediff(startdate, enddate, interval)?

Or am I just going mad?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[rmikesmith]

Don't get mad Loomah, get even!

Correct syntax is DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])


Regards,
Mike

 

[Loomah]

Duurrrrgh!
I use one of the many versions of xl that doesn't document the datedif(f) function and I was just stupid enough to assume that datediff would have the same (style of) syntax as datedif!

Just as well I'm not in a job where mistakes could affect anyone.
Oh!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[sirnose1]

Actually, the times entered are to compute the time difference in minutes. I am testing it be entering times that are exactly 24 hours apart which should give me s result of 1440 minutes.

 

[DaveInIowa]

Put a breakpoint right after you set elapsedTime1, then check your variable values. Are you using Option Explicit? If not, could you have one of your variables misspelled?

 

[xlbo]

Are they true dates ???

firstTime = CDATE(frmTemp.txtCurrSxTime.Value)
secondTime = CDATE(Me.txtPrevDate.Value)

elapsedTime1 = DateDiff("n", firstTime, secondTime)

 

[rmikesmith]

Geoff, et. al.

Just as a point of information: I was able to use syntax like the OP and get it to work properly; e.g.

FirstTime = Userform1.txtT1.Value

with FirstTime (and SecondTime) declared as a Date and entering times in the format hh:mm:ss am


Regards,
Mike

 

[xlbo]

Cheers Mike - guess it comes down to the definition of the variables then.......sirnose1 ??? what are they dimmed as ??

 

[sirnose1]

they are dimmed as Date. Think that may be the problem...

 

[sirnose1]

They way the date is entered is ex. 1/1/06 hh:mm with hh:mm entered as military time.

 

[rmikesmith]

sirnose1,

Your input format works OK for me, as well.

On the other hand, your statement:

I am testing it be entering times that are exactly 24 hours apart which should give me s result of 1440 minutes.

brings to mind a question-Can you show the exact way you are representing this?


Mike

 

[sirnose1]

I am entering time1 as 1/2/06 12:00 on form1. The other time (time2) gets the latest value from a query which is 1/1/06 12:00. The difference in time should be 1440 minutes.

 

[rmikesmith]

Have you inspected the variables firstTime and secondTime after they are populated to see what values they hold (as DaveInIowa suggested)?

Just for the record, my test setup uses TextBoxes on a Userform to input both time/date entries.


Mike

 

[sirnose1]

The variables hold the values from the form, just as expected. The "as Date" on the right side of the dimmed variable says todays date. If it uses these two variables then that makes sense ("elapsedTime1 = 0").

 

[rmikesmith]

VBA's Intellisense (or whatever Microsoft calls the mouseover variable inspection) is being deceptive here; it's interpreting this as the Date function, which returns today's date. I get the same thing in my test setup but this doesn't affect the actual contents of the variables.

I running out of ideas. Which VBA host are you using? Access? Can you describe how your forms are set up and any code associated with creation or instantiation? My test setup is using a single Userform containing both TextBoxes, from Excel.

Mike

 

[sirnose1]

Time1 is in a textbox on form1. Time2 is in a textbox on a subform of this form. Works fine in VB6. VBA does not seem to like it at all. The only way to get the datediff was to make an expression out of a hidden textbox. If anyone has any answers later on the proper way (real code) in VBA please let me know. Thanks for all of your help.