Date, Type mismatch

[mohsin via AccessMonster.com]

Hi Expert

I do a query to get per month. The problem is the output is as below. How can
I make a correction, for the type of date? So as as will get the right result.


Thank you for your help,

MSS Month CSR%
JD1MSS01 29.09.09 0.96
JD1MSS01 30.09.09 0.96
JD1MSS01 December 0.96
RY1MSS01 29.09.09 0.92

My SQL;
SELECT [qry6_CC_CSR%_daily].MSS, Format([qry6_CC_CSR%_daily]![Date],"mmmm")
AS [Month], Avg(([qry6_CC_CSR%_daily]![CSR%])) AS [CSR%]
FROM [qry6_CC_CSR%_daily]
GROUP BY [qry6_CC_CSR%_daily].MSS, Format([qry6_CC_CSR%_daily]![Date],"mmmm")
;

 

[Steve Schapel]

Mohsin,

In the qry6_CC_CSR%_daily, what is the data type of the [Date] field? Is it
a Date/Time data type? It looks to me like it is Text data type. Therefore
the Format function as you are using it here won't work. Can you give some
more information about the baseline data in this field?

By the way, as an aside, you are using 'Date' and 'Month' as field names.
These are both Reserved Words (i.e. have a special meaning) in Access, and
as such should not be used as the name of fields or controls or objects.
Also, it is not a good idea to use a % as part of the name of a field or
object. Thirdly, you are using the same name ([CSR%]) as the alias for a
calculated field, as the name of the field that the calculation is based
on - this is also not a good idea.

 

[mohsin via AccessMonster.com]

Hi Steve

Thanks for your reply.
The date result from qry6, basically from the qry1, which i used, cmd
"Format(Replace(tbl_ClearCodeMeasurement!Time,".","/"),"dd\.mm\.yy") AS [Date]
" to seperate the date and time (mm/dd/yyyy hh:mm am/pm). Probaly the result
from the cmd as a text format, instence of date type.

Any suggestion to fix it?

And thanks for your suggestion about the naming convention, I will modify it.

rgds, Mohsin

Mohsin,

In the qry6_CC_CSR%_daily, what is the data type of the [Date] field? Is it
a Date/Time data type? It looks to me like it is Text data type. Therefore
the Format function as you are using it here won't work. Can you give some
more information about the baseline data in this field?

By the way, as an aside, you are using 'Date' and 'Month' as field names.
These are both Reserved Words (i.e. have a special meaning) in Access, and
as such should not be used as the name of fields or controls or objects.
Also, it is not a good idea to use a % as part of the name of a field or
object. Thirdly, you are using the same name ([CSR%]) as the alias for a
calculated field, as the name of the field that the calculation is based
on - this is also not a good idea.

Hi Expert

[quoted text clipped - 19 lines]

Format([qry6_CC_CSR%_daily]![Date],"mmmm")
;

 

[Steve Schapel]

Mohsin,

The value of a Date/Time field is basically a number. The display as a date
and time is a formatting consideration, but the underlying value does not
change.

If you have a field that contains both date and time components, you can
return the date only by either of these functions:
Int([YourDateTimeField])
DateValue([YourDateTimeField])

With either of these, what you end up with is still a number which can be
shown as a date. Which I think is what you want in this example.

If you want the month number of the field, then use the Month function:
Month([YourDateTimeField])
This is possibly what you will need as the basis of any summary/grouped
data, because if you use the month name, they will be ordered
alphabetically.

 

[mohsin via AccessMonster.com]

Steve

I'm able to output the month after fix the
date qry - "Date": Format([tbl_ClearCodeMeasurement!Time],"short date")
month" qry - "Month": Format([qry6_CC_CSR%_daily]!["Date"],"mmmm")

Thanks for your help.
rgds - mohsin

Mohsin,

The value of a Date/Time field is basically a number. The display as a date
and time is a formatting consideration, but the underlying value does not
change.

If you have a field that contains both date and time components, you can
return the date only by either of these functions:
Int([YourDateTimeField])
DateValue([YourDateTimeField])

With either of these, what you end up with is still a number which can be
shown as a date. Which I think is what you want in this example.

If you want the month number of the field, then use the Month function:
Month([YourDateTimeField])
This is possibly what you will need as the basis of any summary/grouped
data, because if you use the month name, they will be ordered
alphabetically.

Hi Steve

[quoted text clipped - 12 lines]

rgds, Mohsin

 

[Steve Schapel]

Mohsin,

I understand what you have done. But it is, quite frankly, a poor choice.

The Format function only affects the way the data is displayed. In other
words, the value of this:
Format([tbl_ClearCodeMeasurement!Time],"short date")
.... does *not* return the Date portion of the original field. The date
*and* time portions of the original data are still there, it's just that the
time portion will not be shown.

The second example, to get month text, I would not advise to use a Format
function on a field that is itself the result of a Format function. In any
case, it achieves absolutely nothing to use the intermediate step. You have
exactly the same end result, with more efficiency and less confusion, if you
do this:
MonthText: Format([tbl_ClearCodeMeasurement].[Time],"mmmm")

--
Steve Schapel, Microsoft Access MVP

Steve

I'm able to output the month after fix the
date qry - "Date": Format([tbl_ClearCodeMeasurement!Time],"short date")
month" qry - "Month": Format([qry6_CC_CSR%_daily]!["Date"],"mmmm")

Thanks for your help.
rgds - mohsin

Mohsin,

The value of a Date/Time field is basically a number. The display as a
date
and time is a formatting consideration, but the underlying value does not
change.

If you have a field that contains both date and time components, you can
return the date only by either of these functions:
Int([YourDateTimeField])
DateValue([YourDateTimeField])

With either of these, what you end up with is still a number which can be
shown as a date. Which I think is what you want in this example.

If you want the month number of the field, then use the Month function:
Month([YourDateTimeField])
This is possibly what you will need as the basis of any summary/grouped
data, because if you use the month name, they will be ordered
alphabetically.