Page 1 of 2 12 LastLast
Results 1 to 15 of 22

Thread: Date convert

  1. #1
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139

    Unanswered: Date convert

    Hello;

    I have date data stored as mmddyy, however on single digit months and days the format could be mdyy. i.e. 2507 is February 5th, 2007. How can I format the data to reflect a 6 character field, i.e. 02/05/07? And will I be able to select date ranges in queries using this converted format as I would using real date data?

    Substr function seems to indicate specific positions within the field, however in my data the position could and does change.

    Thanks...

    Larry

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    In code:

    Format(Me.MyDateField, "mm/dd/yy")


    In Query:

    Format([mydateField,"mm/dd/yy")


    In Table:

    Select the field in question then in the Format property Short Date.


    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Hi CyberLynx;

    Format([mydateField],"mm/dd/yy") returned 11/01/10 from 40483. When changing the table property to Short Date, the same 40483 returned 11/1/2010. The real result should be 4/04/83 (or 04/04/1983).

    Thanks...

    Larry

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Sorry for the mistake earlier Larry....

    Okay then...try:

    Format(CDate(40483), "00/00/00")

    to get 04/04/83 or try:

    Format(Format(CDate(40483), "00/00/00"), "mm/dd/yyyy")

    to get 04/04/1983

    Now ya know that works and now we know that the format getting spit out does not have delimiters replace 40483 with the Field Name:

    Format(CDate(Me.myTextBoxName), "00/00/00")

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Hey Cyberlynx,

    Just out of curiosity, what exactly does CDate mean?


    Bud

    Hey, just as I closed this out and was about to move on, I noticed the title of the question that started this post....Let me guess now....ConvertDate????

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    storing dates as strings or integers always carries the risk of a problem later on, thats why in my books you should store a value int he most appropriate datatype.. which in this case is date/time, or if you must store it as a string then store it as a 6 or arguably 8 character string (ddmmyy or ddmmyyyy)

    Id be surprised if cdate can accurately resolve mdyy into a valid date

    you may have to write a function to convert the values to a date, in which case I'd look for something like.....

    Code:
    public function createdate(strdatevalue as string) as string
    'may want to check that the ioncioming value is all numeric
    createdate=vbnull 'set up a default value
    dim stryear as string
    dim strmonth as string
    dim strday as string
    dim intstrLength as integer
    intstrLength = strlen(strdatevalue) ' find the length of the string poasswed to the function
    'ok so what is valid
    if intstrLength >=4 and intstrLength<6 then 'we cannot have a date that is less than 4 characters or more than 6
      stryear = right$(strdatevalue,2) ' chop off the last two characters
      if intstrlength = 4 then 'its nice and easy... we have dmyy
        strmonth=left$(strdatevalue,1)
        strday=mid$(strdatevalue,2,1)
      elseif intstrlength = 6 then 'its nice and easy... we have ddmmyy
        strmonth=left$(strdatevalue,2)
        strday=mid$(strdatevalue,3,2)
      else ' we have a problem ... it could be mmd or mdd
    ..so the show stopperis how to resolve when the date starts with a 1 its January, Oct, Nov or Dec..
    'lets handle the easy cases first
        if cint(left$(strdatevalue,1))>1 then 'then its feb to Sept
          strmonth = left$(strdatevalue,1)
          strday = mid$(strdatevalue,2,2))
        elseif cint(mid$(strdatevalue,2,1))=3 then 'its definately January
          strmonth = left$(strdatevalue,1)
          strday = mid$(strdatevalue,3,2))
        elseif cint(mid$(strdatevalue,2,1))=0 then 'its definately October
          strmonth = left$(strdatevalue,2)
          strday = mid$(strdatevalue,3,1))
        else 'what do you do here.......
          'we have a value which is impossible to resolve
          'haven't got a scooby 
          'you will need to refine the algolryhtm to make an educated guess
          'based on other data nearby?
          'based on the state of sun and moon?
          'based on the mood your line manager is in?
          'based on the amount of whacky substances the previous developer was consuming when they developed that bit of code
      ENDIF
      createdate= cdate(strday & "/" & strmonth  & "/" stryear)
    endif
    end function
    HTH

  7. #7
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Hello All;

    CyberLynx... no joy with the newest suggestion. It returned the same result as the first. Using: Open_Date:Format(CDate([OpenDate]),"mm/dd/yy"). Reminder, this is being used in a QBE Make-Table query. And also, the field data can vary from 4 to 6 digits. So 40483 is 04/04/83, and 101906 is 10/19/06.

    Healdem... wow, that's some killer code. At the risk of seeming stupid, how do I get that to work in my query? Regarding the original data, I have no control over how its stored, its simply my source data. I import my AS400 query results via the ODBC Databases function in Access (right-click in the tables screen, select Import).

    Overall what I'm trying to do is once the data is brought into Access, I want to create a Make-Table query (using QBE) to create a table from which all other queries will pull their information from. I'm attempting to make a new table so that the imported date data is in a usable format. My thoughts are it would be easier to create a new table once, than to code everything I do to account for the weird date data in the imported table.

    Thanks so much for everyone's help on this problem.

    Larry

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    to use that function...
    first off put it in a vb module in your access project / mdb
    then call the function in the make query

    eg
    select createdate(mydatevalue) as adtaevalue from blah

    if the data is coming over from an AS400, and as yet it doesn't exist then I'd investigate further waht format options you have ont he AS400. that will save you a vast amount of time and effort. better yet export the AS400 dates as true date values (preferably separated by "/".. or whatever date separator you use)

    one thing I did notice on reviewing the function is that Ive assigned a cdate to the string value.... that isn't clever it should read...
    createdate= strday & "/" & strmonth & "/" stryear
    rather than
    createdate= cdate(strday & "/" & strmonth & "/" stryear)

  9. #9
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Hello Healdem;

    Thanks so much for your suggestions. I will try them and let you know. Regarding the suggestion related to setting the AS400 query to supply the data in date format, I did that, and once imported into Access the data showed up as garbage (unreadable symbols). The query results showed up correctly in report format, however imported in Access they were unreadable. Using the stored format brought me to where I am at now.

    Thanks...

    Larry

  10. #10
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Hello Healdem;

    I created the module, and running Debug produced the following error: Sub or Function not defined. Highlighted was: instrLength = strlen (strdatevalue).

    Thanks...

    Larry

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Larryg
    Hello Healdem;

    I created the module, and running Debug produced the following error: Sub or Function not defined. Highlighted was: instrLength = strlen (strdatevalue).

    Thanks...

    Larry
    thats the problem with writing code on the fly and mixing and matching languages
    replace strlen with len

    ..it retrieves the length of the string

  12. #12
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Hello Healdem;

    OK... worked out the rest of the errors, the code now debugs without errors. Now when running the query: Select createdate(OpenDate) as Open_Date from QGPL_LGTEST1; I receive the following error: Undifined function 'createdate' in expression. I can see the module, and the name is correct. What am I doing wrong?

    Thanks...

    Larry

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dunno
    first off check the function is defined as public in a module
    check the module is in the same access file (mdb) as the query
    failing that best thing to do is to zip up a copy of the query some sample data, the module and I'll have a look at it.

    btw apologies for the compile errors.. but considering it was done on the fly straight into this site without reference to access or vba it's not too bad. mind you the other adage y'get what y'pay for may come to mind

    btw where are you running this query?

  14. #14
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Hello Healdem;

    Re being public... yes. Re module being in the same DB... yes. Re zip up a copy... will do.

    Re apologies... none necessary. Buggy or not it was way better than I could do. Besides... it gave me a little challenge getting it to work. I like that, I learn better. You are to be commended for your effort and dedication to helping the inexperienced like me stagger through Access.

    Zip file to follow shortly.

    Thanks again...

    Larry

  15. #15
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Healdem... here ya go.

    Thanks again...

    Larry
    Attached Files Attached Files

Posting Permissions

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