Re: Using LIKE in a JOIN

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Unfortunately, no. I'd try a Mid if that were the case.

It's a variable number of characters [a-zA-Z], followed by a variable number
of digits [0-9].

The only constant is that there will never be a digit between characters (or
vice versa).

I thought maybe using InStr, but I don't know if you could have the search
string as a wildcard like, "a non-digit". It's failed for me so far.

Jay

"Jason Lepack" wrote:

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

Cheers,
Jason Lepack

Jay wrote:
Hi Jason,

Good point. Output would be:

REPORT_NUM CREATOR
RT00029 Customer
RTP00029 Customer
RTP00129
RT00129

But your question has highlighted a flaw in my idea. I would like the 129
to be skipped. But to do that, I couldn't do a substring search. At least I
don't think so.

Maybe I should try a different route.

Could I strip the letters off the REPORT_NUM values and then conver to Int()?
RT00029 --> 00029 --> 29
RTP00029 --> 00029 --> 29
RTP00129 --> 00129 --> 129
RT00129 --> 00129 --> 129

I'm most concerned about the number part of REPORT_NUM. The prefix of the
REPORT_NUM value can be ignored. Could this be done in a simple query in
Access? I can't see a way to do that without writing a function, and use
recordsets to loop through all the values. Sees time-consuming, but I might
be asking too much for a query.

I'll keep trying and give it some thought.

Thanks,
Jay

"Jason Lepack" wrote:

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?

Jay wrote:
Hello all,

I'm trying to achieve a join, and I'm having no luck. I'm hoping someone
can help me out. I'm hoping my spacing for the column examples will work.

Values:
Table 1
REPORT_NUM various columns...
RPT00011
RT00029
RTP00030
REP00034
RPT00047

Query 1
RPT CREATOR
11 Vendor
29 Customer
30 Employee
47 Vendor


Problem:
I need to look through Table1.REPORT_NUM and Query1.RPT. If Query1.RPT is
found within Table1.REPORT_NUM, then display REPORT_NUM (and additional
columns from Table1) and CREATOR

Output would look like:
REPORT_NUM various columns... CREATOR
RPT00011 Vendor
RT00029 Customer
RTP00030 Employee
REP00034
RPT00047 Vendor

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.

I've tried various LEFT JOIN queries, but I can't find anything that works.
If I have to do a sub-query and join the two qeuries, I'm fine with that.
I'm open to any suggestions. I'll answer any additional questions best I can.

Any help is appreciated.

Thanks,
Jay




.

Relevant Pages

  • Re: Using LIKE in a JOIN
    ... Jay wrote: ... be asking too much for a query. ... "Jason Lepack" wrote: ... columns from Table1) and CREATOR ...
    (microsoft.public.access.queries)
  • Re: Using LIKE in a JOIN
    ... "Jason Lepack" wrote: ... Jay wrote: ... be asking too much for a query. ... columns from Table1) and CREATOR ...
    (microsoft.public.access.queries)
  • Re: Conditional Count
    ... ElectronicVolumeMonthly WHERE ElectronicVolumeMonthly.PnL> 0) AS Winners ... "Jason Lepack" wrote: ... PnL and risk associated wtih each ... In my query, I am grouping by occ_acro and already have built in the ...
    (microsoft.public.access.queries)
  • Re: Problems with Duplicate info.
    ... "Jason Lepack" wrote: ... Enter this query in using the SQL View of the Query Designer. ... GROUP BY Container) AS C ... driversLicenseNumber - Text - PK ...
    (microsoft.public.access.queries)
  • Re: Problems with Duplicate info.
    ... SELECT ID FROM tblCMI AS B ... GROUP BY Container) AS C ... "Jason Lepack" wrote: ... Enter this query in using the SQL View of the Query Designer. ...
    (microsoft.public.access.queries)