Solved

Access Report not showing the resulted data from query

Posted on 2008-11-02
20
433 Views
Last Modified: 2013-11-28
I  have report which takes data from a query (which is written in the record source of report).

This query is resulting in rows when i execute it individually. But i cannot see the resulted data in the report.

Please see the query and report snapshot below and al details below.

This report is generated by the button click on a form.

So many expert here left this issue unresolved. It will be grateful if anyone can resolve the problem.
0
Comment
Question by:jaisonshereen
  • 15
  • 5
20 Comments
 

Author Comment

by:jaisonshereen
ID: 22860741
This is the query which resulting rows when i execute induvidually
SELECT T_FPtr_TempSearch.Temp_PromoID, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Promo_Name, T_100_q010_PromoTitleandDetailsAsOne_SELECT.[T_OUTPUT_Financials&POS_MAIN].EnteredBy AS PromoEnteredBy, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Location_Name, T_100_q010_PromoTitleandDetailsAsOne_SELECT.[T_OUTPUT_Financials&POS_MAIN].Promo_Period AS Promo_period, IIf(IsNull([GenericPOS]) Or [GenericPOS]="No","" & [Article] & IIf(IsNull([AdditionalArticles]) Or [AdditionalArticles]="0","","/ " & [AdditionalArticles]),"Various") AS SKU, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Article, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Cat_ID, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Position, T_Static_016_Position.DisplayOrder, T_100_q010_PromoTitleandDetailsAsOne_SELECT.PPstartDate, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Known_brand, T_100_q010_PromoTitleandDetailsAsOne_SELECT.T_OUTPUT_PromoChosenAtEntry_MAIN.Location AS Location, T_Static_027_LocationDisplayOrders.LocationOrder, T_100_q010_PromoTitleandDetailsAsOne_SELECT.PrimaryProductDesc, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Colour_Variety, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Size_weight, T_100_q010_PromoTitleandDetailsAsOne_SELECT.ProductBenefit, T_100_q010_PromoTitleandDetailsAsOne_SELECT.[T_OUTPUT_Financials&POS_MAIN].MechanicChosen AS MechanicChosen, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Price_was_was_£, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Price_was, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Price_now, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Flooring_was_£, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Flooring_was_euro, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Flooring_Promo_£, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Flooring_Promo_euro, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Price_was_was_euro, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Price_was_euro, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Promo_euro, T_100_q010_PromoTitleandDetailsAsOne_SELECT.DominantOfferMessage, T_100_q010_PromoTitleandDetailsAsOne_SELECT.EireDominantOffer, IIf([DominantOfferMessage]="Multibuy" Or [EireDominantOffer]="Multibuy",IIf(InStr(1,[MBStatement],":")>0,Left([MBStatement],InStr(1,[MBStatement],":")-1),[MBStatement]),"") AS [Buy X], IIf(([DominantOfferMessage]="Multibuy" Or [EireDominantOffer]="Multibuy") And InStr(1,[MBStatement],":")>0," " & Mid([MBStatement],InStr(1,[MBStatement],":")+1,InStr(1,[MBStatement],"~")-InStr(1,[MBStatement],":")-1) & " ","") AS [Get Y], IIf(([DominantOfferMessage]="Multibuy" Or [EireDominantOffer]="Multibuy") And InStr(1,[MBStatement],":")>0,Mid([MBStatement],InStr(1,[MBStatement],"~")+1),"") AS [For], T_100_q010_PromoTitleandDetailsAsOne_SELECT.MBStatement, T_100_q010_PromoTitleandDetailsAsOne_SELECT.HeaderDescription, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Unit_Price_Statement, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Offer_caviat_exclusion, T_100_q010_PromoTitleandDetailsAsOne_SELECT.StartDate, T_100_q010_PromoTitleandDetailsAsOne_SELECT.EndDate, IIf([WEEE]<>"" And [WEEE]<>"0","Price includes PRF charge of " & "¬" & T_Static_036_WEEE_Classification.EMC_charge,"") AS [PRF Charge], T_100_q010_PromoTitleandDetailsAsOne_SELECT.[28_DayRule], T_100_q010_PromoTitleandDetailsAsOne_SELECT.AdditionalArticles, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Insitu_POS, T_100_q010_PromoTitleandDetailsAsOne_SELECT.GenericPOS, T_100_q010_PromoTitleandDetailsAsOne_SELECT.NoPOS, T_100_q010_PromoTitleandDetailsAsOne_SELECT.POSComments, T_100_q010_PromoTitleandDetailsAsOne_SELECT.UKPromotion, T_100_q010_PromoTitleandDetailsAsOne_SELECT.EirePromotion

