Merging Cells 1

[EllieFant]

Hi,

I have two fields (Department and Section) and when I print them in a report I want to merge them together. I know how to do this part. What I want to do is this. Some departments have sections withing them (like Techinical Support Services has DSA as a secection) and some of them don't (Safety as a department don't have sections). I would like to be able to have the Department and Sections have a seperator such as a dash (-) which is easy enough. What do I do if the department don't have a section - I won't need the dash.

Thanks in advance! Ellie
**Using Access 97**

lena.wood@wgint.com (work)
elliefant@starband.net (home)

 

[Lightning]

Hi Ellie

Sorry, I missed this code that you posted earlier:

=[Department] & IIf(IsNull([Section])," - " & [Section])


This code is saying "If Section IS Null, Show Department and - and Null." What is does not say is what it should display if Section IS NOT Null. In other words, it only contains an action for the expression when it evaluates to "True". There is no action for when the expression evaluates to "False" (ie when there is something in the Section field).

Try it as :

=[Department] & IIf(IsNull([Section]),""," - " & [Section])

HTH
Lightning

 

[jebry]

Hi Ellie!

Have you tried combining the two fields at the query level using a global function? That should be more flexible.

hth
Jeff Bridgham

 

[EllieFant]

Lightning,

This didn't work either. It is just showing the department and not the section for those that have a section.

Jeff,
Can you please explain what you mean by this query?

Thanks Ellie
**Using Access 97**

lena.wood@wgint.com (work)
elliefant@starband.net (home)

 

[jebry]

Sure!

Establish a global function:

Public Function DeptSection(Dept as Variant, Sect as Variant) as String

If IsNull(Sect) = True Then
DeptSection = Dept
Else
DeptSection = Dept & "-" & Sect
End If

End Function

Now in your query you make a field:

DeptSect: DeptSection([Department],[Section])

This should return the values you are looking for. If you think that the section field might have an empty string sometimes as well as null, then change the If statement in the function to:

If Nz(Len(Sect),0) = 0 Then etc.

hth
Jeff Bridgham

 

[EllieFant]

OK, don't mean to sound stupid, but where does this global function go? I have only been using access for a year, and I haven't gotten into the advance stuff yet.

Thanks so much for your time....I am amazed everytime I ask a question here and people are jumping up to help.

Ellie
**Using Access 97**

lena.wood@wgint.com (work)
elliefant@starband.net (home)

 

[AccessSQLUser]

This should work:
=[Department] & IIf(nz([Section],&quot;&quot<>&quot;&quot;,&quot; - &quot; & [Section],&quot;&quot

 

[jebry]

Hi!

Go to your database window and click on Modules. Click on new and you can put the global function there.

hth
Jeff Bridgham

 

[EllieFant]

=[Department] & IIf(nz([Section],&quot;&quot<>&quot;&quot;,&quot; - &quot; & [Section],&quot;&quot gives me an #error

Going to try the global thing now.

This was suppose to be a simple database lol. So much for simple!
Ellie
**Using Access 97**

lena.wood@wgint.com (work)
elliefant@starband.net (home)

 

[EllieFant]

GRRRR I can't make it work...not smart enough to do the global thing...and the other don't work either.

I guess I am going to just make the section names have a &quot;-&quot; before them and merge the cells. Then if there is no section, it just won't have anything to merge.

This is not a database that will be maintained once the accountability cards are made for those employees currently working with us.

Thanks for all your help...I can't believe it is this hard...maybe I am just not getting it.

Ellie
**Using Access 97**

lena.wood@wgint.com (work)
elliefant@starband.net (home)

 

[JoeMiller]

It's not this hard, can you forward me a copy of the db so I can see where we might be going wrong? THis is a VERY simple problem, I suspect something else is going on that's getting &quot;lost in the sauce.&quot;

Joe Miller
joe.miller@flotech.net

 

[EllieFant]

Joe,

On its way!

Thank you!
Ellie
**Using Access 97**

lena.wood@wgint.com (work)
elliefant@starband.net (home)

 

[CosmoKramer]

Try this:

=IIf(IsNull([Section]),[Department] & &quot;-&quot; & ([Section]),[Department])

It will only work if you define this in the control source for a &quot;third&quot; text box. Don't use it for either Section or Department....

HTH

 

[EllieFant]

I want to thank EVERYONE that helped me with this problem.

I sent the database to Joe and got it back in working order withing no time.

I was basing the Label Report off of the table and so it wouldn't do what I wanted it too.

Thanks so much Joe!
Ellie
**Using Access 97**

lena.wood@wgint.com (work)
elliefant@starband.net (home)