Isaac-
Ah, I didn't notice that you had included a join on StationName, which nullifies
the WHERE clause. Try 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 (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));
That returns 3,447 rows.
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 6:39 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: What Query Builder Function do I Use to Find Equal
Text Values
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
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar