IIF Query

[spartansFC]

Hi

i'm having problems with a query, as it seems to be returning the autonumber value on a query.

Basically i have 2 tables.

tbl Referrer, which has fieldnames:
referrer_id (autonumber)
referrer_maincontactname (text)
referrer_mainbusiness (number)
referrer_secondarycontactname (text)
referrer_secondarybusiness (number)
referrer_isthismaincontact (yes/no)

and also

tbl business name which has fieldnames:

businessname_id (autonumber)
businessname (text)

the referrer form has a check box on, and i basically want to run the query to report via a virtual name, if the referrer is the main or secondary business.

the referrer_main and referrer_secondary are both combo boxes, and all link to tbl business name, i set them up that way as i wanted to limit the number of options the user has, although i have set up a NotInList option, so users can add new business names.

so i've set up the query:

referrer_maincontactname,
referrer_mainbusiness,
referrer_secondarycontactname
referrer_secondarybusiness
referrer_isthismaincontact (yes/no)

i then set up a virtual name and a IIF statement:

leadname: IIf([referrer_isthismaincontact]=Yes,[referrer_maincontactname],[referrer_secondarycontactname])

this query works great, and returns the correct contactname, but then when i do the same thing for the business name query:

leadrole: IIf([referrer_isthismaincontact]=Yes,[referrer_mainbusiness],[referrer_secondarybusiness])

the query returns a 1 or a 2, instead of the business name, i'm sure its because the business name is on a different table, i've checked the bound and column settings on the tbl Business name, i've even changed them but nothing, the query does work to a point, in that it does show either a 1 or a 2, but i'm not sure how to make it sure the business name, and not the autonumber.

i haven't linked these 2 tables in a relationship, i just use a Table/Query lookup function, so i'm not sure if it's that, but it can't be as the query nearly works.

any ideas

thanks

Mikie

 

[Ian Mac]

Does it noe need to be,[referrer_mainbusinessname],[referrer_secondarybusinessname]) or something similar, if it's returning a result, but not the right one, you are asking for the wrong field.

 

[spartansFC]

Does it noe need to be,[referrer_mainbusinessname],[referrer_secondarybusinessname]) or something similar, if it's returning a result, but not the right one, you are asking for the wrong field.

Hi Ian

a fellow geordie lol, anyways the field names you mentioned don't exist

[referrer_mainbusinessname],[referrer_secondarybusinessname]

so how can it be them?

 

[Ian Mac]

Why aye!!!

I'm fairly certain I put, 'or something similar'.

Anyway, I re-read your request and realised I mustn't have bothered properly the first time whoops

I'm not fully sure why you would have both businessname's in the query? Or both contactname's either?

Anyway,

You could use:

LeadRole: (SELECT businessname FROM tbl_Business_Name WHERE businessname_id = IIf([referrer_isthismaincontact]=Yes,[referrer_mainbusiness],[referrer_secondarybusiness]))

Or you could bring the businessname into your query and use what you have but change it slightly to
IIf([referrer_isthismaincontact]=Yes,[referrer_mainbusinessname],[referrer_secondarybusinessname]))
Hey that looks familiar

 

[spartansFC]

Why aye!!!

I'm fairly certain I put, 'or something similar'.

Anyway, I re-read your request and realised I mustn't have bothered properly the first time whoops

I'm not fully sure why you would have both businessname's in the query? Or both contactname's either?

Anyway,

You could use:

LeadRole: (SELECT businessname FROM tbl_Business_Name WHERE businessname_id = IIf([referrer_isthismaincontact]=Yes,[referrer_mainbusiness],[referrer_secondarybusiness]))

Or you could bring the businessname into your query and use what you have but change it slightly to
IIf([referrer_isthismaincontact]=Yes,[referrer_mainbusinessname],[referrer_secondarybusinessname]))
Hey that looks familiar

Hi Ian

your a genius, thanks for helping me out, i'm fairly new to access, and i've never used that SELECT command before, but i understand it a bit better now.

As for why i have both business names in the query, i thought you had to have all the fields listed in the query for the query to run, although i've just deleted both mainbusiness and secondarybusiness from the query and it still runs, with the virtualnames, so im even more confused.

but i needed the query to run a report that would list either the main or secondary business based on the yes/no box, but now im not even sure if i needed to have a query?

anyways thanks again

Mikie