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
>
Tidak ada komentar:
Posting Komentar