Solved

Report and Associate fields

Posted on 2011-09-17
12
356 Views
Last Modified: 2013-11-28
Experts,

I am having a tough time getting the fields in a report to format like the others in the report.  
I have a Sum (=Sum([SumUSDEquiv])) and I want the formatting to be the same as the one next to it.  The way I can do this is to associate the fields together.

The issue is that I need to get those highlighted tabs to associated to eachother.  
I dont know the proper terminology.  

(FWIW: I use to be able to associate fields in earlier version than 2007 by copy the field, select the one you want to associate it with and paste then they would be linked.  Sorta like if you were to drag a field in the report then there would be a lable associated with it...this is what I want to do..associate the fields so I can apply formatting like the one next to it) associatefields
0
Comment
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 

Author Comment

by:pdvsa
ID: 36554922
if I click on the lable I need the 2 fields below it to be selected also.
A dotted dash line appears around the selected fields when you click on any of the fields.  
You can see that dotted dash line surrounding the first 2 fields selected but the other one is not included.  It has no assocition.  
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36555459
<I am having a tough time getting the fields in a report to format like the others in the report.  >
<The issue is that I need to get those highlighted tabs to associated to eachother.  >

You will have to clearly define your question in terms of your ultimate goal here.

It is not clear what you are wanting to do when you say use vere broad terms like: "Format" and "Associate".

In versions of Access prior to 2007, you can "group" controls, but this will have nothing to do with formatting...

So, if you are looking for something like the "Layout" feature (available in Access 2007 and newer), ...then there is nothing that I know of that will simulate this in Access 2003 or older.

<associate the fields so I can apply formatting like the one next to it)>
Again, this is confusing, ...here you say that you want to do something to the control "next to it", yet in your screenshot, the control you seem to want is "below" it.


So again, please state your Question in terms of your ultimate goal, avoiding ambiguous terms like "Associate" and "format"


Thanks

JeffCoachman
0
 

Author Comment

by:pdvsa
ID: 36556210
When a field is dragged in it is formatted according to the rest of the fields.  
This field is not dragged in hence the no format.  

Does that make sense?

The field with the red label in the pic can not be formatted.
The format paint button does not format the field.

Does that make sense?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36557333
<When a field is dragged in it is formatted according to the rest of the fields.  >
As far as "Formatting" goes the "control" format is the same as the other controls (Bold, Italic, BackColor, Borderstlye, ...etc), ...but not the setting in the Format Property itself.

So we need to distinguish between Control "formats" (Bold, Italic, BackColor, Borderstlye, ...etc, all in separate properties), ... and the (one) Format Property "setting" (Currency, Number, DateTime, ...etc)

So when you say "does not format the field", can you state the *Exact* format you are referring to?
...Because this works fine for me. (see attached screencast and sample file)

This is why I always ask for a sample file, and the exact steps to recreate the issue.
A sample file avoids unnecessary back and forth postings to clarify something that would be obvious in the sample.

Let me know

JeffCoachman





boag2000-501110.flv
Database32.accdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36557344
sorry, for the 0 byte file, ...here is the db...
Database32.accdb
0
 

Author Comment

by:pdvsa
ID: 36557839
<So when you say "does not format the field", can you state the *Exact* format you are referring to?
...Because this works fine for me. (see attached screencast and sample file)

I am nto sure if you are referring to a field that is dragged in?  This field I am referring to that is labeled with the red text in the pic is a calculated field that is not in the qry design area and therefore it is not dragged into the report.  It was created with the text box icon.  I need that calculated field to remain where it is but I need it to have a lable at teh top like the rest of the fields.  

If I could get that field to format with the blue thick line on the top border like the one next to it then I would be ok.  

I think there is a very easy way to associate that calculated field with that lable "Sum USD Equivalent" but I have forgotten how to do this. Do you know what I am talking about?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36558143
<I need that calculated field to remain where it is but I need it to have a lable at teh top like the rest of the fields.  >

1. Is this what your ultimate question was all along...?

If so, then this has nothing to do with "Formatting" or "associations"
To get the Label to be with the others, you must either specify this summary field (if you use the report wizard and have a grouping set) in the Report Wizard, or have this summary as a field in the source data, then when you drag it in from the "Add Existing Fields" dialog box, it will be added to the layout and the Label will appear correctly (with the others).

When you insert a textbox control, it will not be added to the "Layout", hence the Label will not appear with the other existing labels.

