My DSum is killing me! HELP!!!!

[J. Keggerlord]

I have a linked table (dbo_tbl_IVTRequests) with a starting time that is
formatted as long date/time (FTStart) and an end time (FTEnd). On my report
page, I have a starting range time (txtStartDate) and end range time
(txtEndDate). I am trying to use a Dsum function to calculate the hours
spent between the FTStart and FTEnd times that meet the criteria given by the
range. Coded as follows:

=DSum("[dbo_tbl_IVTRequests]![FTEnd] - [dbo_tbl_IVTRequests]![FTStart]
","dbo_tbl_IVTRequests"," [dbo_tbl_IVTRequests]![FTStart] > [txtStartDate]
AND [dbo_tbl_IVTRequests]![FTEnd] < [txtEndDate]")

Anyone have any ideas why I'm getting the helpful "#Error" message when I go
to view the report?

 

[Duane Hookom]

You might want to try:
=DSum("[FTEnd] - [FTStart]","dbo_tbl_IVTRequests",
"[FTStart] >#" & [txtStartDate] & "#AND [FTEnd] <#" & [txtEndDate] & "#")
This assume txtStartDate and txtEndDate are controls in the same section of
the report or fields from the report's record source.

BTW: The format of the FTEnd and FTStart has no effect in the DSum(). Format
is for display purpose only.

 

[J. Keggerlord]

Thank you, Duane! I wasn't aware of the format() limitation, either, so
that's definitely a good piece of information to have. Appreciate the help!!

You might want to try:
=DSum("[FTEnd] - [FTStart]","dbo_tbl_IVTRequests",
"[FTStart] >#" & [txtStartDate] & "#AND [FTEnd] <#" & [txtEndDate] & "#")
This assume txtStartDate and txtEndDate are controls in the same section of
the report or fields from the report's record source.

BTW: The format of the FTEnd and FTStart has no effect in the DSum(). Format
is for display purpose only.
--
Duane Hookom
Microsoft Access MVP

I have a linked table (dbo_tbl_IVTRequests) with a starting time that is
formatted as long date/time (FTStart) and an end time (FTEnd). On my report
page, I have a starting range time (txtStartDate) and end range time
(txtEndDate). I am trying to use a Dsum function to calculate the hours
spent between the FTStart and FTEnd times that meet the criteria given by the
range. Coded as follows:

=DSum("[dbo_tbl_IVTRequests]![FTEnd] - [dbo_tbl_IVTRequests]![FTStart]
","dbo_tbl_IVTRequests"," [dbo_tbl_IVTRequests]![FTStart] > [txtStartDate]
AND [dbo_tbl_IVTRequests]![FTEnd] < [txtEndDate]")

Anyone have any ideas why I'm getting the helpful "#Error" message when I go
to view the report?