• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 141
  • Last Modified:

Newly added performance dates are out of numerical order when displayed

I'm pulling my hair out over this one. I'm what I would call a junior CF programmer and I can't figure this one out. Forgive me if this is a stupid oversight on my part but...I have a simple CF app where performance dates are added to an MS Access DB via CF form then displayed via CF Output Query. When the dates are displayed the newly added dates do not postion themselves in the proper order. For example I may have the following:
Fri March 7
Sat Mar 8
Fri Apr 11
Sat Apr 12

If I add a date such as Sun Mar 9, it appends this date to the bottom of the list instead of after Sat Mar 8 and before Fri Apr 11.

I can't figure out if this is a DB issue or a CF issue. Your help would be much appreciated as it will allow me to move onto more pressing matters which there is no shortage of.
0
douguitarman
Asked:
douguitarman
  • 5
  • 4
1 Solution
 
substandCommented:
in your query, add

order by performancedate asc;

(replace performancedate with the name of the field you want to order by)

asc sorts in ascending order, you can put desc to sort by descending order.

0
 
substandCommented:
so your query might be:

<cfquery name="getdates" datasource="yourDS">
select performancedate from performancetable
order by performancedate asc;
</cfquery>


0
 
douguitarmanAuthor Commented:
Sorry it has taken me so long to respond. I tried that whenI first built the page with no success. I tried again for the hell of it and still no luck. With ORDER BY GigDate asc;
Would this have anything to do with the problem(this is within my output query):
#DateFormat(GigDate, "mm/dd/yyyy")#
What about session variables? Would something in there cause a problem? I just have your basic session management/timeout settings...nothing complex.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
substandCommented:
session vars shouldn't be messing it up.

that dateformat may have something to do with it, but most likely it shouldn't- i've never had trouble with it.  is your table column that contains the gig date defined as a "date" field?  that may cause the problem you're having.

when you put the order by, what happens?  it still comes out in the same order?
0
 
douguitarmanAuthor Commented:
I have to check my table column. I think it might be set to text but I'm not sure. If it is set to date, should I change it to text?

When I put the order by in, it comes out in the same order. In otherwords, no change to the order.
0
 
substandCommented:
no, make sure it is set to a date data type, not text.
0
 
douguitarmanAuthor Commented:
My table column was set to TEXT. I will set it tonight(I'm at work right now) to DATE and let you know what happens. Do you think this will solve the problem? Anything else in the DB I should check while I'm at it? Thanks.
0
 
substandCommented:
it might solve this problem.  if it doesn't, it will solve the next question you'd have.
0
 
douguitarmanAuthor Commented:
Thanks for your help. That solved my problem. But it did raise another potential problem...I had to remove the DateFormat function from the following:
#DateFormat(GigDate, "mm/dd/yyyy")#
Although it is a fixable problem, it seems as though this could limit the use of DateFormat...unless there is some sort of work-around. This must be an issue that someone else has encountered. If you always had to set your data type to Date/time in your DB(for ORDER BY to work), how would you format that date in CF? Would you use the Replace function with DateFormat somehow? Anyway, thanks again substand. Peace - Douguitarman
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now