IF statement on a report

[shanice]

I have a drop down field on a form that lists staff positions. For this position I also lists the persons name in another field. On a report I've listed the staff positions (i.e.):

a. accountant Name:
b. doctor Name:
c. lawyer Name:

I'm trying to write an if statement that pulls the persons name into the field for each position (i.e.): If position = "accountant" Then
Text206 = Name

For some reason this isn't working. Also what event should this go under since I'm not clicking or tabbing through anything.

 

[dkinley]

If I get this right, your report runs and you want ...

Accountants
Acct Name1
Acct Name2
Doctors
Dr Name1
Lawyers
Lwyr Name1
Lwyr Name2

If this is correct, why not just use the grouping levels on the report and not worry about an If to present these for you?

-dK

 

[shanice]

Somewhat, but here's the problem. The professions are listed in a drop down menu. The template is already set to list the professions as such:

a. accountant Name: (textbox206)
b. doctor Name: (textbox207)
c. lawyer Name: (textbox208)

I'm trying to find an easy way to say if profession = accountant then textbox206 = Name

elseif profession=doctor then textbox207 = Name...etc.

I don't think grouping will work for this...

 

[dkinley]

I see what you are saying but having difficulty in understanding because I don't understand the structure. In addition, I am looking for the most efficient implementation that fits your needs.

So when you run this report, is it for an individual 'thing'? That is, let's say it is for a business, then each business has one and only one accountant, lawyer or doctor and one and only one of these types for it?

The reason I am asking is I can see things getting very convoluted because I do not understand the linking. If the profession is a value list then that could be added to the query and the query is centered around the person's name.

I am a little confused because I have a DLookup in my head and I am not used to seeing things this way which could affect any advice anyone might be able to provide.

-dK

 

[shanice]

I see what you're saying. I'm generating reports for 30 states. For each state there are 13 professions that we inquired about. For most of these professions there won't be a name (meaning no one worked in that field), but we still want all of the professions to show on each report. So I'm trying to figure out how to populate each of these 13 fields based on a what was selected from a drop-down list. This would be easy if I had used checkboxes because there would be a value for each of the 13 professions.

I'm not familiar with DLookup...do you think it will work in this case?

 

[dkinley]

Okay. I see. DLookups are not the way to go, but I mentioned it to demonstrate how far off-based I was thinking.

Grouping on the profession may not work because you want the professions the be displayed even if the dataset does not contain any. I don't know of a way to force that but perhaps someone who does can jump in.

Right now, I am thinking that subreports would be the way to go - I know I can force the presentation of professions then. I've some time, I will do a quick mock up and let you decide.

-dK

 

[PNGBill]

Could a query select the names for each profession and where Null is the value, then "Vacant" or Just "." and then the report will have data for all professions and be happy

 

[dkinley]

I think that was one of the issues, that there are no nulls in the dataset - a record is there or not. I couldn't use groups with a LEFT JOIN because if there was data in one group (say for state A) that group wouldn't show for a different state (B) where there were no people in that group.

Shanice, I did come up with something, though using some smoke and mirrors (see attached). I used the LEFT JOIN trick to show all of the States (figured you would want those even if empty, too) but the smoke and mirrors comes in with the Professions. I went ahead and created columns to demonstrate that to save some trees in case that is the way you wanted to go (didn't know how much data you are going to display). The point is - here is a method to accomplish what you want using subreports. Open the rReport report to see the results. If it is good, you should be able to adapt and integrate into yours.

Hope that does what you need, if not - respond back, I will try again or someone with a greater mind than mine might have a solution.

-dK

 

[shanice]

You are the greatest!!! It worked!!! Thanks!

 

[dkinley]

You're welcome. Glad I could offer a solution.

Good luck on your projects!

-dK