I have the attached database file with the form CalcTravelTime. I also have a code associated with the command button as you can see. The code lets the travel time step up to the next 5 minute interval after the travel time gets over 300 seconds or 5 minutes.

Basically what I need help doing is to eliminate the way the code steps up to the next 5 minute interval. Whether that be changing the code or eliminating part of the code. That is the part I'm not sure of.

So really I still need the total travel time summation but without the step up to the next interval. Also I would like to keep it in the same format as the attached file if possible. Thank you

Not sure I 'll have time to look at this further.

Just wanted to say I didn't understand the problem. On the one hand you seem to be deliberately stepping up the time interval by 5 minutes. On the other hand you seem to be saying that this is what you want to avoid. I don't get it. (I'm a bit slow).

jal, if you're slow so am I. I don't understand either.

Thats exactly what I'm trying to do so your not slow. I got the code from a colleague and it is stepping up to the next interval in the attached DB. But I am looking for a way to modify the code to get rid of that 5 minute step up...Does that help at all?

I still don't understand any of this. I don't understand who's traveling and why, what information is needed, who needs it, why they need it, what a segment is, what a segment is used for, what an interval is, what "stepping up" is, why it is stepped up, what time is being calculated, why it is being calculated, what your form is currently doing, what it is you want it to do, etc, etc, etc.

jal I figured out the line of code that I need to delete from the code in my DB it was:

miscRS.Filter = "segTimeStamp = #" & currTime & "# AND segment >" & currSegment

But i have a new problem that maybe you can help with and I tried to explain it the best way I could so here it goes

I have average travel times over certain roadways segments in a road network (segments 1-15 in my DB). The travel times are averaged over five minute intervals (the average travel time for segment x from 12:00 am-12:05 am, for 12:05 am-12:10 am, etc.) throughout the day from raw speed data that I have access to. So in my DB I have the average travel time over every five minute interval throughout the day for every segment.

The next part, which is the topic of my project, is the total travel time. So when I want to find total travel time from segment x to segment y then I must add all the travel times between those two segment to get total travel time. But the catch is, for example, if the total travel time is more than five minutes from segment x to segment y I have to know at what segment BECAUSE once the total travel time is over 5 minutes then i must now use the travel times associated with the next timestamp or five minute interval because the travel times associated with the previous five minute interval are useless since I already have a travel time of 5 miuntes. This is the stepping up of travel times I mentioned in the first post and this is called my actual travel time.

The posted travel time is the same as the above explanation but once the total travel time is over five minutes then it doesn't move to the next five minute interval.

Now I have this information in the two forms attached. But what I would like to do is have this information side by side in a query or table so that I can compare it, the actual and posted travel time, that is.

It will probably involve joining two tables but first I need to get the information from the form into a table so I can join them, but that is the part where I am stuck.

I'm hoping that I can just use the code that I have attached to the command button in the form to the VB editor but then I need to define it in the fieldlist section of the query and am not sure how to do that either.

An example of what I am looking for from the table will be, Every combination of segment to segment at every five minute interval.

Example: So total travel time from segment 1 to segment 2,3,4,5,6,7,8,9,10,11,12,13,14,15 at every 5 minute interval throughout the day (12:00 am, 12:05 am, 12:10 am, etc.)

Then total travel time from segment 2 to segment 3,4,5,6,7,8,9,10,11,12,13,14,15 at every 5 minute interval

etc. for posted and actual travel time for comparison


I hope that helped and thank you for the reply back, please let me know if explanation is not clear enough. Thank you

You cleared up some things. Part of the problem here is that I have a pretty severe malady that makes my health worse every year (it's a miracle I'm still alive). Right now, I am so exhausted I can barely understand 2 plus 2. I'll take a stab at this, though, until I fall asleep.

Let's consider segments 1 to 10 at 1:00 AM. When I total segments 1 to 7, including 7, I surpass the five minute limit.

At that point, for segments 8 to 10, I have to pretend as though the user had selected 1:05 AM (instead of 1:00 AM), according to you.

The question is regarding item 7. Should I be using 1:00 AM for that segment, or 1:05 AM?

For now I am assuming 1:00 AM.

Ok, here's how to calculate one totalTime value based on the user's criteria on the form.

Private Sub calcTime_Click()
If Nz(Me.StartTime, 0) = 0 Or Nz(Me.StartSegment, 0) = 0 Or Nz(Me.endSegment, 0) = 0 Then Exit Sub
Dim TheIntervalSelectedByUser As Date, TheIntervalAfterTheUsersSelection As Date
TheIntervalSelectedByUser = CDate(Me.StartTime)
TheIntervalAfterTheUsersSelection = DateAdd("n", 5, TheIntervalSelectedByUser)
Dim rs As New ADODB.Recordset
'The AND clause below is optional. Improves performance. I thought it would help with the logic too but not really.
Dim sql As String
sql = "SELECT Segment, segTimeStamp, [Travel Time] " & _
"FROM TravelSegments " & _
"WHERE Segment >= " & Me.StartSegment & " AND Segment <= " & Me.endSegment & " " & _
"AND (CDate(segTimeStamp) = #" & TheIntervalSelectedByUser & "# OR CDate(SegTimeStamp) = #" & _
TheIntervalAfterTheUsersSelection & "#) " & _
"ORDER BY segment, CDate(segTimeStamp) "
rs.Open sql, CurrentProject.Connection
Dim totalTravelTime As Double
Dim LastSegmentUsed As Long 'to prevent the cutoff segment from being used twice.
Do While Not rs.EOF
If CDate(rs!SegTimeStamp) = TheIntervalSelectedByUser And totalTravelTime < 300 Then
totalTravelTime = totalTravelTime + rs![Travel Time]
LastSegmentUsed = rs!Segment
ElseIf CDate(rs!SegTimeStamp) = TheIntervalAfterTheUsersSelection And totalTravelTime >= 300 Then
If LastSegmentUsed <> rs!Segment Then totalTravelTime = totalTravelTime + rs![Travel Time]
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Me.ttlTravel = Math.Round(totalTravelTime, 2)
End Sub

Add this line to the start of the loop to calculate the postedTime (well, also Dim out postedTime as a Double variable before the loop starts).

If CDate(rs!SegTimeStamp) = TheIntervalSelectedByUser Then postedTime = postedTime + rs![Travel Time]

I took a stab at your "all possible combinations" request (see attachment).

The loop has to execute thousands of iterations. I stepped through one iteration and then became too tired to test it further. Definitely needs more testing to ensure that I nested the loops correctly.

To execute the loop, click the Calcuate All button on the form.

Not sure how long it takes to generate the output - probably 15 minutess but could be double that for all I know. It outputs the result into a table called CalculateAll.

Note: I changed the Datatype of Segment from Double to Long in your table.

That is impressive..thank you but just have a few questions.

First to answer your question about your example, you were right to assume to use the travel time for segment 7 at 1:00 am instead of 1:05 am.

You mentioned to add the line of code, I just used your DB as a template for my new DB and all travel times seemed to be correct when compared to the forms I used before. Do I need that line of code for any reason?

Just to let you know it took about 1 hour for the output to be generated and a couple of times my computer froze, maybe because of size or CPU, just FYI.

I have one more question if you are up for it. :)
All the data you saw was from one day, say I wanted to add more days like possibly a month or more. Would it just be as simple as add a line of code to previous code or would it be more in depth than that?

