Home > IT Answers > Development > SQL
CompEng
645 pts.
Q:
SQL
SQL, Access Datasheet, Microsoft Access 2000, Access 2000, Windows Server 2000
is it possible to have a where sql statement that is open ended. I have notice that there are time that parameter box pops up with some the Access pass-through queries that I have.  Is it possible  with SQL to be able to have a box pop up that would ask for a specific input to the where statement criteria? 

In the query

Select INMSP300.M3OE# as "OENo", INMSP300.M3OEC as "ComprsOE", INMSP300.M3PRD as "PN", INMSP300.M3CLS as "PrdLine", INMSP100.MSAPP, INMSP100.MSPRD, INMSP100.MSRDTM as "rtd-Month", INMSP100.MSRDTD as "rdc-Day", INMSP100.MSRDTY as "rtc-Year", INMSP100.MSMPC as "Phase" From rdb.CARF1.INMSP300 INMSP300 join rdb.CARF1.INMSP100 INMSP100 on M3PRD = MSPRD Where INMSP300.M3CLS ='77'  and INMSP100.MSRDTY = '09'  and INMSP100.MSRDTM = '11' order by INMSP100.MSMPC

I would like to keep these (INMSP300.M3CLS ='77'   INMSP100.MSRDTY = '09   'INMSP100.MSRDTM = '11' )  open so that a pop-up would allow me to input different values.  Something to the fact (INMSP300.M3CLS ='--'   INMSP100.MSRDTY = '--   'INMSP100.MSRDTM = '--' ) What would be the statement and if this is possible how would it be written.

 



Software/Hardware used:
MS2000, Access 2000
ASKED: Nov 18 2009  4:40 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
Rklanke
1035 pts.
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • Bookmark and Share
No, SQL Server doesn't provide a way to prompt the user for input. You would have to write your own app which supported this. The native tools do not.

---

It is T-SQL (Transact SQL) that you are looking at. T-SQL has the "pop up" feature.
Last Answered: Dec 1 2009  5:34 PM GMT by Rklanke   1035 pts.
Latest Contributors: Mrdenny   49385 pts., CompEng   645 pts.
  •   
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Meandyou   2645 pts.  |   Nov 20 2009  3:18PM GMT

Some products for some RDBMSes do allow for this - for example QMF for DB2 on z/OS has this capability (and has had for 20+ years). One codes the SQL with question marks instead of values and upon execution the user is prompted for the value(s).

Steve

 

CompEng   645 pts.  |   Nov 20 2009  3:56PM GMT

So what you are saying is to substute ? like this ‘??’ or like this ‘?’ or with out the quotation marks?

Where INMSP300.M3CLS =’ ??’ and INMSP100.MSRDTY = ‘??’ and INMSP100.MSRDTM = ”??’ order by INMSP100.MSMPC
so this should look like>>>

Where INMSP300.M3CLS ="??’ and INMSP100.MSRDTY = ‘09′ and INMSP100.MSRDTM = ‘11′ order by INMSP100.MSMPC

 

Carlosdl   32715 pts.  |   Dec 2 2009  4:56PM GMT

Rklanke, could you please tell us more about that “pop up” feature of T-SQL ?

Thanks,

 

CompEng   645 pts.  |   Dec 2 2009  6:34PM GMT

I know that this has nothing to do with “CarlosdI” question. I know that it was directed toward “Rkianke”. I just want to clarify what I am trying to do and ask a few more questions. I am using MS Access 2000 and doing the SQL statements in the query sections of a passthrough query of MS Access. Access is pulling some of the information for an AS400 midranges Server and there are some information form other Access tables. I mention this to let you know where all my information is coming form. I know that there are other software called SQL-Server and other types of programming software too. What I am trying to do with access on the SQL side of the query is to be able to prompt a box to come up asking for a qualifier that would normally be typed into the where statesmen. Is there any way to address this or any symbol that would allow this? As for knowing any thing about the T-SQL I don’t but am it possible to explain the difference between the SQL-Server and the T-SQL? In the last three months that I have been learning to work with databases and have the ability to manipulate them with SQL, I know that I have not even touch the surface. What I would like to know is where can all this take me and what are the other possible applications with SQL and databases? So the more I know the better it is. I would appreciate a rather detailed response if possible. If not that is fine too.

 

Darryn   435 pts.  |   Dec 3 2009  2:21PM GMT

In MSAccess, you can use prompts enclosed by [] in queries i.e.
where INMSP300.M3CLS = [Enter M3CLS] and INMSP100.MSRDTY = [Enter MSRDTY] and INMSP100.MSRDTM = [Enter MSRDTM]

Darryn

 

CompEng   645 pts.  |   Dec 3 2009  3:52PM GMT

Thanks I will try this and let you know how it works. Is there any way you could answere the second part of the question “Where can this take me?”

 

Randym   1445 pts.  |   Dec 4 2009  8:54PM GMT

Parameters may not work in pass through queries. Because the query is actually run on the server that you are passing through to. Therefore the SQL statement syntax needs to be that of the server you are running against. An option would be to use VBA to build that sql text. You could either use inputbox to get the parameters or use a form to enter all the parameters.

 

Randym   1445 pts.  |   Dec 4 2009  8:55PM GMT

I meant prompts, not parameters - sorry

 

Carlosdl   32715 pts.  |   Dec 10 2009  7:09PM GMT

“Where can this take me ?”

If you are planning to work on the software development or database administration areas, SQL is something you will certainly need to know very well.