By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,244 Members | 1,972 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,244 IT Pros & Developers. It's quick & easy.

Difference from two time fields: MS Access

Dököll
Expert 100+
P: 2,364
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT Data.ID, Data.GetTextFromText, Data.Stamp, DateDiff("h",[Stamp],[GetTextFromText]) AS HoursFromTimes
  2. FROM Data;
  3.  
Thanks for your help!

Dököll
Mar 25 '08 #1
Share this Question
Share on Google+
10 Replies


missinglinq
Expert 2.5K+
P: 3,532
Missing reference in the 2003 set up?

Linq ;0)>
Mar 25 '08 #2

Dököll
Expert 100+
P: 2,364
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
Mar 27 '08 #3

missinglinq
Expert 2.5K+
P: 3,532
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)>
Mar 27 '08 #4

Dököll
Expert 100+
P: 2,364
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
Mar 28 '08 #5

Dököll
Expert 100+
P: 2,364
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. 'We need to do additional checking to see what time data being added in
  3. 'Below creates a file with the data added and reads the file to find time partially hard-coded in if statement
  4. '" 7:0": Notice space between first open quote and actual number
  5. 'This is important to make sure we are getting hours and not minutes
  6. '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
  7. '"1:57:01", results in 7:01 AM.  In this case, we add a space to open quote so application looks
  8. 'for a space before the number, therefore 02/02/2008 1:57:01, from 2008 to 1
  9. 'in code we will always get 1 o'clock
  10.  
  11.  
  12. Dim FileNum1
  13. FileNum1 = "C:\ACCESS_2_TXT\usermate.txt"
  14. Open FileNum1 For Output As #1
  15.  
  16.  
  17. 'We are creating the file with both the IssuesItems and Email texboxes
  18. 'This is important because data added to IsuesItems (Email message, log scans, pre processors textbox) will have dates
  19. 'This is important because data added to Email textbox (Name, Email, Date/Time) will have dates
  20.  
  21.  
  22. Print #1, Items.Value 'this will recorde data and time values
  23. Print #1, Email.Value  'this will recorde data and time values
  24.  
  25.  
  26.  
  27. Close #1
  28. Me.Items.SetFocus
  29.  
  30.  
  31. 'We are reading the file with both the IssuesItems and Email texboxes
  32. 'This is important because data added to IsuesItems (Email message, log scans, pre processors textbox will have dates
  33. 'This is important because data added to Email textbox (Name, Email, Date/Time) will have dates
  34.  
  35.  
  36. Dim GoSplit As Variant, FileName1, f
  37. Dim strGetTime As String
  38. FileName1 = "C:\ACCESS_2_TXT\\usermate.txt"
  39. f = FreeFile
  40. Open FileName1 For Input As #1
  41. Do While Not EOF(f)
  42. Line Input #1, strGetTime
  43. GoSplit = Split(strGetTime, " ")    
  44.  
  45. 'Function looks within file as string, reads the string to find instances of the digit(s) in code
  46. '" 7:0": Notice space between first open quote and actual number
  47. 'This is important to make sure we are getting hours and not minutes
  48. '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
  49. '"1:57:01", results in 7:01 AM.  In this case, we add a space to open quote so application looks
  50. 'for a space before the number, therefore 02/02/2008 1:57:01, from 2008 to 1
  51. 'in code we will always get 1 o'clock
  52.  
  53.  
  54. If InStrB(strGetTime$, " 7:0") <> 0 And InStrB(strGetTime, " AM") <> 0 Then 'Function that check time from textboxes that hold time and time...
  55. GetTextFromText.Value = "7:00 AM"
  56. 'Me.GetTextFromText.Value = Format(Me.Stamp.Value - Me.GetTimeFromText.Value, "hh:mm")
  57. End If
  58.  
  59.  
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
Mar 29 '08 #6

P: 2
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]
Apr 1 '08 #7

Dököll
Expert 100+
P: 2,364
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 !

Expand|Select|Wrap|Line Numbers
  1. Function TimeFormat(ParamArray FieldArray() As Variant)
  2.     'declaration
  3.     Dim hours As Single
  4.     Dim minutes As Integer
  5.     Dim totalminutes As Single
  6.     Dim I As Integer
  7.         'collect the minutes
  8.         totalminutes = CSng(FieldArray(0))
  9.         'the formula
  10.         hours = totalminutes / 60
  11.         'since cint rounds these day's ive created my own cint(or i'm just doing something wrong and i'm not smart)
  12.         If InStr(1, hours, ",") > 0 Then
  13.             'take all the character before the comma
  14.             I = InStr(1, hours, ",") - 1
  15.             hours = Left(hours, I)
  16.         End If
  17.         'formula for the minutes
  18.         minutes = totalminutes Mod 60
  19.         'Format them together
  20.         TimeFormat = Format(hours, "00") & ":" & Format(minutes, "00")
  21.  
  22. End Function
Much appreciated, JGilis!

Will give it a go:-)
Apr 3 '08 #8

Dököll
Expert 100+
P: 2,364
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!
Apr 18 '08 #9

puppydogbuddy
Expert 100+
P: 1,923
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
Apr 20 '08 #10

Dököll
Expert 100+
P: 2,364
Hey this is very cool puppydogbuddy!

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

Have a wonderful week-end:-)
Apr 26 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.