Results 1 to 6 of 6
  1. #1
    Poohbear0471 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    78

    Report field formatting


    I'm creating a database to simplify letter creation for my team at work. We use the same data to create one of 12 potential letters, depending on the account status. I'm having an issue with the formatting of three specific fields on my reports.

    My control is ="blah blah blah, "[Entity_Name] =", blah blah blah on "[Event_Date] =". blah blah blah ​by telephone at "[Entity_Phone]=".

    My record source for the report comes directly from the letters table. The problems I'm having are the Entity_Name and Entity_Phone show up as the Entity_ID. I need the Event_Date to display as mmmm dd, yyyy format (January 5, 2019) and bold type, but it's showing up as mm/dd/yyyy (1/5/2019) instead. If I remove the fields from the control and place them in the report directly, the formatting is correct, but then my spacing is all off.

    I'd happily use a plain ol' Word mail merge, if it's possible to sort the data to the correct letters, but I can't seem to find anything that says that's possible. btw... I'm pretty illiterate at vba.

    Thanks,

    Ed

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The EntityID issue sounds like you are using lookup fields at table level which will cause all sorts of grief.
    It would be far better to fix that in table design rather than looking for workrounds.

    You can format the dates as you want them for output

    Suggest you look at Albert Kallal's super easy mail merge utility.
    The code is complex but using it is ...super easy.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Poohbear0471 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    78
    So I should be doing the lookup on the form, not on the table? I've got the dates formatted as mmmm dd, yyyy every place I can find. It appears correctly if it's not within a control. (btw, I checked out your user security and application window codes. Awesome work and very helpful!)

    ***Edit... Kallal's Mail Merge isn't exactly what I wanted, but it's close enough to work as a stable interim solution until our IT department gets around to automating our process. This is going to be a massive time saver for my team!***

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by Poohbear0471 View Post
    So I should be doing the lookup on the form, not on the table? I've got the dates formatted as mmmm dd, yyyy every place I can find. It appears correctly if it's not within a control. (btw, I checked out your user security and application window codes. Awesome work and very helpful!)

    ***Edit... Kallal's Mail Merge isn't exactly what I wanted, but it's close enough to work as a stable interim solution until our IT department gets around to automating our process. This is going to be a massive time saver for my team!***
    Correct. Lookups should ONLY be done at form level. See this article for several reasons: The Evils Of Lookup Fields in Tables.
    By chance I've just added an article on my website with a further reason for not using them: http://www.mendipdatasystems.co.uk/t...lds/4594445135.
    That was done following a parallel discussion here: https://www.accessforums.net/showthread.php?t=75109

    Dates can be formatted any way you like in a control including your preference for mmmm dd, yyyy.
    However, for SQL statements the dates need to be in the format mm/dd/yyyy or you will get some incorrect outcomes

    Glad you found those items useful. There's lots more on my website if you are interested

    I've used Albert's code since around 2006. Its much easier to organise the mail merge from Access than to do the standard method.
    I originally intended it to be an interim solution as well but never had a reason to replace it.
    I've done some tweaks and updated his rather old fashioned forms but otherwise its much the same as when I first found it
    Although the code is very old, its still widely used by many people
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Poohbear0471 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    78
    Thank you VERY much. I greatly appreciate your assistance and knowledge!

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You're welcome
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 10-02-2018, 09:57 AM
  2. Replies: 5
    Last Post: 05-22-2017, 01:34 PM
  3. Replies: 1
    Last Post: 01-05-2017, 05:14 PM
  4. Replies: 11
    Last Post: 12-08-2015, 11:47 AM
  5. Replies: 1
    Last Post: 04-26-2012, 08:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums