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)

 

[Databaseguy]

In the control source for a field in your report enter:

=[Department] & "+" & [Section] Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[Databaseguy]

In the control source for a field in your report enter:

=[Department] & "-" & [Section] Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[jebry]

Hi!

Try this in your control source: IIf(IsNull([Section]),[Department],[Department] & "-' & [Section]). I think that will work though I haven't tried it. You could also write a global function which will return either the department or the department and section depending on the value of section.

hth
Jeff Bridgham

 

[EllieFant]

Thank you, I will give these a try Ellie
**Using Access 97**

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

 

[EllieFant]

I am getting an expression error with this code.

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

says I have one to many ) Ellie
**Using Access 97**

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

 

[JoeMiller]

Try this instead:

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

HTH Joe Miller
joe.miller@flotech.net

 

[EllieFant]

Now it says #Error. How come things can never be as easy as I think they should be. Ellie
**Using Access 97**

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

 

[jebry]

Thanks Joe!

I forgot that you needed the full expression in the IIf statement!

Jeff Bridgham

 

[tlbroadbent]

Change the single quote to a double quote. & "-" & Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[JoeMiller]

Jebry:

No, you don't need the full expression. The problem was the single quote was unpaired with the double quote. I am simply adverse to not explicitly stating the value you are testing for when using the boolean test functions, it's good coding practice.

Joe Miller
joe.miller@flotech.net

 

[jebry]

Do you have the = before the IIf statement? The #Error means that Access doesn't recognize what you have entered for some reason. When you enter a calculated field it usually occurs because there is no = sign. Another possible reason is the misspelling of a field name.

hth
Jeff Bridgham

 

[JoeMiller]

Ellie:

Make sure that the name of the control is not Department or Section, if it's either one of those you'll get the error.

Joe Miller
joe.miller@flotech.net

 

[tempclerk]

Make sure your text box doesn't have the same name as one of your fields. I have a habit of using a wizard, which gives the controls the same name as the control source, and then changing the control source to something like you're talking about. That always gives an #Error. And if you then change the name of the text box, Access will helpfully change the name in the control source for you too!

 

[EllieFant]

I created a text box (The name is text8)and in the control box of the preferences for this text box, I put

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

I get #Error.

The field names are spelled correctly.
Ellie
**Using Access 97**

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

 

[EllieFant]

Guess I could make those that have a section have the dash in their section name. Ellie
**Using Access 97**

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

 

[EllieFant]

Well I got past the #Error, but now it only shows the department and not the section (if there is one). This is the code I am using in a text box control box.

=[Department] & IIf(IsNull([Section])," - " & [Section]) Ellie
**Using Access 97**

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

 

[Lightning]

Hi Ellie

Try testing for an empty string rather than a null string. Access 97 treats these as two different things, even though they both mean "No data".

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

HTH
Lightning

 

[EllieFant]

Now I get the error again :-(

Maybe what I want to do isn't possible?
Ellie
**Using Access 97**

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

 

[EllieFant]

I found this in Access Help - but it didn't help hehe

About combining text values from fields when some fields contain no data

When you combine text values from several fields, some records might contain empty fields, resulting in output that might not be what you want. For example, the output might include extra spaces.
To compensate for records that don't have values in certain fields, you can use the IIf function. You use the IIf function when you want the value (or absence of a value) in one field to determine which one of two possible results you want returned.
For example, if you know that not every record will have a value in the Region field, use the following expression:

=[Address] & ", " & [City] & IIf(IsNull([Region]),"", " " & [Region]) & " " & [PostalCode]
If the Region field is empty, Microsoft Access returns no value, as specified by the argument "". If the Region field contains a value, Microsoft Access returns a space and the region, as specified by the argument " " & [Region].
Ellie
**Using Access 97**

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