Matching the first 3 letters of the field
in [Help]
|
Prev: long proccess time when sending SQL string to form record source
Next: How to export an Image field from Sql Server to Access 2003
From: nofear on 2 Jul 2008 10:35 I have 2 tables with 1 field each the filed of the fist table has a 3 digit number stored as text and the field of the second table has a 6 digit number stored as text I'd like to select each 6 digit number where the first 3 digits match the field in the first table I'm not sure If I can use the "LIKE"
From: Allen Browne on 2 Jul 2008 11:20 You can use LIKE in the FROM clause, like this: SELECT Table1.* FROM Table2 INNER JOIN Table1 ON Table2.[SomeField] Like Table1.[SomeField] & "*" Access will not be able to display this query graphically (i.e. it's SQL View only), and results will be read-only. An alternative approach (probably more efficient) would be to create a query that uses integer division to lose the least-significant 3 digits from table 2: SELECT [SomeField] \ 1000 AS MyAlias FROM Table2; You could then use that query as in input table, and join it to Table1. This will display graphically. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "nofear" <eurolinux(a)hotmail.com> wrote in message news:66d4f25d-5a29-4aca-9ed5-d8050e4a4d34(a)k37g2000hsf.googlegroups.com... >I have 2 tables with 1 field each > the filed of the fist table has a 3 digit number stored as text > and the field of the second table has a 6 digit number stored as text > > I'd like to select each 6 digit number where the first 3 digits match > the field in the first table > > I'm not sure If I can use the "LIKE"
From: nofear on 2 Jul 2008 12:55
Thx On Jul 2, 11:20 am, "Allen Browne" <AllenBro...(a)SeeSig.Invalid> wrote: > You can use LIKE in the FROM clause, like this: > > SELECT Table1.* > FROM Table2 INNER JOIN Table1 > ON Table2.[SomeField] Like Table1.[SomeField] & "*" > > Access will not be able to display this query graphically (i.e. it's SQL > View only), and results will be read-only. > > An alternative approach (probably more efficient) would be to create a query > that uses integer division to lose the least-significant 3 digits from table > 2: > SELECT [SomeField] \ 1000 AS MyAlias > FROM Table2; > > You could then use that query as in input table, and join it to Table1. This > will display graphically. > > -- > Allen Browne - Microsoft MVP. Perth, Western Australia > Tips for Access users -http://allenbrowne.com/tips.html > Reply to group, rather than allenbrowne at mvps dot org. > > "nofear" <euroli...(a)hotmail.com> wrote in message > > news:66d4f25d-5a29-4aca-9ed5-d8050e4a4d34(a)k37g2000hsf.googlegroups.com... > > >I have 2 tables with 1 field each > > the filed of the fist table has a 3 digit number stored as text > > and the field of the second table has a 6 digit number stored as text > > > I'd like to select each 6 digit number where the first 3 digits match > > the field in the first table > > > I'm not sure If I can use the "LIKE" |