adding travel times without regards for timestamp
- Thread starter rossmg9
- Start date
MSAccessRookie
AWF VIP
- Local time
- Today, 07:22
- Joined
- May 2, 2008
- Messages
- 3,428
The "Type Conversion Failure" probably occurs here:
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
Code:
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
MSAccessRookie
AWF VIP
- Local time
- Today, 07:22
- Joined
- May 2, 2008
- Messages
- 3,428
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
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.
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
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
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.
Last edited:
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 tired to just add the :ss part to hh:mm but that didn't seem to work. Thank you
Last edited:
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
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
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?
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.
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.
