Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Joining Fields (2003)

    Is there a way of joining a date/time field and a memo field in a report? What I'm trying to do is put the time and appointment in a report. Also, the style of thememo field in the report is set to normal, but when I try to put a line behind the memo, it runs right through it. The database has been created by someone else, and I'm adapting it to suit our needs. There is a time line running down the left hand side of the group footer but it all appears together when the report is run.

    Rob

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Joining Fields (2003)

    Rob

    Here's a snippet to add a Date/Time line in a memo field:

    Me![Comments] = Format(Now, "ddd/mmm/yy hh:mm") & ": " & Me![TempComments] & vbNewLine & Me![Comments]

    TempComments is a text control which the user enters comments.
    The code can be attached to a command button to update the Memo, so instead of the user filling a memo, the user filla text and transfers the data to the memo.

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Joining Fields (2003)

    Thanks Dave. I'll try and work round what you've given me. There's more than one memo, and the times are specific. They come from a schedule table, where I've got an appointment and a time (Meeting in training room 08:00 to 09:00). Plus I'm get the 24 hour times for after midday, I want the 12 hour.

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Joining Fields (2003)

    Rob
    Glad to help.
    If you need further assistance, feel free to post any problems or code snippets to help you on your way.

    I'm sure if I can't help you further, some-one will.

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

    Re: Joining Fields (2003)

    Do you really need to combine the fields into one control? You may be able to get what you want with separate controls for the individual fields if you set the Can Grow and Can Shrink properties for all of those controls, and also for the Detail section, to Yes.

    If you want the 12 hour clock, set the format of the time field(s) to something like h:mm AM/PM, or if you concatenate fields, use something like

    Format([NameOfField], "dd/mmm/yy h:mm AM/PM")

  6. #6
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Joining Fields (2003)

    No I can't figure it out, as I said, it's not my database, I'm adapting it to meet my needs. There's a time line in the footer of the report and it uses a select query to pick up the appointments from a schedule table. The start time and end time and appointments are all seperate fields. Somehow, the select statement, stretches the appointments box to the correct times, but I've been asked to put the starting and finishing times on the appointments.

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

    Re: Joining Fields (2003)

    That sounds like a heavily customized report; it probably uses VBA code to position and size controls. Without knowing the details, it is hard to give advice.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Joining Fields (2003)

    The select statement isn't doing anything on the report. The behavior has to be in the report itself, either in the can grow/can shrink of the control or in code behind the report. Start and end times in separate fields make sense to me. We do something similar in the software my employer builds, but I don't understand what you're trying to do. What do you mean by "trying to put a line behind the memo"? Hans suggestion still makes sense if you're trying to incorporate the start and end time of an appointment into the detail of the report and tie the times to the memo. However, that only applies if each appointment has a separate memo, not if one big memo field is used to hold all the details.
    Charlotte

  9. #9
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Joining Fields (2003)

    The report I'm trying to achieve is nearly there, but I either want dotted lines going accross the report , adjacent to the times, but I can't hide them behind the appointments. OR have the times in the appointments. OR BOTH. I've tried everything I know.
    Attached Files Attached Files

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

    Re: Joining Fields (2003)

    Does the attached do what you want? I changed the control source of the appointment text box; since it is no longer bound to just the Appointments field itself, I changed its name and modified the code accordingly.
    Attached Files Attached Files

  11. #11
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Joining Fields (2003)

    Yes, that's great thanks Hans. Could you explain, how the report column works, and the timeline. As I stated, I've adapted this database from somewhere else, without really knowing how it works. Thanks again.

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

    Re: Joining Fields (2003)

    The time line itself is a fixed part of the WeekOf footer section, consisting of a lot of line and label controls.

    The On Format event of the detail section of the report contains code to position the text boxes corresponding to the appointments:

    Me.MoveLayout = False
    Me.Appointment.Top = lngTopMargin + DateDiff("n", datSchedStart, Me.StartTime) * lngOneMinute
    Me.Appointment.Height = DateDiff("n", Me.StartTime, Me.EndTime) * lngOneMinute
    Me.Appointment.Left = DateDiff("d", Me.WeekOf, Me.SchedDate) * 2160

    The first of these lines specifies that Access doesn't move down to display/print the detail section. Normally, records are printed below each other. Here, they are all printed in the same space.
    The second, third and fourth of these lines set the position and height of the Appointment text box according to the data:
    - Top (distance from the upper border of the section) is determined by StartTime (to be more accurate, the time between 8 AM and StartTime)
    - Height is determined by the time between StartTime and EndTime, i.e. the duration of the appointment.
    - Left (distance from the left side of the section) is determined by the day of the week.
    There are some constants used to tweak the position and size (lngOneMinute, lngTopMargin, 2160)

  13. #13
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Joining Fields (2003)

    Just one more thing, I hope, how can I format just the times to be in bold?

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

    Re: Joining Fields (2003)

    Ordinary text boxes don't support formatting part of the text. There is a control that does, the Microsoft Rich TextBox Control, but it is a pain to work with, and you might run into problems because it is not installed on all PC's. The alternative would be to use two text boxes: one for the times, and one for the appointment text. You can adapt the code to position them. See the attached version. You can modify the formatting of the text boxes to your liking.

    Note: the height of the appointment box will not be correct if the text of the appointment is too long. This is especially noticeable for appointments with a short duration, say less than 45 minutes.
    Attached Files Attached Files

  15. #15
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Joining Fields (2003)

    That's better than what I'd intended, thanks. The problem with the text of the appointment being too long is one they'll have to put up with, that's why I wanted the times on as well. It looks alright at the moment. Thanks once again for all your help.

    Rob.

Page 1 of 2 12 LastLast

Posting Permissions

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