field lengths ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pat Backowski - 17 Oct 2006 14:00 GMT

Hi Everyone,

Table A
data= 20text
Table B
Key=4text
Data= doesn't matter

Due to modifications beyond my control, I need to select data from table B,
which has a key field length of 4(text) using the first 4 characters of a
field in table A which has a field length 20(text). When I use something
like "Select tableB.data where tableB.key = tableA.data, it doesn't
work(tableA.data = "1234")

Any and all help appreciated.
Pat

Reply to this Message

Brendan Reynolds - 17 Oct 2006 14:12 GMT

SELECT tableB.data FROM tableB INNER JOIN tableA ON Left$(tableB.data, 4) =
tableA.data

You can't create a non-equi-join like this in the graphical query designer's
Design View, but you can create a query using an equi-join (tableB.data =
tableA.data) then switch to SQL View to edit it.

Signature

Brendan Reynolds
Access MVP

> Hi Everyone,
>
[quoted text clipped - 13 lines]
> Any and all help appreciated.
> Pat

Reply to this Message

Pat Backowski - 17 Oct 2006 14:20 GMT

Thanks, Brendan....that's what I neded.

> SELECT tableB.data FROM tableB INNER JOIN tableA ON Left$(tableB.data, 4) =
> tableA.data
[quoted text clipped - 20 lines]
> > Any and all help appreciated.
> > Pat

Reply to this Message