Sorry for the late response... You know its the season. I did remove the WHERE statement and now it is bring back all my address records but no error which is a bonus. What is filter it to the correct contact record? As far as SQL Server is concern, I have only moved over tables that I am using such as address, notes, and restructured my student table (which would be your contact table to bring in some fields from your contact table) and there associated lookup tables. This has been the only glitch that I have ran into so far.
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Anyone who claimed that old age had brought them patience was either lying or senile."
From: "Crystal strive4peace2008@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, December 19, 2014 11:36 PM
Subject: Re: [MS_AccessPros] Re: Contact Database
Hi Art,
If you change the RowSource for many of the comboboxes, the SQL statement must be stored in the control TAG property -- this is used when the SQL is changed to add criteria. I have not tested it on SQL Server.
The RowSource for the find Address listbox is changed each time the contact changes. When I deployed it, apparently I left 759 in there for CID, which was an oversight on my part but shouldn't cause an issue. Perhaps edit the RowSource to remove that criteria.
I would be interested in connecting to you sometime and getting it to work with SQL Server. Email me.
thanks, Art
Warm Regards,
Crystal
*
(: have an awesome day :)
*
On Friday, December 19, 2014 4:46 PM, "Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Sorry I should have added a subject.
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Anyone who claimed that old age had brought them patience was either lying or senile."
From: Art Lorenzini <dbalorenzini@yahoo.com>
To: "Crystal strive4peace2008@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
Sent: Friday, December 19, 2014 5:41 PM
Subject:
Sorry to bother you but I have been working with parts of your Contact database and I have ran into an issue. I have ported the backend to SQL Server but I ran into an issue with fnd_AdrID listbox. When I run the record source:
SELECT qCart.CID AS CIDq, qCart.TypIDadr, Adr.CID, Adr.AdrID, IIf(Nz([Adr].[TypIDadr],9)<>0,"","*") & IIf(IsNull([Adr].[CID]),"","*") & [AdrTy].[TypAdr] AS [Address Type], [Ctry] & (" "+[St]) & (", "+[City]) & (", "+[zip]) & (", "+[Addr1]) & (", "+[Addr2]) & (" -- "+[Area]) & (" ("+[adrNote]+")") AS Address
FROM (SELECT C.CID, AdrTy.TypIDadr, AdrTy.TypAdr FROM c_AdrType AS AdrTy, c_Contact as C) AS qCart LEFT JOIN c_Address AS Adr ON (qCart.TypIDadr = Adr.TypIDadr) AND (qCart.CID = Adr.CID)
WHERE qCart.CID = 759 ORDER BY IIf(Nz([Adr].[TypIDadr],9)<>0,"","*") & IIf(IsNull([Adr].[CID]),"","*") & [AdrTy].[TypAdr], IIf(Nz([Adr].[TypIDadr],9)<>0,"","*") & IIf(IsNull([Adr].[CID]),"","*") & [AdrTy].[TypAdr], Adr.Ctry, Adr.St, Adr.City, Adr.Addr1;
FROM (SELECT C.CID, AdrTy.TypIDadr, AdrTy.TypAdr FROM c_AdrType AS AdrTy, c_Contact as C) AS qCart LEFT JOIN c_Address AS Adr ON (qCart.TypIDadr = Adr.TypIDadr) AND (qCart.CID = Adr.CID)
WHERE qCart.CID = 759 ORDER BY IIf(Nz([Adr].[TypIDadr],9)<>0,"","*") & IIf(IsNull([Adr].[CID]),"","*") & [AdrTy].[TypAdr], IIf(Nz([Adr].[TypIDadr],9)<>0,"","*") & IIf(IsNull([Adr].[CID]),"","*") & [AdrTy].[TypAdr], Adr.Ctry, Adr.St, Adr.City, Adr.Addr1;
I get the following message:
[Microsoft][SQL Server Native Client 11.0][SQL Server]
The multi-part identifier "AdrTy.TypIDadr" could not be bound. (#4104) The multi-part identifier "AdrTy.TypAdr" could not be bound. (#4104) The multi-part identifier "AdrTy.TypIDadr" could not be bound.
t must be SQL Server coughing on it because it runs fine your sample. Any ideas?
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Anyone who claimed that old age had brought them patience was either lying or senile."
__._,_.___
Posted by: Art Lorenzini <dbalorenzini@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar