Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Delete Query right 9

    Problem ONE!

    expr1 : Right([sandicorsearch.list date],8)

    This SELECT query produces the result that I wish, which is to remove the T00:00:00 from the end of a TEXT formatted yyyy-mm-ddT00:00:00 field, but a select query will not delete the T00:00:00 from the field.

    When I change SELECT query to a DELETE query it ask for a WHERE result - WHERE what? If I run it anyway it deletes ALL DATA in all 100 plus fields????? YIKES!

    Problem TWO

    Once I get this to work I will need to convert the yyyy-mm-dd to a dd/mm/yyyy DATE formatted field.

    I have about 6 text formatted date fields in this table with about 18,000 records.

    Any way of making this work and then having it change all 6 text formatted date fields to the dd/mm/yyyy format and change it form a TEXT to a true DATE field?

    Thanks . . . Rick

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Solution ONE:
    Code:
    UPDATE sandicorsearch SET sandicorsearch.[list date] = Left(sandicorsearch.[list date], 10);
    Solution TWO:
    Code:
    UPDATE sandicorsearch SET sandicorsearch.[list date] = Mid(sandicorsearch.[list date], 9, 2) & Mid(sandicorsearch.[list date], 5, 4) & Left(sandicorsearch.[list date], 4);
    Note: With the second query, the first one is useless.
    Have a nice day!

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    YES! The second query does the job well.

    I need (or I would like) to run them successively all at the same time. Is that possible?

    I'm thinking a UNION ALL query but I don' know.

    Code:
    UPDATE sandicorData SET sandicorData.[list Date] = Mid(sandicorData.[list date],9,2) & Mid(sandicorData.[list date],5,4) & Left(sandicorData.[list date],4)
    
    UPDATE sandicorData SET sandicorData.[modified Date] = Mid(sandicorData.[modified date],9,2) & Mid(sandicorData.[modified date],5,4) & Left(sandicorData.[modified date],4)
    
    UPDATE sandicorData SET sandicorData.[pending Date] = Mid(sandicorData.[pending date],9,2) & Mid(sandicorData.[pending date],5,4) & Left(sandicorData.[pending date],4)
    
    UPDATE sandicorData SET sandicorData.[off Market Date] = Mid(sandicorData.[off Market date],9,2) & Mid(sandicorData.[off Market date],5,4) & Left(sandicorData.[off Market date],4)
    
    UPDATE sandicorData SET sandicorData.[ Date] = Mid(sandicorData.[status change date],9,2) & Mid(sandicorData.[ status change date date],5,4) & Left(sandicorData.[ status change date date],4)
    
    UPDATE sandicorData SET sandicorData.[close of escrow date] = Mid(sandicorData.[ close of escrow date],9,2) & Mid(sandicorData.[ close of escrow date],5,4) & Left(sandicorData.[ close of escrow date],4)
    Thanks much . . . Rick

  4. #4
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    OK I know a UNION ALL query will not work.

    Keep in mind that I'm doing this because I need to be able to change these fields from a TEXT field to a DATE field as I run many queries based on date ranges.

    It would save me times if I could convert to the dd/mm/yyyy and at the same time change the six fields to a DATE in lieu of a test field.

    Do I need to this in VBA for which I know very little?

    Thanks . . . Rick

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Like this:
    Code:
    UPDATE sandicorData SET sandicorData.[list Date] = Mid(sandicorData.[list date],9,2) & Mid(sandicorData.[list date],5,4) & Left(sandicorData.[list date],4),  
                            sandicorData.[modified Date] = Mid(sandicorData.[modified date],9,2) & Mid(sandicorData.[modified date],5,4) & Left(sandicorData.[modified date],4),
                            sandicorData.[pending Date] = Mid(sandicorData.[pending date],9,2) & Mid(sandicorData.[pending date],5,4) & Left(sandicorData.[pending date],4), 
                            sandicorData.[off Market Date] = Mid(sandicorData.[off Market date],9,2) & Mid(sandicorData.[off Market date],5,4) & Left(sandicorData.[off Market date],4), 
                            sandicorData.[ Date] = Mid(sandicorData.[status change date],9,2) & Mid(sandicorData.[ status change date date],5,4) & Left(sandicorData.[ status change date date],4), 
                            sandicorData.[close of escrow date] = Mid(sandicorData.[ close of escrow date],9,2) & Mid(sandicorData.[ close of escrow date],5,4) & Left(sandicorData.[ close of escrow date],4);
    If you need to perform this kind of operation often, you can create a VBA function that performs the formatting and call it from a query:
    Code:
    Public Function FormatDate(ByVal ISODate As Variant) As Variant
    
        FormatDate = Mid(ISODate, 9, 2) & Mid(ISODate, 5, 4) & Left(ISODate, 4)
        
    End Function
    Then:
    Code:
    UPDATE sandicorData SET sandicorData.[list Date] = FormatDate (sandicorData.[list date]), ... etc.
    Have a nice day!

  6. #6
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    That worked also. Kewl!

    But me stupid I actually need mm/dd/yyyy

    I looked at your syntax but couldn't figure out the mid 9,2 mid 5,4 and the left 4 (probably the four digit year).

    If I could figure those out I could probably change it.

    now . . .

    This changes it form a TEXT field to a DATE field ? ? ?
    Code:
    Public Function FormatDate(ByVal ISODate As Variant) As Variant
    
        FormatDate = Mid(ISODate, 9, 2) & Mid(ISODate, 5, 4) & Left(ISODate, 4)
        
    End Function
    I also don't understand "VBA function that performs the formatting and call it from a query."

    Not sure how to call VBA from a query.

    Can it all go into a VBA that I could attache to a form button and complete the entire work?

    Thanks much Sinndho . . .

    Rick

  7. #7
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Ahhh the first mid 9,2 - is that the center two digits?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    moral of the tale
    store date or time values as datetime
    store numbers in the most appropriate numeric datatype

    storing either as string/text values is bad design and will always bite you in the ass big time
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    But many choice I don't have.

    The data I import (thousands of records) has six separate date fields in TEST format.

    I can't query against that.

    Do you have a suggestion?

    Thanks . . . . Rick

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    convert the data as you import it. just because the data is coming to you in a specific form doesn't mean you have to bollux your database because someone else can't be bothered to do the job properly, or the data migration path forces such anomolies.

    if its going into your db then make certain the data is correct (correct format, correct column names, validate what you can). do what ever validations you can. once its in your DB you have taken ownership of that data so make certain its correct and stored properly.

    its ok to 'blame' the outside system(s) if you cannot change the datatype but if you are storing that data in your own system then there is absolutely no reason not to store it in the appropriate datatype. some remote systems will only send data as test/string and as such when you connect to such remote systems you have to l,ive by their rules. in your own systems you live by your rules (but only where those rules also compy with good database practice
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    I wholly subscribe to this train of thought - garbage in garbage out!

    98 percent of my queries are based on date ranges. I must have the correct DATE format of the field.

    My elementary thought was to import it as a text field (it's all I know how to perform - Yikes) and then to convert the date format to mm/dd/yyyy and then make the field a DATE type field. If I just change the field type to date on the import no data is imported.

    How to I convert the date data (in its TEXT form) into a DATE format data while importing and end up with mm/dd/yyyy?

    I don't need the time: T00:00:00.

    Sinndho has given me a great start!

    Thanks . . . Rick

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Rick Schreiber View Post
    ;;; How to I convert the date data (in its TEXT form) into a DATE format data while importing and end up with mm/dd/yyyy?
    1. Please explain how you import the text "date" data now (with a query or... ?): the conversion process can be done there.

    2. Jet, the database engine of Access, handles Date/Time value in the "mm/dd/yyyy" format, which is also the format you must specify when inserting a value in a Date/Time column (field), so no problem there.
    Have a nice day!

  13. #13
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    I have a very large data base with near 200 fields, six of those fields are dates however the data aggregator stores the date data as TEXT!

    Presently I'm using Access 2003.

    File
    Get External Data
    Advanced Settings and
    Import

    If I set the date fields to date/time it incurs the error wrath and no data for the date fields are imported.

    So I import the date fields as text fields.

    Then I use what was suggested by Sinndho which removes the T00:00:00 and places text date data in dd/mm/yyyy. This was my mistake as it should have been mm/dd/yyyy. Microsoft Access likes that format.

    The dd/mm/yyyy will not allow me to change the field from a text to a date definition.

    I would like to have Sinndho's formula to return mm/dd/yyyy and then if possible a way to change the date field definitions from TEXT to DATE.

    I did see this but I'm not sure where to place it and how to call it from a query.

    Public Function FormatDate(ByVal ISODate As Variant) As Variant
    FormatDate = Mid(ISODate, 9, 2) & Mid(ISODate, 5, 4) & Left(ISODate, 4)
    End Function

    How would I apply this to the six date fields in question?

    Thanks much . . . Rick

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Simply change:
    Code:
    Mid(ISODate, 9, 2) & Mid(ISODate, 5, 4) & Left(ISODate, 4)
    to:
    Code:
    Mid(ISODate, 6, 5) & "-" & Left(ISODate, 4)
    If you want to replace the hyphens (-) with slashes, you can use:
    Code:
    Replace(Mid(ISODate, 6, 5) & "-" & Left(ISODate, 4), "-", "/")
    As for using a function in a query:

    1. Create the function (FormatDate in this case) in an independent module (i.e. not a Class module nor a Form or Report module):
    Code:
    Public Function FormatDate(ByVal ISODate As Variant) As Variant
    
        FormatDate = Replace(Mid(ISODate, 6, 5) & "-" & Left(ISODate, 4), "-", "/")
    
    End Function
    2. You can now use the function FormatDate() as you would use any Access/VBA built-in function in a query expression:
    Code:
    UPDATE <MyTable> SET <MyField> = FormatDate(<MyField>);
    Note: You can convert the values of several columns in the same query:
    Code:
    UPDATE <MyTable> SET <MyField1> = FormatDate(<MyField1>),
                         <MyField2> = FormatDate(<MyField2>);
    3. If you then want to keep the data in the same table <MyTable> (probably not the best of ideas) and convert <MyField> to a column (field) of Date/Time data type, you can use another query, like this:
    Code:
    ALTER Table <MyTable> ALTER COLUMN <MyField> DateTime;
    Note: You cannot convert the data type of several columns in the same query and this WILL NOT WORK:
    Code:
    ALTER Table <MyTable> ALTER COLUMN <MyField1> DateTime, <MyField2> DateTime;
    Have a nice day!

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    store your dates as dates
    use the cdate function to convert a string to dates

    or insert into the db by encapsualting the date literal with # eg
    insert into aTable (MyDateColumn) values ("#05/01/2012#")

    once the date value is stroed as a date vlaue then you can format it anyway shpae or form you prefer using format
    eg

    format(MyDateColumn,"dd/mm/yyyy")
    or
    format(MyDateColumn,"dd mmm yyyy")
    or
    format(MyDateColumn,"yyyy/mm/dd")
    or if you really, really must
    format(MyDateColumn,"mm/dd/yyyy")

    MS Access: Format Function (with Dates)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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