How can I programatically, take some Date/Time fields present in a table in
the current database and change their type to text?
dixie
11  5869 
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
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
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?
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?
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?
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?
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?
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
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?
"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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Richard Cornford |
last post by:
For the last couple of months I have been trying to get the next round
of updates to the FAQ underway and been being thwarted by a heavy
workload (the project I am working on has to be finished an...
|
by: jjturon |
last post by:
Can anyone help me??
I am trying to pass a Select Query variable to a table using Dlookup
and return the value to same select query but to another field.
Ex.
SalesManID ...
|
by: Kevin Chambers |
last post by:
Hi all--
In an attempt to commit an Access MDB to a versioning system (subversion),
I'm trying to figure out how to convert a jet table's metadata to text, a
la SaveAsText. The end goal is to...
|
by: Jim in Arizona |
last post by:
I have a gridview that's being populated from an access db query. The
problem I'm having is that the date/time fields in access that are
populating the gridview are showing both date and time, when...
|
by: Rex |
last post by:
Hi,
I want to change a value in one table depending on the value(s) in
another table. I am trying to achieve this in a form.
to elaborate
I have a many-to-many relationship between tables...
|
by: Trev |
last post by:
Hi
Can anyone point me in the right direction here, I would like to open
a table in access 2003 by date. I have an asp web page which needs to
read data from a table with each days today's date...
|
by: imnewtoaccess |
last post by:
Hi,
I am getting errors while inserting records in one table from another.
These are the structures of two tables :
file51tm_new
RecordType Text
|
by: Ericks |
last post by:
I want to highlight new data that has been entered in my database since a last meeting so it is easy to see in a subform’s table what info is new.
In my database I have a table called...
|
by: Bface |
last post by:
Hi all,
Hope everyone had a good holiday. I am having a difficult time changing the date format of a field from Excel. I have never had this problem before. I link the excel spreadsheet to my DB,...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |
