[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MS ACCESS query prompts for date range

Posted on 2012-09-06
13
Medium Priority
?
422 Views
Last Modified: 2012-09-07
Hi,
  I have a query in ACCESS. As you see in attached screenshots,   the design view of query doesn't show any date field or anything that would prompt for begin and end dates, but it does ask those two input values for it to run.

Thanks.
ACCESS-Query.docx
0
Comment
Question by:sglee
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 75
ID: 38373977
What about in the NSales by slm# ... query ?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38373979
Look at your query in SQL view - the dates may be listed as parameters.

You can also see  parameters by clicking the "Parameters" button in the ribbon above the query designer.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 780 total points
ID: 38373980
Or Salesman Na ...   if that is a query?

Pretty much has to be in one of these queries ...
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:sglee
ID: 38373989
Here is the SQL view of the query:

SELECT DISTINCTROW [Salesman Names].[salesman name], [NSales by slm#/cust/sum of gp].CUSTNO, [NSales by slm#/cust/sum of gp].[CUST NAME], [NSales by slm#/cust/sum of gp].SumOfGM, [NSales by slm#/cust/sum of gp].SumOfEXTSALE, [nSales by slm#/cust/sum of gp]!SumOfGM/[nSales by slm#/cust/sum of gp]!SumOfEXTSALE AS Expr1, [NSales by slm#/cust/sum of gp].CUSTNO, [NSales by slm#/cust/sum of gp].[LastOfship-ste]
FROM [NSales by slm#/cust/sum of gp] INNER JOIN [Salesman Names] ON [NSales by slm#/cust/sum of gp].[SLM#] = [Salesman Names].[salesman #];
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38373990
You also have a field (identified by Expr1) which obviously does not have the correct field name.
0
 

Author Comment

by:sglee
ID: 38374005
Here is full description of that field. But no error from that ...

Expr1: [nSales by slm#/cust/sum of gp]!SumOfGM/[nSales by slm#/cust/sum of gp]!SumOfEXTSALE
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 780 total points
ID: 38374019
"You also have a field (identified by Expr1) which obviously does not have the correct field name. "
It's literally ... and Expression ...w/o an alias ...

Post the SQL for NSales by slm# ... query
0
 

Author Comment

by:sglee
ID: 38374025
I just spoke to the original designer and he showed me that this particular query (that I posted) uses another query that may include another query ... so on so forth.
So I need to go back to the name of the query used in this query to find out which query is asking dates.

Thanks for all of your help.
0
 
LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 260 total points
ID: 38374027
I assume, as databaseMX did, that [nSales by slm#/cust/sum of gp] is a query.

Did you open up that query in design view and check it out?

The other thing you might want to check is that the query does not have any parameters defined.  If it does, they will be visible in the first line (or two) of the SQL view of the query.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 780 total points
ID: 38374046
"uses another query that may include another query ... so on so forth."

Which is why I suggested look at the other query(s) showing in the query designer. One is a query for sure, can't tell if the other is a table or query.  And of course, the one query may include more ...

mx
0
 
LVL 75
ID: 38377757
So, what was the solution ?
0
 

Author Comment

by:sglee
ID: 38377797
The user who created that query told me that the query is based on the result from another query which is based on the result from another query.
So there were total 3 queries involved: Query1  Query2  Query3(this is the one that I posted SQL for). I kept opening other queries (like NSales by slm#/cust/sum of gp) and eventually found that Query1 had Begin Date and End Date prompt.

Thanks for your help.
0
 
LVL 75
ID: 38377814
Excellent.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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 …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

873 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