IFF Statement

[Guest]

Here is the scenario. I have a list of numbers and due dates. I need to
create a query and an IIF statement. The IIF statement has to say:

iif([number list]=**-****1, "12/31/2006")

number list
12-23121
32-12542
42-12541

Than I have due dates:
all numbers ending on "1" due date is 12/31/2006
all numbers ending on "2" due date is 12/31/2007

My question is what wildcard can I use to remplace the "???" on my
expression? Or is there another way to complete my IIF statement?

 

[Rick B]

Just use the "Right" function to grab the right digit of a field.

It seems rather odd to use a digit in a number to represent a due date,
though.

 

[Tom Ellison]

Dear Gloria:

Your "number list" contains strings. You want to look at the last
character. Use Right([number list], 1) to get this. Convert that to an
integer like this:

CInt(Right([number list], 1))

Use DateAdd to add this many years to 12/31/2005.

DateAdd("yyyy", CInt(Right("12-99997", 1)), CDate("12/31/2005"))

This yields12/31/2012

Sound good?

Tom Ellison

 

[Guest]

You could use the RIGHT$ function e.g.
IIF(RIGHT$(CStr(MyNumber),1)="1",...,...)

-Dorian

 

[Guest]

Thanks for your help

Just use the "Right" function to grab the right digit of a field.

It seems rather odd to use a digit in a number to represent a due date,
though.

--
Rick B

Here is the scenario. I have a list of numbers and due dates. I need to
create a query and an IIF statement. The IIF statement has to say:

iif([number list]=**-****1, "12/31/2006")

number list
12-23121
32-12542
42-12541

Than I have due dates:
all numbers ending on "1" due date is 12/31/2006
all numbers ending on "2" due date is 12/31/2007

My question is what wildcard can I use to remplace the "???" on my
expression? Or is there another way to complete my IIF statement?

 

[Guest]

Thanks for your help

Dear Gloria:

Your "number list" contains strings. You want to look at the last
character. Use Right([number list], 1) to get this. Convert that to an
integer like this:

CInt(Right([number list], 1))

Use DateAdd to add this many years to 12/31/2005.

DateAdd("yyyy", CInt(Right("12-99997", 1)), CDate("12/31/2005"))

This yields12/31/2012

Sound good?

Tom Ellison

Here is the scenario. I have a list of numbers and due dates. I need to
create a query and an IIF statement. The IIF statement has to say:

iif([number list]=**-****1, "12/31/2006")

number list
12-23121
32-12542
42-12541

Than I have due dates:
all numbers ending on "1" due date is 12/31/2006
all numbers ending on "2" due date is 12/31/2007

My question is what wildcard can I use to remplace the "???" on my
expression? Or is there another way to complete my IIF statement?

 

[Guest]

Thanks for your help

You could use the RIGHT$ function e.g.
IIF(RIGHT$(CStr(MyNumber),1)="1",...,...)

-Dorian

Here is the scenario. I have a list of numbers and due dates. I need to
create a query and an IIF statement. The IIF statement has to say:

iif([number list]=**-****1, "12/31/2006")

number list
12-23121
32-12542
42-12541

Than I have due dates:
all numbers ending on "1" due date is 12/31/2006
all numbers ending on "2" due date is 12/31/2007

My question is what wildcard can I use to remplace the "???" on my
expression? Or is there another way to complete my IIF statement?

 

[Guest]

thanks for your help

You could use the RIGHT$ function e.g.
IIF(RIGHT$(CStr(MyNumber),1)="1",...,...)

-Dorian

Here is the scenario. I have a list of numbers and due dates. I need to
create a query and an IIF statement. The IIF statement has to say:

iif([number list]=**-****1, "12/31/2006")

number list
12-23121
32-12542
42-12541

Than I have due dates:
all numbers ending on "1" due date is 12/31/2006
all numbers ending on "2" due date is 12/31/2007

My question is what wildcard can I use to remplace the "???" on my
expression? Or is there another way to complete my IIF statement?