Change a field type from data to date/time??

[MarieG]

I have a date format of 01/01/09 12:59:59 AM that I formatted to be
01/01/01. When I run the query, the 01/01/01 field is put into the table as
a text field.. this screws with my crosstab when I sort ascending...
Thoughts?

 

[KARL DEWEY]

I have a date format of 01/01/09 12:59:59 AM
Where is the above data at when you format it?
What kind of query is it? What is the data type of the field in the table?

Post the SQL of your query that puts it in the table. Open query in design
view, click on VIEW - SQL View, highlight all, copy, and paste in a post.

 

[John Spencer MVP]

When you use the format function you are converting the date to a string?
Try using DateValue(SomeDateField) to return a date. IF SomeDateField Can be
null then you will need a slightly more complex statement to strip off the time.

IIF(IsDate(SomeDateField),DateValue(SomeDateField),Null)



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[MarieG]

Here is the SQL view.. Also, when I format the date, I'm asking it to give
me the MONTH, not just the date. Thanks so much!!!

SELECT "COMPANY" AS Company, [dbo_vBilling_Visits-TMC002-OGA].DateOfService,
[dbo_PatientVisitProcsTMC002-OGA].CPTCode,
[dbo_PatientVisitProcsTMC002-OGA].RVU, Format([DATEOFSERVICE],"mm/dd/yy") AS
[DOS MONTH] INTO [CPT COUNT]
FROM [Date Range], [dbo_vBilling_Visits-TMC002-OGA] INNER JOIN
[dbo_PatientVisitProcsTMC002-OGA] ON
[dbo_vBilling_Visits-TMC002-OGA].PatientVisitID =
[dbo_PatientVisitProcsTMC002-OGA].PatientVisitId
WHERE ((([dbo_vBilling_Visits-TMC002-OGA].DateOfService) Between [FROM DATE]
And [TO DATE]));

 

[MarieG]

And this is the Crosstab Query that I'm making from the first Query. In
designt view, I have the DOS MONTH item to sort ASCENDING.. but it doesn't??

TRANSFORM Count([CPT COUNT].CPTCode) AS CountOfCPTCode
SELECT [CPT COUNT].Company
FROM [Date Range], [CPT COUNT]
GROUP BY [CPT COUNT].Company
ORDER BY [CPT COUNT].[DOS MONTH]
PIVOT [CPT COUNT].[DOS MONTH];

Here is the SQL view.. Also, when I format the date, I'm asking it to give
me the MONTH, not just the date. Thanks so much!!!

SELECT "COMPANY" AS Company, [dbo_vBilling_Visits-TMC002-OGA].DateOfService,
[dbo_PatientVisitProcsTMC002-OGA].CPTCode,
[dbo_PatientVisitProcsTMC002-OGA].RVU, Format([DATEOFSERVICE],"mm/dd/yy") AS
[DOS MONTH] INTO [CPT COUNT]
FROM [Date Range], [dbo_vBilling_Visits-TMC002-OGA] INNER JOIN
[dbo_PatientVisitProcsTMC002-OGA] ON
[dbo_vBilling_Visits-TMC002-OGA].PatientVisitID =
[dbo_PatientVisitProcsTMC002-OGA].PatientVisitId
WHERE ((([dbo_vBilling_Visits-TMC002-OGA].DateOfService) Between [FROM DATE]
And [TO DATE]));

Where is the above data at when you format it?

What kind of query is it? What is the data type of the field in the table?

Post the SQL of your query that puts it in the table. Open query in design
view, click on VIEW - SQL View, highlight all, copy, and paste in a post.

 

[KARL DEWEY]

Format your date in the crosstab query, not in the first query.

Try these changes --
[CPT COUNT] --
SELECT "COMPANY" AS Company, [dbo_vBilling_Visits-TMC002-OGA].DateOfService,
[dbo_PatientVisitProcsTMC002-OGA].CPTCode,
[dbo_PatientVisitProcsTMC002-OGA].RVU
FROM [dbo_vBilling_Visits-TMC002-OGA] INNER JOIN
[dbo_PatientVisitProcsTMC002-OGA] ON
[dbo_vBilling_Visits-TMC002-OGA].PatientVisitID =
[dbo_PatientVisitProcsTMC002-OGA].PatientVisitId
WHERE ((([dbo_vBilling_Visits-TMC002-OGA].DateOfService) Between [FROM DATE]
And [TO DATE]));

TRANSFORM Count([CPT COUNT].CPTCode) AS CountOfCPTCode
SELECT [CPT COUNT].Company
FROM [CPT COUNT]
GROUP BY [CPT COUNT].Company
PIVOT Format([DATEOFSERVICE],"yyyy mm/dd/yy")

And this is the Crosstab Query that I'm making from the first Query. In
designt view, I have the DOS MONTH item to sort ASCENDING.. but it doesn't??

TRANSFORM Count([CPT COUNT].CPTCode) AS CountOfCPTCode
SELECT [CPT COUNT].Company
FROM [Date Range], [CPT COUNT]
GROUP BY [CPT COUNT].Company
ORDER BY [CPT COUNT].[DOS MONTH]
PIVOT [CPT COUNT].[DOS MONTH];

Here is the SQL view.. Also, when I format the date, I'm asking it to give
me the MONTH, not just the date. Thanks so much!!!

SELECT "COMPANY" AS Company, [dbo_vBilling_Visits-TMC002-OGA].DateOfService,
[dbo_PatientVisitProcsTMC002-OGA].CPTCode,
[dbo_PatientVisitProcsTMC002-OGA].RVU, Format([DATEOFSERVICE],"mm/dd/yy") AS
[DOS MONTH] INTO [CPT COUNT]
FROM [Date Range], [dbo_vBilling_Visits-TMC002-OGA] INNER JOIN
[dbo_PatientVisitProcsTMC002-OGA] ON
[dbo_vBilling_Visits-TMC002-OGA].PatientVisitID =
[dbo_PatientVisitProcsTMC002-OGA].PatientVisitId
WHERE ((([dbo_vBilling_Visits-TMC002-OGA].DateOfService) Between [FROM DATE]
And [TO DATE]));

I have a date format of 01/01/09 12:59:59 AM
Where is the above data at when you format it?

When I run the query, the 01/01/01 field is put into the table as a text field.
What kind of query is it? What is the data type of the field in the table?

Post the SQL of your query that puts it in the table. Open query in design
view, click on VIEW - SQL View, highlight all, copy, and paste in a post.

:

I have a date format of 01/01/09 12:59:59 AM that I formatted to be
01/01/01. When I run the query, the 01/01/01 field is put into the table as
a text field.. this screws with my crosstab when I sort ascending...
Thoughts?