2. In Access 2007 you can no longer "group" controls the way you could in Access 2003 and older.
This has been replaced with "Layouts" in Access 2007 (Arrange-->Control Layout)
So if your goal is to have all 3 controls "grouped" together, this is not possible.

However, you can add this new textbox to the "layout" of the existing field, and it will create a separate label in the same location as the others.
The kicker here is that it will no longer be "Under" the original field (see screencast)


<If I could get that field to format with the blue thick line on the top border like the one next to it then I would be ok. >
What "blue thick line"?
I don't see any "blue thick line" anywhere in your screenshot?

Again, this is why it is always best to post a sample database.....


JeffCoachman
boag2000-501171.flv
0
 

Author Comment

by:pdvsa
ID: 36560903
OK I think we are getting somewhere now.  
<or have this summary as a field in the source data, then when you drag it in from the "Add Existing Fields" dialog box, it will be added to the layout and the Label will appear correctly (with the others).
==>I did try this but I ican not SUM this field.  

you can see in the below SQL bold that I do have a SUM in the source data but I now need to have a SUM of this SUM.  When I tried to put the criteria TOTAL to a SUM in the qry design window then it gives me an error saying "cant have aggregate function in expression" (basically saying that you cant choose SUM as the criteria for it)

How can I get this field in the layout?

here is the SQL if that helps:

SELECT Sum(tblLetterOfCredit.Amount) AS SumOfAmount, tblBanks.BankName, tblBankFacilityAmts.BankID,Format(Sum(IIf([CurrencyID]=1,[Amount],[Amount]*[ExchangeRate])),"Currency") AS SumUSDEquiv, tblBankFacilityAmts.Currency, tblBankFacilityAmts.FacilityAmount, Format([FacilityAmount]-[SumUSDEquiv],"Currency") AS Available, tblCurrencyExchange.CurrencyName, Count(tblCurrencyExchange.CurrencyID) AS CountOfCurrencyID, tblCurrencyExchange.ExchangeRate, Count(tblLetterOfCredit.LetterOfCreditID) AS CountOfLetterOfCreditID

FROM (tblCurrencyExchange RIGHT JOIN (tblLetterOfCredit INNER JOIN tblBanks ON tblLetterOfCredit.IssuingBankUSA = tblBanks.BankID) ON tblCurrencyExchange.CurrencyID = tblLetterOfCredit.Currency) INNER JOIN tblBankFacilityAmts ON tblLetterOfCredit.IssuingBankUSA = tblBankFacilityAmts.BankID

WHERE (((tblLetterOfCredit.LCType)<>1 And (tblLetterOfCredit.LCType)<>11 And (tblLetterOfCredit.LCType)<>12 And (tblLetterOfCredit.LCType)<>14 And (tblLetterOfCredit.LCType)<>18 And (tblLetterOfCredit.LCType)<>21 And (tblLetterOfCredit.LCType)<>27 And (tblLetterOfCredit.LCType)<>30) AND ((tblLetterOfCredit.ExpiredYN)=0) AND ((tblLetterOfCredit.DateOfIssueSB) Is Not Null))

GROUP BY tblBanks.BankName, tblBankFacilityAmts.BankID, tblBankFacilityAmts.Currency, tblBankFacilityAmts.FacilityAmount, tblCurrencyExchange.CurrencyName, tblCurrencyExchange.ExchangeRate;
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 36563104
It wont be worth the trouble of complicating the record source, just to make the control automatically become part of the layout.

Just do it manually.
Insert the textbox
Cut the label, and put it with the others (where you want it to be)
Position the textbox directly under the label
Select both the label and the textbox then size and align them to be the same.
Then select "Tabular" from the control layouts.
This will add this new textbox (and it's associated label to the layout).
0
 

Author Comment

by:pdvsa
ID: 36563933
Ok thank you.  I will give it a try and let you know.  Was very busy today and could not test...
0
 

Author Closing Comment

by:pdvsa
ID: 36566605
That was exactly the solution.  My issue was that I was selecting the inserted lable, both sum fields (one in the Detail and in the footer) and it does not work properly when selecting all 3 fields.  I can only select the lable and the sum field in the footer and then manually adjust the sum field in the detail.  

Thanks for sticking with me on this.  I know it could have been solved real quickly if there was a db to view but I dont have the time now because I just changed jobs and it is real busy.  I am trying to get them to trash the db they have now and use mine.  Their db does not even work and nobody knows about Access in my group so I AM the expert here!  haha
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36566638
ok
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question