iif statement issue

[access101]

I have an access database back end and I'm trying to use this iif statement iif(schedule.type like '%p%', staff.rate1, staff.rate2). But I keep getting the error that the like statement is not recognized.

 

[SkipVought]

Hi,

Please post your code.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[access101]

That's all I have it's new table adapter I created with two tables from access one table is called sched which has a column named Type the other is called staff which has a column called rate1 and rate2 the table is joined by employee column.

 

[SkipVought]

iif(schedule.type like

one table is called sched

Do ya see a problem here?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[access101]

Oh...Ok sorry the table is called sched not schedule I missed typed it in my post.

 

[SkipVought]

so now your iif() statement works, yes?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[access101]

No still get the same error

 

[SkipVought]

post your code please, as it currently runs.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[access101]

SELECT DISTINCT Staff.Employee, Staff.Rate1, Staff.Rate2, Sched.Type,iif([sched].[type] like 'P', staff.rate2, iif([sched].[type] not like 'P', staff.rate1)) as exp2

FROM (Sched RIGHT OUTER JOIN
Staff ON Sched.Employee = Staff.Employee)
GROUP BY Staff.Employee, Staff.Rate1, Staff.Rate2, Sched.Type

 

[SkipVought]

Why not sched.type?


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[access101]

not sure what you mean?

 

[SkipVought]

SELECT DISTINCT
  Staff.Employee
, Staff.Rate1
. Staff.Rate2
, Sched.Type
, iif(sched.type like '%P%', staff.rate2, staff.rate1) as exp2

FROM (Sched RIGHT OUTER JOIN
Staff ON Sched.Employee = Staff.Employee)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[access101]

i just tried the code in query builder and the do results show but it still says error in list function arguments: 'LIKE' not recognized. Unable to parse query text

 

[SkipVought]

Try * rather than %

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[access101]

nope still not working. im using query builder. i don't know if that matters?

 

[dhookom]

I would try:

SELECT DISTINCT
  Staff.Employee
, Staff.Rate1
, Staff.Rate2
, Sched.Type
, iif(sched.type like "*P*", staff.rate2, staff.rate1) as exp2

FROM (Sched RIGHT OUTER JOIN
Staff ON Sched.Employee = Staff.Employee)

Are you running this totally in an Access application or is there a different "front-end"?

If this doesn't work, please be more descriptive with what results you are getting.

Duane
Hook'D on Access
MS Access MVP

 

[access101]

I found the issue in access I had a query with two tables that were not joined. Is there way to fix that. The two tables don't have any columns that I can join

 

[SkipVought]

How would you logically join these two?

Would a cartisian join be appropriate?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]