Syntax Error In ALTER TABLE Command

I would like to change the data type of a column of integers to date/time 
format.  I'm using this command in a MS Access 2003 macro RunSQL -> ALTER 
TABLE ExpTable MODIFY Activation_DT DATETIME.  I have chosen No for the Use 
Transaction parameter.

I've "Googled" the Internet for a resolution, but am having some 
difficulties finding my way out of this error.  Would anyone have a 
suggestion?

Many thanks,
Kenny Vick
kenny_vick@playstation.sony.com

0
Utf
11/15/2007 5:52:02 PM
access 16762 articles. 3 followers. Follow

3 Replies
2192 Views

Similar Articles

[PageSpeed] 55

In news:33C0F407-9BA8-4377-A9E8-D7AE76F96895@microsoft.com,
Kenny Vick <Kenny Vick@discussions.microsoft.com> wrote:
> I would like to change the data type of a column of integers to
> date/time format.  I'm using this command in a MS Access 2003 macro
> RunSQL -> ALTER TABLE ExpTable MODIFY Activation_DT DATETIME.  I have
> chosen No for the Use Transaction parameter.
>
> I've "Googled" the Internet for a resolution, but am having some
> difficulties finding my way out of this error.  Would anyone have a
> suggestion?

Did you look in the Access help, in the book "Microsoft Jet SQL 
Reference"?  The syntax of the statement would be:

    ALTER TABLE ExpTable ALTER COLUMN Activation_DT DATETIME

However, if you have integers already entered in this column, you may 
not get them converted the way you expect.  The integer value 0 
corresponds to December 31, 1899; 1 corresponds to January 1, 1900; and 
so on.

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


0
Dirk
11/15/2007 6:06:58 PM
Dirk ...

Thank you VERY much.  This did the trip.  Since I'm not a developer by 
profession, but a novice hacker this made my life much easier.  Would you 
know how to make it so that I only get the date and not the time?

Thanks,
Kenny

"Dirk Goldgar" wrote:

> In news:33C0F407-9BA8-4377-A9E8-D7AE76F96895@microsoft.com,
> Kenny Vick <Kenny Vick@discussions.microsoft.com> wrote:
> > I would like to change the data type of a column of integers to
> > date/time format.  I'm using this command in a MS Access 2003 macro
> > RunSQL -> ALTER TABLE ExpTable MODIFY Activation_DT DATETIME.  I have
> > chosen No for the Use Transaction parameter.
> >
> > I've "Googled" the Internet for a resolution, but am having some
> > difficulties finding my way out of this error.  Would anyone have a
> > suggestion?
> 
> Did you look in the Access help, in the book "Microsoft Jet SQL 
> Reference"?  The syntax of the statement would be:
> 
>     ALTER TABLE ExpTable ALTER COLUMN Activation_DT DATETIME
> 
> However, if you have integers already entered in this column, you may 
> not get them converted the way you expect.  The integer value 0 
> corresponds to December 31, 1899; 1 corresponds to January 1, 1900; and 
> so on.
> 
> -- 
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
> 
> (please reply to the newsgroup)
> 
> 
> 
0
Utf
11/15/2007 9:41:20 PM
In news:DAB098EC-CC4A-45FF-B654-2440C9EE41D4@microsoft.com,
Kenny Vick <KennyVick@discussions.microsoft.com> wrote:
> Dirk ...
>
> Thank you VERY much.  This did the trip.

You're welcome.

> Would
> you know how to make it so that I only get the date and not the time?

I'm not entirely sure what you mean.  A date/time field *always* has 
both date and time components;  that's the nature of the data type. 
There's no difference between #11/15/2007# and #11/15/2007 12:00:00 
AM# -- the exact same value is stored.

You may choose how the field is *displayed* by setting its Format 
property.  There you can decide whether to display the time portion or 
not.

If you have existing data that contains a non-zero time component and 
you want to remove that and leave only the date, you can execute an 
update query that sets the field to DateValue(the field);  e.g.,

    UPDATE MyTable SET MyDateField = DateValue(MyDateField)

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


0
Dirk
11/15/2007 10:07:54 PM
Reply:

Similar Artilces:

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

Proper Table(s) Layout
I have created a couple of Dbs which the table structure seemed to simply fall into place, it was just logical in my head. That said, I am working on a new db and for some reason I am doubting myself and wanted a second opinion. The db is basically a contract db to input all the info, and there is a lot of info, for each contract. Where I am 'lost' is the fact that the contracts are broken into categories: clients, components,engineering, warantee... For all of the components (with the exception of clients) there are a number of fields but only 1 entry per contract....

Office 2003 on WinXP is receiving an error...
This operating is not presently configured to run this application.' This nmessage keeps popping up after I install WInXP Sp2. Right after I install office 2003, and I try to open MS word 2003, the above message appears. Can anyone point me in the right direction? RodneyJ Insert office cd>repair "RodneyJ" <u58446@uwe> wrote in message news:a43130535cd3e@uwe... > This operating is not presently configured to run this application.' This > nmessage keeps popping up after I install WInXP Sp2. Right after I > install > office 2003, and I t...

