Query with IIF Expression Need Help

  • Thread starter Thread starter Ange Kappas
  • Start date Start date
A

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
 
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
Click to expand...

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
 
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
Click to expand...

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
 
Hi ALL,
                I have a query which has the following fields:
ROOMS
RESNAME
ARRIVAL
DEPARTURE
Click to expand...
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"
Click to expand...
LINEN: IIf([ARRIVAL]="([RUNDATE]![DATE]+2)";"TODAY";"2 DAYS")
Click to expand...
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 !!
Click to expand...
Can anyone help please !!
Click to expand...
Thanks
Ange
Click to expand...

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 -
Click to expand...

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.
 
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
..
 
Back
Top