Combine a text and number field to create a date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Grunen - 06 Feb 2007 15:13 GMT

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.

Reply to this Message

fredg - 06 Feb 2007 17:31 GMT

> I have a database which contain quarter and year fields as numbers. How can
> I convert the number fields to a date field?
[quoted text clipped - 8 lines]
> 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'

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

Klatuu - 06 Feb 2007 20:24 GMT

Good one, Fred!

Signature

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?
[quoted text clipped - 23 lines]
> 286335 'ACC2002: Reserved Words in Microsoft Access'
> 321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

Reply to this Message