DSum and Dlookup issue

  • Thread starter Thread starter jtfalk
  • Start date Start date
J

jtfalk

Hello,

I have a form that is looks up data based on dates. I want the form to be
kept open and for users to be able to keep changing the dates to get the data
they are looking for.

I have a StartDate and FinishDate box as well as the query behind the form
has dates. I have this so far but am getting numbers way to high for the 2
days I have been looking at 5/13/2010 (129) and 5/14/2010 (193) I was hoping
to get 322 but it is not even close. Thanks

=DSum("nz([Build],0)","Daily Query"," Format([date], ""ddmmyyyy"") >= " &
Format([StartDate],"ddmmyyyy") And " Format([date], ""ddmmyyyy"") <= " &
Format([FinishDate],"ddmmyyyy"))
 
Your quotes are wrong in the Where Condition part of the statement: the AND
needs to be inside the quotes.

Other things to consider, though, is that converting the dates to strings
formatted as ddmmyyyy will be a problem if you cross over a month boundary
(31052010 is not less than 01062010!!) As well, you're using a reserved
word, Date, as a field name. That's a no-no!

Try:

=DSum("nz([Build],0)","Daily Query","[MyDateField] >= " &
Format([StartDate],"\#yyyy\-mm\-dd\#") & " And [MyDateField] <= " &
Format([FinishDate],"\#yyyy\-mm\-dd\#"))
 
Worked great - thanks a lot.

I totally forgot about using Date as a name - it got me before as well.

Douglas J. Steele said:
Your quotes are wrong in the Where Condition part of the statement: the AND
needs to be inside the quotes.

Other things to consider, though, is that converting the dates to strings
formatted as ddmmyyyy will be a problem if you cross over a month boundary
(31052010 is not less than 01062010!!) As well, you're using a reserved
word, Date, as a field name. That's a no-no!

Try:

=DSum("nz([Build],0)","Daily Query","[MyDateField] >= " &
Format([StartDate],"\#yyyy\-mm\-dd\#") & " And [MyDateField] <= " &
Format([FinishDate],"\#yyyy\-mm\-dd\#"))

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

jtfalk said:
Hello,

I have a form that is looks up data based on dates. I want the form to be
kept open and for users to be able to keep changing the dates to get the
data
they are looking for.

I have a StartDate and FinishDate box as well as the query behind the form
has dates. I have this so far but am getting numbers way to high for the 2
days I have been looking at 5/13/2010 (129) and 5/14/2010 (193) I was
hoping
to get 322 but it is not even close. Thanks

=DSum("nz([Build],0)","Daily Query"," Format([date], ""ddmmyyyy"") >= " &
Format([StartDate],"ddmmyyyy") And " Format([date], ""ddmmyyyy"") <= " &
Format([FinishDate],"ddmmyyyy"))
Click to expand...


.
Click to expand...
 
Back
Top