Page 2 of 2 FirstFirst 12
Results 16 to 27 of 27
  1. #16
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Well the syntax shown is correct, so either your field names are incorrect, or something else is wrong.



    Can you post up a sample of the form and enough data to demonstrate.
    Or a picture of the controls properties?
    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 ↓↓

  2. #17
    mar7632 is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    Spain - Madrid
    Posts
    72
    If I write this: =Format([fecha_entDT]) & "-" & [CODIGO_planta] & "-" & [id_item] it works fine, but it shows: 20/03/2019 11:57:56-NA-120
    I have changed fecha_entDT format in field properties in my table to yyyymmdd. If I go to my form in which this field is in it, it shows correctly this format, but when I click on it appear this date: 20/03/2019 11:57:56. I think that the problem is that access (somehow) take that long date format instead of yyyymmdd.

  3. #18
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Please, in general, don't format anything at a table level, it will give you a world of pain further down the line.
    Formatting like that only applies when viewing the data, and as you should never view table data directly is therefore pointless and confusing.
    You should always apply formatting at the report or from level when you need it.

    Remove the table formatting, and go back to my original answer.

    To prove that the format works in the immediate window in the VBA editor type

    Code:
    ? Format(Date(),"yyyymmdd")
    You should get 20190320
    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. #19
    mar7632 is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    Spain - Madrid
    Posts
    72
    Ok, I deleted the formatting on the tables. But when I type your original answer the same message is shown. Regarding ? Format(Date(),"yyyymmdd" It dont get anything ;( when I write that in VBA window

  5. #20
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by mar7632 View Post
    Ok, I deleted the formatting on the tables. But when I type your original answer the same message is shown. Regarding ? Format(Date(),"yyyymmdd" It dont get anything ;( when I write that in VBA window
    That makes no sense, did you get an error ? (Your quoted text is missing the closing quote " so as typed you would have got an error message)
    The immediate window is shown by pressing control + g in th vba editor.

    As to your original code this should work,

    Code:
    =Format([fecha_entDT],"yyyymmdd") & "-" & [CODIGO_planta] & "-" & [id_item] 
    
    I've just tried it on a similar layout based on columns in a list box (column 2 is a date field) , and a formatted check box;
    Code:
    =Format([lstDBList].[column](2),"yyyymmdd") & "-" & [lstDBList] & "-" & Format([chkUnlockList],"Yes/No")
    and this gives me

    20190320-15-Yes

    In the text box.
    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 ↓↓

  6. #21
    mar7632 is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    Spain - Madrid
    Posts
    72
    Pfffff... Okay, I found out the problem... My access program is in spanish so year = año... So, Finally I got it.... Oh my lord...

    It is a little bit different, but it works for my case: =Format([fecha_entDT];"aaaammdd") & "-" & [CODIGO_planta] & "-" & [id_item]
    And it shows what I want from the beggining. Thank you so much for your patience Minty...

    One more thing, How do I copy the value that I obtain from this textbox called "txtCodigoRFQtxt" into my field "CodigoRFQ" which is in the same form, instead of copying it manually?

    Thank you so much!
    Last edited by mar7632; 03-20-2019 at 06:28 AM. Reason: Spelling

  7. #22
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Glad you have it sorted out - I was beginning to wonder if it was a language issue (I'll try and remember that in future!)

    As for storing this value, personally as already stated I wouldn't.
    You can always create it from already stored information and therefore storing it leads to issues if any of the underlying values are changed anywhere in your system, you have to capture that and update this value. It's not a recommended practice.

    If you must then simply use the afterupdate event of one (or all) of textboxes that hold the constituent parts, and set the control to the same value.
    As you can see you already need to capture this two or three times, whereas a calculated value remains accurate at all times.
    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 ↓↓

  8. #23
    mar7632 is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    Spain - Madrid
    Posts
    72
    One more question,

    In a number field, how do I display after a number is introduced in a form a text like "weeks"

    For example: 5 weeks

  9. #24
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can use a label control with caption set to "weeks".
    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.

  10. #25
    mar7632 is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    Spain - Madrid
    Posts
    72
    Could you provide me a example, please?

  11. #26
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    or you can use the format property for the control e.g. put this in the format property

    0" weeks"

  12. #27
    mar7632 is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    Spain - Madrid
    Posts
    72
    Thank you Ajax!

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 11-27-2017, 02:34 PM
  2. Replies: 3
    Last Post: 12-30-2016, 10:27 AM
  3. Replies: 4
    Last Post: 12-06-2014, 08:49 PM
  4. Replies: 0
    Last Post: 11-19-2014, 05:47 AM
  5. Replies: 4
    Last Post: 03-12-2013, 06:49 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