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

datareport data jumbled

I have a database  project that I am trying to create a report for.......I have followed the help section's tutorial for a new data report, and am mostly there except for one problem.....the  records are jumbled out of order on the report  ( i.e.  there are   records with the date from 5/29  for 10-20  records,  then  from 5/30,  them back to 5/18.

AM             80                                0
                     05/29/01         3:40:58 AM             80                                0
                     05/29/01         3:43:25 AM             80                                0
                     05/29/01         3:45:53 AM             80                                0
                     05/29/01         3:48:21 AM             80                                0
                     05/29/01         3:50:49 AM             80                                0
                     05/29/01         3:53:16 AM             80                                0
                     05/29/01         3:55:43 AM             80                                0
                     05/29/01         3:58:11 AM             80                                0
                     05/29/01         4:00:39 AM             80                                0
                     05/29/01         4:03:07 AM             80                                0
                     05/18/01         2:43:28 AM             84                                0
                     05/18/01         2:43:44 AM             84                                0
                     05/18/01         2:44:00 AM             84                                0
                     05/18/01         2:44:16 AM             84                                0
                     05/18/01         2:44:32 AM             84                                0
                     05/18/01         2:44:48 AM             84                                0
                     05/18/01         2:45:04 AM             84                                0
                     05/18/01         2:45:20 AM             84                                0
                     05/18/01         2:45:36 AM             84                                0
                     05/18/01         2:45:52 AM             84                                0
 
It seem like the records are  skipping  around  for  some  reason,  The  table  has  all of  the fields represented perfectly  and  in the  correct  data sequence ( none out of order )

I program in Foxpro at work, and I am familiar with indexes and using SQL selects.....none of that was required in the tutorial.....  here  is  some more info

Database was created with VB 6 Visdata Manager, This table is named "Airstats"  and  I have fields in the report such as date, currenttime, currenttenp, currentlight and things like that.  It  looks  pretty good, I have the headers  and  footers  in place,  all 3700 records  seem to be on the 67 page report........just  all out  of order.

Project looks like this:

I have a main form with a button that is coded with "DataReport1.Show"

Under the designers folder, I have DataEnvironment 1, and DataReport1

Any ideas?    Thanks  In  advance

Dave
0
DaveMon
Asked:
DaveMon
  • 3
  • 3
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
Use the "Order BY" clause in your Command object.
0
 
Ryan ChongCommented:
Hi, as emoreau said, use "Order By" according to which fields you want to sort.

ORDER BY  - Sorts a query's resulting records on a specified field or fields in ascending or descending order.

SELECT fieldlist
FROM table
WHERE selectcriteria
[ORDER BY field1 [ASC | DESC ][, field2 [ASC | DESC ]][, ...]]]



The default sort order is ascending (A to Z, 0 to 9). Both of the following examples sort employee names in last name order:

SELECT LastName, FirstName
FROM Employees
ORDER BY LastName;

SELECT LastName, FirstName
FROM Employees
ORDER BY LastName ASC;

To sort in descending order (Z to A, 9 to 0), add the DESC reserved word to the end of each field you want to sort in descending order. The following example selects salaries and sorts them in descending order:

SELECT LastName, Salary
FROM Employees
ORDER BY Salary DESC, LastName;

'Cuts from MSDN

0
 
DaveMonAuthor Commented:
Hi  Everyone,  I am  going  to do  some  additional  testing,  but  it  seems  like  I have  tried  every  possible ORDER BY thing  in the command  object  and  still not  gotten the results  I want.  THis is  so weird  because  the  table  is  in perfect  order......VB  is  screwing up  the  data  on the  way  to the  report....  I will try  a few  more  things,  and  get  back  to you.

Dave
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Éric MoreauSenior .Net ConsultantCommented:
Can you send me your project at emoreau99@hotmail.com
0
 
DaveMonAuthor Commented:
I have posted the zip file at www.samhell.com/images/dave_db.zip

It contains the project and the database I am using,  I have checked and it is still doin the same problem.
It is 1.8 megs in size, if thats a problem,  I can  try to shrink the db  alittle,  but  I wanted  you to see exactly  what  Im  working  with.


Dave
0
 
Éric MoreauSenior .Net ConsultantCommented:
Go to the AirStats Command properties (in the DataEnvironment) and change the "Source of Data" to be
"SQL Statement" and copy "SELECT AirStats.* FROM AirStats ORDER BY `Date`, CurrentTime" to the textbox.
0
 
DaveMonAuthor Commented:
That did the trick! I guess I will wonder what the deal was with just sending data from the table later.  For now  I will move on to other things,  thanks.

Dave

P.S> I will be removing the file at the location listed above....sorry for any problem there but the solution applies to any db problem I think.


Dave
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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