Date Field in Table and Query

T

TinleyParkILGal

Professionals:

I have a short date field in a table (example: 2/23/05).
I want to design a query that will use this date but that will separate it
and put the month in one field as February and then 2005 in a second field as
the year.

Example:

Date Field Will Reflect: 2/23/05
Month Field Will Reflect: February
Year Field Will Reflect: 2005

Thanks in advance
 
S

Sprinks

DatePart("yyyy",[YourDateField]) will return 2005 from your example date.
DatePart("m",[YourDateField]) will return 2.

To convert the month integer to a string value "February", you could create
a table:

Months
---------------
MonthInteger Integer
MonthString Text

and get the text through a query joined on MonthInteger, or use a custom
function:

Public Function MonthString (intMonth as Integer) As String
Select Case intMonth
Case 1
MonthString = "January"
Case 2
MonthString = "February"
....etc.
End Select
End Function

Then your string value would be:

MonthString(DatePart("m",[YourDateField]))

Hope that helps.
Sprinks
 
S

Sprinks

Thanks, Danc. That's much more straightforward.

TPIG, you can get the year analogously with Format([YourDate],"yyyy")

Sprinks

danc09 said:
You can also get the month by: Format([TranDate],"mmmm")
dan

TinleyParkILGal said:
Professionals:

I have a short date field in a table (example: 2/23/05).
I want to design a query that will use this date but that will separate it
and put the month in one field as February and then 2005 in a second field as
the year.

Example:

Date Field Will Reflect: 2/23/05
Month Field Will Reflect: February
Year Field Will Reflect: 2005

Thanks in advance
Click to expand...
Click to expand...
 
Top