Results 1 to 10 of 10
  1. #1
    Trace is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2022
    Posts
    12

    How do I concatenate 2 fields from table via query (and import result to table)?

    Hello and thanks to the volunteers helping out here!



    I want to generate an issue number using the date and time, with format yyyymmdd-hhnn.

    In my table "Activity Description" I have three fields:
    [IssueNumber] to be populated once concatenation works,
    [DateInitiated] short text data type, format yyyy-mm-dd with default value =(Now()), showing a date picker for dates
    [TimeInitiated] short text data type, short time format, default value =Time$()

    What I want to happen is that, when a form is populated and then saved, the system date and time will be recorded in the fields above and also concatenate to make an IssueNumber, which will then also be stored in the table.

    1) How do I make this work? Currently I am getting the following in my query:
    Click image for larger version. 

Name:	ConcatActDescQryDesign.JPG 
Views:	23 
Size:	43.3 KB 
ID:	48751 Click image for larger version. 

Name:	ConcatenateActDescQry.JPG 
Views:	22 
Size:	36.9 KB 
ID:	48752

    2) Is this the best way to do what I want?

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Do not do it, period.
    Now() already has the time included, plus you get the issue number just by formatting that field as you want.
    Do not store data that can be calculated, as yours can here.
    If you just want date then use Date()
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Trace is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2022
    Posts
    12
    OK, I won't use a query.
    Default value =Now() gives me an Issue Number with format m/dd/yyyy h:mm:ss PM
    What I'd like is format yyyymmdd-HHMM where HH is two digit hour two digit minute (24hr time)
    I input yyyy-mm-dd-HH, but the NOW format doesn't seem to accept format changes.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Now() and Date() are just numbers.
    You format them anyway you want them.
    You would still see your issue number in any query, just that it would be your field formatted as you want.

    Edit:
    Code:
    ? format(Now(),"yyyymmdd-HHMM")
    20220921-2109
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Trace is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2022
    Posts
    12
    Thank you for your help and I apologize for any misunderstandings.
    Here is what my table displays for Issue Number:
    Click image for larger version. 

Name:	Concatenate Activity NowJPG.JPG 
Views:	20 
Size:	23.8 KB 
ID:	48753

    And here is the design view where the Issue Number field format is yyyy-mm-dd-hhnn but the Default Value is set to =Now(). They don't seem to be playing well together.
    Click image for larger version. 

Name:	ConcatenateActivityNowDesignJPG.JPG 
Views:	20 
Size:	60.5 KB 
ID:	48754

    I had read somewhere that if my system time is set to 12 hr, I could change it to 24hr and then that would make some thing like =Now() display like what I want, BUT....
    ..would that mean that any users of the DB would ALSO have to use 24hr time on their machines?

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    I do not get the AM/PM
    However I can only assume as Format changes the data to a string type, it just puts the data in as system format.
    Then it cannot be formatted as yyyy-mm-dd-hh-nn (nn is minutes BTW), so stays as it is.

    As I stated, there is no need for Issue Number as you can calculate and format from whatever field the Now() is stored in?

    If you insist on keeping that IssuesNumber as a field, then it should be a Date field, not text, and then formatted as you want, preferably in the form, but in the table if you have to.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    Trace is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2022
    Posts
    12
    Thanks for your help. I'll study your responses some more and keep trying your suggestions. I appreciate the advice!!!

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Format(DateInitiated + Nz(TimeInitiated,0), "yyyy-mm-dd-hhnn")

    Is this a multiple simultaneous users split database? What if multiple users generate the same IssueNumber?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    @June7
    Is TimeInitiated even needed?
    Could have just one field DateTimeInitiated? Easy enough to get Date and Time from that one field?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Yep, no idea why OP has separate fields.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 2
    Last Post: 03-11-2020, 04:06 PM
  2. Replies: 13
    Last Post: 01-25-2016, 01:36 PM
  3. Replies: 30
    Last Post: 09-23-2015, 07:24 AM
  4. Replies: 3
    Last Post: 08-07-2013, 02:34 PM
  5. Replies: 3
    Last Post: 08-16-2011, 02:20 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