FROM (((T_FPtr_TempSearch LEFT JOIN T_100_q010_PromoTitleandDetailsAsOne_SELECT ON T_FPtr_TempSearch.Temp_PromoID = T_100_q010_PromoTitleandDetailsAsOne_SELECT.T_OUTPUT_PromoChosenAtEntry_MAIN.Temp_PromoID) LEFT JOIN T_Static_016_Position ON (T_100_q010_PromoTitleandDetailsAsOne_SELECT.T_OUTPUT_PromoChosenAtEntry_MAIN.Location = T_Static_016_Position.Location) AND (T_100_q010_PromoTitleandDetailsAsOne_SELECT.Position = T_Static_016_Position.Position)) LEFT JOIN T_Static_027_LocationDisplayOrders ON T_100_q010_PromoTitleandDetailsAsOne_SELECT.T_OUTPUT_PromoChosenAtEntry_MAIN.Location = T_Static_027_LocationDisplayOrders.Location) LEFT JOIN T_Static_036_WEEE_Classification ON T_100_q010_PromoTitleandDetailsAsOne_SELECT.WEEE = T_Static_036_WEEE_Classification.WEEE_category

GROUP BY T_FPtr_TempSearch.Temp_PromoID, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Promo_Name, T_100_q010_PromoTitleandDetailsAsOne_SELECT.[T_OUTPUT_Financials&POS_MAIN].EnteredBy, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Location_Name, T_100_q010_PromoTitleandDetailsAsOne_SELECT.[T_OUTPUT_Financials&POS_MAIN].Promo_Period, IIf(IsNull([GenericPOS]) Or [GenericPOS]="No","" & [Article] & IIf(IsNull([AdditionalArticles]) Or [AdditionalArticles]="0","","/ " & [AdditionalArticles]),"Various"), T_100_q010_PromoTitleandDetailsAsOne_SELECT.Article, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Cat_ID, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Position, T_Static_016_Position.DisplayOrder, T_100_q010_PromoTitleandDetailsAsOne_SELECT.PPstartDate, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Known_brand, T_100_q010_PromoTitleandDetailsAsOne_SELECT.T_OUTPUT_PromoChosenAtEntry_MAIN.Location, T_Static_027_LocationDisplayOrders.LocationOrder, T_100_q010_PromoTitleandDetailsAsOne_SELECT.PrimaryProductDesc, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Colour_Variety, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Size_weight, T_100_q010_PromoTitleandDetailsAsOne_SELECT.ProductBenefit, T_100_q010_PromoTitleandDetailsAsOne_SELECT.[T_OUTPUT_Financials&POS_MAIN].MechanicChosen, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Price_was_was_£, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Price_was, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Price_now, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Flooring_was_£, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Flooring_was_euro, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Flooring_Promo_£, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Flooring_Promo_euro, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Price_was_was_euro, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Price_was_euro, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Promo_euro, T_100_q010_PromoTitleandDetailsAsOne_SELECT.DominantOfferMessage, T_100_q010_PromoTitleandDetailsAsOne_SELECT.EireDominantOffer, IIf([DominantOfferMessage]="Multibuy" Or [EireDominantOffer]="Multibuy",IIf(InStr(1,[MBStatement],":")>0,Left([MBStatement],InStr(1,[MBStatement],":")-1),[MBStatement]),""), IIf(([DominantOfferMessage]="Multibuy" Or [EireDominantOffer]="Multibuy") And InStr(1,[MBStatement],":")>0," " & Mid([MBStatement],InStr(1,[MBStatement],":")+1,InStr(1,[MBStatement],"~")-InStr(1,[MBStatement],":")-1) & " ",""), IIf(([DominantOfferMessage]="Multibuy" Or [EireDominantOffer]="Multibuy") And InStr(1,[MBStatement],":")>0,Mid([MBStatement],InStr(1,[MBStatement],"~")+1),""), T_100_q010_PromoTitleandDetailsAsOne_SELECT.MBStatement, T_100_q010_PromoTitleandDetailsAsOne_SELECT.HeaderDescription, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Unit_Price_Statement, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Offer_caviat_exclusion, T_100_q010_PromoTitleandDetailsAsOne_SELECT.StartDate, T_100_q010_PromoTitleandDetailsAsOne_SELECT.EndDate, IIf([WEEE]<>"" And [WEEE]<>"0","Price includes PRF charge of " & "¬" & T_Static_036_WEEE_Classification.EMC_charge,""), T_100_q010_PromoTitleandDetailsAsOne_SELECT.[28_DayRule], T_100_q010_PromoTitleandDetailsAsOne_SELECT.AdditionalArticles, T_100_q010_PromoTitleandDetailsAsOne_SELECT.Insitu_POS, T_100_q010_PromoTitleandDetailsAsOne_SELECT.GenericPOS, T_100_q010_PromoTitleandDetailsAsOne_SELECT.NoPOS, T_100_q010_PromoTitleandDetailsAsOne_SELECT.POSComments, T_100_q010_PromoTitleandDetailsAsOne_SELECT.UKPromotion, T_100_q010_PromoTitleandDetailsAsOne_SELECT.EirePromotion;

Open in new window

0
 

Author Comment

by:jaisonshereen
ID: 22860755
This is design view of the above query
0
 

