Sort by date

[pungy]

I have a Visual Basic 6 (Enterprise edition)application that creates a Microsoft Access Database table. The table is partially designed as follows:
TableName: InventoryHistory
Fields:
dbPartNumber text (30)
dbHistoryInfo text (50)
dbEnterDate text (10)
dbEnterTime text (6)

The application creates the records in the table by using the SQL "Insert" statement as follows:
sql = "Insert into InvetoryHistory (dbPartNumber, dbHistoryInfo, dbEnterDate, dbEnterTime) values ("
sql = sql & "'" & InPartNumber & "', "
sql = sql & "'" & InHistoryInfo & "', "
sql = sql & "'" & format(Date,"MM/dd/yyyy") & "', "
sql = sql & "'" & format(Time,"hhmmss") & "'"

When the report is printed, the records in the "Detail Section" need to be printed in Time within DATE descending sequence.

I am having a problem sorting the records. Example:

PartNumber 5C parts pulled for C1 Circuit Board 09/12/2007 162412
PartNumber 22X parts pulled for C3 Circuit Board 09/12/2007 201309
PartNumber 2B parts pulled for C5 Circuit Board 11/08/2007 092054
PartNumber 5C parts pulled for C8 Circuit Board 01/03/2008 113443
PartNumber 6X parts pulled for C2 Circuit Board 01/03/2008 133034
PartNumber 6X parts pulled for C2 Circuit Board 01/02/2008 094231

The DETAIL SECTION output needs to look like this:

01/03/2008 133034 6X parts pulled for C2 Circuit Board
01/03/2008 113443 5C parts pulled for C8 Circuit Board
01/02/2008 094231 6X parts pulled for C2 Circuit Board
11/08/2007 092054 2B parts pulled for C5 Circuit Board
09/12/2007 201309 22X parts pulled for C3 Circuit Board
09/12/2007 162412 5C parts pulled for C1 Circuit Board

Can you help on how I can sort the records in the proper order? One way I can do this is to sort the records in DESCENDING Sequence by YEAR/MONTH/DAY then TIME (Descending). If I use the Record Sort Expert, The Record Sort Expert defines the whole field. The field is defined in the format Month/Day/Year.

 

[lbass]

You could do this by converting the strings for date and time to a datetime, by using a formula like this:

stringvar datex := {table.dbenterdate};
stringvar timex := {table.dbentertime};
datetime(date(val(right(datex,4)),val(left(datex,2)),val(mid(datex,4,2))), time(val(left(timex,2)),val(mid(timex,3,2)),val(right(timex,2))))

Then sort on this formula descending.

This is not really the right forum for this question. In the future, consider forum149 or forum767.

-LB

 

[pungy]

lBass:

Thank you for your response. A couple of questions:
#1: Sorry about putting this under the wrong thread. Should I move it before continuing?

#2:I am not sure where to put the code you supplied. Can you tell me where to place it?

#3: I did put the code under the "Supress (no Drill-Down) Select Expert" of the Detail Section. When I went to save , I got an error message stating "The formula result must be a boolean". can you help me correcy it?

Thanks,
Sam

 

[lbass]

This formula is for sorting. Go to report->sort records and select the formula to sort on, descending.

-LB

 

[pungy]

lBass:

Thank you for your response. A couple of questions:
#1: Sorry about putting this under the wrong thread. Should I move it before continuing?

#2:I am not sure where to put the code you supplied. Can you tell me where to place it?

#3: I did put the code under the "Supress (no Drill-Down) Select Expert" of the Detail Section. When I went to save , I got an error message stating "The formula result must be a boolean". can you help me correcy it?

Thanks,
SAm

 

[lbass]

Why did you post the same post a second time? Please see my last response.

-LB

 

[pungy]

lBass:
About the "double Post" --I apologize for that. Not being that familiar with this BBS, I thought I clicked on the wrong reponse the first time. That will not happen again.

I hate to sound naive but you lost me. Can I restate my questions?
#1. The code you sent me, where do I put that code?
Let me stop here. Maybe your response will answer further questions I may have.

Thanks,
Sam

 

[lbass]

Go to the field explorer->formula->new, name it something like "sort", and then enter the code there, replacing the fields in the first two lines with your actual fields. Then go to report->sort records and choose {@sort} as your sort field.

-LB

 

[pungy]

lBass:

Thank you for your patience. Your solution resolved my problem.

Sam