Query Expression Help!
in [General]
|
Prev: hidding group commands in ribbon and setting password with vb
Next: Running A2003 and A2007 on same machine
From: John W. Vinson on 1 May 2010 22:49 On Sun, 2 May 2010 13:50:14 +1200, "Bob Vance" <rjvance(a)ihug.co.nz> wrote: >Thats excatly what i would like to see maybe like this >"Alpo" HorseName "Mo,Fine,Howard" OwnerLastName >I have a query that may be tweaked below > >SELECT tblHorseDetails.HorseID, tblHorseDetails.OwnerID, >funGetHorse(0,tblHorseInfo.HorseID,False) AS Name, >tblOwnerInfo.OwnerLastName >FROM (tblHorseDetails INNER JOIN tblHorseInfo ON tblHorseDetails.HorseID = >tblHorseInfo.HorseID) INNER JOIN tblOwnerInfo ON tblHorseDetails.OwnerID = >tblOwnerInfo.OwnerID; > If you download fConcatChild from the website, put it into a new module, compile it and save it, you should be able to use SELECT tblHorseDetails.HorseID, tblHorseDetails.OwnerID, funGetHorse(0,tblHorseInfo.HorseID,False) AS Name, fConcatChild("tblOwnerInfo", "OwnerID", "OwnerLastName", "Long", tblHorseDetails.OwnerID) FROM tblHorseDetails INNER JOIN tblHorseInfo ON tblHorseDetails.HorseID = tblHorseInfo.HorseID; Untested air code of course! -- John W. Vinson [MVP]
From: Bob Vance on 1 May 2010 23:03 > Thanks John, > Thats excatly what i would like to see maybe like this > "Alpo" HorseName "Mo,Fine,Howard" OwnerLastName > I have a query that may be tweaked below > > SELECT tblHorseDetails.HorseID, tblHorseDetails.OwnerID, > funGetHorse(0,tblHorseInfo.HorseID,False) AS Name, > tblOwnerInfo.OwnerLastName > FROM (tblHorseDetails INNER JOIN tblHorseInfo ON tblHorseDetails.HorseID = > tblHorseInfo.HorseID) INNER JOIN tblOwnerInfo ON tblHorseDetails.OwnerID = > tblOwnerInfo.OwnerID; > > John I have created this and it works but i get Error 3075 Syntax Error (Missing Operator) In query Expression HorseID= but after oking it 7 times the query is correct? Regards Bob SELECT DISTINCT tblHorseDetails.HorseID, ConcatRelated("OwnerID","tblHorseDetails","HorseID = " & [HorseID]) AS MyClientList FROM tblHorseDetails;
From: John W. Vinson on 2 May 2010 01:28 On Sun, 2 May 2010 15:03:21 +1200, "Bob Vance" <rjvance(a)ihug.co.nz> wrote: > >> Thanks John, >> Thats excatly what i would like to see maybe like this >> "Alpo" HorseName "Mo,Fine,Howard" OwnerLastName >> I have a query that may be tweaked below >> >> SELECT tblHorseDetails.HorseID, tblHorseDetails.OwnerID, >> funGetHorse(0,tblHorseInfo.HorseID,False) AS Name, >> tblOwnerInfo.OwnerLastName >> FROM (tblHorseDetails INNER JOIN tblHorseInfo ON tblHorseDetails.HorseID = >> tblHorseInfo.HorseID) INNER JOIN tblOwnerInfo ON tblHorseDetails.OwnerID = >> tblOwnerInfo.OwnerID; >> >> >John I have created this and it works but i get >Error 3075 Syntax Error (Missing Operator) In query Expression HorseID= >but after oking it 7 times the query is correct? >Regards Bob > >SELECT DISTINCT tblHorseDetails.HorseID, >ConcatRelated("OwnerID","tblHorseDetails","HorseID = " & [HorseID]) AS >MyClientList >FROM tblHorseDetails; > Since you're using a different function and a different query from what I suggested I really cannot comment. Do you have a function named ConcatRelated in a Module? If you type Ctrl-G and type ?ConcatRelated("OwnerID","tblHorseDetails","HorseID = 333") using a valid HorseID in the Immediate window, do you get the names you expect? -- John W. Vinson [MVP]
From: Bob Vance on 2 May 2010 01:37 >> > > If you download fConcatChild from the website, put it into a new module, > compile it and save it, you should be able to use > > SELECT tblHorseDetails.HorseID, tblHorseDetails.OwnerID, > funGetHorse(0,tblHorseInfo.HorseID,False) AS Name, > fConcatChild("tblOwnerInfo", "OwnerID", "OwnerLastName", "Long", > tblHorseDetails.OwnerID) > FROM tblHorseDetails INNER JOIN tblHorseInfo ON tblHorseDetails.HorseID = > tblHorseInfo.HorseID; > > Untested air code of course! > -- > > John W. Vinson [MVP] Thanks John It is showing My Horses and Owners but it is not putting my owners on the same line , I am getting multiple records for my same Horse with different Owners.........Regards Bob I have fConcatChild in a Module [basConatChild]
From: Bob Vance on 2 May 2010 01:57
"John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message news:t63qt5d3nikt2k189fpg1vlcf6vpdim733(a)4ax.com... > On Sun, 2 May 2010 15:03:21 +1200, "Bob Vance" <rjvance(a)ihug.co.nz> wrote: > >> >>> Thanks John, >>> Thats excatly what i would like to see maybe like this >> > > Since you're using a different function and a different query from what I > suggested I really cannot comment. Do you have a function named > ConcatRelated > in a Module? If you type Ctrl-G and type > > ?ConcatRelated("OwnerID","tblHorseDetails","HorseID = 333") > > using a valid HorseID in the Immediate window, do you get the names you > expect? > -- > > John W. Vinson [MVP] Thanks John that was an earlier attempt I am using your code and function in a module I did that test and got a Compile Error Expected:= Thanks Bob |