We help IT Professionals succeed at work.

regarding MS Access report

John_BAS
John_BAS asked
on
255 Views
Last Modified: 2013-11-28
I am working on a report in MS ACcess.
I have one text-field on the report, whose value is calculated at the runtime(with-in the report). So the value of this field is not coming from the query(source of report).
Or I can say that, this field is not present in the query.
My question is that, can I sort the records in my report, in a descending order of value of this field???
Thanx a lot
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Hi,

no, you can't. Think of a report in Access as a print-out - values which are "printed" cannot be changed later.
But there is a simple solution: Add the calculation to the query where you can sort the query and then do the report. Another advantage of this is that the report can use this result without calculating itself again.

Cheers,

Christian
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Also be aware that sort orders in the Report or Query are secondary to any Groupings you have declared. This can throw you off if you're not aware of it.

Author

Commented:
Thanx for your advice.
Actually I knew it already, but I was just waiting about if somebody can tell me a miracle.

Yes I already tried to make a field in a query and calculate it there, but I couldn't run the query. When I try to run, it gives me the following error message "you trying to carry out query where the given up expression CellNaam determines no part of a statistic function".

My query is as follows:::::SELECT QSel_AfloopOpdracht_stapUNION.CelNaam, QSel_AfloopOpdracht_stapUNION.Celleider, QSel_AfloopOpdracht_stapUNION.CelId, QSel_AfloopOpdracht_stapUNION.Personeelsid, QSel_AfloopOpdracht_stapUNION.NaamPersoneelVoluit, QSel_OpdrachtLastDatum.MaxOfUitvoeringTot, QSel_AfloopOpdracht_stapUNION.Vrij_perreken AS Statusdatum, QSel_AfloopOpdracht_stapUNION.StatusOmsch AS Statusnaam, Right([StatusOmsch],Len([StatusOmsch])-1) AS Status, IIf([Bedrijfsnaam] Is Null,"Geen opdracht",[Bedrijfsnaam]) AS Opdrachtgever, QSel_AfloopOpdracht_stapUNION.UrenPerWeek AS ArbeidsUren, IIf([ContractUrenPW] Is Null,0,[ContractUrenPW]) AS OpdrachtUren, tbl_Personeel.Achternaam AS Accountmanager, QSel_AfloopOpdracht_stapUNION.Uurtarief, [uurtarief]*(IIf([ContractUrenPW] Is Null,0,IIf([UitvoeringVan]>Date(),0,IIf([UitvoeringTot]>Date(),[ContractUrenPW],0)))) AS opdrachtweekomzet, IIf([bedrijfsnaam]="BAS Consultancy",0,IIf([ContractUrenPW] Is Null,0,IIf([UitvoeringVan]>Date(),0,IIf([UitvoeringTot]>Date(),[ContractUrenPW],0)))) AS OpdrachtUrenDecl, IIf([ContractUrenPW] Is Null,0,IIf([UitvoeringVan]>Date(),0,IIf([UitvoeringTot]>Date(),[ContractUrenPW],0))) AS OpdrachtUrenNu, QSel_AfloopOpdracht_stapUNION.SectorClusterOmschrijvingKort, Year([MaxOfUitvoeringTot]) & Right("00" & Month([MaxOfUitvoeringTot]),2) & Right("00" & Day([MaxOfUitvoeringTot]),2) & " " & [NaamPersoneelVoluit] AS sort, tbl_Personeel.Plaats, IIf([Opdrachtgever]="Beschikbaar",[OpdrachtUren],0) AS Expr2, IIf([Status]="Nieuw in dienst","",IIf(([ArbeidsUren]-Sum([OpdrachtUren]))<=0,IIf([Expr2]<>0,[Expr2],0),([ArbeidsUren]-Sum([OpdrachtUren])))) AS Expr1
FROM (QSel_AfloopOpdracht_stapUNION LEFT JOIN QSel_OpdrachtLastDatum ON QSel_AfloopOpdracht_stapUNION.Personeelsid = QSel_OpdrachtLastDatum.Personeelsid) LEFT JOIN tbl_Personeel ON QSel_AfloopOpdracht_stapUNION.OpdrAM = tbl_Personeel.Personeelsid
WHERE (((QSel_AfloopOpdracht_stapUNION.CelId)<>1 And (QSel_AfloopOpdracht_stapUNION.CelId)<>2 And (QSel_AfloopOpdracht_stapUNION.CelId)<>3 And (QSel_AfloopOpdracht_stapUNION.CelId)<>4 And (QSel_AfloopOpdracht_stapUNION.CelId)<>5 And (QSel_AfloopOpdracht_stapUNION.CelId)<>6 And (QSel_AfloopOpdracht_stapUNION.CelId)<>7 And (QSel_AfloopOpdracht_stapUNION.CelId)<>8 And (QSel_AfloopOpdracht_stapUNION.CelId)<>9 And (QSel_AfloopOpdracht_stapUNION.CelId)<>13 And (QSel_AfloopOpdracht_stapUNION.CelId)<>14 And (QSel_AfloopOpdracht_stapUNION.CelId)<>16 And (QSel_AfloopOpdracht_stapUNION.CelId)<>19 And (QSel_AfloopOpdracht_stapUNION.CelId)<>20 And (QSel_AfloopOpdracht_stapUNION.CelId)<>21 And (QSel_AfloopOpdracht_stapUNION.CelId)<>22 And (QSel_AfloopOpdracht_stapUNION.CelId)<>23 And (QSel_AfloopOpdracht_stapUNION.CelId)<>25 And (QSel_AfloopOpdracht_stapUNION.CelId)<>31 And (QSel_AfloopOpdracht_stapUNION.CelId)<>32 And (QSel_AfloopOpdracht_stapUNION.CelId)<>35 And (QSel_AfloopOpdracht_stapUNION.CelId)<>36 And (QSel_AfloopOpdracht_stapUNION.CelId)<>37 And (QSel_AfloopOpdracht_stapUNION.CelId)<>38 And (QSel_AfloopOpdracht_stapUNION.CelId)<>43) AND ((([QSel_AfloopOpdracht_stapUNION].[CelId])<>36)<>37));



But if I remove "Sum", in front of [OpdrachtUren]....then it works but gave wrong results.

Can you plz help me now???
Thanx
CERTIFIED EXPERT

Commented:
Hi,

without having a demo database? A little bit complicated for THIS query.
But you can exchange the WHERE clause by using IN instead to make it a lot shorter and a little bit faster, like this:

WHERE QSel_AfloopOpdracht_stapUNION.CelId NOT IN (1,2,3,4,5,6,7,8,9,13,14,16,19,20,21,22,23,25,31,32,35,36,37,38,43)

Cheers,

Christian

Author

Commented:
Thanx,
but my problem lies in "Expr1". This is the field which is being calculated in report itself. And which I tried to create in my query. It is this expression which shows message if I includes "Sum" in front of [Opdracht Uren]. But if I remove "Sum", then that message does not appear, but gives me wrong result.

Thanx
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanx a lot, i will try it...

Author

Commented:
It was helpful to solve my problem

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.