Solved

datareport data jumbled

Posted on 2001-06-03
7
179 Views
Last Modified: 2011-10-03
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
Comment
Question by:DaveMon
[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
  • 3
  • 3
7 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6150289
Use the "Order BY" clause in your Command object.
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 6151274
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
 
LVL 2

Author Comment

by:DaveMon
ID: 6166718
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6167323
Can you send me your project at emoreau99@hotmail.com
0
 
LVL 2

Author Comment

by:DaveMon
ID: 6172087
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
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 50 total points
ID: 6173327
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
 
LVL 2

Author Comment

by:DaveMon
ID: 6175833
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

710 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