none
Saving a date as CCYYMMDD in a field on MS Access 2016 RRS feed

  • Question

  •   What I have spent time trying to create is how to save a date in MS Access 2016 in the format of CCYYMMDD.  When I create or update a record, I want to save the Date as the Date Modified field.  Yet, I want to save it as CCYYMMDD, since in another part of the system, when I generate reports, I want to use the Date Picker to allow the user to select a date, then I will create the reports where all records have been modified after the selection date.

      What I have done is create a field on the table call DateModified and it has a field type as Date/Time.  The Format is Short Date, this is just to get it to work for now and the Default Value is =Date().  What I have been testing was to create a Custom Format of "YYYYMMDD", thinking it should format the value and save it as CCYYMMDD.  Yet, when I did this, it actually put YYYYMMDD in the field.  Can someone plz help me to understand how to convert save the value as CCYYMMDD? 

      This way I am setting the information up on the table, so when I get ready to do the reporting I can easily use the Date Picker to allow the user to select a date and then when they click on Execute, I can take the Date from the Date Picker, change it's format to CCYYMMD and say select all the records from the table where DateModified is > DateSelected.

      Any ideas on doing this would be greatly appreciated and I am sure I am not the first person than wants to store the data and do reporting like this in MS Access 2016, but cannot find items on the net to guide me.  Thanx in advance.

    Jerry

    Thursday, February 21, 2019 5:23 PM

Answers

  • Hi Jerry. Is CC a valid format argument? Is it for the century? If the format gets changed to YYYYMMDD instead of CCYYMMDD, what difference does it make for the value? For example, today's date would have been 20190221 in YYYYMMDD format. So, if CC is for the century, wouldn't it also be 20190221 in CCYYMMDD format? Sorry if I am misunderstanding.
    • Marked as answer by ballj_351 Thursday, February 21, 2019 5:46 PM
    Thursday, February 21, 2019 5:33 PM

All replies

  • Hi Jerry. Is CC a valid format argument? Is it for the century? If the format gets changed to YYYYMMDD instead of CCYYMMDD, what difference does it make for the value? For example, today's date would have been 20190221 in YYYYMMDD format. So, if CC is for the century, wouldn't it also be 20190221 in CCYYMMDD format? Sorry if I am misunderstanding.
    • Marked as answer by ballj_351 Thursday, February 21, 2019 5:46 PM
    Thursday, February 21, 2019 5:33 PM
  • No it is not a valid format.  I am just saying this is what I want it to be.  I know it should be YYYYMMDD and this is what I tried.  I use CCYYMMDD just for the content of this message.
    Thursday, February 21, 2019 5:40 PM
  • No it is not a valid format.  I am just saying this is what I want it to be.  I know it should be YYYYMMDD and this is what I tried.  I use CCYYMMDD just for the content of this message.
    Thursday, February 21, 2019 5:40 PM
  • This is working now and I think the issue I had was I put the information in quotes when I entered it on the format field.  It is now working as designed.  Thanx.
    Thursday, February 21, 2019 5:47 PM
  • This is working now and I think the issue I had was I put the information in quotes when I entered it on the format field.  It is now working as designed.  Thanx.
    Hi. Glad to hear you got it sorted out. What exactly do you have now in the Format property? Is it CC or YY? Just curious...
    Thursday, February 21, 2019 5:58 PM
  • I made it yyyymmdd.  I ddi not have to add anything to the form and it is 20190221.  Now I have to modify my report selection form and use the DatePicker to ensure the user selects a valid date.  Then once I get the value from this field, switch it over and then write the select clause.  Trying to do things step by step as I go along.
    Thursday, February 21, 2019 6:02 PM
  • I made it yyyymmdd.  I ddi not have to add anything to the form and it is 20190221.  Now I have to modify my report selection form and use the DatePicker to ensure the user selects a valid date.  Then once I get the value from this field, switch it over and then write the select clause.  Trying to do things step by step as I go along.

    Okay. Thanks for the update. Good luck with the rest of it. Cheers!
    Thursday, February 21, 2019 7:18 PM