Pivot tables / Formatting
Hi everyone, I'm currently working on a pivot table in Excel 2003 and I want to put a dotted line (border) from the Account Description through to the 2 months..... Is there an easy way to do it? I can do the borders in the Account Description by doing the entire selection on the header and insert the border that way, but its not so easy with the DATA. I so wish work had Office 2007 and Pivot Tables are much better than Office 2003 but unfortunately I'm stuck.... Is there any free Add-Ons for Pivot Table formatting around that might be of any assistance? ...

Annotations sticking to data point & data table
Hello! I know that many people have asked the question here, but I can't seem to figure it out. The data I use is a 'rolling' 12 month calendar (in my example below they only want to see a rolling 7 quarters). I add annotations currently with a textbox for specific months until that month 'rolls' off my chart when the month's data is no longer displayed. I have downloaded the free add-in for the XYCharting, but it doesn't seem to work for me. When I hide the column with the data I no longer want to display, the data label does not move as it is supposed to. My...

Errors saving to network drive
When my users open an Excel spreadsheet located on a network drive, make their changes, and then try to save the spreadsheet, they get an error message saying "Your changes could not be saved to Whateverfile.xls, but were saved to XXXXXXXX ( 8 digit hex number ). Close the existing document, then open the temporary document and save it under a new name" The users have been explicitly granted full rights, including Delete and Modify, to the shared folder in which the spreadsheet resides. I have disabled their Symantec Antivirus realtime protection for the time being. This pr...

Table link documentation
I am having trouble trying to locate A/P check data that has project related costs. I found the check data but it is does not indicate the projects, I found the project data but can not determine thye logical link between the two tables, I may be using the wrong tables the tables I am using are PM80500 and PA31102. Is there any documentation of how all the tables in the system are logically link. I am trying to write reports in MS Access, but there are 1500+ tables in GP (version 10) -- Dave F In an effort to find the correct table you can do a number of things (believe me I do)....

Duplicate record in RM tables
We experienced an issue in Apply Sales Document that may have caused a duplicate record somewhere. We found this when running Paid Sales Transaction Removal and received this message: Violation of PRIMARY KEY contraint PKRM3101. Cannot insert duplicate key in object RM30101. I ran the RM duplicate tool found in the automated help area of this website and found the following: --- Begin copy here ---- Duplicates between RM Open and RM History Document #: 07-003021-17 Customer #: 079100 RMDTYPAL #: 7 --- End copy --- It looks like the duplicte tool also logs the qu...

Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data columns. Can the data in the columns of a chart table be right justified? In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf- 8?B?c2FtIGVhZ2xl?= <sam eagle@discussions.microsoft.com> says... > Ecxel 2003 and previous versions of the product center the data in the data > columns. Can the data in the columns of a chart table be right justified? > Have you tried to format the table? If yes, and you haven't been successful it is probably because XL allows very limited cust...

140 MB file went to 5.08 MB after editting 1 table
Hello All - I need some ACCESS insight...please... Several years ago, I built an access db to track my business scheduling and accounts payable/receivable. So this database is EXTREMELY IMPORTANT TO ME. The file has grown to 140 MB. Today I made a copy of the file and then edited my calendar table. I removed all columns which had 2006 data (72 totals columns) - the table had about 144 columns originally. I then added 72 columns with 2008 headers. These columns are now blank since I have not added any 2008 data yet. Afterwards, I looked around and everything looks good - my 2007 data is the...

Error loading XML into DataSet
Hello, When I try to load this xml file (see below) into an asp.net dataset using the following code: ds.ReadXml("xmlfilepath\xmlfile.xml", XmlReadMode.Auto) I get the following error: "The same table (description) cannot be the child table in two nested relations. " If I take out the anchor tag in the description it works fine, however, I need the anchor tag in there. Does anyone have any suggestions? <?xml version="1.0" ?> <rss version="2.0"> <channel> <title>Liftoff News</title> <link>http://liftoff.msf...

Run time error 5 : HQClient
When I double click HQ Client I have message box "Run time error 5 : Invalid procedure call or argument" how should I do? I used RMS 2.0 -- TOY2TOY ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGr...

LNK2001 and LNK2019 errors with template class
This is a multi-part message in MIME format. ------=_NextPart_000_00BB_01C695ED.3958DA70 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a smart pointer class that uses template functions that works = fine under VC6 but it not linking under VS2005. 1>TestGridToolkitView.obj : error LNK2019: unresolved external symbol = "void __cdecl PrismGCSmartPointer(class CPrismSmartPointer<class = CDialog> &)" = (?PrismGCSmartPointer@@YAXAAV?$CPrismSmartPointer@VCDialog@@@@@Z) = referenced in function "protected: __...

