Formatting data into mm/yyyy

[RobertStanke]

Ok, I am having problems formatting a date field to display a certain way.
Here is how the data sits in the table:

PHONE CONTACT 6/10/06
EMAIL 6/24/06
PHONE CONTACT 6/29/06
PHONE CONTACT 7/11/06
PHONE CONTACT 7/15/06
EMAIL 8/02/06

In the query results, I want to see each action sum'ed by month:

PHONE CONTACT 6/2006 2
EMAIL 6/2006 1

Etc., Etc., Etc.

I have the query set up right to group and count, but I can't get the date
field formatted properly. Can someone please help me out? Thanks-

Robert Stanke

 

[John Spencer]

Field: TheDate: Format([YourDateField],"mm/yyyy")

SELECT [ContactType]
, Format([YourDateField],"mm/yyyy") as TheDate
, Count([ContactType]) as CountThem
FROM [Your Table]
GROUP BY [ContactType]
, Format([YourDateField],"mm/yyyy")
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[RobertStanke]

Ok, this is what I have in the query field, but now I am not getting a data
return. The query runs, but no records (and I know there should be):

DATA_MONTH: Format([DATECREATED],"mm/yyyy")

And what should I have in the "Total" line?

Thanks for the help!

Field: TheDate: Format([YourDateField],"mm/yyyy")

SELECT [ContactType]
, Format([YourDateField],"mm/yyyy") as TheDate
, Count([ContactType]) as CountThem
FROM [Your Table]
GROUP BY [ContactType]
, Format([YourDateField],"mm/yyyy")
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Ok, I am having problems formatting a date field to display a certain way.
Here is how the data sits in the table:

PHONE CONTACT 6/10/06
EMAIL 6/24/06
PHONE CONTACT 6/29/06
PHONE CONTACT 7/11/06
PHONE CONTACT 7/15/06
EMAIL 8/02/06

In the query results, I want to see each action sum'ed by month:

PHONE CONTACT 6/2006 2
EMAIL 6/2006 1

Etc., Etc., Etc.

I have the query set up right to group and count, but I can't get the date
field formatted properly. Can someone please help me out? Thanks-

Robert Stanke

 

[RobertStanke]

Better yet, here is the SQL code:

SELECT dbo_ACTIVITIES.APPTSTAFFREP, dbo_ACTIVITIES.ACTCODE AS [ACTIVITY
CODE], Count(dbo_ACTIVITIES.ID) AS CountOfID,
Format([dbo_ACTIVITIES.DATECREATED],"mm/yyyy") as DataMonth
FROM dbo_ACTIVITIES
WHERE (((Format([DATECREATED],"mm/yyyy")) Between #7/1/2006# And
#5/31/2007#) AND ((dbo_ACTIVITIES.APPTSTATUS)="Completed"))
GROUP BY dbo_ACTIVITIES.APPTSTAFFREP, dbo_ACTIVITIES.ACTCODE,
dbo_ACTIVITIES.DATECREATED
HAVING (((dbo_ACTIVITIES.APPTSTAFFREP)="RSTANKE"))
ORDER BY dbo_ACTIVITIES.APPTSTAFFREP, dbo_ACTIVITIES.ACTCODE;

Any thoughts?

Field: TheDate: Format([YourDateField],"mm/yyyy")

SELECT [ContactType]
, Format([YourDateField],"mm/yyyy") as TheDate
, Count([ContactType]) as CountThem
FROM [Your Table]
GROUP BY [ContactType]
, Format([YourDateField],"mm/yyyy")
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Ok, I am having problems formatting a date field to display a certain way.
Here is how the data sits in the table:

PHONE CONTACT 6/10/06
EMAIL 6/24/06
PHONE CONTACT 6/29/06
PHONE CONTACT 7/11/06
PHONE CONTACT 7/15/06
EMAIL 8/02/06

In the query results, I want to see each action sum'ed by month:

PHONE CONTACT 6/2006 2
EMAIL 6/2006 1

Etc., Etc., Etc.

I have the query set up right to group and count, but I can't get the date
field formatted properly. Can someone please help me out? Thanks-

Robert Stanke

 

[Tom Lake]

Ok, this is what I have in the query field, but now I am not getting a
data
return. The query runs, but no records (and I know there should be):

Don't use the formatted date for comparison. Keep using the unformatted
date for the criteria comparison but don't display that field. Instead
display
the formatted date field.

Tom Lake

 

[RobertStanke]

Got it to work!!! Thank you so much gentlemen!

Don't use the formatted date for comparison. Keep using the unformatted
date for the criteria comparison but don't display that field. Instead
display
the formatted date field.

Tom Lake