 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2006Help with Query!
This is what the Query code is and What is between the stars ** is what I would like added to it......Thanks Bob SELECT tblHorseInfo.HorseID, funGetHorse(0,tblHorseInfo.HorseID,false) AS Name, [StableReturnDate] FROM tblHorseInfo WHERE Status LIKE 'Active*' ORDER BY funGetHorse(0,tblHorseInfo.HorseID,true), funGetHorse(0,tblHorseInfo.HorseID,false); **And if the Horse is in TblInvoice_ItMdl dont show his name*** Thanks in advance.........Bob Vance Try this SELECT tblHorseInfo.HorseID, funGetHorse(0,tblHorseInfo.HorseID,false) AS Name, [StableReturnDate] FROM tblHorseInfo WHERE Status LIKE 'Active*' AND tblHorseInfo.HorseID NOT IN (SELECT HorseID FROM TblInvoice_ItMdl) ORDER BY funGetHorse(0,tblHorseInfo.HorseID,true), funGetHorse(0,tblHorseInfo.HorseID,false); No Horses showed up ! Bob > Try this > [quoted text clipped - 7 lines] > ORDER BY funGetHorse(0,tblHorseInfo.HorseID,true), > funGetHorse(0,tblHorseInfo.HorseID,false); Maybe all the horses in the tblHorseInfo are already in the TblInvoice_ItMdl. Try to verify this,please run these two query seperately and compare the result. SELECT tblHorseInfo.HorseID, funGetHorse(0,tblHorseInfo.HorseID,false) AS Name, [StableReturnDate] FROM tblHorseInfo WHERE Status LIKE 'Active*' ORDER BY funGetHorse(0,tblHorseInfo.HorseID,true), funGetHorse(0,tblHorseInfo.HorseID,false); and SELECT HorseID FROM TblInvoice_ItMdl Arthur I put both indivualy into sql window the first code gave me the same as it shows now and the 2nd code showed no horses , thanks Bob > Maybe all the horses in the tblHorseInfo are already in the > TblInvoice_ItMdl. [quoted text clipped - 13 lines] > > SELECT HorseID FROM TblInvoice_ItMdl Query1 ---------------------------------------------------------------------------------------------------------- SELECT tblHorseInfo.HorseID, funGetHorse(0,tblHorseInfo.HorseID,false) AS Name, [StableReturnDate] FROM tblHorseInfo WHERE Status LIKE 'Active*' AND tblHorseInfo.HorseID NOT IN (SELECT HorseID FROM TblInvoice_ItMdl) ORDER BY funGetHorse(0,tblHorseInfo.HorseID,true), Query2 ---------------------------------------------------------------------------------------------- SELECT tblHorseInfo.HorseID, funGetHorse(0,tblHorseInfo.HorseID,false) AS Name, [StableReturnDate] FROM tblHorseInfo WHERE Status LIKE 'Active*' ORDER BY funGetHorse(0,tblHorseInfo.HorseID,true), funGetHorse(0,tblHorseInfo.HorseID,false); Query3 ---------------------------------------------------------------------------------------------------------- SELECT HorseID FROM TblInvoice_ItMdl If Query3 doesnt returned any record then Query1 should returned same record as Query 2, but you said Query1 showed no horses. Quite strange. Can u please provide the structure of your tables, like the fields of each table so i can figure it out. Arthur Query 1 gave me a Syntax Error in ORDER BY clause......... Query 2 & 3 just showed all horses in Active mode, How do you want me to show you structure of table? Thanks Bob > Query1 > ---------------------------------------------------------------------------------------------------------- [quoted text clipped - 28 lines] > each > table so i can figure it out. Try double quotes. "Active*" SELECT tblHorseInfo.HorseID, funGetHorse(0,tblHorseInfo.HorseID,false) AS Name, [StableReturnDate] FROM tblHorseInfo WHERE Status LIKE "Active*" ORDER BY funGetHorse(0,tblHorseInfo.HorseID,true), funGetHorse(0,tblHorseInfo.HorseID,false); Probably dead or not having fun horses. :) Still showing the horses that are in this table......Thanks BobtblInvoice_ItMdt IntermediateID dtDate HorseID HorseName FatherName MotherName DateOfBirth HorseDetailInfo Sex SubTotal TotalAmount GSTOptionsText GSTOptionsValue 1 05/01/06 24 Belle Of Marju 23/06/2005 ---- 0 yo -- Filly Filly $1,477.00 $1,661.62 Plus Tax $184.62 2 01/05/06 10 Delay No More Woodbough Kowtow 24/06/2005 Woodbough--Kowtow-- 1 yo -- Geld Geld $1,705.00 $1,918.12 Plus Tax $213.12 4 01/05/06 22 Kollegi Anziyan Learnalot 25/06/2005 Anziyan--Learnalot-- 1 yo -- Geld Geld $1,705.00 $1,918.12 Plus Tax $213.12 5 01/05/06 18 My Lips Ar Sealed Maroof It's a secret 22/06/2005 Maroof--It's a secret-- 1 yo -- Geld Geld $1,390.00 $1,563.75 Plus Tax $173.75 6 01/05/06 7 Restitution Daggers Drawn Kaska 23/06/2005 Daggers Drawn--Kaska-- 1 yo -- Geld Geld $2,007.00 $2,257.88 Plus Tax $250.88 7 01/05/06 1 Kazarokin Anziyan Pianosa 1/08/2001 Anziyan--Pianosa-- 5 yo -- Geld Geld $1,452.00 $1,633.50 Plus Tax $181.50 8 05/12/06 27 Desert Fox--Zivania-- 2 yo--Colt Desert Fox Zivania 8/01/2004 Desert Fox--Zivania-- 2 yo -- Colt Colt $575.00 $646.88 Plus Tax $71.88 9 22/05/06 12 Sand Hawk Sandtrap Majestic Hunter 25/06/2005 Sandtrap--Majestic Hunter-- 1 yo -- Geld Geld $43.00 $48.38 Plus Tax $5.38 > Try double quotes. "Active*" > [quoted text clipped - 6 lines] > > Probably dead or not having fun horses. :) Oops Sorry Table name, tblInvoice_ItMdt Thanks Bob > Still showing the horses that are in this table......Thanks > BobtblInvoice_ItMdt IntermediateID dtDate HorseID HorseName FatherName [quoted text clipped - 32 lines] >> >> Probably dead or not having fun horses. :) May i see also the tblHorseInfo tblHorseInfo HorseID HorseName MotherName FatherName Sex DateOfBirth Race HorsePrice StableReturnDate Remark Status Worksheet 1 Kazarokin Pianosa Anziyan Geld 2001 $20,000.00 29/04/2006 Active Yes 2 Itza Beel Anziyan Geld 2002 $0.00 30/04/2006 Finished No 3 Milena Tiana Stravinski Filly 2001 $0.00 Finished No 4 Colombian Princess Isabella Columbia Filly 2001 $0.00 Finished No 5 Panza Anne McGinty Geld 2001 $0.00 Finished No 6 Zipperup O'Riley Geld 2002 $0.00 18/09/2005 Finished No 7 Restitution Kaska Daggers Drawn Geld 2001 $13,000.00 23/01/2006 Active Yes 8 Desert Queen Queens Honour Daggers Drawn Filly 2002 $0.00 16/05/2005 Finished No 9 Tee Gee Blue Reality Anziyan Geld 2002 $0.00 18/09/2005 Finished No 10 Delay No More Kowtow Woodbough Geld 2002 $0.00 18/09/2005 Active Yes 11 Mulroy Bay Champagne On Ice Anziyan Geld 2000 $18,000.00 28/03/2005 Finished No 12 Sand Hawk Majestic Hunter Sandtrap Geld 2003 $20,000.00 16/03/2006 Finished No 13 Tiara Sands Kara Kum Sandtrap Filly 2003 $8,500.00 16/03/2006 Finished Yes 14 Resolve Columbia Geld 2003 $19,000.00 Finished No 15 Zivania Johan Cruyff Geld 2003 $0.00 Finished No 16 Snow Goddess Quorum Filly 2002 $0.00 Finished No 17 Cut And Trust Sister Mary Daggers Drawn Geld 2002 $0.00 4/06/2005 Finished No 18 My Lips Ar Sealed It's a secret Maroof Geld 2000 $0.00 18/09/2005 Active Yes 19 Providence Bay 2000 $0.00 21/06/2005 Finished No 22 Kollegi Learnalot Anziyan Geld 2003 $14,000.00 16/03/2006 Active Yes 24 Belle Of Marju Filly 2001 $0.00 1/04/2006 Active Yes 27 Zivania Desert Fox Colt 2004 $0.00 Active Yes > May i see also the tblHorseInfo SELECT tblHorseInfo.HorseID, funGetHorse(0,tblHorseInfo.HorseID,false) AS Name, [StableReturnDate] FROM tblHorseInfo WHERE Status LIKE 'Active*' AND tblHorseInfo.HorseID NOT IN (SELECT HorseID FROM TblInvoice_ItMdl) ORDER BY funGetHorse(0,tblHorseInfo.HorseID,true), funGetHorse(0,tblHorseInfo.HorseID,false); Note: All of your horses with status "Active" in the tblHorseInfo are already in tblInvoice_ItMdt that's why you will get 0 record. the tbl I tried putting other horses in active mode, that where not in TblInvoice_ItMdl but still no horses are showing..thanks Bob > SELECT tblHorseInfo.HorseID, funGetHorse(0,tblHorseInfo.HorseID,false) > AS [quoted text clipped - 12 lines] > > the tbl Might have something to do with qryOrderByHorseName because I am getting a >I tried putting other horses in active mode, that where not in >TblInvoice_ItMdl but still no horses are showing..thanks Bob [quoted text clipped - 14 lines] >> >> the tbl qryOrderByHorseName HorseID Name StableReturnDate 27 Desert Fox--Zivania-- 1 yo--Colt 24 Belle Of Marju 1/04/2006 10 Delay No More 18/09/2005 1 Kazarokin 29/04/2006 22 Kollegi 16/03/2006 18 My Lips Ar Sealed 18/09/2005 7 Restitution 23/01/2006 > Might have something to do with qryOrderByHorseName because I am getting a >>I tried putting other horses in active mode, that where not in [quoted text clipped - 15 lines] >>> >>> the tbl This actually lists the horses by alphabetical order, Unnamed horses first > qryOrderByHorseName HorseID Name StableReturnDate > 27 Desert Fox--Zivania-- 1 yo--Colt [quoted text clipped - 25 lines] >>>> >>>> the tbl It works here.i tried to set some horses in active status that were not in the TblInvoice_ItMdl and it showed here. OK Arthur I know now I can get your code to save, what am I doing wrong....Thanx Bob > It works here.i tried to set some horses > in active status that were not in the TblInvoice_ItMdl > and it showed here. Ah Ha (SELECT HorseID FROM TblInvoice_ItMdl) should be (SELECT HorseID FROM TblInvoice_ItMdt) Thanks but still got prob with my drop down active horse list....Thanks > OK Arthur I know now I can get your code to save, what am I doing > wrong....Thanx Bob > >> It works here.i tried to set some horses >> in active status that were not in the TblInvoice_ItMdl >> and it showed here. that is a very tough one. hahaha ROTFL....:) Arthur its working good but now I have a drop down list that shows me all my horses in active Mode so now when they leave qryOrderByHorseName and go into tblInvoice_ItMdt I lose them off this dropdown list untill they appear again in qryOrderByHorseName. Its a Combo box using the same Query, How can I get around this....I am very much appreciated for you help on this........regards Bob > that is a very tough one. hahaha > > ROTFL....:) This is the code in My ComboBox drop down list maybe something added to it: To show Horses that are in tblInvoice_ItMdt as well SELECT tblHorseInfo.HorseID, funGetHorse(0,tblHorseInfo.HorseID,false) AS Name, [StableReturnDate] FROM tblHorseInfo WHERE Status LIKE 'Active*' AND tblHorseInfo.HorseID NOT IN (SELECT HorseID FROM TblInvoice_ItMdt) ORDER BY funGetHorse(0,tblHorseInfo.HorseID,true), funGetHorse(0,tblHorseInfo.HorseID,false); Thanks Bob you can use requery but you have to do it in VBA, like this. Me.Combo0.Requery YEAH I did it , I made a new Query Name using your code an assigned it to that List Box , Brilliant Thanx :) good job :) Bye my friend, its already late in the evening here. I have to go home. Arthur |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.