Help with Query!

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

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);
 
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
 
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. :)
 
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.
 
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
 
Oops Sorry Table name, tblInvoice_ItMdt
Thanks Bob


Bob said:
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

AccessVandal via AccessMonster.com said:
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. :)
Click to expand...
Click to expand...
 
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
 
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
 
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
 
Might have something to do with qryOrderByHorseName because I am getting a
error on the drop down list now. Thanx Bob
Bob said:
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
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
Click to expand...
Click to expand...
 
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


Bob said:
Might have something to do with qryOrderByHorseName because I am getting a
error on the drop down list now. Thanx Bob
Bob said:
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
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
Click to expand...
Click to expand...
Click to expand...
 
This actually lists the horses by alphabetical order, Unnamed horses first
then Named Horses... Thanks Bob

Bob said:
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


Bob said:
Might have something to do with qryOrderByHorseName because I am getting
a error on the drop down list now. Thanx Bob
Bob said:
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
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
Click to expand...
Click to expand...
Click to expand...
 
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
Brilliant Bob
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Addition to a Query! 1
Addition to Query 10
Double Click List Box! 2
Change Row Source to Query! 10
Dropdown Select Problem 2
Print order by Date 13
Change Order on a Report! 2
Adding a new column to ListBox! 7

Back
Top