Difference from two time fields: MS Access

[Dököll]

Hey Guys!

I ran into a puzzle; below query gets me what I need (Difference from two time fields) in Ms Access 2000 but not in MS Access 2003, any idea why:

SELECT Data.ID, Data.GetTextFromText, Data.Stamp, DateDiff("h",[Stamp],[GetTextFromText]) AS HoursFromTimes
FROM Data;
 

Thanks for your help!

Dököll

[missinglinq]

Missing reference in the 2003 set up?

Linq ;0)>

[Dököll]

Missing reference in the 2003 set up?

Linq ;0)>

Hey there missinglinq!

I am not sure how you mean. Do you suppose when this version was installed something was not added?

What's the remedy for that, if this is the case?

Thanks for replying!

Dököll

[missinglinq]

As time goes by in a given installation/version of Access, new features/functions are needed, and in order to provide some of them, new refernce libraries are added. We tend to forget this as time goes by and we get use to using these functions automatically. But a new installation only installs the very basic libraries, and so some of the needed libraries may be missing. Here's a link to Allen Browne's paper on checking for this kind of probelm and fixing it:

http://allenbrowne.com/ser-38.html

Anytime you have functions/code that's been working fine and it breaks when you move to another installation/version on the same PC, you need to think about missing references. Also moving an app to another machine and having it break calls for checking the references. I have even heard of cases where applying service packs have caused this problem, but can't remember the version/SP involved.

Linq ;0)>

[Dököll]

http://allenbrowne.com/ser-38.html

Anytime you have functions/code that's been working fine and it breaks when you move to another installation/version on the same PC, you need to think about missing references....

Linq ;0)>

Much appreciated assistance, will check into it. I see what you mean now, the code works here just fine, running 2000 Premium, when I load it to 2003, I get strange readings, you know 94375643 as opposed to one digit.

Thanks again!

Dököll

[Dököll]

Much appreciated assistance, will check into it. I see what you mean now, the code works here just fine, running 2000 Premium, when I load it to 2003, I get strange readings, you know 94375643 as opposed to one digit.

Thanks again!

Dököll

Added info...

I figured perhaps mentioninf what I truly hope to achieve may give me a better idea...

Data added to a database carries date and time, and I have skillfully matestred the date portion where/if date is less than todaydate, user gets a pop up

But if time is after 4 PM even if date is less than todaydate, user does not get a pop.

So in order to achieve this I need to first grab time out of the data:

 
'We need to do additional checking to see what time data being added in
'Below creates a file with the data added and reads the file to find time partially hard-coded in if statement
'" 7:0": Notice space between first open quote and actual number
'This is important to make sure we are getting hours and not minutes
'Time may be as 1:57:01 AM, if 1 is not in code and 7 is recorded in code as number/digit to find
'"1:57:01", results in 7:01 AM.  In this case, we add a space to open quote so application looks
'for a space before the number, therefore 02/02/2008 1:57:01, from 2008 to 1
'in code we will always get 1 o'clock
 
 
Dim FileNum1
FileNum1 = "C:\ACCESS_2_TXT\usermate.txt"
Open FileNum1 For Output As #1
 
 
'We are creating the file with both the IssuesItems and Email texboxes
'This is important because data added to IsuesItems (Email message, log scans, pre processors textbox) will have dates
'This is important because data added to Email textbox (Name, Email, Date/Time) will have dates
 
 
Print #1, Items.Value 'this will recorde data and time values
Print #1, Email.Value  'this will recorde data and time values
 
 
 
Close #1
Me.Items.SetFocus
 
 
'We are reading the file with both the IssuesItems and Email texboxes
'This is important because data added to IsuesItems (Email message, log scans, pre processors textbox will have dates
'This is important because data added to Email textbox (Name, Email, Date/Time) will have dates
 
 
Dim GoSplit As Variant, FileName1, f
Dim strGetTime As String
FileName1 = "C:\ACCESS_2_TXT\\usermate.txt"
f = FreeFile
Open FileName1 For Input As #1
Do While Not EOF(f)
Line Input #1, strGetTime
GoSplit = Split(strGetTime, " ")    
 
'Function looks within file as string, reads the string to find instances of the digit(s) in code
'" 7:0": Notice space between first open quote and actual number
'This is important to make sure we are getting hours and not minutes
'Time may be as 1:57:01 AM, if 1 is not in code and 7 is recorded in code as number/digit to find
'"1:57:01", results in 7:01 AM.  In this case, we add a space to open quote so application looks
'for a space before the number, therefore 02/02/2008 1:57:01, from 2008 to 1
'in code we will always get 1 o'clock
 
 
If InStrB(strGetTime$, " 7:0") <> 0 And InStrB(strGetTime, " AM") <> 0 Then 'Function that check time from textboxes that hold time and time...
GetTextFromText.Value = "7:00 AM"
'Me.GetTextFromText.Value = Format(Me.Stamp.Value - Me.GetTimeFromText.Value, "hh:mm")
End If
 
 

This is working, but when I get say 7:00 I want to comapre against the TimeStamp, thus 7:00 - the TimeStamp time would give me time elapsed in hours..

