Where SQL statement is open ended

1185 pts.
Tags:
Access 2000
Access Datasheet
Microsoft Access 2000
SQL
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 10  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Meandyou
    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
    5,220 pointsBadges:
    report
  • CompEng
    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
    1,185 pointsBadges:
    report
  • carlosdl
    Rklanke, could you please tell us more about that "pop up" feature of T-SQL ? Thanks,
    69,175 pointsBadges:
    report
  • CompEng
    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.
    1,185 pointsBadges:
    report
  • Darryn
    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
    765 pointsBadges:
    report
  • CompEng
    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?"
    1,185 pointsBadges:
    report
  • Randym
    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.
    1,740 pointsBadges:
    report
  • Randym
    I meant prompts, not parameters - sorry
    1,740 pointsBadges:
    report
  • carlosdl
    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.
    69,175 pointsBadges:
    report
  • VP
    Since you are using MS ACCESS to access tables, even if those tables are linked from another RDBMS database located on an SQL compliant server, you can use the [...] prompt to create a pop up like Darryn mentioned. Because MS ACCESS uses the Jet Engine it tries to convert your MS ACCESS specific queries into an SQL complaint statement. One trick is to try using MS ACCESS' QBE to develop your query, once your query is working, change your View to SQL View from the menu. This will give you the SELECT... statement you are seeking. Good luck.
    25 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following