?
Solved

Newly added performance dates are out of numerical order when displayed

Posted on 2003-03-06
9
Medium Priority
?
140 Views
Last Modified: 2013-12-24
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
Comment
Question by:douguitarman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 10

Expert Comment

by:substand
ID: 8082155
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
 
LVL 10

Expert Comment

by:substand
ID: 8082166
so your query might be:

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


0
 

Author Comment

by:douguitarman
ID: 8108063
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
Optimum High-Definition Video Viewing and Control

The ATEN VM0404HA 4x4 4K HDMI Matrix Switch supports 4K resolutions of UHD (3840 x 2160) and DCI (4096 x 2160) with refresh rates of 30 Hz (4:4:4) and 60 Hz (4:2:0). It is ideal for applications where the routing of 4K digital signals is required.

 
LVL 10

Expert Comment

by:substand
ID: 8113061
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
 

Author Comment

by:douguitarman
ID: 8115089
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
 
LVL 10

Accepted Solution

by:
substand earned 200 total points
ID: 8115251
no, make sure it is set to a date data type, not text.
0
 

Author Comment

by:douguitarman
ID: 8120007
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
 
LVL 10

Expert Comment

by:substand
ID: 8120391
it might solve this problem.  if it doesn't, it will solve the next question you'd have.
0
 

Author Comment

by:douguitarman
ID: 8128182
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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question