Results 1 to 6 of 6
  1. #1
    eddd is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    3

    Concatenating a Date (converted into a String) & Another String in a Text Box

    This seems like such a easy question, but I cannot for the life of me figure this out.



    This access query works fine:

    Code:
    SELECT Format(modified,"MM/DD HH:MM AM/PM") & [Modified BY] AS Expr1
    FROM dual;
    When I try to put it in the control source property of a text_box, it completely fails.

    Code:
    =Format([modified],"MM/DD HH:MM AM/PM") & [Modified BY]
    Any idea of how I can get this to work?

    Thanks!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,853
    'fails' means what - error message, wrong results, nothing happens?

    Use NN for minutes, MM is for months.

    The query should fail as well.

    Might want to concatenate a space or other character between the date and ModifiedBy.

    What about year?
    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.

  3. #3
    eddd is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    3
    You know what- that is strange that the query works, I double checked and it gives the same result interchanging MM and NN.

    I would prefer if the the year didn't show up in the textbox.

    Just to simply things I get the same error with this:

    "The expression you have entered contains invalid syntax- you may have entered a comma without a preceding value or identifier"

    Code:
    =Format([modified],"mm/dd hh:nn AM/PM")

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,859
    Strange. =Format([Created],"mm/dd hh:nn AM/PM") works for me in an unbound textbox. Anything that I can think of that might make this not work (regional settings re the date separator, [modified] not being a suitable value, control ) shouldn't cause a syntax error. You get this error message when exactly?
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  5. #5
    eddd is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    3
    I get the error as soon as clicking out of the box. But I was able to resolve the issue by creating a new blank text box. The one that I was editing was added using the "add existing fields option". This thread I was reading from on Stack Overflow was for a different error but I think this may have been the same general root cause:

    [Parent] is throwing off your Textbox as it is trying to access the Parent form/report (Confirmed through experimentation.) This could be potentially useful but keeps you from being able to display the value of the field Parent when you use an = sign, because Access assumes you are referring to the form's parent.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,859
    The one that I was editing was added using the "add existing fields option"
    Then the control was bound?
    I considered that but dismissed because I figured you'd
    a) get a message about an invalid control source and/or circular reference
    b) or simply break the link to the field you chose by inserting an expression as its control source

    OK, after that, I guess never mind. It seems you're saying the referenced field is not on the same form as the one containing the expression. At least, that's what I think the solution you posted is implying. If that's the case, try to add clarity to your post by providing information as to what is where. If it's not, then I'm none the wiser as to the cause. Glad you got it working though.

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

Similar Threads

  1. Replies: 2
    Last Post: 06-23-2017, 05:19 AM
  2. Replies: 6
    Last Post: 05-29-2015, 10:21 AM
  3. Replies: 9
    Last Post: 01-14-2015, 05:10 PM
  4. Stuck on Access concatenating into string
    By orangeman2003 in forum Queries
    Replies: 6
    Last Post: 12-25-2013, 03:16 AM
  5. format text string as date
    By rbrem in forum Access
    Replies: 2
    Last Post: 08-28-2012, 08:39 AM

Tags for this Thread

Posting Permissions

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