I do not want to write too much code for it, I know it is pretty simple I just cannot see, a quick attempt to make the database solid.

What are your thoughts? Thanks!

Dököll

[JGilis]

Hi

I've written a function on converting minutes to hh:mm format

So now the syntax in you query should just be this:

TimeFormat(datediff("n"; <starttime>; <endtime>))

This is the function just make a module (name not important)
and paste it in enjoy !

[HTML]Function TimeFormat(ParamArray FieldArray() As Variant)
'declaration
Dim hours As Single
Dim minutes As Integer
Dim totalminutes As Single
Dim I As Integer
'collect the minutes
totalminutes = CSng(FieldArray(0))
'the formula
hours = totalminutes / 60
'since cint rounds these day's ive created my own cint(or i'm just doing something wrong and i'm not smart)
If InStr(1, hours, ",") > 0 Then
'take all the character before the comma
I = InStr(1, hours, ",") - 1
hours = Left(hours, I)
End If
'formula for the minutes
minutes = totalminutes Mod 60
'Format them together
TimeFormat = Format(hours, "00") & ":" & Format(minutes, "00")

End Function[/HTML]

[Dököll]

Hi

I've written a function on converting minutes to hh:mm format

So now the syntax in you query should just be this:

TimeFormat(datediff("n"; <starttime>; <endtime>))

This is the function just make a module (name not important)
and paste it in enjoy !

Function TimeFormat(ParamArray FieldArray() As Variant)
    'declaration
    Dim hours As Single
    Dim minutes As Integer
    Dim totalminutes As Single
    Dim I As Integer
        'collect the minutes
        totalminutes = CSng(FieldArray(0))
        'the formula
        hours = totalminutes / 60
        'since cint rounds these day's ive created my own cint(or i'm just doing something wrong and i'm not smart)
        If InStr(1, hours, ",") > 0 Then
            'take all the character before the comma
            I = InStr(1, hours, ",") - 1
            hours = Left(hours, I)
        End If
        'formula for the minutes
        minutes = totalminutes Mod 60
        'Format them together
        TimeFormat = Format(hours, "00") & ":" & Format(minutes, "00")
 
End Function

Much appreciated, JGilis!

Will give it a go:-)

[Dököll]

Hi

I've written a function on converting minutes to hh:mm format

So now the syntax in you query should just be this:

TimeFormat(datediff("n"; <starttime>; <endtime>))

This is the function just make a module (name not important)
and paste it in enjoy !

[HTML]Function TimeFormat(ParamArray FieldArray() As Variant)
'declaration
Dim hours As Single
Dim minutes As Integer
Dim totalminutes As Single
Dim I As Integer
'collect the minutes
totalminutes = CSng(FieldArray(0))
'the formula
hours = totalminutes / 60
'since cint rounds these day's ive created my own cint(or i'm just doing something wrong and i'm not smart)
If InStr(1, hours, ",") > 0 Then
'take all the character before the comma
I = InStr(1, hours, ",") - 1
hours = Left(hours, I)
End If
'formula for the minutes
minutes = totalminutes Mod 60
'Format them together
TimeFormat = Format(hours, "00") & ":" & Format(minutes, "00")

End Function[/HTML]

Hello JGilis!

This was truly helpful, I was able to make fit my needs... To tell you the truth new management has made it possible to no longer have to seek dates nor have a report rendered; but wanted to thank you anyway for all your help, nice of you. Have a great week-end!

[puppydogbuddy]

Hi Dokoll,

If you are interested in date/time stamping and maintaining a log table for your transactions, then this excellent code from the tips page of
www.aadconsulting.com should help you out.

Audit Trail for Table Records

In a Data Form:

Private Sub Form_BeforeUpdate(Cancel As Integer)

'Purpose: Timestamp current form record
'Field Controls:
'LastEdit - General Date DataType
'DateAdded - General Date DataType
'CreatedBy - Text DataType
'LastEditBy - Text DataType

On Error GoTo RecStamp_Error

Me.LastEdit = Now
Me.LastEditBy = GetUser()

' Only stamp DateAdded and CreatedBy fields if a new record
If Me.NewRecord Then
___Me.DateAdded = Now
___Me.CreatedBy = GetUser()
End If

RecStamp_Exit:

Exit Sub

RecStamp_Error:

MsgBox Err.Description
Resume RecStamp_Exit

End Sub


In a Code Module:


Option Compare Database
Option Explicit

Public Declare Function GetUserName _
Lib "Advapi32.dll" Alias "GetUserNameA" _
(ByVal ABuffer As String, nSzie As Long) As Long

Public Function GetUser() As String
'Returns Windows User LogOn ID
On Error GoTo GetUser_Err

Dim sUserName As String
Dim lSize As Long
Dim lLength As Long

sUserName = String(15, " ")
lSize = Len(sUserName)
lLength = GetUserName(sUserName, lSize)
GetUser = Left(sUserName, lSize - 1)

Exit Function
GetUser_Err:

GetUser = "Unknown"
Exit Function

End Function

[Dököll]

Hey this is very cool puppydogbuddy!

I will keep it in my library and also send to the group...

Have a wonderful week-end:-)