query parameters/functions

[Carlos555]

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

[Tanis]

Your approach is right. Can you post your full SQL?

[Carlos555]

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.cl aimid
FROM claim_master
WHERE ((Right([claimid],5)="only)"))
GROUP BY claim_master.cl aimid;

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

[NeoPa]

You can use

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?

[Carlos555]

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.

[NeoPa]

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.

[PEB]

Take care my frien on this line:

Try as:

WHERE ((Right([claimid],5)="only"))
:)

[NeoPa]

Good spot PEB, but a mistake :-(

He actually needs the ')' as part of the string he's searching for.

[MMcCarthy]

SELECT IIf(InStr([ClaimID, 'Only'),[claimid],Null) As ClaimOnly
FROM claim_master
WHERE ClaimOnly Is Not Null;

[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?

:)

[PEB]

If not...

The solutions of mcarthy will help you! :)

[Carlos555]

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.:-)

[MMcCarthy]

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.