Re: Comparing first 5 characters

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

From: Dave Redmond (anonymous_at_discussions.microsoft.com)
Date: 06/27/04 

Date: Sun, 27 Jun 2004 09:11:33 -0700

Ken, unfortunately it's giving me "Syntax error in JOIN
operation", and then it highlights the last instance of
[Current Clients].

>-----Original Message-----
>I think I left in an extra ) before the = sign:
>
>SELECT Left([Companies in Redmond Territory].[Company
>Name],5) AS MatchMe
>FROM [Companies in Redmond Territory] INNER JOIN [Current
>Clients] ON Left([Companies in Redmond Territory].
[Company
>Name],5)=Left([Current Clients].[Company],5);
>
>
>--
>
> Ken Snell
><MS ACCESS MVP>
>
>"Dave Redmond" <anonymous@discussions.microsoft.com>
wrote in message
>news:21d6f01c45bc8$fb64f1a0$a101280a@phx.gbl...
>>
>> Ken,
>>
>> When I paste either into the SQL window and click
save, I
>> get a message that says, "Join expression not
supported,"
>> and it highlights Left([Companies in Redmond
Territory].
>> [CompanyName],5).
>>
>> Sorry for the hassle. Any suggestions?
>>
>> Thanks,
>> Dave
>>
>> >-----Original Message-----
>> >OK - slight changes to your SQL (you need to use a
non-
>> equi join query).
>> >Note that you will NOT be able to see this query in
>> design view -- only in
>> >SQL view. So don't try switching to design view after
>> you paste this into
>> >the SQL window and save it.
>> >
>> >
>> >SELECT Left([Companies in Redmond Territory].[Company
>> >Name],5) AS MatchMe
>> >FROM [Companies in Redmond Territory] INNER JOIN
[Current
>> >Clients] ON Left([Companies in Redmond Territory].
>> [Company
>> >Name],5))=Left([Current Clients].[Company],5);
>> >
>> >
>> >
>> >If you want to show both companies' full names:
>> >
>> >SELECT [Companies in Redmond Territory].[Company
>> >Name], [Current Clients].[Company]
>> >FROM [Companies in Redmond Territory] INNER JOIN
[Current
>> >Clients] ON Left([Companies in Redmond Territory].
>> [Company
>> >Name],5))=Left([Current Clients].[Company],5);
>> >
>> >--
>> >
>> > Ken Snell
>> ><MS ACCESS MVP>
>> >
>> >
>> >"Dave Redmond" <anonymous@discussions.microsoft.com>
>> wrote in message
>> >news:21d5c01c45bc4$b625ba60$a101280a@phx.gbl...
>> >> Here's the SQL. Note that the table and field names
are
>> >> not exactly the same as the ones I earlier
mentioned (I
>> >> had simplified them for my example).
>> >>
>> >> SELECT Left([Companies in Redmond Territory].
[Company
>> >> Name],5) AS MatchMe
>> >> FROM [Companies in Redmond Territory] INNER JOIN
>> [Current
>> >> Clients] ON [Companies in Redmond Territory].
[Company
>> >> Name] = [Current Clients].Company
>> >> WHERE (((Left([Companies in Redmond Territory].
[Company
>> >> Name],5))=Left([Current Clients].[Company],5)));
>> >>
>> >> Thanks again for your help,
>> >> Dave
>> >>
>> >>
>> >> >-----Original Message-----
>> >> >Post the SQL of the query. Open the query in design
>> >> view, change to SQL view
>> >> >(see toolbar icon on top left), copy the entire
text
>> >> that you'll see there,
>> >> >and post it here.
>> >> >
>> >> >--
>> >> >
>> >> > Ken Snell
>> >> ><MS ACCESS MVP>
>> >> >
>> >> >"Dave Redmond"
<anonymous@discussions.microsoft.com>
>> >> wrote in message
>> >> >news:21d4e01c45bbf$24e11b30$a101280a@phx.gbl...
>> >> >> Gary and Ken,
>> >> >>
>> >> >> Thanks for the help.
>> >> >>
>> >> >> However, it looks like that expression is still
>> >> returning
>> >> >> only fields that match exactly.
>> >> >>
>> >> >> If one field has "Microsoft Inc." and the other
>> field
>> >> >> has "Microsoft Corp.", it's not catching the
match.
>> My
>> >> >> idea was that by focusing on the first 5
characters,
>> >> the
>> >> >> idea query would see that both fields start
>> >> with "Micro"
>> >> >> and would pull that record.
>> >> >>
>> >> >> What do you think?
>> >> >>
>> >> >> Thanks,
>> >> >> Dave Redmond
>> >> >>
>> >> >>
>> >> >> >-----Original Message-----
>> >> >> >Gary is correct.
>> >> >> >
>> >> >> >--
>> >> >> >
>> >> >> > Ken Snell
>> >> >> ><MS ACCESS MVP>
>> >> >> >
>> >> >> >"Gary Walter" <garylwpleasenospam@wamego.net>
>> wrote in
>> >> >> message
>> >> >> >news:ebqlkS6WEHA.556@tk2msftngp13.phx.gbl...
>> >> >> >> Hi Dave,
>> >> >> >>
>> >> >> >> PMFBI
>> >> >> >>
>> >> >> >> you only need one column
>> >> >> >>
>> >> >> >> Field: MatchMe: Left([Current Clients].
[Company
>> >> 1],5)
>> >> >> >> Table:
>> >> >> >> Sort: N/A
>> >> >> >> Show: unchecked
>> >> >> >> Criteria: Left([Potential Clients].[Company
2],5)
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >> "Dave Redmond" wrote
>> >> >> >> > Fred and Ken,
>> >> >> >> >
>> >> >> >> > Thanks for your help. Here's the Where
clause.
>> >> >> >> >
>> >> >> >> > Query in Design View. I dragged two fields,
one
>> >> from
>> >> >> each
>> >> >> >> > table, down to the lower grid.
>> >> >> >> >
>> >> >> >> > FIRST BOX:
>> >> >> >> > Field: Company 1
>> >> >> >> > Table: Current Clients
>> >> >> >> > Sort: N/A
>> >> >> >> > Show: checked
>> >> >> >> > Criteria: Left([Current Clients]![Company
1],5)
>> >> >> >> >
>> >> >> >> > SECOND BOX:
>> >> >> >> > Field: Company 2
>> >> >> >> > Table: Potential Clients
>> >> >> >> > Sort: N/A
>> >> >> >> > Show: checked
>> >> >> >> > Criteria: Left([Potential Clients]![Company
>> 2],5)
>> >> >> >> >
>> >> >> >> > Ken, is this what you mean by using "the
Left
>> >> >> function on
>> >> >> >> > both of the fields that you're comparing"?
>> >> >> >> >
>> >> >> >> > Any help would be appreciated.
>> >> >> >> >
>> >> >> >> > Thanks,
>> >> >> >> > Dave Redmond
>> >> >> >> >
>> >> >> >> > >-----Original Message-----
>> >> >> >> > >On Fri, 25 Jun 2004 17:32:16 -0700, Dave
>> Redmond
>> >> >> wrote:
>> >> >> >> > >
>> >> >> >> > >> Van,
>> >> >> >> > >>
>> >> >> >> > >> I tried your suggestion. For some reason,
>> the
>> >> Left
>> >> >> ()
>> >> >> >> > >> function only returns company names that
>> have
>> >> less
>> >> >> >> > than 5
>> >> >> >> > >> characters total.
>> >> >> >> > >>
>> >> >> >> > >> For example, it returned a match
>> between "TSI"
>> >> >> >> > and "TSI,"
>> >> >> >> > >> but it did not return a match
>> >> between "Microsoft"
>> >> >> >> > >> and "Microsoft."
>> >> >> >> > >>
>> >> >> >> > >> How can I improve this function to return
>> >> company
>> >> >> >> > names
>> >> >> >> > >> containing the same first 5 characters
but
>> >> which
>> >> >> have
>> >> >> >> > >> longer company names?
>> >> >> >> > >>
>> >> >> >> > >> Thanks,
>> >> >> >> > >> Dave
>> >> >> >> > >>
>> >> >> >> > >>>-----Original Message-----
>> >> >> >> > >>>You can use the Left() function to
truncate
>> the
>> >> >> Field
>> >> >> >> > >> values before
>> >> >> >> > >>>comparing them. For example:
>> >> >> >> > >>>
>> >> >> >> > >>>Left([YourField], 5) will truncate your
>> Field
>> >> >> values
>> >> >> >> > to
>> >> >> >> > >> the first 5
>> >> >> >> > >>>characters.
>> >> >> >> > >>>
>> >> >> >> > >>>--
>> >> >> >> > >>>HTH
>> >> >> >> > >>>Van T. Dinh
>> >> >> >> > >>>MVP (Access)
>> >> >> >> > >>>
>> >> >> >> > >>>
>> >> >> >> > >>>
>> >> >> >> > >>>
>> >> >> >> > >>>"Dave Redmond"
>> >> >> <anonymous@discussions.microsoft.com>
>> >> >> >> > >> wrote in message
>> >> >> >> > >>>news:21e0301c45b11$cf02dee0
>> $a001280a@phx.gbl...
>> >> >> >> > >>>> I'm sure there's a simple answer to
this
>> >> >> question.
>> >> >> >> > I'm
>> >> >> >> > >>>> new to Access.
>> >> >> >> > >>>>
>> >> >> >> > >>>> I have 2 tables, each with a column
>> >> >> titled "Company
>> >> >> >> > >>>> Name." I would like to use a query to
>> compare
>> >> >> the two
>> >> >> >> > >>>> columns and find matches.
>> >> >> >> > >>>>
>> >> >> >> > >>>> However, I only want to compare the
first
>> 5
>> >> >> letters
>> >> >> >> > of
>> >> >> >> > >>>> each company name, because I know that
>> >> >> companies may
>> >> >> >> > be
>> >> >> >> > >>>> spelled a little differently at the
end.
>> >> >> >> > >>>>
>> >> >> >> > >>>> For example, "Microsoft" might appear
in
>> one
>> >> >> table
>> >> >> >> > >>>> and "Microsoft, Inc." might appear in
>> another
>> >> >> table.
>> >> >> >> > >> If I
>> >> >> >> > >>>> force the query to only return exact
>> names, I
>> >> >> won't
>> >> >> >> > be
>> >> >> >> > >>>> able to catch this match.
>> >> >> >> > >>>>
>> >> >> >> > >>>> How do I tell the query to compare only
>> the
>> >> >> first 5
>> >> >> >> > >>>> letters of the fields in each column?
>> >> >> >> > >>>>
>> >> >> >> > >>>> Many thanks,
>> >> >> >> > >>>> Dave Redmond
>> >> >> >> > >>>
>> >> >> >> > >>>
>> >> >> >> > >>>.
>> >> >> >> > >>>
>> >> >> >> > >
>> >> >> >> > >Instead of having us play 20 questions, why
>> don't
>> >> >> you
>> >> >> >> > copy and paste
>> >> >> >> > >the exact Where clause you are using.
>> >> >> >> > >--
>> >> >> >> > >Fred
>> >> >> >> > >Please only reply to this newsgroup.
>> >> >> >> > >I do not reply to personal email.
>> >> >> >> > >.
>> >> >> >> > >
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >> >.
>> >> >> >
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>
>.
>

Relevant Pages

  • Re: Syntax error text in VBA
    ... The first two are not incorrect as VBA is treating them as variables. ... The third is the same but you added a syntax error by having a space before ... so it highlights in red. ... > ActiveSheet e .Range.Select ...
    (microsoft.public.excel.misc)
  • Re: allocate array during run-time
    ... Eclipse ... highlights the reason as such ... ... Syntax error on token "]", ...
    (comp.lang.java.programmer)
  • Re: Syntax error
    ... stops and highlights the entire sql statement. ... Error Syntax Error. ... Try getting into the VBA editor and select Debug... ... Perhaps you could post the entire event code for this event. ...
    (microsoft.public.access.formscoding)