Re: Using LIKE in a JOIN
- From: Jay <Jay@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 23 Jan 2007 12:04:01 -0800
Jason,
I'll have to try it out, thanks. Even if it doesn't work, you helped me
learn something. I wasn't aware you could call a function from a query. I
might play with that a bit more.
Currently, I edited my upload routine to perform the same actions as your
function and added another column to Table1.
Now it'll be stored in the DB. Not a perfect solution, but it seems to work.
Thanks again,
Jay
"Jason Lepack" wrote:
I wrote this function, paste it into a module and save the module to be.
able to use it:
Public Function getNumber(s As String) As Long
Dim x As String
x = s
Do While Not IsNumeric(x)
x = Mid(x, 2)
Loop
getNumber = CLng(x)
End Function
I used this query: (Table 1 and Query 1 are from your definitions in
your original post)
SELECT A.*, B.Creator
FROM (SELECT *, getNumber([REPORT_NUM]) AS X FROM [Table 1]) AS A
INNER JOIN [Query 1] AS B
ON A.X = B.RPT
It may be a bit slow but it will work. See what you think.
Cheers,
Jason Lepack
Jay wrote:
Yeah, Jason's post pointed out the error in my thinking.
What I think the better option will be is to strip the characters from the
beginning of the value and convert to int. Therefore:
RT00029 --> 00029 --> 29
RTP00029 --> 00029 --> 29
RTP00129 --> 00129 --> 129
RT00129 --> 00129 --> 129
The only 'consistent' aspect of the REPORT_NUM is the format is any number
of characters followed by any number of digits. No digits between characters
(or vice versa).
I'm beginning to believe this is too complicated for a SELECT statement.
"John Vinson" wrote:
On Tue, 23 Jan 2007 09:01:00 -0800, Jay
<Jay@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
The problem is, the prefix of REPORT_NUM won't be consistent. That's why I
want to do a substring search within REPORT_NUM, looking for a RPT that's
within it. And I can't make it consistent. The data comes from a separate
system which I can't control.
So it can be ANYWHERE!?
What would you do with a REPORT_NUM of
RPT034730
That contains 34, 47, 73, and 30 - which is the desired substring?
Garbage in, garbage out, I fear!!
Your *examples* all have the creator code as the rightmost two
characters: can you at least count on that? If so,
SELECT <whatever fields from Table1>, Table2.Creator
FROM Table1 INNER JOIN Table2
ON Table2.RPT = Right(Table1.Report_Num, 2);
But if you can't ascertain WHICH two digits in the field provide the
link I think you're out of luck.
John W. Vinson[MVP]
- References:
- Re: Using LIKE in a JOIN
- From: John Vinson
- Re: Using LIKE in a JOIN
- From: Jason Lepack
- Re: Using LIKE in a JOIN
- Prev by Date: Re: Using LIKE in a JOIN
- Next by Date: Re: Append qry only works the second time
- Previous by thread: Re: Using LIKE in a JOIN
- Next by thread: Re: Using LIKE in a JOIN
- Index(es):
Relevant Pages
|
