Re: need help..(WHERE.....OR (Right(original_missing.field2,6) = R

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

From: John Spencer (MVP) (spencer4_at_comcast.net)
Date: 01/28/05 

Date: Thu, 27 Jan 2005 19:46:10 -0500

Try surrounding the tablename (27049) with square brackets. Other than that I
don't see where I missed something.

helpmeplease wrote:
>
> it's saying that i have a syntax error when i go to save either of those
> queries. the first is in the from statement the second in the where statement.
>
> "John Spencer (MVP)" wrote:
>
> > If you are joining on the fields, then you won't get any records that are a
> > partial match. You can use a non-equi join.
> >
> > SELECT original_missing.field1, original_missing.field2,
> > original_missing.field3, original_missing.field4, [27049].reference_num,
> > original_missing.field6, [27049].participation_date, original_missing.field8,
> > original_missing.field9, original_missing.field10, original_missing.field11,
> > original_missing.field12, original_missing.field13, [27049].program_status,
> > original_missing.field15, original_missing.field16, original_missing.field17,
> > original_missing.field18, original_missing.field19, original_missing.field20,
> > original_missing.field21, original_missing.field22, original_missing.field23
> > FROM original_missing INNER JOIN 27049
> > ON original_missing.field2 = 27049.loan_number OR
> > Right(original_missing.field2,6) = Right(27049.loan_number,6)
> >
> > OR DROP the JOIN
> >
> > SELECT original_missing.field1, original_missing.field2,
> > original_missing.field3, original_missing.field4, [27049].reference_num,
> > original_missing.field6, [27049].participation_date, original_missing.field8,
> > original_missing.field9, original_missing.field10, original_missing.field11,
> > original_missing.field12, original_missing.field13, [27049].program_status,
> > original_missing.field15, original_missing.field16, original_missing.field17,
> > original_missing.field18, original_missing.field19, original_missing.field20,
> > original_missing.field21, original_missing.field22, original_missing.field23
> > FROM original_missing , 27049
> > WHERE ((original_missing.field2 = 27049.loan_number) OR
> > (Right(original_missing.field2,6) = Right(27049.loan_number,6)));
> >
> > helpmeplease wrote:
> > >
> > > i have a list of numbers in one table (along with other columns), and another
> > > list of numbers (and other columns) in another table. i want to design a
> > > query that pulls these fields together where the one id number and the other
> > > are equal or one is equal to the right 6 numbers of the other (numbers in the
> > > one table MAY have a "prefix" of 0100 or 0300 before the id number) my
> > > example query is below. Thanks in advance for any help you can give me.
> > >
> > > SELECT original_missing.field1, original_missing.field2,
> > > original_missing.field3, original_missing.field4, [27049].reference_num,
> > > original_missing.field6, [27049].participation_date, original_missing.field8,
> > > original_missing.field9, original_missing.field10, original_missing.field11,
> > > original_missing.field12, original_missing.field13, [27049].program_status,
> > > original_missing.field15, original_missing.field16, original_missing.field17,
> > > original_missing.field18, original_missing.field19, original_missing.field20,
> > > original_missing.field21, original_missing.field22, original_missing.field23
> > > FROM original_missing INNER JOIN 27049 ON original_missing.field2 =
> > > [27049].loan_number
> > > WHERE ((original_missing.field2 = 27049.loan_number) OR
> > > (Right(original_missing.field2,6) = Right(27049.loan_number,6)));
> >

Relevant Pages

  • Re: SQL Server Syntax error or access violation puzzle in VB
    ... I tried the square brackets idea - it didn't work. ... >> You need to use the syntax INSERT INTO tablename, ... >>> I've tried connecting with ODBC and ADODB connection strings. ...
    (microsoft.public.vb.database.ado)
  • Subject Line Tags Re: /whatever/ topic topic
    ... misc.writing I see little Towse saying: ... Why not square brackets? ...
    (misc.writing)
  • Re: Access 2000 with ADOTable error - Unspecified error
    ... is the table name called 'session' a key word in Delphi? ... tablename between square brackets ... Do a "Compact and Repair" of the database within Access in case any of the internal structures have become corrupt. ...
    (borland.public.delphi.database.ado)
  • Re: dentalcom.net - smd gateway
    ... Are you saying that putting square brackets and ... "img" around a link will shield it from being stripped from the various ... I tried holding down the Windoze key and typing "" but ...
    (sci.med.dentistry)