Parameter query in sql?

[vidus]

I have switched my db to an adp that runs on sql. In one of the queries our criteria is simply [] to generate a parameter input box. But this does not work with sql.

Does anyone know the sql equivalent? I have searched the web for the past hour or so and I just cant find anything relevant.

Thanks!

 

[PNGBill]

Is this what you are looking for ?

SELECT TblOrders.OrderNumber, TblOrders.OrderDate
FROM TblOrders
WHERE (((TblOrders.OrderNumber)=[Enter Order Number]));

Message Enter Order Number appears, you enter a number and the sql works.

 

[vidus]

Is this what you are looking for ?

SELECT TblOrders.OrderNumber, TblOrders.OrderDate
FROM TblOrders
WHERE (((TblOrders.OrderNumber)=[Enter Order Number]));

Message Enter Order Number appears, you enter a number and the sql works.

Perhaps thats it, but im not sure how to apply that. I have the following and I want to ask for [Job] in an inputbox... The following does not work, im assuming its wrong, access changes all the ((( etc to what you see below automatically.

SELECT      ID, Job, PO, Item, Grade, Heat, MTR, Supplier
FROM          dbo.Materials
WHERE      (Jobs.Job = [Enter Order Number])

 

[PNGBill]

Does your sql work without the parameter? just Select From?

If so, then try some of the ()'s in the Where clause until you get your syntax correct.

 

[vidus]

Does your sql work without the parameter? just Select From?

If so, then try some of the ()'s in the Where clause until you get your syntax correct.

Well I have other queries with just a simple where and they work.

It seems no matter what I do with the (), as soon as I leave the field access changes it to what I pasted above... which when saved gives me a bunch of errors.

 

[vbaInet]

I don't do ADPs but you're FROM and WHERE tables don't match. That is dbo.Materials and Jobs.

 

[vidus]

I don't do ADPs but you're FROM and WHERE tables don't match. That is dbo.Materials and Jobs.

Your right. oops. But when I correct it, access auto changes it to this:

SELECT      ID, Job, PO, Item, Grade, Heat, MTR, Supplier
FROM          dbo.Materials
WHERE      (Job = [Enter Order Number])

Which is useless, because there is no field name called "enter order number".

 

[PNGBill]

Yes, I didn't notice that the data is from two diff tables.

 

[PNGBill]

[Enter Order Number] is not a field, it is the question the sql will ask you. You can type what you had for breakfast between [] and it will return that string to you and a wait your input which it will then process.

I guess the story here is, sql doesn't recognize the data so asks you to explain it. You do and off it goes.

 

[vidus]

[Enter Order Number] is not a field, it is the question the sql will ask you. You can type what you had for breakfast between [] and it will return that string to you and a wait your input which it will then process.

I guess the story here is, sql doesn't recognize the data so asks you to explain it. You do and off it goes.

Well thats the point of the post.. sql doesnt recognize the regular [] to initiate a input box like we used to use. I cant find anything to make this work in sql...

 

[vidus]

Yes, I didn't notice that the data is from two diff tables.

Its not.. that was just a mistake. The data is from table [Materials], query the field [Job]

 

[PNGBill]

My example post above works in my access.

Table is tblOrders and fields are OrderNumber and OrderDate - all in the same table.

Maybe your system is different but you would imagine there couldn't be that many options and you should have stumbled on the correct syntax assuming, your simple select version works first.

If your select sql, no criteria, doesn't work then you are wasting your time trying to filter the result.

 

[vbaInet]

ADPs are a different kettle of fish. It does weird things.

By the way, does your query work without the parameters?

 

[vidus]

My example post above works in my access.

Table is tblOrders and fields are OrderNumber and OrderDate - all in the same table.

Maybe your system is different but you would imagine there couldn't be that many options and you should have stumbled on the correct syntax assuming, your simple select version works first.

If your select sql, no criteria, doesn't work then you are wasting your time trying to filter the result.

Your code does not work. You must be using an access database, because that it what my old code looked like. This is now an adp, running on sql.

When I enter this style code, access changed it to sql rendering is useless.

 

[vidus]

ADPs are a different kettle of fish. It does weird things.

By the way, does your query work without the parameters?

Yes it does.

 

[PNGBill]

My SQL Book, not access, has

SELECT *
FROM [COLOR=Red]tableName[/COLOR]
WHERE [COLOR=Red]filedname[/COLOR] = '[COLOR=Red]String[/COLOR]';

Maybe, the version to have String as a variable could be 'Enter Your Name' or String, if there isn't any data in your records with the word String listed in what ever the fieldname is.

sql won't recognize Enter Your Name and asks you to explain.

Otherwise, how many options are there? [ ], ' ', " "

 

[vidus]

My SQL Book, not access, has

SELECT *
FROM [COLOR=Red]tableName[/COLOR]
WHERE [COLOR=Red]filedname[/COLOR] = '[COLOR=Red]String[/COLOR]';

Maybe, the version to have String as a variable could be 'Enter Your Name' or String, if there isn't any data in your records with the word String listed in what ever the fieldname is.

sql won't recognize Enter Your Name and asks you to explain.

Otherwise, how many options are there? [ ], ' ', " "

But access changes it every time. Then when I save it, it tells me there is no such field called Enter Your Name. Regardless of [] () '' "" stuff.

 

[PNGBill]

Can you post your working select query.

 

[vidus]

Can you post your working select query.

here is an example of one that works, you just want to see an example of one that functions properly?

SELECT      Quote, Customer, JobName, FollowUp, Date
FROM          dbo.Jobs
WHERE      (Quote LIKE 'M%') AND (Active = 0)

 

[PNGBill]

That is not the sql you were trying to get working and said works a select sql.

Maybe,

WHERE (Quote LIKE 'Enter Value')