Prev: Append Query Not Working
Next: Anyone who can help my fried mind with this solution?
From: Jim on 5 May 2010 14:51
Thanks.


"Marshall Barton" <marshbarton(a)wowway.com> wrote in message
news:mvu0u5l5euvdpl5m19hfve09mn75ob7ps1(a)4ax.com...
> 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


First  |  Prev  | 
Pages: 1 2
Prev: Append Query Not Working
Next: Anyone who can help my fried mind with this solution?