By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,069 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

Change table date/time field to text field in code

P: n/a
How can I programatically, take some Date/Time fields present in a table in
the current database and change their type to text?

dixie
Dec 2 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
You can alter the column type by executing a DDL query.

Example:
Dim strSql As String
strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(20);"
DBEngine(0)(0).Execute strSql, dbFailOnError

I'm having difficulty imagining a scenario where it would be a good idea to
use a Text type field to hold date/time data though. The approach would
unleash a plethoria of issues, with criteria, sorting, date math, invalid
entries, international format issues, and so on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...
How can I programatically, take some Date/Time fields present in a table
in the current database and change their type to text?

dixie

Dec 2 '05 #2

P: n/a
I'm probably going about it the wrong way, but I am using TransferText with
an export specification to produce a tab delimited text file I need and the
date/time fields have got 00:00:00 appended on the end of the date. There
is no time and I can't afford this to go into the text file. I thought that
if I changed the table which is a temporary table made by a Maketable query
after it was produced so that the date/time fields were text fields the
problem would go away. I am sure there is a much more elegant solution
though.

dixie

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43**********************@per-qv1-newsreader-01.iinet.net.au...
You can alter the column type by executing a DDL query.

Example:
Dim strSql As String
strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(20);"
DBEngine(0)(0).Execute strSql, dbFailOnError

I'm having difficulty imagining a scenario where it would be a good idea
to use a Text type field to hold date/time data though. The approach would
unleash a plethoria of issues, with criteria, sorting, date math, invalid
entries, international format issues, and so on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...
How can I programatically, take some Date/Time fields present in a table
in the current database and change their type to text?

dixie


Dec 2 '05 #3

P: n/a
Easy solution: create a query that formats the date as you desire, and
export the query.

For example, you might type this into the Field row in query design:
Format([Date1], "Short Date")
replacing Date1 with the name of your date field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dixie" <di***@dogmail.com> wrote in message
news:43******@duster.adelaide.on.net...
I'm probably going about it the wrong way, but I am using TransferText
with an export specification to produce a tab delimited text file I need
and the date/time fields have got 00:00:00 appended on the end of the
date. There is no time and I can't afford this to go into the text file.
I thought that if I changed the table which is a temporary table made by a
Maketable query after it was produced so that the date/time fields were
text fields the problem would go away. I am sure there is a much more
elegant solution though.

dixie

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43**********************@per-qv1-newsreader-01.iinet.net.au...
You can alter the column type by executing a DDL query.

Example:
Dim strSql As String
strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(20);"
DBEngine(0)(0).Execute strSql, dbFailOnError

I'm having difficulty imagining a scenario where it would be a good idea
to use a Text type field to hold date/time data though. The approach
would unleash a plethoria of issues, with criteria, sorting, date math,
invalid entries, international format issues, and so on.

"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...
How can I programatically, take some Date/Time fields present in a table
in the current database and change their type to text?

Dec 2 '05 #4

P: n/a
I'm not sure if this is a problem, but the reason I used a maketable query
is to put the data into a table to export was that when I tried to export
direct from the select query I need to produce the data, it wouldn't let me.
I believe it was because there were two parameter in the query. Can I
export a query using transfertext if there are parameters coming from a form
in that query?

dixie

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43**********************@per-qv1-newsreader-01.iinet.net.au...
Easy solution: create a query that formats the date as you desire, and
export the query.

For example, you might type this into the Field row in query design:
Format([Date1], "Short Date")
replacing Date1 with the name of your date field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dixie" <di***@dogmail.com> wrote in message
news:43******@duster.adelaide.on.net...
I'm probably going about it the wrong way, but I am using TransferText
with an export specification to produce a tab delimited text file I need
and the date/time fields have got 00:00:00 appended on the end of the
date. There is no time and I can't afford this to go into the text file.
I thought that if I changed the table which is a temporary table made by
a Maketable query after it was produced so that the date/time fields were
text fields the problem would go away. I am sure there is a much more
elegant solution though.

dixie

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43**********************@per-qv1-newsreader-01.iinet.net.au...
You can alter the column type by executing a DDL query.

Example:
Dim strSql As String
strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(20);"
DBEngine(0)(0).Execute strSql, dbFailOnError

I'm having difficulty imagining a scenario where it would be a good idea
to use a Text type field to hold date/time data though. The approach
would unleash a plethoria of issues, with criteria, sorting, date math,
invalid entries, international format issues, and so on.

"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...
How can I programatically, take some Date/Time fields present in a
table in the current database and change their type to text?


Dec 2 '05 #5

