Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 469
  • Last Modified:

Access Report not showing the resulted data from query

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
jaisonshereen
Asked:
jaisonshereen
  • 15
  • 5
5 Solutions
 
jaisonshereenAuthor Commented:
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
 
jaisonshereenAuthor Commented:
This is design view of the above query
0
 
jaisonshereenAuthor Commented:
this is query design
queryDetailDesc.JPG
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
jaisonshereenAuthor Commented:
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
 
jaisonshereenAuthor Commented:
Below is the button which will the query intern calls the report
Form.JPG
0
 
jaisonshereenAuthor Commented:
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
 
jaisonshereenAuthor Commented:
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
 
jaisonshereenAuthor Commented:
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
 
jaisonshereenAuthor Commented:
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
 
Jim P.Commented:
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
 
jaisonshereenAuthor Commented:
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
 
Jim P.Commented:
If you run the query as a standalone you get results, but when you run it for the report it fails?
0
 
jaisonshereenAuthor Commented:
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
 
Jim P.Commented:
If you go to the reports list and run "T_043_POSBriefReport" without the form does it work?
0
 
jaisonshereenAuthor Commented:
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
 
jaisonshereenAuthor Commented:
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
 
Jim P.Commented:
If you go into the report, what is the Data Source for that report?
0
 
jaisonshereenAuthor Commented:
yes , Data source source is correct..and its pointing to a query ..that query is resulting rows when i execute it standalone !
0
 
Jim P.Commented:
Can you upload an example of the DB?

Maybe just the report, a few lines of data and such?
0
 
jaisonshereenAuthor Commented:
nope..:-(

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

:-)
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 15
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now