listbox Row source question

  • Thread starter Thread starter adriany
  • Start date Start date
A

adriany

trying to list dates in order previous to now
listbox-

SELECT DISTINCT [Query1].[Date1] FROM [Query1] ORDER BY
[Query1].[Date1];
works great

SELECT DISTINCT [Query1].[Date1], [Query1].[Date1] FROM
[Query1] Union Select "*", "All" from [Query1] ORDER BY
[Query1].[Date1];
doesn't order by date but text, anyone knows how to fix
this problems
 
SELECT DISTINCT [Query1].[Date1], [Query1].[Date1] FROM
[Query1] Union Select "*", "All" from [Query1] ORDER BY
[Query1].[Date1];
doesn't order by date but text, anyone knows how to fix
this problems
Click to expand...

Searching a date/time field using LIKE "*" isn't going to work very
well in any case. The problem is that your UNION query is unioning a
text string and a Date/Time and then sorting by the text value (the
lowest common denominator).

Try using instead

UNION SELECT Null, "All" FROM Query1 ORDER BY [Query1].[Date1]

and using a criterion of

=Forms!yourform!comboboxname OR Forms!yourform!comboboxname IS NULL
 
adriany said:
trying to list dates in order previous to now
listbox-

SELECT DISTINCT [Query1].[Date1] FROM [Query1] ORDER BY
[Query1].[Date1];
works great

SELECT DISTINCT [Query1].[Date1], [Query1].[Date1] FROM
[Query1] Union Select "*", "All" from [Query1] ORDER BY
[Query1].[Date1];
doesn't order by date but text, anyone knows how to fix
this problems
Click to expand...

How about something like

SELECT Date1, CStr(Date1) FROM Query1
UNION
SELECT Null, "All" FROM Query1;

? That will put the "All" entry at the end. If you want to put that
item at the beginning of the list, you can add a sequencing field like
this:

SELECT Date1, CStr(Date1), 1 As Seq FROM Query1
UNION
SELECT Null, "All", 0 As Seq FROM Query1
ORDER BY Seq, Date1;
 
Back
Top