DateDiff Problem

[DS]

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

 

[Ruskin Hardie]

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...

 

[Ruskin Hardie]

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;

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.
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

 

[Jeff Boyce]

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?

 

[Marshall Barton]

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

 

[Jeff Boyce]

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

Jeff

 

[DS]

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.
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

Thanks I'll give this a try.
DS

 

[DS]

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

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

 

[Marshall Barton]

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

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")

 

[DS]

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")

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