ACCESS FEED: Re: [MS_AccessPros] MOST URGENT: Some data not displaying in report

John,

Its done with your idea of setting Keep together property to Yes.

In sorting and grouping of report for MemberID i changed Keep together to Whole group and all is OK. Thanks for your inspiration and giving new ideas.

Now authorities are also demanding that there may be situations to enter more separate entries for MiscCost, as in detail section i am displaying AmountDeposited. So should i add a group Header for MiscCost to get more than one rows of MiscCost?

Regards,

Khalid

On Monday, July 21, 2014 3:44 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Khalid-

Actually, using code to eliminate zero rows is the hard way to do it. You could have simply added a filter to your WHERE clause in the Record Source to not return any zero rows.

Report sections have a Keep Together property - set that to Yes to put all records from a group on a single page. Keep in mind that if there are more detail rows than will fit on one page, it will still overflow.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jul 21, 2014, at 2:01 AM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Good morning after weekend.

Well it was a new information/knowledge for me that we can write different events on reports sections.

I put the following lines in the Format event of the Detail Section

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If AmountDeposited = 0 Then

Cancel = True

End If

End Sub

GREAT! Now i am not getting rows for zero entries.

John,

One more thing i am observing on reports preview that on the end of page if some members entries are exceeding the page length they move on to next page (these are on the Detail Section where i am displaying three controls

Details

TranscationDate

AmountDeposited

Is there any way that the report shows complete data of a MemberID at the bottom of the page, if exceeding the page length that members complete entry should move to the next page.

Best regards & Thanks once again, and proud to be a member of this wonderful knowledgeable group support me and others always.

Khalid

On Sunday, July 20, 2014 1:56 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Khalid-

If there's a zero or blank record you do not want to display, you can set Cancel = True to not print the row in the Format event of the Detail section.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jul 19, 2014, at 12:49 PM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

The problem is resolved i tried adding in Sorting and Grouping of report "TransactionType" with sort order Descending. (TransactionType had two values "Payment", "Deposit") and as i was showing deposit entries in detail section and Payment entries in MemberID header.

Now only one thing is left in the report preview on the detail section i have three controls:

Details, TransactionDate, AmountDeposited

For each MemberID i am getting in detail section first row showing AmountDeposited as 0 (Zero).

AmountDeposited default value is set to zero, and if i remove that then the same problem occours for which i started the thread.

Lots of thanks for being with me and suggesting different ways to resolve the matter. Now could this be resolved that first row with zero entry in detail section may not display?

Regards,

Khalid

On Saturday, July 19, 2014 7:20 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Khalid-

You can run the query outside the report. The query will prompt you for the parameter value. Simply enter the CstName that you want to test.

For the one that is failing, what do you see in the table for that CstName value?

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jul 19, 2014, at 9:51 AM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Control source must be correct for the fields as on certain rows i am getting the data. Further i cannot run the query directly as field "CstName" has the criteria "[Forms]![Members CST Payment Summary Dialog]![cmbCstName]"

One thing more i have opened the same report selecting another "CstName" and it is giving no problems.

I will be away for some time, as it is time for opening fasting here.

See you,

Khalid

On Saturday, July 19, 2014 6:34 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Khalid-

Is the Control Source correct for the fields that aren't displaying data? Do you have any code in the report?

If the controls are bound directly to the fields in the query, there should be no problem. Have you tried just running the query that is the Record Source?

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jul 19, 2014, at 9:14 AM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Nothing such like you are saying, for numeric controls i can see 0 (Zero), and i can see NameOfCurrencyTour, NameOfCurrencyMisc. (which is "EUR")

On some rows "MiscCostDetail" is displaying and on some not.

Khalid

On Saturday, July 19, 2014 5:54 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Khalid-

Do you see anything in the controls? If you see something like ####, that means the control isn't big enough to display the data.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jul 19, 2014, at 8:48 AM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

CstPayment is a table and and Yes i can see values in the columns "missing" data in report.

Regards,

Khalid

On Saturday, July 19, 2014 5:33 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Khalid-

Is CstPayment a query or a table? If you open CstPayment as a Datasheet, do you see values in the columns that are "missing" data? The report can't display data that doesn't exist.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Outy

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jul 19, 2014, at 8:15 AM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi John,

I have a report "Members CST Payment Summary" with Record Source: "Members CST Payment Summary Report Query"

SELECT CstPayment.MemberID, CstPayment.SurName, CstPayment.GivenName, CstPayment.GenderStatus, CstPayment.CstName, CstPayment.TransactionDate, CstPayment.Details, CstPayment.AirTicket, CstPayment.TourBasicAmount, CstPayment.NameOfCurrencyTour, CstPayment.TourCost, CstPayment.MiscCostDetail, CstPayment.MiscBasicAmount, CstPayment.NameOfCurrencyMisc, CstPayment.MiscCost, CstPayment.AmountPayable, CstPayment.AmountDeposited

FROM CstPayment

WHERE (((CstPayment.CstName)=[Forms]![Members CST Payment Summary Dialog]![cmbCstName]))

ORDER BY CstPayment.MemberID;

On reports MemberID Header some records are showing complete data OR fields, but some are not displaying fields:

AirTicket, TourBasicAmount, NameOfCurrencyTour, TourCost, MiscCostDetail, MiscBasicAmount, NameOfCurrencyMisc, MiscCost

I am displaying fields Details, TransactionDate, AmountDeposited on reports Detail section.

John in need to resolve it most urgently as my boss is in a meeting and needs this report.

I hope i would get prompt help now.

Best regards,

Khalid