Combine a text and number field to create a date

[Guest]

I have a database which contain quarter and year fields as numbers. How can
I convert the number fields to a date field?


Quarter Year Value
1 2006 1000
2 2006 500
3 2006 250
4 2005 1250


I would like to combine the 1st Quarter 2006 information to a 3/31/06 date
field or something similar. Due to existing reports/forms/queries of the
system, the table entries do need to remain as numbers. Please help.

 

[fredg]

I have a database which contain quarter and year fields as numbers. How can
I convert the number fields to a date field?

Quarter Year Value
1 2006 1000
2 2006 500
3 2006 250
4 2005 1250

I would like to combine the 1st Quarter 2006 information to a 3/31/06 date
field or something similar. Due to existing reports/forms/queries of the
system, the table entries do need to remain as numbers. Please help.

=DateSerial([YearField],([Quarter]*3)+1,0)

? DateSerial(2005,(4*3)+1,0)
12/31/2005

Note: Year and Value are reserved Access/VBA/Jet words and should not
be used as a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

 

[Guest]

Good one, Fred!
--
Dave Hargis, Microsoft Access MVP

I have a database which contain quarter and year fields as numbers. How can
I convert the number fields to a date field?

Quarter Year Value
1 2006 1000
2 2006 500
3 2006 250
4 2005 1250

I would like to combine the 1st Quarter 2006 information to a 3/31/06 date
field or something similar. Due to existing reports/forms/queries of the
system, the table entries do need to remain as numbers. Please help.

=DateSerial([YearField],([Quarter]*3)+1,0)

? DateSerial(2005,(4*3)+1,0)
12/31/2005

Note: Year and Value are reserved Access/VBA/Jet words and should not
be used as a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'