Report

[pcastner1]

In my report I'm trying to use the job title field to pull the first and last name. Can anyone steer me in the right direction?

 

[theDBguy]

Hi. Can you give us more information to go on? How are the information related? Are they stored in the same or separate tables?

 

[pcastner1]

The names fields and job title fields are all in the same table.

 

[theDBguy]

So then, I am not sure I understand the problem. If the job title is available, the names should be too. Have you tried binding the textbox to the appropriate field?

 

[pcastner1]

If I have a job title of let say "Director" I wanted maybe a Dlookup to find who is the director and put their name in and so on. Im basically building an organizational chart.

 

[theDBguy]

Hi. But you said both title and names are in the same table. Are you now saying the report is not based on the same table as the one with the title and names? If so, why not? Please remember, we can’t see your database, so unless you give us enough details, we can’t give you any specific advice. If you want to use DLookup(), what have you tried and what happened? Maybe we can help you fix it.

 

[pcastner1]

I only have one table "Contacts", the report is based off that table. On the report I have an unbound "Job Title" field, which I put the names in myself. I also have an unbound filed for the "First and Last Name". What I'm trying to do is have the report look at the job title and fill in the "First and Last Name". Hope this makes sense.

 

[theDBguy]

Hi. I’m sorry but it still doesn’t make sense. You can’t really have an unbound textbox on a report and expect the user to enter a job title on it. If, on the other hand, you are entering the job title in the report’s design view, then why can’t you just enter the names at the same time? Again, without seeing what you got, it’s hard to imagine what you really need. Please consider posting some screenshots or a demo of your database.

 

[pcastner1]

Here is the database. In the report I tried this in the control source of the "Name" field,

= DLookup("[LastName]", "Contacts", _"[JobTitle] = Director"),

 

[JHB]

Here is the database. In the report I tried this in the control source of the "Name" field,

= DLookup("[LastName]", "Contacts", _"[JobTitle] = Director"),

What report, what control, (seems not to exist a control with that name)?
When you post a database, please post some sample data + an exactly description how to reproduce the problem.

 

[pcastner1]

I reposted the Database with the report (Org Chart). I think once you see it you will understand what I'm trying to do. I really appreciate the help!

 

[JHB]

I hope you like the solution!

I reposted the Database with the report (Org Chart). I think once you see it you will understand what I'm trying to do. I really appreciate the help!

Please tell, where you did repost the database?

 

[pcastner1]

Here is the database with the report.

 

[Cronk]

If it is report [Org Chart] that you are trying to generate, then
the control soure for text box Text1 would be

=DLookUp("[First Name] & ' ' & [Last Name]","Contacts","[Job Title]='" & [Label4].[Caption] & "'")

Have you thought what you are going to do to differentiate between Asst Directors 1 and 2 and .....

You will need to modify your table design to be able then to show who is reporting to a particular Asst Director.

Before worrying about reports, I'd plan your table design to achieve the output as well as input.

 

[pcastner1]

Thanks for the advice I certainly will look at that.