problem with DSum function using >= operators

E

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

Dirk Goldgar

Eric said:
I have the following

totcomm = DSum("[SumOfTotal]", "detailslinerental", "[ItemStartDate]
= !startdate And ![ItemEndDate] <= !enddate And [TelephoneNumber] =
Click to expand...
'" & 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.
Click to expand...

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

Eric

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

Dirk Goldgar said:
Eric said:
I have the following

totcomm = DSum("[SumOfTotal]", "detailslinerental", "[ItemStartDate]
= !startdate And ![ItemEndDate] <= !enddate And [TelephoneNumber] =
Click to expand...
'" & 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.
Click to expand...

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)
Click to expand...
 
Top