Senin, 18 Juli 2011

RE: [MS_AccessPros] Re: Combobox not "find as you type" for non numerical

 

Gina & Connie-

The problem is I bet HomeInfo.Nbr is a numeric field, not text. The SQL engine tries to "add" a blank to the number and thus generates an error. When you use + as the concatenation operator on two strings and one of them is a Null, you get a Null string. But if you try to use + to "concatenate" to a blank and the number has a value, Access tries to add and generates an error. Try this:

AddressCity: IIf(IsNull([HomeInfo].[Nbr]), "", [HomeInfo].[Nbr] & " ") & ([HomeInfo].[Direction1] + " ") & ([Street_List].[Street] + " ") & ([HomeInfo].[Direction2] + ", ") & [City_List].[City]

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 Gina Whipp
Sent: Monday, July 18, 2011 10:31 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Re: Combobox not "find as you type" for non numerical

I guess it didn't like my parenthesis! Glad you got it working (pretty or not).

Thanks,
Gina Whipp
2010 - 2011 Microsoft MVP (Access)

www.regina-whipp.com

From: mrsgoudge
Sent: Monday, July 18, 2011 4:02 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Combobox not "find as you type" for non numerical

I have it working now although it's not pretty.

Thanks for all help! I'm pasting the code below in case someone has the time to help me clean it up.

AddressCity: IIf([HomeInfo].[Nbr] Is Not Null,[HomeInfo].[Nbr] & " "+[HomeInfo].[Direction1] & " "+[Street_List].[Street] & " "+[HomeInfo].[Direction2] & ", "+[City_List].[City],IIf([HomeInfo].[Direction1] Is Not Null,[HomeInfo].[Direction1] & " "+[Street_List].[Street] & " "+[HomeInfo].[Direction2] & ", "+[City_List].[City],[Street_List].[Street] & " "+[HomeInfo].[Direction2] & ", "+[City_List].[City]))

