Solved: Query Criteria for "Mid" and "Right"

[akn112]

What im trying to do is make query that checks if the last digits of the entries match. the only thing is, i need it so that it only checks up to and including the first alphabit shown starting from the end. ie:

2341h43 and 234141421532jklj432h43 match
asdfasdfje3 and 234141231231e3 match
e2 and 32e2 match

Is there anyway to do this? I have tried MSDN but havent found anything.

[OBP]

akn, I think what you are trying to do is a bit too complicated for a query, it may be possible for an SQL expert to put together.
It is fairly straightforward to do in Visual Basic.
Let me know if you want me to give that a try.

[Carl A]

I believe you may find what you need here:
http://support.microsoft.com/kb/209045

[OBP]

Carl, the Mid, Right and Trim part is very straightforward, the problem for akn is the fact that the length of the "Text and Number portion" is not the same from Record to Record, so although right(string,3) works for the first record, it won't work for the second record which requires right(string,2).
It is trying to establish just how much of the right section is required for each record makes me think that this is very difficult in a query but fairly easy in VBA where you can use the Instr function or a for/next loop to find the position of the first text character from the right.

[mdmackillop]

I'm venturing in the unknown here, but here's a udf to return the right portion and a query.


Option Compare Database

Function MyR(Data As String)
Dim i As Long
For i = 1 To Len(Data)
MyR = Right(Data, i)
If Not IsNumeric(MyR) Then Exit For
Next
End Function


SELECT Table1.Data1
FROM Table1
WHERE (((MyR([data1]))=MyR([data2])));

[OBP]

mdmackillop, I have learnt something new today, Functions used in Queries!
I didn't know that you could do that, it is something that I will have to explore. :bow:
Thanks :thumb

[mdmackillop]

I knew you could use mid, left etc. so it just seemed worthwhile to try a UDF, which would simplify things greatly. Happy to see it worked!

[OBP]

mdmackillop, I have just tried it with akn's data and as written it doesn't actually work. :dunno
I just didn't realise that you could call a Function from a Queries SQL.
I am sure that the Function part is fine, it is calling it from the SQL that I have the problem with.
Do you do that from an actual query or from a Visual Basic SQL statement?

[mdmackillop]

I'm comparing two fields in the same table. I'm not sure from the OP's post what he is trying to match.

[OBP]

MD, Brilliant, I now have a working example of using a Function called from a query. :friends: :cloud9:

I assumed and tried to make it work for checking from record to record on the SAME field. No wonder mine didn't work. :o:

[akn112]

plz delete this reply *(double post)

[akn112]

wow, thanks everyone! just got bak to work from the weekend and saw all these feedbacks. I will try to implement them into my query. Thanks again!