• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

Access 2003: Convert date into text

Hi:
I have a field called [ShortDate] which reads a year and month as:  "2006 06" for 2006 June.  I would like to make another field from this one that reads: "2006 June".  I imagine that an amendment to the following query would be easiest.

SELECT tblTFILE_New_TFILE_DateA60504_Converted.KEY, tblTFILE_New_TFILE_DateA60504_Converted.DEPT, tblTFILE_New_TFILE_DateA60504_Converted.REC, tblTFILE_New_TFILE_DateA60504_Converted.NAME, tblTFILE_New_TFILE_DateA60504_Converted.CODE, tblTFILE_New_TFILE_DateA60504_Converted.FDATE_Converted, tblTFILE_New_TFILE_DateA60504_Converted.FDATE, tblTFILE_New_TFILE_DateA60504_Converted.DESC, tblTFILE_New_TFILE_DateA60504_Converted.OP_ID, tblTFILE_New_TFILE_DateA60504_Converted.ShortDate INTO tblTeam3APPOsByOperator
FROM tblTFILE_New_TFILE_DateA60504_Converted
WHERE (((tblTFILE_New_TFILE_DateA60504_Converted.OP_ID) Like "RM" Or (tblTFILE_New_TFILE_DateA60504_Converted.OP_ID) Like "CD" Or (tblTFILE_New_TFILE_DateA60504_Converted.OP_ID) Like "NC"));

Thanks!

Charlie
0
cepes
Asked:
cepes
  • 2
  • 2
  • 2
1 Solution
 
rockiroadsCommented:
format your date field like this

Format([datefld],"YYYY MMMM")

0
 
rockiroadsCommented:
which field is it u want
0
 
mbizupCommented:
It sounds like [ShortDate ] is a string and not a date type?  Give this a try...

SELECT tblTFILE_New_TFILE_DateA60504_Converted.KEY, tblTFILE_New_TFILE_DateA60504_Converted.DEPT, tblTFILE_New_TFILE_DateA60504_Converted.REC, tblTFILE_New_TFILE_DateA60504_Converted.NAME, tblTFILE_New_TFILE_DateA60504_Converted.CODE, tblTFILE_New_TFILE_DateA60504_Converted.FDATE_Converted, tblTFILE_New_TFILE_DateA60504_Converted.FDATE, tblTFILE_New_TFILE_DateA60504_Converted.DESC, tblTFILE_New_TFILE_DateA60504_Converted.OP_ID, tblTFILE_New_TFILE_DateA60504_Converted.ShortDate,
Left( tblTFILE_New_TFILE_DateA60504_Converted.ShortDate,4) & " "  & MonthName( Right(tblTFILE_New_TFILE_DateA60504_Converted.ShortDate,2))  As FormattedShortDate
 INTO tblTeam3APPOsByOperator
FROM tblTFILE_New_TFILE_DateA60504_Converted
WHERE (((tblTFILE_New_TFILE_DateA60504_Converted.OP_ID) Like "RM" Or (tblTFILE_New_TFILE_DateA60504_Converted.OP_ID) Like "CD" Or (tblTFILE_New_TFILE_DateA60504_Converted.OP_ID) Like "NC"));


I added this:
Left( tblTFILE_New_TFILE_DateA60504_Converted.ShortDate,4) & " "  & MonthName( Right(tblTFILE_New_TFILE_DateA60504_Converted.ShortDate,2))  As FormattedShortDate

to convert formats like 2006 06 to 2006 June
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
cepesAuthor Commented:
[ShortDate]
0
 
cepesAuthor Commented:
Sorry... just thought of another issue...  If I change to "2006 June" formatting, I lose the sort sequence order of having 2006 06...  I suppose I could sort by ShortDate and make it not visible in the report...

Charlie
0
 
mbizupCommented:
That would be the best way to go... simpler than working out a sort  on 2006 June etc.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now