Solved

datareport data jumbled

Posted on 2001-06-03
7
176 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
  • 3
  • 3
7 Comments
 
LVL 70

Expert Comment

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

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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

805 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