Dsum Syntax Help

[Guest]

I am using Access 2000 trying to retrieve a total from a table using the
syntax below. I get the prompts to type in the beginning and ending dates
but the value returned is always 0. If I revise it to NOT use the range of
dates and just give me a total of ALL records it works. What's wrong with
this syntax?


=Format(Nz(DSum("dailyHours","attendance_clock_in","studentid=" &
[employeeid] And "date" Between [Enter the Beginning Date] And [Enter the
Ending Date ]),0),"Fixed")

Thanks

 

[Duane Hookom]

You should have posted in the original thread. However, if you want to slow
down your report with DSum(), try (hard carriage returns added for
understandability):
=Format(
Nz(
DSum("dailyHours","attendance_clock_in",
"studentid=" & [employeeid] &
" And [date] Between #" & [Enter the Beginning Date] &
"# And #" & [Enter the Ending Date ] & "#"),0),"Fixed")
I generally don't format in the expression. I would probably set the format
in the Format property and set the control source to:
=Val(
Nz(
DSum("dailyHours","attendance_clock_in",
"studentid=" & [employeeid] &
" And [date] Between #" & [Enter the Beginning Date] &
"# And #" & [Enter the Ending Date ] & "#"),0))

 

[Guest]

Sorry Duane. Thank you though, it worked perfectly!

You should have posted in the original thread. However, if you want to slow
down your report with DSum(), try (hard carriage returns added for
understandability):
=Format(
Nz(
DSum("dailyHours","attendance_clock_in",
"studentid=" & [employeeid] &
" And [date] Between #" & [Enter the Beginning Date] &
"# And #" & [Enter the Ending Date ] & "#"),0),"Fixed")
I generally don't format in the expression. I would probably set the format
in the Format property and set the control source to:
=Val(
Nz(
DSum("dailyHours","attendance_clock_in",
"studentid=" & [employeeid] &
" And [date] Between #" & [Enter the Beginning Date] &
"# And #" & [Enter the Ending Date ] & "#"),0))

--
Duane Hookom
MS Access MVP
--

I am using Access 2000 trying to retrieve a total from a table using the
syntax below. I get the prompts to type in the beginning and ending dates
but the value returned is always 0. If I revise it to NOT use the range
of
dates and just give me a total of ALL records it works. What's wrong with
this syntax?


=Format(Nz(DSum("dailyHours","attendance_clock_in","studentid=" &
[employeeid] And "date" Between [Enter the Beginning Date] And [Enter the
Ending Date ]),0),"Fixed")

Thanks

 

[Guest]

Duane,

You did so well on that one I wondered if I could get your help gain on a
variation of that same Dsum statement.
The last one totalled records between a range of dates. For this one I need
to total all records less than or equal to the ENDING date of the date range.

Here's what I have so far, but it brings back ZERO each time.

=Format(Nz(DSum("dailyHours","attendance_clock_in","studentid=" &
[employeeid] & " And [date]<= " & [Enter the Ending Date
(mm/dd/yyyy)]),0),"Fixed")

You should have posted in the original thread. However, if you want to slow
down your report with DSum(), try (hard carriage returns added for
understandability):
=Format(
Nz(
DSum("dailyHours","attendance_clock_in",
"studentid=" & [employeeid] &
" And [date] Between #" & [Enter the Beginning Date] &
"# And #" & [Enter the Ending Date ] & "#"),0),"Fixed")
I generally don't format in the expression. I would probably set the format
in the Format property and set the control source to:
=Val(
Nz(
DSum("dailyHours","attendance_clock_in",
"studentid=" & [employeeid] &
" And [date] Between #" & [Enter the Beginning Date] &
"# And #" & [Enter the Ending Date ] & "#"),0))

--
Duane Hookom
MS Access MVP
--

I am using Access 2000 trying to retrieve a total from a table using the
syntax below. I get the prompts to type in the beginning and ending dates
but the value returned is always 0. If I revise it to NOT use the range
of
dates and just give me a total of ALL records it works. What's wrong with
this syntax?


=Format(Nz(DSum("dailyHours","attendance_clock_in","studentid=" &
[employeeid] And "date" Between [Enter the Beginning Date] And [Enter the
Ending Date ]),0),"Fixed")

Thanks

 

[Duane Hookom]

You left out the date delimiters "#".

--
Duane Hookom
MS Access MVP

Duane,

You did so well on that one I wondered if I could get your help gain on a
variation of that same Dsum statement.
The last one totalled records between a range of dates. For this one I
need
to total all records less than or equal to the ENDING date of the date
range.

Here's what I have so far, but it brings back ZERO each time.

=Format(Nz(DSum("dailyHours","attendance_clock_in","studentid=" &
[employeeid] & " And [date]<= " & [Enter the Ending Date
(mm/dd/yyyy)]),0),"Fixed")

You should have posted in the original thread. However, if you want to
slow
down your report with DSum(), try (hard carriage returns added for
understandability):
=Format(
Nz(
DSum("dailyHours","attendance_clock_in",
"studentid=" & [employeeid] &
" And [date] Between #" & [Enter the Beginning Date] &
"# And #" & [Enter the Ending Date ] & "#"),0),"Fixed")
I generally don't format in the expression. I would probably set the
format
in the Format property and set the control source to:
=Val(
Nz(
DSum("dailyHours","attendance_clock_in",
"studentid=" & [employeeid] &
" And [date] Between #" & [Enter the Beginning Date] &
"# And #" & [Enter the Ending Date ] & "#"),0))

--
Duane Hookom
MS Access MVP
--

I am using Access 2000 trying to retrieve a total from a table using the
syntax below. I get the prompts to type in the beginning and ending
dates
but the value returned is always 0. If I revise it to NOT use the
range
of
dates and just give me a total of ALL records it works. What's wrong
with
this syntax?


=Format(Nz(DSum("dailyHours","attendance_clock_in","studentid=" &
[employeeid] And "date" Between [Enter the Beginning Date] And [Enter
the
Ending Date ]),0),"Fixed")

Thanks