How can I create a new field?

  • Thread starter Thread starter John
  • Start date Start date
J

John

I have a table as follows:

NO. Year Month Dealer Name

1 2008 10 Dealer A
2 2008 11 Dealer A
3 2009 1 Dealer B
4 2009 2 Dealer B


I want to create a query to compose field "Year" and "Month" like this:

"2008"+"10" --> 2008-10 (a new field)

How could I get it? Thanks
 
John,

First issue is that if you have fields with the names Year and/or Month
you'll want to change those as they are reserved words in Access. To answer
your question, in the Query grid window copy/paste "YearMonth: [Year] & "-"
& [Month]", no quotation marks.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
Gina:

Yes,I got it.Thanks!

Yet I have another question : What is the type of the new field
"Yearmonth",date?

If I want to define the attribute of the new field as a date type,how
can I get it?

Thank you very much!
 
John,

Not sure what you mean by define... In a query you don't need to define the
attribute. If I HAD TO I guess I would say text because technically Access
doesn't 'store' year-month so can't use Date/Time.

And Your Welcome...
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
I have a table as follows:

NO. Year Month Dealer Name

1 2008 10 Dealer A
2 2008 11 Dealer A
3 2009 1 Dealer B
4 2009 2 Dealer B


I want to create a query to compose field "Year" and "Month" like this:

"2008"+"10" --> 2008-10 (a new field)

How could I get it? Thanks
Click to expand...

A Date must be a full date, year, month, day (and actually a time, though if
you leave that off it will use midnight). You can use a calculated field

TheDate: DateSerial([Year], [Month], 1)

to get the first of the month; this date/time field can be displayed in any
format you wish, such as "yyyy-mm".

This field should NOT exist in your table unless you want to permanently
*move* the two fields into a date field, using an Update query; if you do so
you should delete the two fields once you're sure the date is working.
 
Gina Whipp and John W. Vinson

Thank you for your help!

John

John W. Vinson said:
I have a table as follows:

NO. Year Month Dealer Name

1 2008 10 Dealer A
2 2008 11 Dealer A
3 2009 1 Dealer B
4 2009 2 Dealer B


I want to create a query to compose field "Year" and "Month" like this:

"2008"+"10" --> 2008-10 (a new field)

How could I get it? Thanks
Click to expand...

A Date must be a full date, year, month, day (and actually a time, though
if
you leave that off it will use midnight). You can use a calculated field

TheDate: DateSerial([Year], [Month], 1)

to get the first of the month; this date/time field can be displayed in
any
format you wish, such as "yyyy-mm".

This field should NOT exist in your table unless you want to permanently
*move* the two fields into a date field, using an Update query; if you do
so
you should delete the two fields once you're sure the date is working.
Click to expand...
 
Back
Top