| Author |
 Topic  |
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
 Posted - 16 February 2004 : 07:33:28
|
I've a search form that takes a string and passes it as a parameter to this (stored) Access Query:
- qrySearchMembers -
SELECT [MemberID], [Fname], [Sname], [Email], [NewMember]
FROM tblMembers
WHERE [MemberID]>1 And ([Fname] Like '*'+[param]+'*' Or [Sname]) Like '*'+[param]+'*'
ORDER BY [Sname], [Fname];
I've become too close to the code I think - Is there something blindingly wrong here? |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 16 February 2004 : 07:40:40
|
SELECT [MemberID], [Fname], [Sname], [Email], [NewMember]
FROM tblMembers
WHERE [MemberID]>1 And ([Fname] Like '*'+[param]+'*' Or [Sname] Like '*'+[param]+'*')
ORDER BY [Sname], [Fname];
The parenthesis is wrongly placed. I've moved it to the proper place now. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
|
miperez
Junior Member
Spain
243 Posts |
Posted - 16 February 2004 : 07:44:23
|
Hi, D3mon.
I have a query in one of my databases, that is (the field names are in Spanish, but it doesn't mater):
SELECT Incidencias.NumeroIncidencia, Incidencias.Producto, Incidencias.Situacion
FROM Incidencias
WHERE ((Incidencias.Producto) Like [Nombre del producto:] & '*')
ORDER BY Incidencias.NumeroIncidencia;
and it works fine, so maybe the trick is using the "&" symbol to concatenate the string, instead of using the "+" symbol.
BTW, I know that access supports an ending "*" in the "like" clauses, but I'm not sure if it supports a leading "*", check that if it doesn't work just by changing the "&". |
Best Regards
Mikel Perez
"Hell is the place where everything test perfectly, and nothing works"
|
Edited by - miperez on 16 February 2004 07:46:34 |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 16 February 2004 : 07:47:12
|
OK thanks.
Now here's the wierd part (for me at least!)
When I call this query (from ASP) as follows:
strSearchText = "da"
strSQL = "qrySearchMembers '" & strSearchText & "'"
Set objRS = objDB.Execute(strSQL)
I get an empty RS (RS.EOF = true), but when I run the query in Access GUI and add in the same parameter, it returns the list of results as expected? |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 16 February 2004 : 08:16:39
|
| I'm wondering now If I'm passing the string as a parameter correctly in the ASP - I've only ever passed numbers before... |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 16 February 2004 : 08:27:22
|
I don't think you can call it like that. Either create a parameter object for the value you want to pass or (easier), use the execute method of the connection object, like this:
Set objRs = con.Execute ("exec qrySearchMembers '" & strSearchText &"'")
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 16 February 2004 : 08:58:28
|
| solved - moved the whole query out to the ASP - now works ok. Must be too complex for the query subsystem. Note though, that I had to use % in place of * for the wildcards. |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 16 February 2004 : 09:08:26
|
I'm on a tight schedule with this project at the moment, but I'll make a note to check this out further in a 'less-busy' moment. Thanks guys.  |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 19 February 2004 : 11:51:07
|
I want to copy a row from one table (Basket) to another table (orders). Both tables have the same stucture.
How about this query statement:
INSERT INTO tblOrders
(SELECT * FROM tblBasket WHERE tblBasket.BasketID = 819) |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 19 February 2004 : 11:59:01
|
| no worries - I got it sorted - The error I was getting, was indirectly telling me that a column appeared in the source table that did not appear in the destination table - Duh! |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
| |
Topic |
|
Topic Locked
