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;)
zoltac007
Participant
1199 Points
1916 Posts
Works with SQL but not in ACCESS
Dec 04, 2012 07:57 PM|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) )
zoltac007
Participant
1199 Points
1916 Posts
Re: Works with SQL but not in ACCESS
Dec 04, 2012 08:17 PM|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.
zoltac007
Participant
1199 Points
1916 Posts
Re: Works with SQL but not in ACCESS
Dec 05, 2012 12:24 AM|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 . . . . .
hans_v
All-Star
35986 Points
6550 Posts
Re: Works with SQL but not in ACCESS
Dec 05, 2012 07:02 AM|LINK
How would we know which records you're looking for?
hans_v
All-Star
35986 Points
6550 Posts
Re: Works with SQL but not in ACCESS
Dec 05, 2012 07:04 AM|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 %
zoltac007
Participant
1199 Points
1916 Posts
Re: Works with SQL but not in ACCESS
Dec 05, 2012 04:13 PM|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.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Works with SQL but not in ACCESS
Dec 06, 2012 12:44 AM|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;)