--- In mailto:MS_Access_Professionals%40yahoogroups.com, mrsgoudge <no_reply@...> wrote:
>
> I'm getting Error# in all rows for the column.
>
> I pasted this into the field:
> AddressCity: ([HomeInfo].[Nbr] + " ") & ([HomeInfo].[Direction1] + " ") & ([Street_List].[Street] + " ") & ([HomeInfo].[Direction2] + ", ") & [City_List].[City]
>
> Just to check I pasted [HomeInfo].[Nbr] + " " into a column as Expr 1 and got the same--Error# in all the rows for the column. Most of them have an entry for HomeInfo.Nbr.
>
> I'd love to understand why it's not working. Any other ideas?
> Connie
>
> --- In mailto:MS_Access_Professionals%40yahoogroups.com, "Gina Whipp" <classon@> wrote:
> >
> > Hmmm, they are quotations and that is not they way I typed them... let me try again...
> >
> > AddressCity: ([HomeInfo].[Nbr] + " ") & ([HomeInfo].[Direction1] + " ") & ([Street_List].[Street] + " ") & ([HomeInfo].[Direction2] + ", ") & [City_List].[City], ""]
> >
> >
> > Thanks,
> > Gina Whipp
> > 2010 - 2011 Microsoft MVP (Access)
> >
> > www.regina-whipp.com
> >
> > From: mrsgoudge
> > Sent: Monday, July 18, 2011 2:04 PM
> > To: mailto:MS_Access_Professionals%40yahoogroups.com
> > Subject: [MS_AccessPros] Re: Combobox not "find as you type" for non numerical
> >
> >
> > Gina,
> >
> > Thanks for helping. I cut and pasted it into the queries field and got the message: The expression you entered contains invalid syntax.
> >
> > Thanks,
> > Connie
> >
> > --- In mailto:MS_Access_Professionals%40yahoogroups.com, "Gina Whipp" <classon@> wrote:
> > >
> > > Connie,
> > >
> > > Try...
> > >
> > > AddressCity: ([HomeInfo].[Nbr] + “ “) & ([HomeInfo].[Direction1] + “ “) & ([Street_List].[Street] + “ “) & ([HomeInfo].[Direction2] + ", ") & [City_List].[City], ""]
> > >
> > >
> > > Thanks,
> > > Gina Whipp
> > > 2010 - 2011 Microsoft MVP (Access)
> > >
> > > www.regina-whipp.com
> > >
> > > From: mrsgoudge
> > > Sent: Monday, July 18, 2011 1:36 PM
> > > To: mailto:MS_Access_Professionals%40yahoogroups.com
> > > Subject: [MS_AccessPros] Re: Combobox not "find as you type" for non numerical
> > >
> > >
> > > Help! I've figured out what the problem is, but am stymied with fixing it. And I should be able to. The qAddress that the Row Source is based on has a field AddressCity (see below) which is adding a space before the Street whether or not there is a Number preceding the Street.
> > >
> > > In my original coding I used + and & to try to work with not getting extra spaces with info that's not entered. It puts together Number, direction1, street, direction2. We'll always enter a street but sometimes none of the other info.
> > >
> > > How would I change this to show only the street with no extra spaces if street is the only info we have?
> > >
> > > AddressCity: [HomeInfo].[Nbr] & " "+[HomeInfo].[Direction1] & " "+[Street_List].[Street] & " "+[HomeInfo].[Direction2] & ", "+[City_List].[City], ""]
> > >
> > > Thanks!
> > > Connie
> > >
> > > --- In mailto:MS_Access_Professionals%40yahoogroups.com, mrsgoudge <no_reply@> wrote:
> > > >
> > > > Good morning!
> > > >
> > > > I have a combo box HomeInfoID. Its control source is HomeInfoID. Its Row Source is qAddressCity which has three columns: HomeInfoID (bound) and AddressCity (visible) and ParcelID.
> > > >
> > > > When I type an address such as 7 North Court it works. It initialy shows 7 Circle Pines and as I type the N from North it changes.
> > > >
> > > > When "Lot in East River Hills" is typed it does not find as you type it even though it is in the drop down list. I get the message "this is not in the list, do you want to add it?" I have tried using other non numerical items such as Iowa Ave. and it does not find them either.
> > > >
> > > > I have decompiled, compacted and repaired the database.
> > > >
> > > > Included below is the SQL for qAddressCity.
> > > >
> > > > Also included is the record source for the form (qListings)
> > > >
> > > > Any thoughts?
> > > > Connie
> > > >
> > > > qAddressCity
> > > > SELECT qAddressCity.HomeInfoID, qAddressCity.AddressCity, HomeInfo.ParcelNbr
> > > > FROM qAddressCity INNER JOIN HomeInfo ON qAddressCity.HomeInfoID = HomeInfo.HomeInfoID
> > > > ORDER BY qAddressCity.AddressCity;
> > > >
> > > > SELECT DISTINCTROW Listings.ListID, Listings.HomeInfoID, Listings.PropertyName, Listings.ListDate, Listings.ExpDate, Listings.ListingAgency, Listings.OriginalListPrice, Listings.CurrentListing, Listings.OccupancyID, Listings.LockboxNbr, Listings.LotSize, Listings.ZoningID, Listings.HouseTypeID, Listings.Beds, Listings.baths, Listings.Fullbaths, Listings.ThreeQuarterBaths, Listings.Partialbaths, Listings.SquareFoot, Listings.Notes, HomeInfo.ParcelNbr, Listings.TelCombo, Listings.XtraInfo, Listings.YearBuilt, Listings.Rural
> > > > FROM (HomeInfo INNER JOIN (Listings LEFT JOIN ListingContacts ON Listings.[ListID] = ListingContacts.[ListID]) ON HomeInfo.HomeInfoID = Listings.HomeInfoID) LEFT JOIN PotentialListings ON Listings.ListID = PotentialListings.ListID
> > > > WHERE (((Listings.CurrentListing)=-1)) OR (((PotentialListings.ListID)=[Listings].[ListID]));
> > > >
> > >
> > >
> > >
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar