Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Report and Associate fields

Posted on 2011-09-17
12
Medium Priority
?
360 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 2000 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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 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