DateDiff Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DS - 28 Nov 2005 03:12 GMT

I can't seem to get this to work.
Its on a command button.
StartTime and EndTime are fields in MenuDetails Table as is StartDay
Basically I need TxtTotal to give me the total time between StartTime
and EndTime where the StartDay =1

Me.TxtTotal = DSum(DateDiff("n", "StartTime", "MenuDetails", StartDay =
1, "EndTime", "MenuDetails", StartDay = 1) / 60)

Thanks
DS

Reply to this Message

Ruskin Hardie - 28 Nov 2005 03:28 GMT

Try something like;

Dim dteStart As Date
Dim dteFinish As Date

dteStart = DLookup("[StartTime],"MenuDetails","[StartDay]=1")
dteFinish = DLookup("[EndTime],"MenuDetails","[StartDay]=1")

Me.TxtTotal = DateDiff("n", dteStart, dteFinish)

NOTE: I haven't done any error checking, to make sure that dteStart and
dteFinish are valid dates (ie: that the record is found, in the MenuDetails
table and not null). Also, there are other considerations that may need to
be used, such as; is StartDay = 1 a unique way to identify a record (or are
there multiple records, which have a StartDay equal to 1), etc...

> I can't seem to get this to work.
> Its on a command button.
[quoted text clipped - 7 lines]
> Thanks
> DS

Reply to this Message

Ruskin Hardie - 28 Nov 2005 03:29 GMT

Oooppppsss... Sorry, missed the end quotes, around the [StartTime] and
[EndTime] dlookup functions... Should read;

dteStart = DLookup("[StartTime]","MenuDetails","[StartDay]=1")
dteFinish = DLookup("[EndTime]","MenuDetails","[StartDay]=1")

> Try something like;
>
[quoted text clipped - 23 lines]
> > Thanks
> > DS

Reply to this Message

DS - 28 Nov 2005 14:31 GMT

> Try something like;
>
[quoted text clipped - 23 lines]
>>Thanks
>>DS

Thanks I'll give this a try.
DS

Reply to this Message

Jeff Boyce - 28 Nov 2005 03:30 GMT

I'm not sure, but I don't believe the syntax of the DateDiff() function
allows for what you described. Have you checked Access HELP for the exact
syntax?

Signature

Regards

Jeff Boyce
<Office/Access MVP>

> I can't seem to get this to work.
> Its on a command button.
[quoted text clipped - 7 lines]
> Thanks
> DS

Reply to this Message

Marshall Barton - 28 Nov 2005 06:39 GMT

>StartTime and EndTime are fields in MenuDetails Table as is StartDay
>Basically I need TxtTotal to give me the total time between StartTime
>and EndTime where the StartDay =1
>
>Me.TxtTotal = DSum(DateDiff("n", "StartTime", "MenuDetails", StartDay =
>1, "EndTime", "MenuDetails", StartDay = 1) / 60)

To paraphrase Jeff, your syntax is a mess ;-)

This should be closer to what you want:

Me.TxtTotal =
DSum("DateDiff('n',StartTime,EndTime)","MenuDetails","StartDay=1")\60

Signature

Marsh
MVP [MS Access]

Reply to this Message

Jeff Boyce - 28 Nov 2005 13:26 GMT

Now Marsh, I don't know if the syntax was "messy", but it certainly was
"creative"...<g>

Jeff

> >StartTime and EndTime are fields in MenuDetails Table as is StartDay
> >Basically I need TxtTotal to give me the total time between StartTime
[quoted text clipped - 9 lines]
> Me.TxtTotal =
> DSum("DateDiff('n',StartTime,EndTime)","MenuDetails","StartDay=1")\60

Reply to this Message

DS - 28 Nov 2005 14:37 GMT

>>StartTime and EndTime are fields in MenuDetails Table as is StartDay
>>Basically I need TxtTotal to give me the total time between StartTime
[quoted text clipped - 9 lines]
> Me.TxtTotal =
> DSum("DateDiff('n',StartTime,EndTime)","MenuDetails","StartDay=1")\60

This works great! Only One thing I need to change and thats the time
format. Right now the n gives me the hours but I also need the minutes.
I'm getting 23 now, when I need 23:43
Thanks Marshall
DS

Reply to this Message

Marshall Barton - 28 Nov 2005 15:04 GMT

>>>StartTime and EndTime are fields in MenuDetails Table as is StartDay
>>>Basically I need TxtTotal to give me the total time between StartTime
[quoted text clipped - 11 lines]
>format. Right now the n gives me the hours but I also need the minutes.
> I'm getting 23 now, when I need 23:43

Ok, try this:

Dim lngMin As Long
lngMin=DSum("DateDiff('n',StartTime,EndTime)","MenuDetails","StartDay=1")
Me.TxtTotal = lngMin \ 60 & Format(lngMin Mod 60, "\:00")

Signature

Marsh
MVP [MS Access]

Reply to this Message

DS - 28 Nov 2005 15:32 GMT

>>>>StartTime and EndTime are fields in MenuDetails Table as is StartDay
>>>>Basically I need TxtTotal to give me the total time between StartTime
[quoted text clipped - 17 lines]
> lngMin=DSum("DateDiff('n',StartTime,EndTime)","MenuDetails","StartDay=1")
> Me.TxtTotal = lngMin \ 60 & Format(lngMin Mod 60, "\:00")

Thanks,
It works Perfectly!!!!!!!!!
DS

Reply to this Message