Importing text and calculating time interval

  • Thread starter Thread starter Mary
  • Start date Start date
M

Mary

Hi! I have 2 questions: 1) When Access calculates the
difference between two times which were entered as time()
in fields with Medium Time format, it is off by one
minute in some instances. Is it retaining the seconds
even though I store it in the Medium Time format? 2)I
asked this last week, but my post never appeared - I am
importing a txt file using an Import Specification in the
TransferText of a macro and it fails, saying that
specification doesn't exist. But if I go to do
it "manually", I am able to choose that Import Spec. when
importing - can anyone help?
Thanks,
Mary
 
The answer to your first question is "It depends"

The way MS Office saves Date/Time is as follows:

Date is the whole number portion of the value and it starts with the value
of 1 for January 1st, 1900 (unless you are using the 1904 year system that
Macs use, of which I'm not sure why Macs used that unless it's cause year
1900 was NOT a leap year). Therefore, January 2, 1900 would be a value of
2, and so on.

Time is the decimal portion of the value, of which if you want the time of
the date/time value (military format), it would be:

Int(DTVar*24) mod 24 & ":" & Int(DTVar*1440) mod 60 & ":" & Int(DTVar*86400)
mod 60

Now that we have down the way Access stores time values, now the depends
part, where does it get the information from?

If the information is initially a text type format and the user only types
in the time of the hour and the minute, but not the format (given the
format), MS Office assumes the seconds is "00". If on the other hand, the
seconds has been provided and it's stored as a date/time data type with the
format of medium time or stored as a text with the information (to be
converted at a later time when it retrieves it), it will still retain the
seconds, so what it boils down to, it really depends on how you are
providing and storing the data in Access (rather it be through bound or
unbound forms).
 
The data is coming from the time function - that is, I
set the values of Medium Time bound fields (Start and
End) to time(). So it sounds like it's storing the
seconds also. Therefore, how do I get it to calculate
the difference using only the values that show? It
currently displays an Endtime-Starttime of 2:15 - 1:39 as
35 minutes. What function would enable me to ignore the
seconds from the values?
 
In that case, you would use the following code to get the result that you
are looking for:

Dim Start as Long, End as Long, Diff as Long
Start = Int(Starttime * 1440)
End = Int(Endtime * 1440)
Diff = End - Start

Note the "* 1440" part in the code. That's cause since you are wanting to
capture the hour and the minutes, but not the seconds, we are multiplying
the date/time value by 1440 as there are that many minutes (24 hours per day
* 60 minutes per hour) in a single day.

Why I calculated those values to a Long variable, 2 reasons. 1, we wanted
to be sure we don't run into an overflow type situation since MS Office is
only setup to store dates between January 1, 1900 and December 31, 9999.
The second reason, Long variable is more than easily capable of handling the
capacity and since we are only needed to take the date/time to an integer
number with us dropping the seconds, there is no need to use a decimal type
format (I.e. Single or Double Data Type).

As I just hinted at, by taking the value to it's Integer number (dropping
off the decimal portion of the value) after multiplying the value by the
1440 minutes per day, we drop the seconds off the time.

If you want to store the times with the seconds set to "00", you could do
the following:

StartTime = Int(Now * 1440) / 1440
 
Not sure which version you are using, but I wouldn't really know what may be
causing your issue right off hand. One thing though to pay close attention
to is the FileName (if you are including that as an argument) as that must
include the full path name and if using the the Universal Naming Convention
(UNC) format for the path name of the file, it must include the server name
as well.

UNC format is

\\<servername>\<pathname>\<filespecname>

mapped drive format is

<DriveLetter>:\<pathname>\<filespecname>
 
They are on a network drive and I specify the path all
the way back to T:\... You are saying that may not be far
enough?
 
With you taking the path name back to the "T:\", that's as far back as you
can go using the mapped drive method. The main thing to be concerned about
using the Mapped Drive method for the path name, be sure the shared folder
that the "T:\" is expecting, it is mapped that way on the system that the
code runs on else it will error out, which is one nice thing about the UNC,
you don't have to worry about mapping issues, when using the UNC method for
path names. But then I can see when using UNC versus when to use mapped
drives, as they both have PROs and CONs.
 
Back
Top