problem with DSum function using >= operators

[Eric]

I have the following

totcomm = DSum("[SumOfTotal]", "detailslinerental", "[ItemStartDate] >=
!startdate And ![ItemEndDate] <= !enddate And [TelephoneNumber] = '" &
searchnumber & "'")

!stardate and !enddate are derived from a recordset and are 2 dates.

This should return an amount but it doesn't.
When running a select query with 2 dates it returns the correct records and
totals.

Does anyone see what I'm doing wrong here? TIA.

 

[Dirk Goldgar]

I have the following

totcomm = DSum("[SumOfTotal]", "detailslinerental", "[ItemStartDate]

= !startdate And ![ItemEndDate] <= !enddate And [TelephoneNumber] =

'" & searchnumber & "'")

!stardate and !enddate are derived from a recordset and are 2 dates.

This should return an amount but it doesn't.
When running a select query with 2 dates it returns the correct
records and totals.

Does anyone see what I'm doing wrong here? TIA.

You need to bring the references to the fields in the recordset outside
of the string literal. You also appear to have one bang (!) where it
doesn't belong. Is this code being executed inside a "With <recordset
object>" block? Assuming so, you probably want something like this:

totcomm = DSum("[SumOfTotal]", "detailslinerental", _
"[ItemStartDate] >= " & _
Format(!startdate, "\#mm/dd/yyyy\#") & _
" And [ItemEndDate] <= " & _
Format(!enddate, "\#mm/dd/yyyy\#") & _
" And [TelephoneNumber] = '" & searchnumber & "'")

I also took the liberty of forcing the date values into the "mm/dd/yyyy"
format that Access prefers.

 

[Eric]

Thanks Dirk, it works fine now!
I appreciate your help.

I have the following

totcomm = DSum("[SumOfTotal]", "detailslinerental", "[ItemStartDate]

= !startdate And ![ItemEndDate] <= !enddate And [TelephoneNumber] =

'" & searchnumber & "'")

!stardate and !enddate are derived from a recordset and are 2 dates.

This should return an amount but it doesn't.
When running a select query with 2 dates it returns the correct
records and totals.

Does anyone see what I'm doing wrong here? TIA.

You need to bring the references to the fields in the recordset outside
of the string literal. You also appear to have one bang (!) where it
doesn't belong. Is this code being executed inside a "With <recordset
object>" block? Assuming so, you probably want something like this:

totcomm = DSum("[SumOfTotal]", "detailslinerental", _
"[ItemStartDate] >= " & _
Format(!startdate, "\#mm/dd/yyyy\#") & _
" And [ItemEndDate] <= " & _
Format(!enddate, "\#mm/dd/yyyy\#") & _
" And [TelephoneNumber] = '" & searchnumber & "'")

I also took the liberty of forcing the date values into the "mm/dd/yyyy"
format that Access prefers.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)