Forcing a Date/TIme into an Access Make Table Query


I have a query that I am executing in Access that is making another table. In this table are 2 placeholder columns that would later have a date time value added to them. right now when I run the query, I have an alias name for each column:


When running it that way, the make table treats the columns as numbers. I tried formatting the columns in the properties window for each of the columns and I get the same result. Leaving the formatting for  the columns, I also tried:

Column1:Format(#1/1/2010#,"Short Date")
Column2:Format('12:00 AM',"Medium Time")

The formatting does not carry over to the make table and is given a TEXT data type.

What can be done to get this to work? Thanks in advance.
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.

Dale FyeCommented:
Generally, when I want to create a table that I can write data to, I will do something like:

SELECT cint(1) as ColInt, cdbl(1.0) as ColDouble, 1 as ColLong, #1/1/12# as ColDate
INTO tbl_MakeTable

I will execute that query to build the table and give the columns the correct data type.
Then I will run a DELETE query to delete that record from the table.

DELETE FROM tbl_MakeTable

The only conversion function I've tried this with that did not create the column with the data type I wanted was cBool()

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
These will create DateTime fields with the value 12:00:00 AM
Column1:#12:00 AM#
Column2:#12:00 AM#
DB-ahaAuthor Commented:
These will create DateTime fields with the value 12:00:00 AM
Column1:#12:00 AM#
Column2:#12:00 AM#

This solution worked MOSTLY, however I need to display  medium time which is 12:00 AM, doing it this way, some reason a set of 0's are added for seconds. I type in:

Alias:#12:00 AM# and when I leave the column, the second set of 0's are added and this is seen:

12:00:00 AM

How can I just get medium time as intended?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Dale FyeCommented:
You will need to set the display format in your query or form that displays the data from this table.

Or you could use syntax similar to the following:

currentdb.TableDefs("yourTableName").Fields("Column1").Properties("Format") = "Medium Date"
DB-ahaAuthor Commented:
Fyed, I am having trouble understanding your responses. Your code doesn't look like SQL I can use in Access. Also I was looking for medium time in the remaining column, not medium date. Where do you place the code you are giving as examples?
Dale FyeCommented:
that code would not be SQL.  I don't believe that you are going to be able to use SQL to format that field.  My original SQL, and the recommendation by IrogSinta will create a date/time field in an Access table.

To get it to display  as medium time, you will need to issue a vba command that changes the format to Medium Time:

currentdb.TableDefs("tbl_Numbers").Fields("DateField").Properties("format") = "Medium time"

Back to absolute Access basics -- you really don't want two columns, one for date and one for time.
Access ALWAYS stores a date/time together, so your one column will be filled with (yesterday and today)
05-Sep-2013 12:00:00 AM
06-Sep-2013 12:00:00 AM
and the other (1 AM and 1 PM) with
30-Dec-1899 01:00:00 AM
30-Dec-1899 01:00:00 PM

Access store dates as a special type of Double.
The integer part marks days fro 30-Dec-1899, while the decimal --which is always positive -- marks the time 0.00000000 being midnight and 0.99999999~ being 11:59 and change.

You can get a field to be FORMATTED any way you want -- but it doesn't change what is stored there!

This SQL will get you a table.
But as @fyed stated, you have to kill the row afterward
SELECT format(#30/12/1899#,"Short date") as TheDate, format(#30/12/1899#,"short time") as TheTime INTO tblTimes;

Open in new window

Here's a sample to play with and some food for thought
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
Query Syntax

From novice to tech pro — start learning today.