By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,986 Members | 2,042 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,986 IT Pros & Developers. It's quick & easy.

query parameters/functions

P: 4
I've got a question regarding query parameters:
I am trying to filter the master data for items whivh have a specific text sting at the end.

I was experimenting with using 'Right([category],5)' as part of the expression but I always ge #Error as a result, even if I try to get the 5 right-most characters.

Is there something similar to 'Like "...."', which works fro the back end of the text? Or am I trying to approach this from the wrong direction.

I am using Access '97

Thanks in advance
Oct 27 '06 #1
Share this Question
Share on Google+
12 Replies


100+
P: 143
Your approach is right. Can you post your full SQL?
Oct 27 '06 #2

P: 4
the SQL code for only trying to display teh last 3 characters of field:

SELECT Right([claimid],3) AS Expr1
FROM claim_master;

Here i get #Error as a result

If I want to filter the results for records, where the end of the string is "only)" the code that I'm trying to use would be:

SELECT claim_master.claimid
FROM claim_master
WHERE ((Right([claimid],5)="only)"))
GROUP BY claim_master.claimid;

if I try to run this query I get a Waning/error message: "This expression is typed incorrectly, or it is too complex to be evaluated ..."

Thanks
Oct 27 '06 #3

NeoPa
Expert Mod 15k+
P: 31,347
You can use
Expand|Select|Wrap|Line Numbers
  1. WHERE ([claimid] Like "*only)")
if you want to make life a little easier.

Having said that I've studied your SQL and it looks fine to me.
I would expect it to provide you with the results you're after.
It's not possible you have any strange characters hidden within?
Oct 27 '06 #4

P: 4
thanks

it seems to be working better than mine - but I get only 1 record as a result - but there should be way more.

actually I was using Right(x,y), because it should have been part of a more complex formula to sort my data - but as it was not working on the first attempt, so I started experimenting with the basic function, and noticed, that something is wrong.

now I think, that it is caused by the installed version of Access '97. A lot of functions are not available.
Anyway, I'll give it a try at home during the weekend, and see if it works there.
Oct 27 '06 #5

NeoPa
Expert Mod 15k+
P: 31,347
If functions aren't available - that's probably due to the libraries not being selected in your VBA window.
With project inactive, select 'References...' from the 'Tools' menu to add the relevant libs.
Oct 27 '06 #6

PEB
Expert 100+
P: 1,418
PEB
Take care my frien on this line:

Try as:

WHERE ((Right([claimid],5)="only"))
:)
Oct 27 '06 #7

NeoPa
Expert Mod 15k+
P: 31,347
Good spot PEB, but a mistake :-(

He actually needs the ')' as part of the string he's searching for.
Oct 27 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
SELECT IIf(InStr([ClaimID, 'Only'),[claimid],Null) As ClaimOnly
FROM claim_master
WHERE ClaimOnly Is Not Null;
Oct 28 '06 #9

PEB
Expert 100+
P: 1,418
PEB
Good spot PEB, but a mistake :-(

He actually needs the ')' as part of the string he's searching for.
Yeah really a big mistake :)

It's strange why right([yourfield],3) gives you an error as result!

What is the type of this field text or memo? or something else?

If the function isn't well done and not found the query shouldn't run so the problem isn't with the references

Try to change the expression to right([yourfield];3) in function with your regional setting but it also isn't the problem I think coz your query shouldn't work!

Really tell us what kind of field is this field?

:)
Oct 28 '06 #10

PEB
Expert 100+
P: 1,418
PEB
If not...

The solutions of mcarthy will help you! :)
Oct 28 '06 #11

P: 4
Thanks guys!

I'll try yoursuggestions on Tuesday (Monday is a Bank Holyday here), and advise if it works.
However, I tried to re-create the same query home - where I've got Access 2003 installed - and it works fine with the formula, I wanted to use originally.
So I suspect the source of this issue is in the installed version of Access'97 at my workplace.
Oh well, life would be too easy, if everything would go smooth.:-)
Oct 28 '06 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
SELECT IIf(InStr([ClaimID], 'Only'),[claimid],Null) As ClaimOnly
FROM claim_master
WHERE ClaimOnly Is Not Null;


Sorry this was missing a closing bracket.

BTW

Are you sure ClaimID is a string, if it's not that would explain your problem.
Oct 28 '06 #13

Post your reply

Sign in to post your reply or Sign up for a free account.