"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:el**************@TK2MSFTNGP06.phx.gbl...
Roland Hall wrote:
>In Access you use "*" + [passed variable] + "*", + can be replaced
with & Calling a parameterized query in Access requires % be used in
place of *, however, all that I have read show dynamic SQL passed to
Access:
WHERE [some column] LIKE '" & ASPvar & "' % ORDER BY ...
However, my call is similar to:
conn.qMyLookup strVar, rs
If I modify the query in Access to:
"%" & [passed variable] & "%"
I get all records. If I only put it at the end, as suggested,
I don't understand what you mean by this. Put what "at the end"?
%
>I only
get matches at the end, not throughout the column.
Ex.
search for 'a'
Rows:
abc
bca
cab
all that is returned is 'bca'
So you used
... LIKE "%' & parm
in the query, right?
Actually to get my query value to return any row where my value is found, I
need it before and after (it = %)
> If I used the % before and after, it
will return all 3 rows in my recordset. I've done this before but I
cannot remember if I ever used performed a wildcard search with a
parameterized query. Am I restricted to using only dynamic SQL to
get this to work?
It's not clear to me what you want. Which row do you want returned and
why?
I found my errors, I had two. I had 3 nested IIFs in my query and decided
to make 3 different queries for two reasons:
1. Access is very limited compared to SQL
2. I couldn't get it to work
After that, I couldn't get the query which returns projects by name to work
but I was still passing the type of request, the client ID and the client
job number, all of which I no longer needed. I only needed to now pass the
project name.
I also had an error in my main app where I was passing the wrong ID in my
javascript to my remote scripting function.
Good to hear from you. Haven't talked to you in awhile and thanks for
responding.
This is the query I'm using that actually was not the part of the problem.
SELECT Projects.[Client ID], Projects.[Client Job Number], Projects.[Project
Name], Projects.[Project Number]
FROM Projects
WHERE [Project Name] LIKE "%" & [iprojectName] & "%"
ORDER BY [Project Name];
This is the nested IIF query I gave up on:
SELECT Projects.[Client ID], Projects.[Client Job Number], Projects.[Project
Name], Projects.[Project Number]
FROM Projects
WHERE (((IIf([itype]=2,[Project Name] LIKE "%" + [iprojectName] +
"%",IIf([itype]=0,[Client ID]=[iclientid],IIf([itype]=1,[Client Job
Number]=[iclientJobNumber]))))<>False))
ORDER BY IIf([itype]=2,[Project Name],IIf([itype]=0,[Client Job
Number],IIf([itype]=1,[Client ID])));
I have 3 text fields that allow only one entry type to be passed; client ID,
client job number or project name. This is a timesheet app for an
architectual firm.
My remote scripting script:
// getprojects.js
var x;
function getProjects(itype,v) {
var cid = '', cjn = '', pn = '', p = '';
switch(itype) {
case 0:
cid = v;
break;
case 1:
cjn = v;
break;
default:
pn = v;
break;
}
var url = "getprojects.asp?itype=" + itype + "&cid=" + cid + "&cjn=" + cjn
+ "&pn=" + pn;
try { x = new XMLHttpRequest(); }
catch (error) {
try { x = new ActiveXObject("MSXML2.XMLHTTP"); }
catch (error) { return false; }
}
x.open("GET", url, true);
x.setRequestHeader('Content-Type','application/x-www-form-urlencoded');
x.onreadystatechange = function() {
if (x.readyState == 4 && x.status == 200) {
// alert(x.responseText);
document.getElementById('projects').innerHTML = x.responseText;
return true;
}
}
x.send(null);
return true;
}
My timesheet script that called the remote scripting:
prt "function cjncheck(id) {"
prt " switch(id) {"
prt " case 'cid':"
prt " document.getElementById('cjn').value='';"
prt " document.getElementById('pn').value='';"
prt " event.keyCode == 13 ?
getProjects(0,document.getElementById('cid').value ): null;"
prt " break;"
prt " case 'cjn':"
prt " document.getElementById('cid').value='';"
prt " document.getElementById('pn').value='';"
prt " event.keyCode == 13 ?
getProjects(1,document.getElementById('cjn').value ): null;"
prt " break;"
prt " case 'pn':"
prt " document.getElementById('cid').value='';"
prt " document.getElementById('cjn').value='';"
prt " event.keyCode == 13 ?
getProjects(2,document.getElementById('pn').value) : null;"
prt " }"
prt " }"
The last keyCode test was passing id cjn, instead of pn. I often copy my
code instead of rewriting similar lines and I missed changing the ID.
My little asp script that made the call to the query that was still passing
all variables, which were no longer needed once they were split into 3
different queries.
sub getProjects(itype, iclientid, iclientJobNumber, iprojectName)
dim projects, i
SQLrsConnect()
select case itype
case "0"
conn.qProjectClientID iclientid, rs
case "1"
conn.qProjectClientJobNumber iclientJobNumber, rs
case "2"
conn.qProjectName iprojectName, rs
case else
SQLrsDisconnect()
lprt "Error! itype is not 0, 1 or 2"
Response.End
end select
prt "<table>"
if not (rs.BOF or rs.EOF) then
projects = rs.GetRows()
lprt "records: " & ubound(projects,2)
SQLrsDisconnect()
for i = 0 to ubound(projects,2)
prt "<tr><td>" & right("00" & projects(0,i),3) & "-" & right("00" &
projects(1,i),3) & " " & projects(2,i) & "</td></tr>"
next
else
SQLrsDisconnect()
prt "<tr><td>no records returned</td></tr>"
end if
prt "</table>"
end sub
It's pretty simple code but I still lost hair on it. If the database wasn't
so big and complicated I'd move it to SQL before writing the app but I
didn't have time. It's one day overdue and I have a target on my back.
--
Roland Hall
