Results 1 to 15 of 15
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Merge into word giving wrong time (2000)

    Hi,

    When I pass a time over into word from a command button. It goes to a query which then sends it to a txt file to import into the word document.

    In the query it is displayed at 12:00, however in the text file it isdisplayed as 30/12/1899 12:00:00

    Any ideas?

    Luke
    Best Regards,

    Luke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Merge into word giving wrong time (2000)

    You can add a format switch to the merge field in Word:
    - Right-click the merge field.
    - Select Toggle Field Codes from the popup menu.
    - You'll see something like { MERGEFIELD TheTime * MERGEFORMAT }
    - Make it look like { MERGEFIELD TheTime @ "hh:mm" }
    - Press F9 to hide the field code and update the field.

  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge into word giving wrong time (2000)

    The time still remains the same

    Luke
    Best Regards,

    Luke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Merge into word giving wrong time (2000)

    Try updating the field (click in it and press F9).
    If that doesn't work, you either didn't enter the formatting switch correctly, or the field is not a real date/time field, but a text field.

  5. #5
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge into word giving wrong time (2000)

    I cannot see any errors and have done what you suggest.
    Best Regards,

    Luke

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Merge into word giving wrong time (2000)

    I hadn't read your original question carefully enough and overlooked the fact that you're using a text file as data source instead of the Access query. If you use the query directly, you can use the formatting switch as I indicated. If you want to use a text file, that won't work - the value is not seen as a time value but as plain text. So you must do the formatting in the query. Instead of just including the AptTime field in the query, you must use
    <code>
    AptTime: Format([tblValuations].[AptTime],"hh:mm")
    </code>
    When you export to a text file, the AptTime field will already be formatted correctly, and you don't need a formatting switch in the merge field.

  7. #7
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge into word giving wrong time (2000)

    That works a trest now, however the time in the field is 14:00 but comes into word as 02:00 - any ideas?
    Best Regards,

    Luke

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Merge into word giving wrong time (2000)

    Did you remove the @ "hh:mm" switch?

  9. #9
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge into word giving wrong time (2000)

    done that and it fixes the situation. Could the same be done to the date aspect on the query so that when it passes it 8/2/06 it procudes Thursday the 8th of February, 2006

    Luke
    Best Regards,

    Luke

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Merge into word giving wrong time (2000)

    You can use

    NameOfField: Format([NameOfTable].[NameOfField],"dddd d mmmm, yyyy")

    to produce Thursday 8 February, 2006. Putting in the ordinal (8th) is more work because you'd have to decide whether to use -st, -nd, -rd or -th.

  11. #11
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge into word giving wrong time (2000)

    Is it much more work? Is it possible any pointers?

    Luke
    Best Regards,

    Luke

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Merge into word giving wrong time (2000)

    You could copy the following function into a module in the Visual Basic Editor:
    <code>
    Function DisplayDate(varDate As Variant) As Variant
    Dim s1 As String
    Dim s2 As String
    Dim s3 As String
    Dim d As Integer
    If IsNull(varDate) Then
    DisplayDate = Null
    ElseIf Not IsDate(varDate) Then
    DisplayDate = Null
    Else
    d = Day(varDate)
    s1 = Format(varDate, "dddd") & " the " & d
    Select Case d
    Case 1
    s2 = "st"
    Case 2
    s2 = "nd"
    Case 3
    s2 = "rd"
    Case Else
    s2 = "th"
    End Select
    s3 = " of " & Format(varDate, "mmmm, yyyy")
    DisplayDate = s1 & s2 & s3
    End If
    End Function
    </code>
    In your query, use an expression like this:

    NameOfField: DisplayDate([NameOfTable].[NameOfField])

  13. #13
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge into word giving wrong time (2000)

    Genius!

    Another creme cake I owe you! - You will want loyalty rights on this db soon!
    Best Regards,

    Luke

  14. #14
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge into word giving wrong time (2000)

    Hans,

    Playing about with it now, the 22nd comes up as the 22th
    Best Regards,

    Luke

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Merge into word giving wrong time (2000)

    Try this version:

    Function DisplayDate(varDate As Variant) As Variant
    Dim s1 As String
    Dim s2 As String
    Dim s3 As String
    Dim d As Integer
    If IsNull(varDate) Then
    DisplayDate = Null
    ElseIf Not IsDate(varDate) Then
    DisplayDate = Null
    Else
    d = Day(varDate)
    s1 = Format(varDate, "dddd") & " the " & d
    Select Case d
    Case 1, 21, 31
    s2 = "st"
    Case 2, 22
    s2 = "nd"
    Case 3, 23
    s2 = "rd"
    Case Else
    s2 = "th"
    End Select
    s3 = " of " & Format(varDate, "mmmm, yyyy")
    DisplayDate = s1 & s2 & s3
    End If
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •