How can I create a new field?

[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

 

[Gina Whipp]

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

 

[John]

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!

 

[Gina Whipp]

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

 

[John W. Vinson]

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

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.

 

[John]

Gina Whipp and John W. Vinson

Thank you for your help!

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

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.