Solved

Access Report not showing the resulted data from query

Posted on 2008-11-02
20
422 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

758 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

20 Experts available now in Live!

Get 1:1 Help Now