Solved

Access Report not showing the resulted data from query

Posted on 2008-11-02
20
442 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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
 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

832 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