Help with Query!

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
 
A

arthurjr07

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);
 
A

arthurjr07

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
 
B

Bob

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
 
A

AccessVandal via AccessMonster.com

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. :)
 
A

arthurjr07

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.
 
B

Bob

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
 
B

Bob

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...
 
B

Bob

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
 
B

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
 
A

arthurjr07

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
 
B

Bob

I tried putting other horses in active mode, that where not in
TblInvoice_ItMdl but still no horses are showing..thanks Bob
 
B

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...
 
B

Bob

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...
 
B

Bob

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...
 
A

arthurjr07

It works here.i tried to set some horses
in active status that were not in the TblInvoice_ItMdl
and it showed here.
 
B

Bob

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

Top