DateDiff with variable criteria?
in [Queries]
|
From: Jim on 3 May 2010 16:18 I have a query field that uses DateDiff("d",[dtmDateShipped],[dtmDatePaid]) to calculate the difference between a ship date and paid date. I also have <=50 in the criteria row to restrict payments to 50 days or less. I need to change the criteria to <=80 for a couple customers. I've tried using IIf([tblCustomerUS.strCustomerId] In ("LE4212","AM0303"),<=80,<=50) in the criteria row as a sub query, but I get no results at all. Any suggestions on how to set this up correctly? Thanks Jim
From: vanderghast on 3 May 2010 16:26 Try: <= IIf([tblCustomerUS.strCustomerId] In ("LE4212","AM0303"),80,50) since the goal of iif is about returning a constant, not about returning a " part of " a test. Vanderghast, Access MVP "Jim" <jim(a)gordonferon.com> wrote in message news:ufUEG3v6KHA.2220(a)TK2MSFTNGP04.phx.gbl... >I have a query field that uses DateDiff("d",[dtmDateShipped],[dtmDatePaid]) >to calculate the difference between a ship date and paid date. I also have ><=50 in the criteria row to restrict payments to 50 days or less. > > > > I need to change the criteria to <=80 for a couple customers. I've tried > using IIf([tblCustomerUS.strCustomerId] In ("LE4212","AM0303"),<=80,<=50) > in the criteria row as a sub query, but I get no results at all. Any > suggestions on how to set this up correctly? > > Thanks > > > > Jim > >
From: Marshall Barton on 3 May 2010 20:28 Jim wrote: >I have a query field that uses DateDiff("d",[dtmDateShipped],[dtmDatePaid]) >to calculate the difference between a ship date and paid date. I also have ><=50 in the criteria row to restrict payments to 50 days or less. > >I need to change the criteria to <=80 for a couple customers. I've tried >using IIf([tblCustomerUS.strCustomerId] In ("LE4212","AM0303"),<=80,<=50) in >the criteria row as a sub query, but I get no results at all. Any >suggestions on how to set this up correctly? > You can not put a partial expression in IIf. In this case you can simply rearrange the criteria: <=IIf([tblCustomerUS.strCustomerId] In ("LE4212","AM0303"),80,50) Personally, I would add a field (named PayTerm) to the customer table to so the terms of payment can be included without specifying specific customers: <= tblCustomerUS.PayTerm -- Marsh MVP [MS Access]
From: Jim on 4 May 2010 13:57 This is a report for sales commissions. I do have a field that has payment terms in it. 99% of our customers have 30 day terms or less, a couple have terms of 60 days. I'm calculating the difference between the date product shipped (dtmDateShipped) and the date the invoice is paid (dtmPaidDate) plus 20 days. If the time difference is 50 days or less, it shows up on the commission statement; if it's over, it doesn't. But I needed a way to take into account the longer terms of a couple customers. How would I do that with the date field instead of the way I'm doing it now? Thanks Jim "Marshall Barton" <marshbarton(a)wowway.com> wrote in message news:h6qut513u22tro4tp7aegvndvibncfdv5i(a)4ax.com... > Jim wrote: >>I have a query field that uses >>DateDiff("d",[dtmDateShipped],[dtmDatePaid]) >>to calculate the difference between a ship date and paid date. I also have >><=50 in the criteria row to restrict payments to 50 days or less. >> >>I need to change the criteria to <=80 for a couple customers. I've tried >>using IIf([tblCustomerUS.strCustomerId] In ("LE4212","AM0303"),<=80,<=50) >>in >>the criteria row as a sub query, but I get no results at all. Any >>suggestions on how to set this up correctly? >> > > You can not put a partial expression in IIf. In this case > you can simply rearrange the criteria: > <=IIf([tblCustomerUS.strCustomerId] In > ("LE4212","AM0303"),80,50) > > Personally, I would add a field (named PayTerm) to the > customer table to so the terms of payment can be included > without specifying specific customers: > <= tblCustomerUS.PayTerm > > -- > Marsh > MVP [MS Access]
From: Marshall Barton on 4 May 2010 16:12 Just cahnge your DateDiff calculated field's criteria to: <= tblCustomerUS.PayTerm + 20 Then you would not need to check for those specific customers. It's almost always best to use data from a table instead of typing values in a query. The way you had it, think about the code/queries you would have to change if you should ever need to change/correct a customer's name or change the terms of other customers to 60 days. With the above criteria, you would not have to anything beyond changing a name or terms value in the customer's record. -- Marsh MVP [MS Access] Jim wrote: >This is a report for sales commissions. I do have a field that has payment >terms in it. 99% of our customers have 30 day terms or less, a couple have >terms of 60 days. I'm calculating the difference between the date product >shipped (dtmDateShipped) and the date the invoice is paid (dtmPaidDate) plus >20 days. If the time difference is 50 days or less, it shows up on the >commission statement; if it's over, it doesn't. But I needed a way to take >into account the longer terms of a couple customers. How would I do that >with the date field instead of the way I'm doing it now? > >"Marshall Barton" wrote in message >> Jim wrote: >>>I have a query field that uses >>>DateDiff("d",[dtmDateShipped],[dtmDatePaid]) >>>to calculate the difference between a ship date and paid date. I also have >>><=50 in the criteria row to restrict payments to 50 days or less. >>> >>>I need to change the criteria to <=80 for a couple customers. I've tried >>>using IIf([tblCustomerUS.strCustomerId] In ("LE4212","AM0303"),<=80,<=50) >>>in >>>the criteria row as a sub query, but I get no results at all. Any >>>suggestions on how to set this up correctly? >>> >> >> You can not put a partial expression in IIf. In this case >> you can simply rearrange the criteria: >> <=IIf([tblCustomerUS.strCustomerId] In >> ("LE4212","AM0303"),80,50) >> >> Personally, I would add a field (named PayTerm) to the >> customer table to so the terms of payment can be included >> without specifying specific customers: >> <= tblCustomerUS.PayTerm
|
Next
|
Last
Pages: 1 2 Prev: Append Query Not Working Next: Anyone who can help my fried mind with this solution? |