Below WHERE clause works in a Stored Procedure on my server but it will not play using ACCESS 2010 on PC. Server returns 146 records, PC returns zero results. Any thoughts why? Does ACCESS have casting issues?
WHERE ( ( (tblCTInfo.L_Zip LIKE @zip1 + '%' ) OR (tblCTInfo.L_Zip LIKE @zip2 + '%' ) OR (tblCTInfo.L_Zip LIKE @zip3 + '%' ) ) AND ( tblCTInfo.L_Enabled = 1) )
So far the ACCESS 2010 syntax appears to be working but have not verified the full dataset returned:
( ((tblCTInfo.L_Zip) LIKE [@Zip1] & "*") OR = @Zip1) OR ((tblCTInfo.L_Zip) LIKE [@Zip1] & "*") ) AND . . . . .
In Access, the Wildcard Character is *. However, in a webenvironment, when connection to a MDB file, you're not dealing with an Access Database, but with a Jet Database Enigine, and then the wildcard character is %
It did return all records, our problem was that we did not know the "*" was the ACCESS APPLICATION wildcard. As you inform, we incorrectly assumed that SQL stored procedures were portable.
If you solve your problem. I'll close this thread by marking as an answer and if you have anything urgent, please feel free to ask again by creating another new thread;)
Participant
790 Points
1944 Posts
Works with SQL but not in ACCESS
Dec 04, 2012 03:57 PM|zoltac007|LINK
Below WHERE clause works in a Stored Procedure on my server but it will not play using ACCESS 2010 on PC. Server returns 146 records, PC returns zero results. Any thoughts why? Does ACCESS have casting issues?
PROCEDURE CtByZip
@zip1 nvarchar(5),
@zip2 nvarchar(5),
@zip3 nvarchar(5)
WHERE ( ( (tblCTInfo.L_Zip LIKE @zip1 + '%' ) OR (tblCTInfo.L_Zip LIKE @zip2 + '%' ) OR (tblCTInfo.L_Zip LIKE @zip3 + '%' ) ) AND ( tblCTInfo.L_Enabled = 1) )
Participant
790 Points
1944 Posts
Re: Works with SQL but not in ACCESS
Dec 04, 2012 04:17 PM|zoltac007|LINK
code works if LIKE is changed to =
tblCTInfo.L_Zip = @Zip1 OR tblCTInfo.L_Zip = @Zip2
but that of course does not pull all the records we are looking for.
Participant
790 Points
1944 Posts
Re: Works with SQL but not in ACCESS
Dec 04, 2012 08:24 PM|zoltac007|LINK
So far the ACCESS 2010 syntax appears to be working but have not verified the full dataset returned:
( ((tblCTInfo.L_Zip) LIKE [@Zip1] & "*") OR = @Zip1) OR ((tblCTInfo.L_Zip) LIKE [@Zip1] & "*") ) AND . . . . .
All-Star
25756 Points
7014 Posts
Re: Works with SQL but not in ACCESS
Dec 05, 2012 03:02 AM|hans_v|LINK
How would we know which records you're looking for?
All-Star
25756 Points
7014 Posts
Re: Works with SQL but not in ACCESS
Dec 05, 2012 03:04 AM|hans_v|LINK
In Access, the Wildcard Character is *. However, in a webenvironment, when connection to a MDB file, you're not dealing with an Access Database, but with a Jet Database Enigine, and then the wildcard character is %
Participant
790 Points
1944 Posts
Re: Works with SQL but not in ACCESS
Dec 05, 2012 12:13 PM|zoltac007|LINK
It did return all records, our problem was that we did not know the "*" was the ACCESS APPLICATION wildcard. As you inform, we incorrectly assumed that SQL stored procedures were portable.
All-Star
94130 Points
18111 Posts
Re: Works with SQL but not in ACCESS
Dec 05, 2012 08:44 PM|Decker Dong - MSFT|LINK
Hi,
If you solve your problem. I'll close this thread by marking as an answer and if you have anything urgent, please feel free to ask again by creating another new thread;)