Table Question 1

[jalenben1]

I have a table that I created in Access. From the spreadsheet that was imported I have a column called Month. In this column I typed in for example, January 2008 and in the formula box it appears as 1/1/2008. When I imported the spreadsheet into Access in the table 1/1/2008 appears instead of January 2008. How do I change it to show January 2008. This is for the purpose of craeting queries and I need that date to read or show as January 2008 and not 1/1/2008. Does that make sense?

 

[lespaul]

First, a little explanation. A date is not 1/1/2008 for these purposes. A date is the number of days since 12/30/1899. If you open excel and enter:

10/20/2008 9:21 am

and then convert that cell to a number with 6 decimals you get:
39741.3895833333

What you are thinking of as a "date" is actually a formatted string.

There are functions that you can call for use on a date field that will format in a manner you'd like (for instance January 2008).

Month(fieldName) will provide you with the number month the date is. Year(FieldName) will provide you with the year of the date.

So, it doesn't really matter what is in the table as long as it is a valid date field, you can then in your query "fix" the date to the proper string format you'd like to see it in.

HTH

Leslie

Have you met Hardy Heron?

 

[jalenben1]

Leslie - I am not sure if I follow you...I made the changes as you sggested but in the formula box in my spreadsheet i still see the date as 1/1/2008 even though in my columns it is showing January 2008. I guess what you may be saying to me is that when I import the data into a table in Access and I open up the table and click on design view for I change the data type to text or date/time to change 1/1/2008 into January 2008. Am I correct?

 

[lespaul]

if you have a date field in access it doesn't matter what you "see" in the table. What is stored is a value that looks like:

39741.389583
(there have been 39741 days from 12/30/1899 until 10/20/2008)

what you see can be:

10/20/2008 9:21 am
10/20/2008
October 20, 2008

So if you are importing a date into a table, what the table knows is 39741, what you see in the format is 10/20/2008.

If you only need the month and year then in a query (or form or report) you can format that date anyway you want:

Format (YourDateField, "mm/dd/yyyy") = 10/20/2008
Format (YourDateField, "mmm, yyyy") = October, 2008
Format (YourDateField, "yy/mm/dd") = 08/10/20

HTH
Leslie

 

[jalenben1]

In the table,after I import the spreadsheet do I or can set up the format to read January 2008? because what I did was take my spreadsheet and added two cloumns...one for the month and the other for the year and in the year column it is 2008.

I did not want to do that and have one column. but I am not sure what i am doing wrong because now I have a table and for the data tpye for the month column I tried this format "mmmm yyyy" for it to show January 2008 and it shows "mmmm yyyy". What am I doing wrong?

 

[lespaul]

so when you import your original excel spreadsheet into an Access table your date field doesn't LOOK right? You want in the TABLE to see "January 2008"? If that is so, then you need to create a TEXT field and put in it "January 2008", now realize if you do this it is NOT a date field. You will NOT be able to do any date functions on this field.

Now, if you want to have a DATE field in your table you are going to have to accept the fact that when you look in the TABLE it's not going to look like "January 2008"....it's going to look like whatever your default date setup is in the operating system. But in a query (or form or report) you can FORMAT that date field to LOOK like "January 2008".

That's what all the explanation above was trying to explain to you. A DATE is a very special thing in the computer world. A DATE is the number of days since 12/30/1899...What you want to see is a FORMATTED STRING of a DATE.

Leslie

Have you met Hardy Heron?