duedate

  • Thread starter Thread starter gmenon100
  • Start date Start date
G

gmenon100

I am a novice to Access and I have a small Access project file and need to
use the below, can some help me with formulating :

[subdate], [duedate] & [shipmode] being the fields in a query.

i want to formulate

[duedate] = if [shipmode] = "AIR", duedate + 30 days
if [shipmode] = "OCEAN" duedate + 60days
 
in the duedate field use an IIF statement

IIF([shipmode] = "Air","30","60")
This will only work if there are only two shipmodes. If there are more you
can use nested IIF statements otherwise I would write a function to handle it.


I am a novice to Access and I have a small Access project file and need to
use the below, can some help me with formulating :

[subdate], [duedate] & [shipmode] being the fields in a query.

i want to formulate

[duedate] = if [shipmode] = "AIR", duedate + 30 days
if [shipmode] = "OCEAN" duedate + 60days
Click to expand...
 
If this is an Access project and you're using a SQL Server back end, then
IIF won't work; use a CASE statement.

Instead of "shipmode", you would enter "CASE [shipmode] WHEN 'AIR' THEN
[duedate] + 30 WHEN 'OCEAN' THEN [duedate] + 60 END"



Rob
 
Thank you Robert, It worked with the case statement. I really appreciate your
help. This was my first Access project and my first posting. I may need some
help as I advance further. Thanks again.

Robert said:
If this is an Access project and you're using a SQL Server back end, then
IIF won't work; use a CASE statement.

Instead of "shipmode", you would enter "CASE [shipmode] WHEN 'AIR' THEN
[duedate] + 30 WHEN 'OCEAN' THEN [duedate] + 60 END"

Rob
I am a novice to Access and I have a small Access project file and need to
use the below, can some help me with formulating :
Click to expand...
[quoted text clipped - 5 lines]
[duedate] = if [shipmode] = "AIR", duedate + 30 days
if [shipmode] = "OCEAN" duedate + 60days
Click to expand...
Click to expand...
 
Thanks Alan,

It worked with the CASE statement given by Robert. Appreciate your response.
in the duedate field use an IIF statement

IIF([shipmode] = "Air","30","60")
This will only work if there are only two shipmodes. If there are more you
can use nested IIF statements otherwise I would write a function to handle it.
I am a novice to Access and I have a small Access project file and need to
use the below, can some help me with formulating :
Click to expand...
[quoted text clipped - 5 lines]
[duedate] = if [shipmode] = "AIR", duedate + 30 days
if [shipmode] = "OCEAN" duedate + 60days
Click to expand...
Click to expand...
 
Back
Top