A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

DateDiff with variable criteria?



 
 
Thread Tools Display Modes
  #1  
Old May 3rd, 2010, 09:18 PM posted to microsoft.public.access.queries
Jim
external usenet poster
 
Posts: 39
Default DateDiff with variable criteria?

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


  #2  
Old May 3rd, 2010, 09:26 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default DateDiff with variable criteria?

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" wrote in message
...
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



  #3  
Old May 4th, 2010, 01:28 AM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default DateDiff with variable criteria?

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]
  #4  
Old May 4th, 2010, 06:57 PM posted to microsoft.public.access.queries
Jim
external usenet poster
 
Posts: 39
Default DateDiff with variable criteria?

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" 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

--
Marsh
MVP [MS Access]



  #5  
Old May 4th, 2010, 09:12 PM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default DateDiff with variable criteria?

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

  #6  
Old May 5th, 2010, 07:51 PM posted to microsoft.public.access.queries
Jim
external usenet poster
 
Posts: 39
Default DateDiff with variable criteria?

Thanks.


"Marshall Barton" wrote in message
...
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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:37 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.