Author Comment

by:jaisonshereen
ID: 22860766
this is query design
queryDetailDesc.JPG
0
 

Author Comment

by:jaisonshereen
ID: 22860778
Below is the report i am trying to create

I have to get the above query result in this report (detail pane) i can see everything in the report except in the details pane
report.JPG
0
 

Author Comment

by:jaisonshereen
ID: 22860788
Below is the button which will the query intern calls the report
Form.JPG
0
 

Author Comment

by:jaisonshereen
ID: 22860792
this is button click event
Private Sub Cmd_POSBrief_Click()

On Error GoTo Err_Cmd_POSBrief_Click

 

    Call Project1.Form_T_F900_GeneralSearch.CheckForNullSearch

   

    FixSearchCriteria

   

    DoCmd.OpenReport "T_043_POSBriefReport", acViewPreview

 

Exit_Cmd_POSBrief_Click:

    Exit Sub

 

Err_Cmd_POSBrief_Click:

    ApplicationError Err.Number, Err.Description, "Form_T_F130_ViewRollupPromotions\Cmd_POSBrief_Click"

    Resume Exit_Cmd_POSBrief_Click

 

End Sub

Open in new window

0
 

Author Comment

by:jaisonshereen
ID: 22860797
This is the detail of the above procedure FixSearchCriteria

Public Sub FixSearchCriteria()

On Error GoTo Err_FixSearchCriteria

 

    'CurrentProject.Connection.Execute "DELETE * FROM T_FPtr_TempSearch"

    

    DoCmd.SetWarnings False

    'DoCmd.RunSQL "DELETE * FROM T_FPtr_TempSearch"

    DoCmd.OpenQuery "T_001_SearchCriteriaFix"

       

Exit_FixSearchCriteria:

    Exit Sub

 

Err_FixSearchCriteria:

    ApplicationError Err.Number, Err.Description, "Module1\FixSearchCriteria"

    Resume Exit_FixSearchCriteria

    

End Sub

Open in new window

0
 

Author Comment

by:jaisonshereen
ID: 22860804
T_001_SearchCriteriaFix is the query which call another query T_100_q010_PromoTitleandDetailsAsOne_SELECT ( shown inside the query above)

and this query will call the above posted query
0
 

Author Comment

by:jaisonshereen
ID: 22860825
Below code of one of the fields in several fields which gives the if condition which needs to select the type of report which needed to be showed.


This is the second column of the bottom box in the above query design.

IIf([T_FPtr_GeneralSearch].[PromoPeriod]="Period",1,IIf([T_FPtr_GeneralSearch].[PromoPeriod]=[T_100_q010_PromoTitleandDetailsAsOne_SELECT].[T_OUTPUT_PromoChosenAtEntry_MAIN].[promo_period],1,0))

Open in new window

0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 22867084
If you go to the report and pull it up it works?


But if you click the button -- it fails?

Is there an error message?

The first thing to do if getting an error or a failure is to take out the error handling:
Put a single quote mark in front of the On Error statements:
--------------------------
' On Error GoTo
---------------------------

And then run the statement.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:jaisonshereen
ID: 22867856
If you go to the report and pull it up it works?

Report is commiing up with all labels . but there is no data in detail pane....please see the above report


But if you click the button -- it fails?

no report comes up

Is there an error message?

All i given .... there is no failure...


The first thing to do if getting an error or a failure is to take out the error handling:
Put a single quote mark in front of the On Error statements:
--------------------------
' On Error GoTo
---------------------------

And then run the statement.
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 500 total points
ID: 22868021
If you run the query as a standalone you get results, but when you run it for the report it fails?
0
 

Author Comment

by:jaisonshereen
ID: 22868604
yes,when i run query standalone i will get results ...

when i run report (means when i click button) ..report has an ability to run the particular query(which i shown above) to run and show the results in the details pane... that is  not working !!
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 500 total points
ID: 22868708
If you go to the reports list and run "T_043_POSBriefReport" without the form does it work?
0
 

Author Comment

by:jaisonshereen
ID: 22868883
no,If i go to the reports list and run "T_043_POSBriefReport" without the form ,its not working... still i am getting results when i run the query standalone !
0
 

Author Comment

by:jaisonshereen
ID: 22869624
an if i change the join in my query i ll get the data in my report....but that is not my required data
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 500 total points
ID: 22940650
If you go into the report, what is the Data Source for that report?
0
 

Author Comment

by:jaisonshereen
ID: 22941039
yes , Data source source is correct..and its pointing to a query ..that query is resulting rows when i execute it standalone !
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 500 total points
ID: 22941268
Can you upload an example of the DB?

Maybe just the report, a few lines of data and such?
0
 

Author Comment

by:jaisonshereen
ID: 22941366
nope..:-(

this is highly secured ..no hope to give you mdb..i will give the extracts as your request

:-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

947 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

19 Experts available now in Live!

Get 1:1 Help Now