Solved

Report and Associate fields

Posted on 2011-09-17
12
352 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
  • 6
  • 6
12 Comments
 

Author Comment

by:pdvsa
Comment Utility
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
Comment Utility
<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
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
<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
Comment Utility
sorry, for the 0 byte file, ...here is the db...
Database32.accdb
0
 

Author Comment

by:pdvsa
Comment Utility
<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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
<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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
ok
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now