P: n/a
You can use TransfrerText with a query that has parameters.
It pops up the boxes asking for the parameters, as you would expect.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...
I'm not sure if this is a problem, but the reason I used a maketable query
is to put the data into a table to export was that when I tried to export
direct from the select query I need to produce the data, it wouldn't let
me. I believe it was because there were two parameter in the query. Can I
export a query using transfertext if there are parameters coming from a
form in that query?

dixie

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43**********************@per-qv1-newsreader-01.iinet.net.au...
Easy solution: create a query that formats the date as you desire, and
export the query.

For example, you might type this into the Field row in query design:
Format([Date1], "Short Date")
replacing Date1 with the name of your date field.

"Dixie" <di***@dogmail.com> wrote in message
news:43******@duster.adelaide.on.net...
I'm probably going about it the wrong way, but I am using TransferText
with an export specification to produce a tab delimited text file I need
and the date/time fields have got 00:00:00 appended on the end of the
date. There is no time and I can't afford this to go into the text
file. I thought that if I changed the table which is a temporary table
made by a Maketable query after it was produced so that the date/time
fields were text fields the problem would go away. I am sure there is a
much more elegant solution though.

dixie

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43**********************@per-qv1-newsreader-01.iinet.net.au...
You can alter the column type by executing a DDL query.

Example:
Dim strSql As String
strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(20);"
DBEngine(0)(0).Execute strSql, dbFailOnError

I'm having difficulty imagining a scenario where it would be a good
idea to use a Text type field to hold date/time data though. The
approach would unleash a plethoria of issues, with criteria, sorting,
date math, invalid entries, international format issues, and so on.

"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...
> How can I programatically, take some Date/Time fields present in a
> table in the current database and change their type to text?

Dec 2 '05 #6

P: n/a
But the parameters are picked up from text boxes on a form anyway, so it
should be automatic.

I am still playing around with sending from the query, but I am getting
dates now in the text file that have six numbers maximum, so I get 1/7/2005,
1/10/200 or 10/10/20. Any idea on what is causing this?

dixie

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43**********************@per-qv1-newsreader-01.iinet.net.au...
You can use TransfrerText with a query that has parameters.
It pops up the boxes asking for the parameters, as you would expect.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...
I'm not sure if this is a problem, but the reason I used a maketable
query is to put the data into a table to export was that when I tried to
export direct from the select query I need to produce the data, it
wouldn't let me. I believe it was because there were two parameter in the
query. Can I export a query using transfertext if there are parameters
coming from a form in that query?

dixie

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43**********************@per-qv1-newsreader-01.iinet.net.au...
Easy solution: create a query that formats the date as you desire, and
export the query.

For example, you might type this into the Field row in query design:
Format([Date1], "Short Date")
replacing Date1 with the name of your date field.

"Dixie" <di***@dogmail.com> wrote in message
news:43******@duster.adelaide.on.net...
I'm probably going about it the wrong way, but I am using TransferText
with an export specification to produce a tab delimited text file I
need and the date/time fields have got 00:00:00 appended on the end of
the date. There is no time and I can't afford this to go into the text
file. I thought that if I changed the table which is a temporary table
made by a Maketable query after it was produced so that the date/time
fields were text fields the problem would go away. I am sure there is
a much more elegant solution though.

dixie

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43**********************@per-qv1-newsreader-01.iinet.net.au...
> You can alter the column type by executing a DDL query.
>
> Example:
> Dim strSql As String
> strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(20);"
> DBEngine(0)(0).Execute strSql, dbFailOnError
>
> I'm having difficulty imagining a scenario where it would be a good
> idea to use a Text type field to hold date/time data though. The
> approach would unleash a plethoria of issues, with criteria, sorting,
> date math, invalid entries, international format issues, and so on.
>
> "Dixie" <di***@dogmail.com> wrote in message
> news:43********@duster.adelaide.on.net...
>> How can I programatically, take some Date/Time fields present in a
>> table in the current database and change their type to text?


Dec 2 '05 #7

P: n/a
If you want leading zeros, try:
Format([Date1], "mm/dd/yyyy")
or whatever format you use in your region.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dixie" <di***@dogmail.com> wrote in message
news:43******@duster.adelaide.on.net...
But the parameters are picked up from text boxes on a form anyway, so it
should be automatic.

I am still playing around with sending from the query, but I am getting
dates now in the text file that have six numbers maximum, so I get
1/7/2005, 1/10/200 or 10/10/20. Any idea on what is causing this?

dixie

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43**********************@per-qv1-newsreader-01.iinet.net.au...
You can use TransfrerText with a query that has parameters.
It pops up the boxes asking for the parameters, as you would expect.
"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...
I'm not sure if this is a problem, but the reason I used a maketable
query is to put the data into a table to export was that when I tried to
export direct from the select query I need to produce the data, it
wouldn't let me. I believe it was because there were two parameter in
the query. Can I export a query using transfertext if there are
parameters coming from a form in that query?

