Results 1 to 4 of 4
  1. #1
    IIBRAHIM is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    7

    Adding a New Column to an existing MS Access table using VBA and assign a value in a date

    I have an existing ms access table.




    I want to use VBA to append a new field (column) with the DataType as short text. I want to assign the value of the field in the format of "YYYYMMDDhhss" where YYYY is the year which is determined by a date in another column, MM is the month determined by the month in another column, DD is the date determined by a date in another column. The date format for that column is DD/MM/YYYY.

    The hh is the hours, the ss is the seconds. these can be assigned values of 01 and 01 respectively.


    Kindly assist with the appropriate VBA Code.

  2. #2
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162
    I usually use format function to input date in a table. If input is from a date type variable I would use the following formula to input date from a control of my form:
    dt = yourform.dtControl (here dtControl is the control of your form where you put date)
    "#" & Format(dt, "yyyy-mm-dd") & "#".
    If they come from separate fields an additional function may be required.

  3. #3
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If you already have all that data saving it again is unnecessary and not good practice, you can always create it from the stored data.
    If any of the source values change you would have to capture that and update your new field.

    You can simply create a calculated result in a query

    MyTextField = Format([YourDateField],"yyyymmdd")
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I see a couple of posts saying you must use vba to modify your tables. If you must add to/modify tables and you must use VBA, then review this Allen Browne material and examples.

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

Similar Threads

  1. Replies: 6
    Last Post: 11-24-2021, 06:50 PM
  2. Replies: 2
    Last Post: 11-24-2021, 11:36 AM
  3. Replies: 1
    Last Post: 09-08-2020, 01:09 AM
  4. Replies: 7
    Last Post: 10-19-2016, 11:11 AM
  5. Replies: 3
    Last Post: 07-28-2011, 11:24 AM

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