Results 1 to 8 of 8
  1. #1
    Stillgate is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    2

    Beginner ACCESS user

    I'm self teaching myself Access. Hopefully someone can give me pointers on how to resolve my situation.




    I currently downloaded a table that gives me peoples DOB's in text format "01011999."

    I run an "update query" to make the date "01/01/1999." But the table still has it as a text field. I know I can change it in the table "design view" data type option. But I'm going to be downloading this table every month. Later I'll make a Marco.

    Now that my table has the correct outline, can I create another query or something to convert it over to a date/time field?


    My end goal is to download this report and return to me all the children that are about to turn one. Its about 4000 people per month I need to separate children turning one.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you should have made the field a DATE field.
    Date fields hold both date and time, which is what you want in order to calculate elapsed time.

    if you cannot convert the field, you can reformat it in a query CDate([txtfield])

  3. #3
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If this is a regular importing task, then you should make a staging table, import your data to the staging table then run an Append query from that that performs any "data translations" tasks to your final destination table.
    The DOB should definitely be saved as into a date field, as when trying to calculate ages etc. it is relatively simple.

    The DateValue() function is probably the best route here, it will take a string and convert it to a valid date.
    In the immediate window of the VBA editor ;

    ? datevalue("01/01/1999")
    01/01/1999


    ? CLng(datevalue("01/01/1999"))
    36161

    Dates are actually stored as decimal numbers in access. The Integer part represents the Date, the decimal part the time.
    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. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    DateValue won't work with 01011999 and that is the format of the data? I agree with the staging table but I think a custom function will be needed to translate 01011999 into 01/01/1999. Would be interesting to know if there is an intrinsic function that will make a date out of 01011999.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Micron

    This works in a query where Field1 contains the value 01011999 :-

    NewDate: CDate(Format([Field1],"00-00-0000"))
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    We still do not know if that date is ddmmyyyy or mmddyyyy ?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by mike60smart View Post
    Hi Micron

    This works in a query where Field1 contains the value 01011999 :-

    NewDate: CDate(Format([Field1],"00-00-0000"))
    Again, are you not having to alter the value from 01011999 to something else (in your case, 01-01-1999)? I'm only commenting according to what was posted re the input value.
    @Welshgasman, good point.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    apr pillai's Avatar
    apr pillai is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2010
    Location
    Alappuzha, India
    Posts
    209
    Window's Language Selection and Date Format setting influences the Date conversion and Display.
    Last edited by apr pillai; 03-05-2021 at 04:00 AM.

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

Similar Threads

  1. User-level Security Beginner
    By Monty51 in forum Access
    Replies: 3
    Last Post: 03-17-2015, 05:01 PM
  2. Query Help - Beginner User
    By Derrick0690 in forum Queries
    Replies: 1
    Last Post: 03-28-2012, 07:49 AM
  3. Scheduling DB (Access beginner)
    By Mr listermint in forum Database Design
    Replies: 4
    Last Post: 12-13-2011, 06:26 AM
  4. Beginner Access Help
    By Joseph in forum Access
    Replies: 1
    Last Post: 03-07-2010, 04:36 PM
  5. Need help! Access beginner here!
    By Joshin in forum Access
    Replies: 1
    Last Post: 06-09-2009, 05:12 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