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\#"))

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

"jtfalk" <(E-Mail Removed)> wrote in message
news:B8B77B6B-565D-402F-BECB-(E-Mail Removed)...
> 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"))

Worked great - thanks a lot.

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

"Douglas J. Steele" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:B8B77B6B-565D-402F-BECB-(E-Mail Removed)...
> > 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"))
>
>
> .
>