Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    139

    Unanswered: Date value in Text field of Table

    I would like to make the default of a text field the date value of the current date. When I used the date() function in the default column, it will not work on a text field. So, I created a date field put the default as date() and the format to display as mmddyyyy. This displayed the information like I wanted but when I append the record to another table, if goes as 01/04/07. I do not want the slashes only numerals. Currently, I append the information into this table and could use a query to append the date value into the text field but I cannot get a query to accomplish this task. Either way query or default in table, I would like to get the date value in numerals.

    Any suggestions would be appreciated.
    B&R

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Might I ask why you need it displayed as mmddyyy?
    This displayed the information like I wanted but when I append the record to another table, if goes as 01/04/07. I do not want the slashes only numerals.
    Also, if you're trying to save the date as data, then I would recommend saving it as a date. You can just change the way it appears using the format.
    Me.Geek = True

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    It is best to store a date as a date and not as a string(text). When the value is a date you can display it any way you want. The real advantage of storing it as a date is when you are creating queries. As a date you will always get the right information when you filter the data using =, >, <, etc. As a string you may or may not get the right info.

    I agree with Nick, use the Format property to make the date appear the way you want it to, but store the data as a date. At the table level you can set the Format property and then anything new that you base off of that table will default to the appearance you prefer.

    Also, to convert a date to a string you can use the Format function.

  4. #4
    Join Date
    Feb 2004
    Posts
    139
    Thanks for your reply! I want the date as numeric in the table so I can
    append it to a table in Quickbooks. You cannot manipulate the way it
    displays in Quickbooks so I must send it over as text not a date. The data
    will represent the PO number on an invoice and they want it to be the date
    but without the slashes.

    I hope I have answered your question. If you have any other ideas, I would
    appreciate the help.

    Thanks,
    B&R

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    In that case, save the data as a date. On the table design, go to the field you're interested in, in the general tab for that field type "mmddyyyy" in the Format field. That should display like you want.
    Me.Geek = True

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Store the data as a date. Use a query to pull the data you want to export to QuickBooks. Something like this:

    Select thisfield1, thatfield, CStr(FORMAT(datefield,"mmddyyyy")) as field3....

    It will convert your date to text in the format you want.
    Inspiration Through Fermentation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •