# Thread: Delete Query right 9

1. Registered User
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. Moderator
Join Date
Mar 2009
Posts
5,442
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.

3. Registered User
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. Registered User
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. Moderator
Join Date
Mar 2009
Posts
5,442
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.`

6. Registered User
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. Registered User
Join Date
Mar 2004
Location
California
Posts
502
Ahhh the first mid 9,2 - is that the center two digits?

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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

9. Registered User
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

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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

11. Registered User
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. Moderator
Join Date
Mar 2009
Posts
5,442
Originally Posted by Rick Schreiber
;;; 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.

13. Registered User
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
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. Moderator
Join Date
Mar 2009
Posts
5,442
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;`

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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)

#### Posting Permissions

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