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

thanks John! I had figured out the numberr problem, but couldn't fix it. I appreciate you cleaning up and shortening my code!

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> 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
>