Thank you again. I really appreciate it

Also I'm assuming that the final summation of both travel times is rounded to the nearest whole number after all individual travel times between segments were not rounded before summation (though in you DB the final travel times are not rounded to nearest whole number and mine are). Just asking for when I'm comparing travel time with previous answers from forms..Thanks

After further observation, I am having a little trouble with the totalTravelTime. I tried to insert code where you mentioned but got error messages. Do I place it in the code for calcTime_Click loop or the last part of code, in that loop?

After further observation, I am having a little trouble with the totalTravelTime. I tried to insert code where you mentioned but got error messages. Do I place it in the code for calcTime_Click loop or the last part of code, in that loop?

I thought you said you wanted to calculate postedTime (in addition to TTT) when the user clicked "Calculate" on the form. I thought you said that PostedTime is the same as TTT except there is no stepping up. If so, that line of code should calculate PostedTime (and then you can decide where to display it) if you put it in the original button click event (not the button event that I added myself).

All the data you saw was from one day, say I wanted to add more days like possibly a month or more. Would it just be as simple as add a line of code to previous code or would it be more in depth than that?

At this point (haven't given it much thought) I doubt that you would need to change any code. Maybe I'm missing something?

Also I'm assuming that the final summation of both travel times is rounded to the nearest whole number after all individual travel times between segments were not rounded before summation (though in you DB the final travel times are not rounded to nearest whole number and mine are). Just asking for when I'm comparing travel time with previous answers from forms..Thanks

If I recall, I didn't do any rounding at all, except for display purposes, in the textbox on the form. I did no rounding when building the all-combo table, as best I can recall.

What errors are you getting?

No, I wanted the two to be displayed side by side in a table format in order to compare them. The posted TT in the table is the same as TTT but no stepping up. The information you gave me for the CalculateAll table is mostly right. It has the information side-by-side like I need amd it seems to calculate the posted travel time correctly but there are a few instance where the TTT doesn't appear to be calculating correctly.

I mentioned in a previous post that I have two forms in two different DB's that each calculate the posted TT and the TTT but I wanted to combine them in a table form to compare them. I will attach them. The posted from yesterday at 1:00 will explain what each does. (11-3-08 is TTT, 11-5-08-test is the posted)

At this point (haven't given it much thought) I doubt that you would need to change any code. Maybe I'm missing something?

I was asking because I may need to add more days data and if I were to do that then maybe I would have to add a date column to distinguish the timestamps

I was asking because I may need to add more days data and if I were to do that then maybe I would have to add a date column to distinguish the timestamps
That could call for rewriting code - and I might not be available to help.

You're probably better off updating the TimeStamp column as to include the date portion of the time. If you do so, the VBA code could probably remain intact.

No, I wanted the two to be displayed side by side in a table format in order to compare them. Your original form only calculated one value at a time. How can I display one value "in a table format side by side in order to compare them" ???

So I built you a table where I calcuate ALL the values. True, I didn't add any logic to display this table (because I felt that I had done enough).

By the way, I don't use the navigation buttons at the bottom - I dont' code for those. Not my style.

The posted TT in the table is the same as TTT but no stepping up. The information you gave me for the CalculateAll table is mostly right. It has the information side-by-side like I need amd it seems to calculate the posted travel time correctly but there are a few instance where the TTT doesn't appear to be calculating correctly. Maybe give me some specific examples where it is wrong, so I can debug. I was planning for you to do the debugging, but maybe I can give it a shot.

I mentioned in a previous post that I have two forms in two different DB's that each calculate the posted TT and the TTT but I wanted to combine them in a table form to compare them. I will attach them. The posted from yesterday at 1:00 will explain what each does. (11-3-08 is TTT, 11-5-08-test is the posted)

The two forms look identical so i don't see the relevance. Maybe there's some differenence in the coding (which I am not eager to study at this moment).

If you need to display a table on the form, you can drop a listbox onto it.

Attached is a section of my notebook explaining how to populate a listbox using VBA. Basically it's two lines of code.

lb.RowSource = "SELECT col1, col2 FROM Table1 ORDER BY Col1, Col2"
lb.Requery

I noticed you haven't provided any specific examples where my logic miscalculated. I won't do any debugging until you do.

[/quote]
The two forms look identical so i don't see the relevance. Maybe there's some differenence in the coding (which I am not eager to study at this moment).[/quote]

the only difference is I deleted the line:

miscRS.Filter = "segTimeStamp = #" & currTime & "# AND segment >" & currSegment

If you need to display a table on the form, you can drop a listbox onto it.

Attached is a section of my notebook explaining how to populate a listbox using VBA. Basically it's two lines of code.

lb.RowSource = "SELECT col1, col2 FROM Table1 ORDER BY Col1, Col2"
lb.Requery

I noticed you haven't provided any specific examples where my logic miscalculated. I won't do any debugging until you do.

Just a couple instances I noticed, for example at 3:20 pm a few of the segments starting with 8-15. 9-15, 10-15, 11-15 to 14-15 all display the same TTT. I can attach the DB I ran and you can see tomorrow and show you the examples if you are willing to help with that.

It's really weird because it's only for some segments at random times and don't see how that happens that the TTT is miscalculated. Thank you

Finding the line with the problem wasn't hard (but I'm not sure yet how to fix it).
Here's how I found the problem. I exported the source data to Excel. In Excel, select Data > AutoFilter. This places filter menus in the column names. Using a filter menu, I selected 3:20 PM (this hides all rows but those for 3:20 PM). I pasted these rows for 3:20PM into another sheet (Sheet1). Then I likewise added, into Sheet1, the rows for 3:25Pm.

Going back to Access, I deleted all the rows from the source table, and then imported Excel Sheet1.

The result is that I was able to run the loop for 3:20 pm alone (this runs in the blink of an eye).

I find that when the totalTime exceeds 300 seconds, totalTime doesn't further increment. That means this condition is failing:

ElseIf rsAllData!TimeStamp = steppedUpTime And totalTime >= 300

In my next post I'll explain why I am having difficulty "fixing" this line - to me it seems correct.

Could someone help me with this? I can't understand why this line of code is failing.
ElseIf rsAllData!TimeStamp = steppedUpTime And totalTime >= 300
Because, in the debugger, the output looks correct. The condition should prove true. Here's what the debugger says when I set it to break after the 300-seconds mark. First of all, it confirms that totalTime >= 300:

? totalTime
346.442688596414

? totalTime >= 300
True

Obviously, then, it's the other condition which is failing.

? rsAllData!TimeStamp = steppedUpTime
False

Why False? Here's how the debugger outputs each of those times, in military time (3:25 PM):

? Format(rsAllData!TimeStamp, "MM/DD/YYYY hh:mm:ss")
12/30/1899 15:25:00

? Format(steppedUpTime, "MM/DD/YYYY hh:mm:ss")
12/30/1899 15:25:00

The two dates are equal down to the second !!! So why is the debugger stating "False" ??? Maybe there is somehow a difference in milliseconds, but I don't know how to extract milliseconds in Access.

Anyone?

Well, I never figured out the discrepancy so I now propose a different solution. I changed the line to this:

ElseIf totalTime >= 300 And rsAllData!FormattedTimeStamp = steppedUpTime

Where the value FormattedTimeStamp is obtained by adding this clause to the original query:

(SELECT)....FORMAT(CDate(SegTimeStamp), 'MM/DD/YYYY hh:mm:ss') as FormattedTimeStamp

The comparision now returns True instead of False because I am now working directly with:

'MM/DD/YYYY hh:mm:ss'

Attached is the updated code. Run it again, and let me know if it is calculating correctly.

In fact, I'm uploading the 3:20 PM data so that you can begin your evaulation there.

Ok wow man that seems to work just great thanks you so much...you also mentioned if I wanted to add multiple days of data just add the date with the timestamp and the code should remain unaffected??

Again unbelieveable thanks

Ok wow man that seems to work just great thanks you so much...you also mentioned if I wanted to add multiple days of data just add the date with the timestamp and the code should remain unaffected??
Well, that's my theory anyway. But above we just saw how subtle the bugs can be. I'm not smart enough to be sure about a theory until real testing has confirmed it. Here's one possible test.

(To make this test run faster, do all this work on the 3:25 pm subset of the data that I uploaded). Right click the table and copy it to create TravelSegmentsCopy (or whatever). Then do something like this (in sql View) - adds 30 days to each of the times.
UPDATE TravelSegmentsCopy SET SegTimeStamp = SegTimeStamp + 30


Then add this table to the original so that you now have double the data.


INSERT INTO TravelSegments SELECT * FROM TravelSegmentsCopy


Now run the code again, and check the results. Check at least one sample of the original data, and one sample of the plus-30 days data. If it fails, let me know, and maybe I'll get a chance to debug it.

Sorry it took so long to reply...I haven't tried this yet cuz I can only open your file as a read-only file but when I get to the last stages of this project I will be adding more travel time data from different days, probably about 3 months worth, and will have the run the code for that many days.

So I think, and correct me if I'm wrong, couldn't we just modify the CalculateAll table to include the date AND time and then the code shouldn't have to be changed. Then when we selected timestamp in the form it will also include the date.

I know I made that sound easy and I'm sure it isn't so, but I just wanted to get your thoughts..Thanks

I think, and correct me if I'm wrong, couldn't we just modify the CalculateAll table to include the date AND time and then the code shouldn't have to be changed.
As I said, that's my theory, but it needs testing.

ok so once I load all the 3 months data into my DB, then try your method and then run the code, then it should work?

ok so once I load all the 3 months data into my DB, then try your method and then run the code, then it should work?
Let's hope so.

Alright well I will try small scale test, first, the way you explained before with just a few timestamps and then get back to you and let you know the results

(To make this test run faster, do all this work on the 3:25 pm subset of the data that I uploaded). Right click the table and copy it to create TravelSegmentsCopy (or whatever). Then do something like this (in sql View) - adds 30 days to each of the times.
UPDATE TravelSegmentsCopy SET SegTimeStamp = SegTimeStamp + 30


Then add this table to the original so that you now have double the data.


INSERT INTO TravelSegments SELECT * FROM TravelSegmentsCopy


Now run the code again, and check the results. Check at least one sample of the original data, and one sample of the plus-30 days data. If it fails, let me know, and maybe I'll get a chance to debug it.

Ok so I copied the data and made the table TravelSegmentsCopy and transferred that to a query so I could insert the line in SQL view you mentioned above.

I'll explain what I did: I put the TravelSegmentsCopy in the query, selected SegTimeStamp to update, then tried to input your UPDATE line in SQL view but couldn't get the query to run. It's giving me an empty query, no data at all.

I'm still a little confused, should I input more days data other than just the one day that is provided in your example? That wasn't really clear.

Is this how you proposed I do it or am I doin something terribly wrong?

Ok so I copied the data and made the table TravelSegmentsCopy and transferred that to a query so I could insert the line in SQL view you mentioned above. As for the boldfaced words, huh? I don't know what that means. I asked you to copy the table. I don't recall saying anything about "transfer the table to a query" nor do I know what such words mean.


I'll explain what I did: I put the TravelSegmentsCopy in the query......... huh?

Maybe you're using the editor or the wizards. I don't use those. I use one thing. Pure CODE. Period.



.......selected SegTimeStamp to update, then tried to input your UPDATE line in SQL view but couldn't get the query to run. It's giving me an empty query, no data at all. Just paste the CODE into SQL View of a new query replacing any code that may or may not be there. Don't select anything from menus, wizards, or windows.

Then choose the menu item Query > Run.

Problem is I just tried it. First it says, "You are about to UPDATE 30 rows. Proceed?" Then it gives me an error. This is an annoyance that has been irritating me since this thread started. You have the TimeStamp column as "Text" (String) rather than "Date". Hence it doesn't know I mean 30 days:


UPDATE TravelSegmentsCopy SET SegTimeStamp = SegTimeStamp + 30

I guess you'll have to do it like this:


UPDATE TravelSegmentsCopy SET SegTimeStamp = Cstr(CDate(SegTimeStamp) + 30)


When you run it, an UPDATE query doesn't return any data. So you won't see any "confirmation" necessarily - but if you go to the table you should see the results.


The next task is to take these rows of TravelSegmentsCopy and insert them into TravelSegements using an INSERT query. I'll try it right now.

The INSERT query worked just fine - again just paste the code and run it (run it only once otherwise you'll get dup data inserted).

INSERT INTO TravelSegments SELECT * FROM TravelSegmentsCopy

Again, you won't see any confirmation.

Now that you' ve got this data into TravelSegments, run the Form again, and let me know if the figures come out wrong.

Just paste the CODE into SQL View of a new query replacing any code that may or may not be there. Then choose the menu item Query > Run.

Problem is I just tried it. First it says, "You are about to UPDATE 30 rows. Proceed?" Then it gives me an error. This is an annoyance that has been irritating me since this thread started. You have the TimeStamp column as "Text" (String) rather than "Date". Hence it doesn't know I mean 30 days:


UPDATE TravelSegmentsCopy SET SegTimeStamp = SegTimeStamp + 30

I guess you'll have to do it like this:


UPDATE TravelSegmentsCopy SET SegTimeStamp = Cstr(CDate(SegTimeStamp) + 30)

I pasted the code exactly as you have it here and I got the same message about "updating 30 rows" "Proceed?"

Then got the error "MS Access can't update all the records in the update query. MS Office Access didn't update 30 fields due to a type conversion failure, 0 records due to key violations, 0 records due to lock violations. and 0 records due to validation rule violations"

So any ideas there? I guessing that's the same error you got when you tried also.

I'll ask this question also: Should I load all my data into the DB first e.g. all my days data vs. the one day that is present in the DB we have been working with or is that irrelevant right now?

The "Type Conversion Failure" probably occurs here:

Cstr(CDate(SegTimeStamp)


This probably means that SegTimeStamp is unable to be converted to a date. A likely cause of this is that it is NULL. Verify the contents of SegTimeStamp, and determine if there are 30 fields that either are NULL, or contain a value that is not a date. Post some of the values if you need more assistance

Thanks MSAccessRookie, that sure was a stupid mistake by me

Thanks MSAccessRookie, that sure was a stupid mistake by me

I do not like the word stupid. I used to call mine (and probably still should) "Rookie Mistakes"

jal

With that little correction from MSAccessRookie, I got the update query to update the TravelSegmentsCopy table as each SegTimeStamp column either reads 1/29/1900 3:20:00 pm or 1/29/1900 3:25:00 PM.

Then I do the append query to insert the data into the TravelSegments table and then run the code.

So after the code is run the I have the CalculateAll table with the travel times for timestamps without the 1/29/1900 date in them and with the date in them and the data is the same for both sets of data. So I guessing this is the way you meant the data to come out in the new CalculateAll table?

You can ignore the previous two posts I had for today, since I got it figured out. Thanks

I do not like the word stupid. I used to call mine (and probably still should) "Rookie Mistakes"


Point taken..thanks

jal

With that little correction from MSAccessRookie, I got the update query to update the TravelSegmentsCopy table as each SegTimeStamp column either reads 1/29/1900 3:20:00 pm or 1/29/1900 3:25:00 PM.

Then I do the append query to insert the data into the TravelSegments table and then run the code.

So after the code is run the I have the CalculateAll table with the travel times for timestamps without the 1/29/1900 date in them and with the date in them and the data is the same for both sets of data. So I guessing this is the way you meant the data to come out in the new CalculateAll table?

You can ignore the previous two posts I had for today, since I got it figured out. Thanks
I'm embarassed to say that I had completely forgotten the specifics of what this form and VBA code actually compute.

But yes, the numbers should come out the same, I should think, since we are merely using a different date. The logic sums up total travel time for a particular date-time stamp no matter what that date happens to be, therefore changing the date should not alter the totals. If you're getting the same totals, I think that's a good sign that the logic is correct.

ok well now I am going to trying loading all the data for all my days included in my study. I will let you know the results and any possible problems I may incur. Thanks for the help and wish me luck

Well not long before I ran into my first problem. I just noticed that in my original raw data for the timestamp, the date is never included. Each day's data is stored by the day.

I've tried the update query you mentioned earlier but that gives the data as 12/31/1899 or dates from the 1900's which I'm assuming is how Access reads it.

So is there a way to include the actual date even though it isn't included in the raw data? Thanks

Someone suggested that I need to provide the date as a parameter to a function which creates a date datatype from a string parameter. Probably something like date('20/11/08')

Someone suggested that I need to provide the date as a parameter to a function which creates a date datatype from a string parameter. Probably something like date('20/11/08')




Sorry I didn't address this before - the problem was I wasn't sure where you were getting your data from and how it was arriving. Nor am I sure how you are appending new data to the table.

Here's a suggestion. Extract the hours and minutes (and seconds?) from the original date and append that to the timestamp, using the following query. Let's suppose the date is Nov 20th 2008.

UPDATE TravelSegments SET SegTimeStamp = CDate("11/20/2008" + Space(1) + FORMAT(CDate(SegTimeStamp), "hh:mm AM/PM"))
WHERE CDate(SegTimeStamp) < 1901

The idea is to affect only those records of date 1899, that is, records dated before the year 1901. I haven't tested this - maybe I'll give it a quick try.

I decided to omit "seconds" and just deal with hours and minutes.

After a couple of edits, it seems to be working. I'll leave the testing up to you.

Yea that does work but just one problem if the timestamp is 12:00 AM then only the date shows up wtihout the timestamp. You mentioned omitted seconds above but I need the seconds because in my original data I use the seconds.

I tired to just add the :ss part to hh:mm but that didn't seem to work. Thank you

I want to thank you again for everything but I have just one more question to go with the one above.

Earlier I mentioned I needed all combinations of all segments but now it looks like I will only use segments that start with 1 (e.g. 1-2, 1-3,1-4,...1-15) not all the other combinations. So is there some way to modify the loop so the output will only be the segment starting with 1 at all 5 minute intervals throughout the day. Because I am gonna be working with about 90 days of data and that will probably take about 4 days to complete running day and night.

Again I appreciate what you did but due to time constraints I'm gonna have to use only those segment combinations starting with segment 1. Thank you

Is there an easy way to modify the code to include only the segments that start with 1 or I am just thinking that it is easy? Because I am not to good with the code if you haven't already noticed

Yea that does work but just one problem if the timestamp is 12:00 AM then only the date shows up wtihout the timestamp. You mentioned omitted seconds above but I need the seconds because in my original data I use the seconds.

I tired to just add the :ss part to hh:mm but that didn't seem to work. Thank you
Yes, that's correct - use :ss to extract the seconds.

I don't think the data you gave me included any seconds. I say this because I got the same results (zero seconds) even when I used "ss".

12:00 AM is the default time. Therefore it's possible that the time part will be omitted at that point - I can force it to include the time portion by wrapping the earlier query in an outer FORMAT:

UPDATE TravelSegments SET SegTimeStamp = FORMAT(CDate("11/20/2008" + Space(1) + FORMAT(CDate(SegTimeStamp), "hh:mm AM/PM")), "MM/DD/YYYY hh:mm:ss")
WHERE CDate(SegTimeStamp) < 1


But the problem is that the time functions gravitate toward military time and I'm not sure that's what you want to see. Personally I think it's odd that neither Ms Access nor Sql Server has non-military formatting as a built-in option. I could probably find a way to get it, but it won't be pretty.

Do want to go with military time or standard time?


Secondly, is this choice important for both the form and the table - or just for the form? That is, do you care whether the table shows military or standard? Or do you only care about what shows on the form?

I want to thank you again for everything but I have just one more question to go with the one above.

Earlier I mentioned I needed all combinations of all segments but now it looks like I will only use segments that start with 1 (e.g. 1-2, 1-3,1-4,...1-15) not all the other combinations. So is there some way to modify the loop so the output will only be the segment starting with 1 at all 5 minute intervals throughout the day. Because I am gonna be working with about 90 days of data and that will probably take about 4 days to complete running day and night.

Again I appreciate what you did but due to time constraints I'm gonna have to use only those segment combinations starting with segment 1. Thank you

The loop works by pairing up all possible start-segments with all possible end-segments. Here's how we get a list of all possible start segments (just two lines of code).

Dim rsStartSegments As New ADODB.Recordset
rsStartSegments.Open "SELECT DISTINCT Segment FROM TravelSegments ORDER BY Segment", CurrentProject.Connection

To limit the start segments to Segment-1, I'm guessing the easiest way - meaning the least possible code-changes - is to add a WHERE clause:

rsStartSegments.Open "SELECT DISTINCT Segment FROM TravelSegments WHERE Segment = 1 ORDER BY Segment", CurrentProject.Connection

In other words let this line replace the original line of code. I'll leave the testing up to you.

On another thread I saw a function that I'd never used before. It's called the TimeValue function. I think it can be used to convert military time to standard time. Let me know if you're interested - maybe I'll give it a try in your project.

Whenever the time reads 12:00 am I am still not getting the time with the date just the date is showing up but I'm not too concerned with that now I am going to create another column just for the date so don't worry about the time issue I think I can figure it out.

I'm working on downloading all the days data now and then going to get my final results in a couple days. Thanks again for everything I'll let you know about it when I have all the data ready for the analysis.

Good for you. And I'm sorry that it runs so slow. I could have coded it to run faster but was worried that complex code introduces bugs and is harder for you to edit and maintain, so I kept it as simple as possible.