ACCESS FEED: [MS_AccessPros] Re: What Query Builder Function do I Use to Find Equal Text Values

Thank you, both.

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Duane is correct. The combination of City and State in the two tables is not
> unique in either, so you get the Cartesian Product across city and state, which
> will not be updatable.
>
> 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 Duane Hookom
> Sent: Wednesday, October 05, 2011 11:51 PM
> To: Access Professionals Yahoo Group
> Subject: RE: [MS_AccessPros] Re: What Query Builder Function do I Use to Find
> Equal Text Values
>
>
> Are City and ST unique/primary key fields in the table [7-26-22]? Typically a
> query with more than one table should be joined on primary/foreign key fields in
> order to be updateable.
>
> Duane Hookom
> MS Access MVP
>
>
>
>
> To: MS_Access_Professionals@yahoogroups.com
> From: yrichter@...
> Date: Wed, 5 Oct 2011 20:47:57 +0000
> Subject: [MS_AccessPros] Re: What Query Builder Function do I Use to Find Equal
> Text Values
>
>
>
>
>
>
> John,
>
> Any reason why the resulting query is not updatable?
>
> Isaac
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > 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
> >
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>