UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Parameter Query But Can't Use A Form, Access 2013    
 
   
phuelgod
post Apr 20 2017, 09:56 AM
Post#1



Posts: 212
Joined: 16-January 04
From: Tacoma, WA


Hello,

I'm running a pass-through query on a database where I have permission to create queries but not forms. The query uses a left outer join to compare records from one month to records from the following month. Currently, I manually edit the SQL to change the starting and ending dates, as well the value of the date field displayed.

CODE
SELECT
Initial_Dimension AS [Dimension],
DOUBLE(SUM(Final_Measure+0.0))/DOUBLE(SUM(Initial_Measure+0.0)) - 1 as Pct_Change
DATE('2107-03-01') AS [Date]

FROM

((SELECT [Dimension_1] AS Initial_Dimension,[Measure_1] AS Initial_Measure
FROM v23.datatable
WHERE [Year] = 2017 AND [Month] = 2
GROUP BY [Dimension_1]) Initial_data

LEFT OUTER JOIN

(SELECT [Dimension_1] as Final_Dimension,[Measure_1] AS Final_Measure
FROM v23.datatable
WHERE ([Year] = 2017 AND [Month] = 3)
GROUP BY [Dimension_1]) Final_data

ON Initial_Dimension = Final_Dimension)

GROUP BY Initial_Dimension



I know how to set the Year and Month parameters for the where clauses using parameter prompts, but I don't know how to set the value of the Date field in the initial Select clause.

I'm also assuming there's a way to enter just one date and use it throughout the SQL, but I'm not well versed enough in SQL to figure it out.

Thanks in advance for any assistance you can provide.

Best regards,

Frank

--------------------
"Do or do not; there is no try."
- Yoda
Go to the top of the page
 
GroverParkGeorge
post Apr 20 2017, 10:26 AM
Post#2


UA Admin
Posts: 29,161
Joined: 20-June 02
From: Newcastle, WA


Have you considered creating a stored procedure from your current SQL? You can call it from a Passthru and pass the argument for the date thru it.


EXEC sp_YourStorProcNameGoesHere '2017-04-20'

You still have to have a way to change the SQL in the Passthru, which I would think should be done from a form.

However, you could try to set up a macro that calls a VBA function....

I'm assuming also that you could create the stored proc on the SQL Server. Maybe you'd have to have the DBA do that part for you.
This post has been edited by GroverParkGeorge: Apr 20 2017, 10:28 AM

--------------------
Go to the top of the page
 
phuelgod
post Apr 20 2017, 11:09 AM
Post#3



Posts: 212
Joined: 16-January 04
From: Tacoma, WA


Thanks for the info...I've never done a stored procedure before, so will have to learn a bit more about that.

QUOTE
You still have to have a way to change the SQL in the Passthru, which I would think should be done from a form.


Alas, therein lies the rub...I'm prevented from making a form by my user permissions.

QUOTE
However, you could try to set up a macro that calls a VBA function....


I'll probably start with this option...I wouldn't say I'm good with VBA, but I'm better with it than with SQL

--------------------
"Do or do not; there is no try."
- Yoda
Go to the top of the page
 
GroverParkGeorge
post Apr 20 2017, 11:14 AM
Post#4


UA Admin
Posts: 29,161
Joined: 20-June 02
From: Newcastle, WA


Stored Procedures are pretty handy.

Here's a fairly good intro.

--------------------
Go to the top of the page
 
Jeff B.
post Apr 20 2017, 11:22 AM
Post#5


UtterAccess VIP
Posts: 9,677
Joined: 30-April 10
From: Pacific NorthWet


Who gave you those permissions? Do they have a valid (i.e., security-based) reason? Could they change the permissions?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
GroverParkGeorge
post Apr 20 2017, 11:36 AM
Post#6


UA Admin
Posts: 29,161
Joined: 20-June 02
From: Newcastle, WA


Wait a second. It just dawned on me.

You are using Access 2013. You can create queries. You can't create a form?

Do you have only the Access runtime?

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    30th April 2017 - 11:46 AM