Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

Report and Associate fields

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
pdvsa
Asked:
pdvsa
  • 6
  • 6
1 Solution
 
pdvsaAuthor Commented:
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
 
Jeffrey CoachmanCommented:
<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
 
pdvsaAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jeffrey CoachmanCommented:
<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
 
Jeffrey CoachmanCommented:
sorry, for the 0 byte file, ...here is the db...
Database32.accdb
0
 
pdvsaAuthor Commented:
<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
 
Jeffrey CoachmanCommented:
<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
 
pdvsaAuthor Commented:
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
 
Jeffrey CoachmanCommented:
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
 
pdvsaAuthor Commented:
Ok thank you.  I will give it a try and let you know.  Was very busy today and could not test...
0
 
pdvsaAuthor Commented:
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
 
Jeffrey CoachmanCommented:
ok
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now