Those who know MS Access

[Argo]

Do you guys know if there is a way to as user for input in a Query?

 

[Train]

you would need to use VBA and some sort of forms, and use the input in a field to construct your query

 

[Argo]

Actually, I figured how to it (needed to set parameters field in Query menu). However, I ran into other problems. Access, being optimized for QBE doesn't support a few important SQL features. For example query:

INSERT INTO Orders VALUES (SELECT MAX (Order_ID) + 1 FROM Orders, Customer_Name, Doctor_Name, Medication_Name, Medication_Amount, Order_Date, "Entered"

Reports to have an error. The same exact query works fine under Oracle.

 

[Train]

WEll it would help if you stated what the error message was, but my guess is that SQL statement wouldnt work on any database i know, including oracle, if it did, then it executed it wrong.

your first value you are inserting, which is a subquery, in the FROM query, Access expects a list of tables after the FROM clause, in this case access is gonna look for the tables Orders, Customer_Name, Doctor_Name, Medication_Name, Medication_Amount, Order_Date, "Entered" which I'm gonna guess a mistake because most of those look like field names not table names, the last being a explicit string, which im sure isnt allowed as a table name.

INSERT INTO Orders VALUES (SELECT MAX (Order_ID) + 1 FROM Orders, Customer_Name, Doctor_Name, Medication_Name, Medication_Amount, Order_Date, "Entered"

 

[Argo]

No, the statement works fine on Oracle, plus the comma makes SQL know that the statement is over. However, apparently, Access doesn't support suqueries inside INSERT statement.

 

[Train]

NO, it will understand subqueries anywhere, you need to specify that it is over by using a closing ) not a comma

 

[Argo]

I tried to parenthesize the expression, but even this one didn't work:

INTO Orders VALUES ((SELECT MAX (Order_ID) + 1 FROM Orders), Customer_Name, Doctor_Name, Medication_Name, Medication_Amount, Order_Date, "Entered"

 

[Train]

your missing the list of field names to insert to, and if those names arent all defined as variables, what are they?

 

[Argo]

Customer_Name and all the others are the variable to insert. Access doesn't requrie to explicitly specify variables, since every time it sees an unknown variable it just asks the user to input a value for it.

 

[Train]

well, either, way, we need the error message to help you, and i still think youd be safer by specifying the list of field names before you give the values to insert

 

[Argo]

Well, the thing is if I replace the first sub-query by a variable it works fine. The error says something about syntax error. Furthermore, I installed oracle last night, and everything worked fine. I guess Access' version of SQL is a little bit less power. Either way, thanks a lot for your time, I guess I'll stick to Oracle from now on.