VBA-seconds difference: stuck with 00:00:00!

[mfaqueiroz]

Hi
I'm calculating the difference of 4 seconds before my date register,
but i'm having some troubles when my date is "dd-mm-yyyy 00:00:00" or "dd-mm-yyyy 00:00:04"
It brokes!
Do you have any suggestion?
I thought that can be an good ideia use sql, but i'm stuck :banghead:


there ir my code:

set Table1= dbs.OpenRecordset (" Select Tab1.[ID], Tab1[date] from tab1 ;", DB_OPEN_DYNASET)
Date=Table1.Fields(2).Value
DateDiference=DateDiff("s",-4,Date)



Thank yoou all for your knowledge sharing and help

 

[Ranman256]

Datediff("s",date1,date2)

 

[mfaqueiroz]

Thanks Ranman, but i want always - 4 seconds than my Date..(and this date is always changing.. so i think that i really have to write the "-4")

 

[pbaldy]

That would be more appropriate to the DateAdd() function then. By the way, I wouldn't use Date as a field name OR variable name. Access will at some point confuse it with the Date() function.

 

[mfaqueiroz]

Thanks, i have changed for DateAdd. However it stills stuck when the time is "00:00:00" or "00:00:04" , and in this case if i debug.print my date appears only "01-01-2015" istead of "01-01-205 00:00:00"

 

[gemma-the-husky]

it's because the time element is zero

you would need to force it to display the zero time element. eg, UK format.

format(sometime,"dd-mm-yyyy hh:nn:ss")

 

[mfaqueiroz]

Thank you for the suggestion, however I have forced the formmat but stills appears only the date :/ any ideia how can i solve that?

 

[mfaqueiroz]

I've done in this way: [In this specific case mydate= 01-01-2015 00:00:00)
Date = DateValue(mydate) & " " & TimeValue(mydate)
Dif=DateAdd("s",-4,Date) ---> it's giving 29-12-1899 23:59:56

Do you have any idea how can i correct this?

 

[arnelgp]

use format:
dateadd("s",-4,cdate(format(mydate,"mm/dd/yyyy hh:mm:ss")))

dont use Date as variable, it is reserved word and at the same time a function.

 

[mfaqueiroz]

Perfect!!! Thank yoou Arnelgp!! !!!
i wish you a good year !!