Rabu, 05 Oktober 2011

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

 

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

__._,_.___
Recent Activity:
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___

Tidak ada komentar:

Posting Komentar