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 MS_Access_Professionals@yahoogroups.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]));
>
Tidak ada komentar:
Posting Komentar