Query with IIF Expression Need Help

[Ange Kappas]

Hi ALL,
I have a query which has the following fields:
ROOMS
RESNAME
ARRIVAL
DEPARTURE


I have added another field in my query named ROOMS IN as an expression where
I want it to return TODAY by looking at another table which has only one
record which is the running date table name:RUNDATE and field DATE and then
looking at the arrival date and adding two days if applicable to return to
the expression field LINEN "TODAY"

LINEN: IIf([ARRIVAL]="([RUNDATE]![DATE]+2)";"TODAY";"2 DAYS")

it returns 2 days to the other records which is fine, but shows #error in
the applicable. I must have the logical expression all wrong !!

Can anyone help please !!

Thanks
Ange

 

[Ron2006]

Hi ALL,
                I have a query which has the following fields:
ROOMS
RESNAME
ARRIVAL
DEPARTURE

I have added another field in my query named ROOMS IN as an expression where
I want it to return TODAY by looking at another table which has only one
record which is the running date table name:RUNDATE and field DATE and then
looking at the arrival date and adding two days if applicable to return to
the expression field LINEN "TODAY"

LINEN: IIf([ARRIVAL]="([RUNDATE]![DATE]+2)";"TODAY";"2 DAYS")

it returns 2 days to the other records which is fine, but shows #error in
the applicable. I must have the logical expression all wrong !!

Can anyone help please !!

Thanks
Ange

Two things.
1) It is strongly, strongly, strongly suggested that you NOT use
"Date" as a field name. It can become difficult for Access to
distinguish the field from the functioin Date which returns today's
date.

2) In your phrase
LINEN: IIf([ARRIVAL]="([RUNDATE]![DATE]+2)";"TODAY";"2 DAYS")

Take the " out of the compare part. Try
LINEN: IIf([ARRIVAL]=([RUNDATE]![DATE]+2);"TODAY";"2 DAYS")

This assums that you have the "RunDate" table included in your query
selection.


Ron

 

[Ange Kappas]

Thanks Ron, you've pointed me in the right direction!!!

Ange

Hi ALL,
I have a query which has the following fields:
ROOMS
RESNAME
ARRIVAL
DEPARTURE

I have added another field in my query named ROOMS IN as an expression
where
I want it to return TODAY by looking at another table which has only one
record which is the running date table name:RUNDATE and field DATE and
then
looking at the arrival date and adding two days if applicable to return to
the expression field LINEN "TODAY"

LINEN: IIf([ARRIVAL]="([RUNDATE]![DATE]+2)";"TODAY";"2 DAYS")

it returns 2 days to the other records which is fine, but shows #error in
the applicable. I must have the logical expression all wrong !!

Can anyone help please !!

Thanks
Ange

Two things.
1) It is strongly, strongly, strongly suggested that you NOT use
"Date" as a field name. It can become difficult for Access to
distinguish the field from the functioin Date which returns today's
date.

2) In your phrase
LINEN: IIf([ARRIVAL]="([RUNDATE]![DATE]+2)";"TODAY";"2 DAYS")

Take the " out of the compare part. Try
LINEN: IIf([ARRIVAL]=([RUNDATE]![DATE]+2);"TODAY";"2 DAYS")

This assums that you have the "RunDate" table included in your query
selection.


Ron

 

[Ron2006]

Hi ALL,
                I have a query which has the following fields:
ROOMS
RESNAME
ARRIVAL
DEPARTURE

I have added another field in my query named ROOMS IN as an expression where
I want it to return TODAY by looking at another table which has only one
record which is the running date table name:RUNDATE and field DATE and then
looking at the arrival date and adding two days if applicable to return to
the expression field LINEN "TODAY"

LINEN: IIf([ARRIVAL]="([RUNDATE]![DATE]+2)";"TODAY";"2 DAYS")

it returns 2 days to the other records which is fine, but shows #error in
the applicable. I must have the logical expression all wrong !!

Can anyone help please !!

Thanks
Ange

Two things.
1) It is strongly, strongly, strongly suggested that you NOT use
"Date" as a field name. It can become difficult for Access to
distinguish the field from the functioin Date which returns today's
date.

2) In your phrase
       LINEN: IIf([ARRIVAL]="([RUNDATE]![DATE]+2)";"TODAY";"2 DAYS")

Take the " out of the compare part. Try
LINEN: IIf([ARRIVAL]=([RUNDATE]![DATE]+2);"TODAY";"2 DAYS")

This assums that you have the "RunDate" table included in your query
selection.

Ron- Hide quoted text -

- Show quoted text -

From another posting about the use of DATE as a field name:


BTW, DATE is a reserved word and could cause problems. Use square
brackets
around the name here and it should work; in other contexts Access
might
misunderstand and use the system date instead of the field if you
don't
rename it.

 

[John Spencer]

Assuming that you have included the table RunDate in your query
LINEN: IIf([ARRIVAL]=[RUNDATE].[DATE]+2 ;"TODAY";"2 DAYS")

IF you haven't included the table in your query, then you need something
like
LINEN: IIf([ARRIVAL]= DLookup("[Date]","RUNDATE")+2;"TODAY";"2 DAYS")

Of course, if RunDate.Date is always today's date then use the system date
and don't even include the RunDate table in your query

LINEN: IIf([ARRIVAL]= DateAdd("d",2,Date());"TODAY";"2 DAYS")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..