DSum and Dlookup issue
in [Forms]
|
Prev: How to Show Form ComboBox value into other Form Label caption
Next: Getting the auto generated number.
From: jtfalk on 20 May 2010 07:18 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"))
From: Douglas J. Steele on 20 May 2010 07:59 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" <jtfalk(a)discussions.microsoft.com> wrote in message news:B8B77B6B-565D-402F-BECB-DAC7998F9439(a)microsoft.com... > 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"))
From: jtfalk on 20 May 2010 14:28
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" <jtfalk(a)discussions.microsoft.com> wrote in message > news:B8B77B6B-565D-402F-BECB-DAC7998F9439(a)microsoft.com... > > 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")) > > > . > |