Dear AgreedBlu- (name?)
The problem is a UNION converts the data type of the number to text because "Select Case" is text. You might try this:
SELECT tblCases.CaseID, tblCases.OutbreakID, Format(tblCases.casenumber, "##0") AS [Case]
FROM tblCases
WHERE (((tblCases.OutbreakID)=20))
ORDER BY CaseID,tblCases.Casenumber
UNION SELECT 0, null, " Select Case " from tblCases
FROM tblCases
WHERE (((tblCases.OutbreakID)=20))
ORDER BY CaseID,tblCases.Casenumber
UNION SELECT 0, null, " Select Case " from tblCases
ORDER BY 3;
That will force up to three digits to be "right aligned" to get them in the proper sequence.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Dec 7, 2015, at 4:26 PM, argeedblu@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
My application assigns a case number to tblCases. The case number is not the PK of the table as the table represents cases for multiple Outbreaks (parent table). My issue is that I want my users to select from a combo list of cases for the current outbreak and I want the case numbers to appear in sequential order. I also want the text "Select Case" to appear in the combo before the user picks a case.
The problem is that the Union query does not present the case list in numerical order. The query without the Union does present the list in numerical order.
I have uploaded the file UnionQueryIssue.docx showing the table schema, the standard query sql and datasheet and the Union query sql and datasheet.
I have tried a number of workaround be keep ending up with the same results.
Any suggestions?
The problem is that the Union query does not present the case list in numerical order. The query without the Union does present the list in numerical order.
I have uploaded the file UnionQueryIssue.docx showing the table schema, the standard query sql and datasheet and the Union query sql and datasheet.
I have tried a number of workaround be keep ending up with the same results.
Any suggestions?
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (6) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar