Modify Table fields with SQL


How do I modify the SQL below so the field created is a date but short date.


        cSQL = "ALTER TABLE POINT_OF_SALES_MAIN_POS0 ADD COLUMN spring DATE"
        DoCmd.RunSQL (cSQL)
       

How do I modify the SQL below so the field created is a YES/NO field, obviously the SQL below dosent work.

        dSQL = "ALTER TABLE POINT_OF_SALES_MAIN_POS0 ADD COLUMN spring TRUE"
        DoCmd.RunSQL (dSQL)
NorthumberlandAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

thenelsonCommented:
>How do I modify the SQL below so the field created is a date but short date.
There are actually no short date fields.  A date field is a double where the number before the decimal point is the date and the number after the decimal point is the time.  Short date, long date is a matter of display and entry formatting.  For example:
cdbl(#7-4-05 4:08 PM#) = 38537.6722222222
format(38537.6722222222, "short date") = 7/4/2005
format(38537.6722222222, "long date") = Monday, July 04, 2005
format(38537.6722222222, "mm/dd/yyyy hh:nn A/P") = 07/04/2005 04:08 P

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thenelsonCommented:
>How do I modify the SQL below so the field created is a YES/NO field, obviously the SQL below dosent work.
You can't add a field to an existing table with a query.  You can create a new table based on the original table with the new field added:
SELECT TABLE POINT_OF_SALES_MAIN_POS0.*, True AS spring
INTO NewTable
FROM TABLE POINT_OF_SALES_MAIN_POS0;
Arthur_WoodCommented:
actually, this statement "A date field is a double where the number before the decimal point is the date " while technically correct, can be rather confusing, since clearly 38537 does not look like July 4, 2005.

The number is the NUMBER OF DAYS since Dec 30, 1899 to the date in question (there have been 38537 days since 12/30/1899 to 7/4/2005)

AW
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

heer2351Commented:
First part of the question is answered. For the second part the following SQL will do the trick:

dSQL = "ALTER TABLE POINT_OF_SALES_MAIN_POS0 ADD COLUMN spring YESNO"
DoCmd.RunSQL (dSQL)


Sometimes it is very simple :)
thenelsonCommented:
heer2351,
I didn't know about the ALTER clause.  Thanks!!!

Nelson
dannywarehamCommented:
Just my $0.02

I would avoid doing anything that alters the structure of your database - particularly table structures.
You will find yourself in a mire of cack very quickly
stevbeCommented:
danny ... how do you make updates to your structure then?
thenelsonCommented:
stevbe,

You create a new database and start over, obviously ;^)
dannywarehamCommented:
>.how do you make updates to your structure then
I don't

As a last resort, if I know that I will need data structure to change, I include a temp tabl with two columns.
One for the new data and one for a link field to the table that i would otherwise have to change.

I try and avoid make-tables, as these (I found) are susseptable to corruption.

Generally, this is not needed, as I find that I can change as much data as I need with UPDATE queries - I always avoid changing field names or data types, as, if this is done through code at some point, you can guarantee that it will be changed on multiple occasions (which is bad design).

If it's being changed on multiple occasions, then you have to ensure that the event fires - which can be difficult when users quit the app through task manager.

This leaves the database with an incorrect structure and more leg work for the DBA...

If it's a one off change after importing data into a database and it never going to be needed again, then I change the table structure manually.

:-)
stevbeCommented:
<I don't > lucky you ... must be an anvironment that doesn't chnage their business requirements :-)

<I try and avoid make-tables> me too ... I never use them

<then I change the table structure manually.>
I find that when I have a major version change (yes each version could be considered a  one off) that requires new tables with foreign keys to existing tables or new Cascading on existing relationships or yes ... sometimes I need to chnage a data type that by coding this I can accurately reproduce the necessary changes rather than having to remember the manual steps.

Steve
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.