Developer Forum

The question is what would happen if there was:

REPORT_NUM various columns...
RT00029
RTP00029
RTP00129
RT00129

Query 1
RPT CREATOR
29 Customer

what would the expected output be in this situation?

o the report_num items always have some letters and then a 5 digit
number?

Cheers,
Jason Lepack

Jay wrote:

On Tue, 23 Jan 2007 09:01:00 -0800, Jay



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]

forgot to mention, I'm using Access 97. That might limit my options.

"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

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

Here's a more direct answer to the question, "Using LIKE in a JOIN".

I note you are using Access 97 and the following is ANSI-92 Query Mode
syntax. However, the same principle should be possible in Access 97 if
you substitute the ANSI-89 Query Mode wildcard character * in place of
%; also, I'm not sure if Jet 3.n supports derived tables, in which case
you may need to persist the SQL definition of the derived table (DT1)
in a Query object:

CREATE TABLE Table1 (
REPORT_NUM VARCHAR(8) NOT NULL UNIQUE
)
;
INSERT INTO Table1 (REPORT_NUM)
VALUES ('RPT00011')
;
INSERT INTO Table1 (REPORT_NUM)
SELECT TEMP.REPORT_NUM FROM
(
SELECT 'RT00029' AS REPORT_NUM
FROM Table1
UNION ALL
SELECT 'RTP00030'
FROM Table1
UNION ALL
SELECT 'REP00034'
FROM Table1
UNION ALL
SELECT 'RPT00047'
FROM Table1
) AS TEMP
;
CREATE TABLE Query1 (
RPT CHAR(2) NOT NULL UNIQUE,
CREATOR VARCHAR(12) NOT NULL
)
;
INSERT INTO Query1 (RPT, CREATOR)
VALUES ('11', 'Vendor')
;
INSERT INTO Query1 (RPT, CREATOR)
SELECT TEMP.RPT, TEMP.CREATOR
FROM (
SELECT '29' AS RPT, 'Customer' AS CREATOR
FROM Query1
UNION ALL
SELECT '30', 'Employee'
FROM Query1
UNION ALL
SELECT '47', 'Vendor'
FROM Query1
) AS TEMP
;

Proposed ANSI-92 solution:

SELECT T1.REPORT_NUM, DT1.CREATOR
FROM Table1 AS T1
LEFT JOIN
(
SELECT T2.REPORT_NUM, Q1.RPT, Q1.CREATOR
FROM Table1 AS T2, Query1 AS Q1
WHERE T2.REPORT_NUM LIKE ('%' & Q1.RPT)
) AS DT1
ON T1.REPORT_NUM = DT1.REPORT_NUM
;

Alternative proposed ANSI-89 solution (untested): with the following
persisted a Query object named View1:

SELECT T2.REPORT_NUM, Q1.RPT, Q1.CREATOR
FROM Table1 AS T2, Query1 AS Q1
WHERE T2.REPORT_NUM LIKE ('*' & Q1.RPT)
;

use View1 in a separate query:

SELECT T1.REPORT_NUM, DT1.CREATOR
FROM Table1 AS T1
LEFT JOIN View1 AS V1
ON T1.REPORT_NUM = V1.REPORT_NUM
;

Jamie.

Oops! Typo in that last query. Should be:

SELECT T1.REPORT_NUM, V1.CREATOR
FROM Table1 AS T1
LEFT JOIN View1 AS V1
ON T1.REPORT_NUM = V1.REPORT_NUM
;

Jamie.