Thank you so much for your quick reply.
Unfortunately, your SQL returned the same number of results as my version. I uploaded my test file (Closing Test.zip) into the Assistance Needed folder. Just for background the table 7-26-11 is a list of Post Office here in the States that are being considered for closure. The table Property is a list of all Post Offices. We are trying to check off on the Property table every Post Office that has been marked for closure. I've been working with MVP's Juan Soto and Ben Clothier but we didn't get much further than this and decided to do it manually. I just can't leave a challenge alone though, so I have kept it at the back of my mind and when I saw a similar post I thought I'd enter the fray.
Isaac
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Isaac-
>
> Yes, it does, but it's not obvious.
>
> SELECT tbl_Property.City, tbl_Property.State, tbl_Property.Zip,
> tbl_Property.StationName, tbl_Property.OnClosingList
> FROM tbl_Property INNER JOIN [7-26-11] ON ([7-26-11].StationName =
> tbl_Property.StationName) AND (tbl_Property.City = [7-26-11].City) AND
> (tbl_Property.State = [7-26-11].ST)
> WHERE InStr(tbl_Property.StationName, [7-26-11].[StationName]) <> 0;
>
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of I
> Sent: Wednesday, October 05, 2011 5:43 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: What Query Builder Function do I Use to Find Equal
> Text Values
>
> I have struggled with a similar task:
>
> Where Property.StationName contains a string like "Bayview Station" and
> [7-26-11].[StationName] contains "Bayview"
>
> I try using something like this:
>
> SELECT tbl_Property.City, tbl_Property.State, tbl_Property.Zip,
> tbl_Property.StationName, tbl_Property.OnClosingList
> FROM tbl_Property INNER JOIN [7-26-11] ON ([7-26-11].StationName =
> tbl_Property.StationName) AND (tbl_Property.City = [7-26-11].City) AND
> (tbl_Property.State = [7-26-11].ST)
> WHERE (((tbl_Property.StationName) Like "*" & [7-26-11].[StationName] & "*"));
>
> I think the problem is just that Access doesn't use LIKE the way SQL Server
> would use CONTAINS so unless you have an exact match you can't query
> successfully even using *
>
> What's interesting is that in datasheet view you can manually enter a CONTAINS
> filter and Access will find the string wherever it is in the field.
>
> Thanks,
>
> Isaac Richter
>
> --- In MS_Access_Professionals@yahoogroups.com, "Graham Mandeno" <graham@>
> wrote:
> >
> > Hi Terry
> >
> > Further to what John said, if you have a problem with leading or trailing
> > spaces, then you can use the Trim() function:
> >
> > WHERE Trim(Table1.Column) = Trim(Table2.Column)
> >
> > If there are trailing spaces that really should not be there then you can
> > Trim() them in an update query:
> >
> > UPDATE Table1 SET Column = Trim(Column) WHERE Column<>Trim(Column);
> >
> > Best wishes,
> > Graham
> >
> > > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
> > > Sent: Sunday, 2 October 2011 06:52
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: RE: [MS_AccessPros] What Query Builder Function do I Use to Find
> > Equal Text Values
> > >
> > >
> > > Terry-
> > >
> > > Clearly, one of the two values doesn't exactly equal the other. Is there a
> > > blank at the end of the string in either table? LIKE should work as long
> > as
> > > you're comparing to the shorter string.
> > >
> > > WHERE Table1.Column LIKE Table2.Column & "*"
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > SQL Queries for Mere Mortals
> > > http://www.viescas.com/
> > > (Paris, France)
> > >
> > > -----Original Message-----
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Terry Olsen
> > > Sent: Saturday, October 01, 2011 6:15 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] What Query Builder Function do I Use to Find
> > Equal Text
> > > Values
> > >
> > > Hi
> > > I have two tables, each having a text column with this value
> > > "P01_V0041_U0000.YC0". Using = returns nothing. Using Like returns more
> > than I
> > > need.
> > > Is it possible to compare two text types?
> > > Thanks
> > > Terryomsn
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
Tidak ada komentar:
Posting Komentar