dixie

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43**********************@per-qv1-newsreader-01.iinet.net.au...
Easy solution: create a query that formats the date as you desire, and
export the query.

For example, you might type this into the Field row in query design:
Format([Date1], "Short Date")
replacing Date1 with the name of your date field.

"Dixie" <di***@dogmail.com> wrote in message
news:43******@duster.adelaide.on.net...
> I'm probably going about it the wrong way, but I am using TransferText
> with an export specification to produce a tab delimited text file I
> need and the date/time fields have got 00:00:00 appended on the end of
> the date. There is no time and I can't afford this to go into the
> text file. I thought that if I changed the table which is a temporary
> table made by a Maketable query after it was produced so that the
> date/time fields were text fields the problem would go away. I am
> sure there is a much more elegant solution though.
>
> dixie
>
> "Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
> news:43**********************@per-qv1-newsreader-01.iinet.net.au...
>> You can alter the column type by executing a DDL query.
>>
>> Example:
>> Dim strSql As String
>> strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(20);"
>> DBEngine(0)(0).Execute strSql, dbFailOnError
>>
>> I'm having difficulty imagining a scenario where it would be a good
>> idea to use a Text type field to hold date/time data though. The
>> approach would unleash a plethoria of issues, with criteria, sorting,
>> date math, invalid entries, international format issues, and so on.
>>
>> "Dixie" <di***@dogmail.com> wrote in message
>> news:43********@duster.adelaide.on.net...
>>> How can I programatically, take some Date/Time fields present in a
>>> table in the current database and change their type to text?

Dec 2 '05 #8

P: n/a
When I try this method, I am getting error 3293 Syntax error in ALTER TABLE
statement.
I am running it from a button on a form right now.

dixie
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43**********************@per-qv1-newsreader-01.iinet.net.au...
You can alter the column type by executing a DDL query.

Example:
Dim strSql As String
strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(20);"
DBEngine(0)(0).Execute strSql, dbFailOnError

I'm having difficulty imagining a scenario where it would be a good idea
to use a Text type field to hold date/time data though. The approach would
unleash a plethoria of issues, with criteria, sorting, date math, invalid
entries, international format issues, and so on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...
How can I programatically, take some Date/Time fields present in a table
in the current database and change their type to text?

dixie


Dec 2 '05 #9

P: n/a
That means your SQL statement is not correct. For example, if your table or
field name contains spaces or other odd characters, you must surround them
with square brackets.

(BTW, this approach works only in Access 2000 and later.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...
When I try this method, I am getting error 3293 Syntax error in ALTER
TABLE statement.
I am running it from a button on a form right now.

dixie
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43**********************@per-qv1-newsreader-01.iinet.net.au...
You can alter the column type by executing a DDL query.

Example:
Dim strSql As String
strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(20);"
DBEngine(0)(0).Execute strSql, dbFailOnError

I'm having difficulty imagining a scenario where it would be a good idea
to use a Text type field to hold date/time data though. The approach
would unleash a plethoria of issues, with criteria, sorting, date math,
invalid entries, international format issues, and so on.

"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...
How can I programatically, take some Date/Time fields present in a table
in the current database and change their type to text?

Dec 2 '05 #10

P: n/a
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:43**********************@per-qv1-newsreader-01.iinet.net.au:
If you want leading zeros, try:
Format([Date1], "mm/dd/yyyy")
or whatever format you use in your region.


A much more portable format is:

Format([Date1], "yyyy/mm/dd")

It's the top ISO standard for data formats, I believe. It wasn't
widely used until around Y2K, and Access never seems to have
included it in its default formats, which is a huge mistake, in my
opinion. Indeed, I think it ought to be the default.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Dec 2 '05 #11

P: n/a
Thanks to everyone who helped me. I have found that by using the
DoCmd.TransferText command from a table rather than a query (which I can
create from a MakeTable query) and having an export specification, fixes the
problems I was having.

dixie

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@216.196. 97.142...
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:43**********************@per-qv1-newsreader-01.iinet.net.au:
If you want leading zeros, try:
Format([Date1], "mm/dd/yyyy")
or whatever format you use in your region.


A much more portable format is:

Format([Date1], "yyyy/mm/dd")

It's the top ISO standard for data formats, I believe. It wasn't
widely used until around Y2K, and Access never seems to have
included it in its default formats, which is a huge mistake, in my
opinion. Indeed, I think it ought to be the default.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Dec 3 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.