Access report Record SOurce Query Syntax

Although it is uncommon - I need to put a huge query in a record source of a textbox on an access report - is this in the proper syntax?

SELECT Sum([qryPlanner-ProdMethod-WkREPORT].Sales), Sum([qryPlanner-ProdMethod-WkREPORT].Fcst)
FROM [qryPlanner-ProdMethod-WkREPORT]
WHERE ((([qryPlanner-ProdMethod-WkREPORT].WkBeginDate)>=Date()-32)) OR ((([qryPlanner-ProdMethod-WkREPORT].WkBeginDate)<=Date()-6));
dawber39Database Analyst / Application DeveloperAsked:
Who is Participating?
 
PatHartmanCommented:
If you are trying to conditionally sum fields from the report's recordsource, you may be able to create two controls in the report footer:
SalesSummary:
= Sum(IIf(WkBeginDate>=Date()-32 OR WkBeginDate<=Date()-6, Sales, 0)

ForecastSummary:
= Sum(IIf(WkBeginDate>=Date()-32 OR WkBeginDate<=Date()-6, Fcst, 0)
0
 
dawber39Database Analyst / Application DeveloperAuthor Commented:
Oh - and the query being used in this query - is the report record source
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
1) A Textbox does not have a Record Source, it has a Control Source.
2) You cannot put an SQL statement directly in a Text Box Control Source.

mx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
dawber39Database Analyst / Application DeveloperAuthor Commented:
That's what I put it in... my bad

Here's the errorErr.png
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"That's what I put it in... my bad"
In what?  
If you mean the Control Source ....
You cannot put an SQL statement directly in a Text Box Control Source.

What you can do in a Control Source is for example ... use DLookup() to extract a *single* value from a Field in a query to display in the Text Box.
0
 
dawber39Database Analyst / Application DeveloperAuthor Commented:
Excellent as always - Thank you people
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.