Pivot Table Defaults
In the pivot table field list, whenever I create a new pivot table and I am inserting fields into the value area, I generally get as default field setting the 'Count' value. Is there a way to format the spreadsheet to make Excel recognize the data as all numbers so it defaults to the "Sum" function as opposed to "text"? Hi The rule that the PT Wizard adopts is, If all the values in the field being added to the data area are Numeric, then it uses Sum. If any of the values are Text or BLANK, then it uses Count. It sounds as though you have defin...

delivery failure error
"The e-mail account does not exist at the organization this message was sent to" then says user unknown, even though other comupters don't have that problem. I get this error for a few clients, yet other clients can email the same address. I have Windows small business server 2000, I am pretty sure everything has updated patches. I know i security updates automatically. My clients are an updated Windows XP SR 1 with Office 2003(outlook) SR1. ...

on Writexml error
(Type your message here) Hi all, I have a XML file as a datasource. I read that file using dataset.readxml.Then in that dataset I make some changes. Now I want to write that changed dataset by overwriting the file that was created initially. I someone please tell me how to do it. -------------------------------- From: ratnesh gupta ----------------------- Posted by a user from .NET 247 (http://www.dotnet247.com/) <Id>sCyOfoucUUyYsxAsNxg9AA==</Id> ...

Excel error 1706
Whenever I try to start excel on my computer it displays an error message and it shuts itself down, any other program from the office pack works properly, except for the one I mentioned. I reinstalled it and it does the exact same thing. Thanks in advance. ...

Error with Bold
What's wrong? With Worksheets("Recent") Range(Cells(1, 1), Cells(1, 2)).Font.FontStyle = "Bold" End with Take a look at the activesheet. I bet that range is bolded. But if you want Recent to be the sheet with the change, add a couple of dots. With Worksheets("Recent") .Range(.Cells(1, 1), .Cells(1, 2)).Font.FontStyle = "Bold" End With Without those dots, the range refers to the activesheet (if the code is in a general module). crapit wrote: > > What's wrong? > With Worksheets("Recent") > Range(Cells(1...

Oulook 2003 dial up sync errors
we are using outlook 2003 will all current updates insatlled and are using cashed exchange mode. our operating system is windows 2000 SP4 with all the current updates as well. when the user connects and hits f9 to sync we get the following error in the sync log (i have run detect and repair and removed and recreated the users outlook profile) any suggestions??: 18:57:44 Synchronizer Version 11.0.5604 18:57:44 Could not connect to public folder server. 18:57:44 [8004011D-526-80040115-0] 18:57:44 The Microsoft Exchange Server computer is not available. Either there are networ...

Error loading outlook 2003
Hey Guys need a little help I'm running office 2003 with outlook, I'm using my own exchange server, everytime I load up outlook on one of my workstations it loads up fine until I hit the inbox, once I do that Outlook closes down, I check in the event view and got this error message: "The MOF file created for the outlook service could not be loaded. The error code returned by the MOF Complier is contained in the Record Data. Before the performance counters of this service can be collected by WMI the MOF file will need to be loaded manually." Anyone know a fix inste...

Rule Wizard: "Rules In Error"
I have a blackberry, and I have my outlook forwarding emails to it, but about once a day, I get a message that says "Rules in Error: Error in Rule Execution" and it won't forward any emails after that. I only have one single rule, so it's not a rule conflict. Does anyone know the deal? This is extremely frustrating as sometimes I'll be out of the office during the weekend or at night and won't receive any message for hours on my blackberry, only to find out later that this "Rules in Error" dialog box popped up and it won't forward emails. It see...

Like a pivot table
Hello every body I'm first time requesting in this group, so I opologize in advance for any mistakes or something annoying I repeat what I have sent before 10 min because I see it unclear when it goes to news group If any one can help me I'm working with data which most of it comes like a table with feilds as columns and records as rows. I want it to be as many rows with each feild an example what is exist name age Joining Date Tele John 20 Jun-90 4321251 Iqbal 30 Jul-95 6583752 George 40 Sep-85 7843125 What I wa...

Duplicating one Field from One table to Another
Hi - I have two tables - one position, one personnel - which has a 1-to-many relationship (1 position record to many personnel records). The department had a new requirement which made it necessary to change some coding (I inherited this). I'm using tab forms so that when a position is pulled up, you can click on the tab that has the personnel information (if there is any). There is a button on the Personnel form that allows the user to add a new Personnel record. Since I am using an Auto-number field in the Position table (which doubles as the PK) the functionality is fine. Wh...

Error #5
I've tried to save my transaction data to my desktop. However, each time I try this I get the error "Error reading or writing file "." " Could someone tell me what's going on? ...

Outlook error message
Unable to expand the folder. The rest of folders could not be opened. There are no additional Microsoft Exchange client licenses available. Contact your adminstrator to obtain a client license to use Microsoft Exchange This is the error message that our clients are receiving when attempting to open outlook version 2000 or above to Exchange Server 5.5 SP3 server. We do have the available licenses and have already set them up in license manager on the server and then license logging service is started. We keep having to stop and start the License Logging Service and then they can get in fi...