Need to convert Access Query to MSSQL 1

[an0nyM0use]

I have a report in Access that calls for something like this:
WHERE ((([Lookup Table - Process Team].[Process Team]) Like [Process Team?])
But I am making the DB into an .adp and the design view for my report doesn't seem to support this kind of query. It comes up looking like this at best:
WHERE ([Lookup Table - Process Team].[Process Team] LIKE N'[Process Team?]')
which of course is not giving me the same results. The orig DB pops open a window for me to input my process team code. But this is just returning a blank page, because it is not pulling any of the results. I hope that makes sense. I am very much a newbie here and this is the first time I have even upsized an Access DB to SQL or created an .adp

Thanks,
Kevin

 

[PHV]

Have you tried this ?
WHERE [Lookup Table - Process Team].[Process Team] Like "'" & [Process Team?] & "'"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cmmrfrds]

Some differences in sql server syntax are;
% as wildcard instead of *
' single quote instead of " double quote for literals

for example looking for "sa" in a string.
like '%sa%'
instead of like "*sa*

 

[an0nyM0use]

PHV,
this is the full Where statement I am using:
WHERE ([Lookup Table - Process Team].[Process Team] LIKE ['] & [Process Team?] & [']) AND
([Master Table - Process Information].[Process Number] LIKE ['] & [Process Number?] & ['])

Note: Access is replacing the "'" with ['] after I save. When checking SQL it is giving me an ADO error.
<img src="

http://www.flickr.com/photos/1558107_62a50e9d45.jpg">

BTW thx for both of the rapid responses so far i am very impressed with this forum.

Kevin

 

[PHV]

Seems tha .adp doesn't like parametized queries.
Sorry don't know anything about access projects.

 

[PHV]

Seems that .adp doesn't like parametized queries.
Sorry don't know anything about access projects.

 

[bboffin]

In the original Access query the term [Process Team?] is a prompt to enter some or all of the name of the Process Team and it would expect to have at least one * (or ?) in the value entered.

This syntax is not recognised when using SQL or MSDE in a project file.

You need to create a stored procedure with a single parameter that specifies the Process Team value and then use the name of this stored procedure as the Record Source for the report.

The Stored Procedure would look something like this:

Create Procedure ProcTeamList

	(		@Process_Team nvarchar(20) )
As
	set nocount on
	SELECT * FROM ... 
        WHERE ([Lookup Table - Process Team].[Process Team] LIKE @Process_Team)
	return

When you open the report it will prompt for the Process Team as before but now you MUST use % instead of * in the value entered.

Another syntax difference is _ (underscore) instead of ? in LIKE comparisons to indicate a single wildcard character



Bob Boffin

 

[an0nyM0use]

I was doing all of this in the Record Source for the Report, but i created it as a query now and then referenced the that query from the Report Dataroperties. Unfortunately, it still seems to be returning a blank report instead of asking me for values for Process Team etc. New code, notice the dbo.

WHERE (dbo.[Lookup Table - Process Team].[Process Team] LIKE ' & [Process Team?] & ') AND
(dbo.[Master Table - Process Information].[Process Number] LIKE ' & [Process Number?] & ') AND
(dbo.[Master Table - Process Information].[Internal Review Cycle] <> 'O')

 

[an0nyM0use]

Bob,
You are the bomb, but all I really needed to do in access was add the LIKE @Process_Team to the criteria.

Thanks All!

BTW, I will be starting a new post in a moment about getting security access for my .adp users to my SQL Server.