Link to home
Start Free TrialLog in
Avatar of chulland
chulland

asked on

'Order By' Statement in MS Access V2.0 -- HELP

Hello,

I need some help,

Please Examine the Two Pieces of SQL (The Order By part mainly)

This Works

Start Of SQL
SELECT DISTINCTROW Quote.Date, Quote.Initails, Quote.[Estimate No],
Quote.[Company Name], Quote.[Contact Name], Quote.[Job Description],
Quote.[% Rate], Quote.[Value £], Quote.Status
FROM Quote
WHERE (
( (([Forms]![Query Menu]![From Date] = NULL) AND ([Forms]![Query
Menu]![To Date] = NULL) )
OR
(Quote.Date Between [Forms]![Query Menu]![From Date] And
[Forms]![Query Menu]![To Date]))
AND
(([Forms]![Query Menu]![Initails] = NULL) OR
(Quote.Initails=[Forms]![Query Menu]![Initails]))
AND
(([Forms]![Query Menu]![Contact Name] = NULL) OR ([Quote]![Contact
Name] LIKE [Forms]![Query Menu]![Contact Name]))
AND
(([Forms]![Query Menu]![Company Name] = NULL) OR ([Quote]![Company
Name] LIKE [Forms]![Query Menu]![Company Name]))
AND
( (([Forms]![Query Menu]![From %] = NULL) AND ([Forms]![Query
Menu]![To %] = NULL) )
OR
([Quote]![% Rate] Between [Forms]![Query Menu]![From %] And
[Forms]![Query Menu]![To %]))
AND
( (([Forms]![Query Menu]![From Value] = NULL) AND ([Forms]![Query
Menu]![To Value] = NULL) )
OR
([Quote]![Value £] Between [Forms]![Query Menu]![From Value] And
[Forms]![Query Menu]![To Value]))
AND
(([Forms]![Query Menu]![Estimate No] = NULL) OR ([Quote]![Estimate No]
LIKE [Forms]![Query Menu]![Estimate No]))
AND
(([Forms]![Query Menu]![Status] = NULL) OR ([Quote]![Status] =
[Forms]![Query Menu]![Status]))

)
ORDER BY Quote.Initails;
End Of SQL




This Does Not Work (Even Though [Forms]![Query Menu]![Sort By]
="Quote.Initails") This is a value list listing all Fields


Start of SQL
SELECT DISTINCTROW Quote.Date, Quote.Initails, Quote.[Estimate No],
Quote.[Company Name], Quote.[Contact Name], Quote.[Job Description],
Quote.[% Rate], Quote.[Value £], Quote.Status
FROM Quote
WHERE (
( (([Forms]![Query Menu]![From Date] = NULL) AND ([Forms]![Query
Menu]![To Date] = NULL) )
OR
(Quote.Date Between [Forms]![Query Menu]![From Date] And
[Forms]![Query Menu]![To Date]))
AND
(([Forms]![Query Menu]![Initails] = NULL) OR
(Quote.Initails=[Forms]![Query Menu]![Initails]))
AND
(([Forms]![Query Menu]![Contact Name] = NULL) OR ([Quote]![Contact
Name] LIKE [Forms]![Query Menu]![Contact Name]))
AND
(([Forms]![Query Menu]![Company Name] = NULL) OR ([Quote]![Company
Name] LIKE [Forms]![Query Menu]![Company Name]))
AND
( (([Forms]![Query Menu]![From %] = NULL) AND ([Forms]![Query
Menu]![To %] = NULL) )
OR
([Quote]![% Rate] Between [Forms]![Query Menu]![From %] And
[Forms]![Query Menu]![To %]))
AND
( (([Forms]![Query Menu]![From Value] = NULL) AND ([Forms]![Query
Menu]![To Value] = NULL) )
OR
([Quote]![Value £] Between [Forms]![Query Menu]![From Value] And
[Forms]![Query Menu]![To Value]))
AND
(([Forms]![Query Menu]![Estimate No] = NULL) OR ([Quote]![Estimate No]
LIKE [Forms]![Query Menu]![Estimate No]))
AND
(([Forms]![Query Menu]![Status] = NULL) OR ([Quote]![Status] =
[Forms]![Query Menu]![Status]))

)
ORDER BY [Forms]![Query Menu]![Sort By];
End Of SQL


I am In trouble, please help....
ASKER CERTIFIED SOLUTION
Avatar of guillems
guillems

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial