Solved

datareport data jumbled

Posted on 2001-06-03
7
169 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 69

Expert Comment

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

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 69

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 69

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now