data type mismatch when using dates

J

JT

Dear all

I have a data field which contains a mixture of date entries and text
entries which read either "ongoing" or "missing".

I use the following function to convert the date entries into the "yyyy mm"
format in a query:

End date: IIf([Provision end date]="missing","missing",IIf([Provision end
date]="ongoing","ongoing",Format(Val([Provision end date]),"yyyy mm")))

In a subsequent query I then attempt to group data based on this End date,
but this results in a "Data type mismatch in criteria expression" error.

I have no idea why I cannot group data in this way and haven't been able to
find a solution elsewhere in this group.

Thanks

John
 
J

John Spencer

As a guess, you have a null value in [Provision End Date] field of at least
one record. Val(Null) will generate an error. When you attempt to do a group
by (or a sort) on a column that has errors, you will get the "data type
mismatch" error.

You could try the following expression
End Date: IIF(IsDate([Provision End Date])=False, [Provision End Date],
Format(DateValue([Provision end date]),"yyyy mm"))

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

Golfinray

I don't necessarily know the answer, but I do know that when I have used VAL
to do conversions, Access sometimes does not see those results as dates and
won't function correctly.
 
J

JT

Thanks John

Unfortunately this hasn't solved my problem. Using this function doesn't
work as the [Provision End Date] field has the text data type. This means
that the "Isdate" function always returns false and the Enddate function
returns either "missing", "ongoing" or the date in it's serial number format.

Furthermore, I still get the data mismatch error when I try to group by End
date.

There are no null values in my dataset.

John

John Spencer said:
As a guess, you have a null value in [Provision End Date] field of at least
one record. Val(Null) will generate an error. When you attempt to do a group
by (or a sort) on a column that has errors, you will get the "data type
mismatch" error.

You could try the following expression
End Date: IIF(IsDate([Provision End Date])=False, [Provision End Date],
Format(DateValue([Provision end date]),"yyyy mm"))

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

I have a data field which contains a mixture of date entries and text
entries which read either "ongoing" or "missing".

I use the following function to convert the date entries into the "yyyy mm"
format in a query:

End date: IIf([Provision end date]="missing","missing",IIf([Provision end
date]="ongoing","ongoing",Format(Val([Provision end date]),"yyyy mm")))

In a subsequent query I then attempt to group data based on this End date,
but this results in a "Data type mismatch in criteria expression" error.

I have no idea why I cannot group data in this way and haven't been able to
find a solution elsewhere in this group.

Thanks

John
Click to expand...
Click to expand...
 
J

JT

Thanks again.

The initial function that I am using to convert the data into the "YYYY MM"
format is working correctly. I'm not sure whether access sees the results as
dates or text, so it's hard to tell whether this is the source of the problem.

The issue may be something to do with access seeing the "ongoing" and
"missing" entries as text and the others as dates. This would mean two
different data types in the same field. I'm not sure whether this is the
case, or if it would prevent me from using group by even if it is.

Any other ideas would be greatly appreciated.

Thanks

John

Golfinray said:
I don't necessarily know the answer, but I do know that when I have used VAL
to do conversions, Access sometimes does not see those results as dates and
won't function correctly.

JT said:
Dear all

I have a data field which contains a mixture of date entries and text
entries which read either "ongoing" or "missing".

I use the following function to convert the date entries into the "yyyy mm"
format in a query:

End date: IIf([Provision end date]="missing","missing",IIf([Provision end
date]="ongoing","ongoing",Format(Val([Provision end date]),"yyyy mm")))

In a subsequent query I then attempt to group data based on this End date,
but this results in a "Data type mismatch in criteria expression" error.

I have no idea why I cannot group data in this way and haven't been able to
find a solution elsewhere in this group.

Thanks

John
Click to expand...
Click to expand...
 
J

John Spencer

Obviously your field [Provision End Date] is a text field. Since you say it
has a serial number format, I am guessing that you mean the date is stored as
something like 39703 for 2008-09-12.

If that is the case, try using the IsNumeric function to test for a number value.
End Date: IIF(IsNumeric([Provision End Date])=False, [Provision End Date],
Format(Val([Provision end date]),"yyyy mm"))

Also, it is possible that you could have a number that is out of range for a
date. Unlikely, but possible. One little typo could cause this. 3e9703
would cause an error as would 3970339703 (a double entry of the same number).

I really suspect a data problem of some type. Good luck.

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

Unfortunately this hasn't solved my problem. Using this function doesn't
work as the [Provision End Date] field has the text data type. This means
that the "Isdate" function always returns false and the Enddate function
returns either "missing", "ongoing" or the date in it's serial number format.

Furthermore, I still get the data mismatch error when I try to group by End
date.

There are no null values in my dataset.

John

John Spencer said:
As a guess, you have a null value in [Provision End Date] field of at least
one record. Val(Null) will generate an error. When you attempt to do a group
by (or a sort) on a column that has errors, you will get the "data type
mismatch" error.

You could try the following expression
End Date: IIF(IsDate([Provision End Date])=False, [Provision End Date],
Format(DateValue([Provision end date]),"yyyy mm"))

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

I have a data field which contains a mixture of date entries and text
entries which read either "ongoing" or "missing".

I use the following function to convert the date entries into the "yyyy mm"
format in a query:

End date: IIf([Provision end date]="missing","missing",IIf([Provision end
date]="ongoing","ongoing",Format(Val([Provision end date]),"yyyy mm")))

In a subsequent query I then attempt to group data based on this End date,
but this results in a "Data type mismatch in criteria expression" error.

I have no idea why I cannot group data in this way and haven't been able to
find a solution elsewhere in this group.

Thanks

John
Click to expand...
Click to expand...
Click to expand...
 
J

JT

Thanks John,

Switching to isnumeric seems to have worked.

Regards

John

John Spencer said:
Obviously your field [Provision End Date] is a text field. Since you say it
has a serial number format, I am guessing that you mean the date is stored as
something like 39703 for 2008-09-12.

If that is the case, try using the IsNumeric function to test for a number value.
End Date: IIF(IsNumeric([Provision End Date])=False, [Provision End Date],
Format(Val([Provision end date]),"yyyy mm"))

Also, it is possible that you could have a number that is out of range for a
date. Unlikely, but possible. One little typo could cause this. 3e9703
would cause an error as would 3970339703 (a double entry of the same number).

I really suspect a data problem of some type. Good luck.

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

Unfortunately this hasn't solved my problem. Using this function doesn't
work as the [Provision End Date] field has the text data type. This means
that the "Isdate" function always returns false and the Enddate function
returns either "missing", "ongoing" or the date in it's serial number format.

Furthermore, I still get the data mismatch error when I try to group by End
date.

There are no null values in my dataset.

John

John Spencer said:
As a guess, you have a null value in [Provision End Date] field of at least
one record. Val(Null) will generate an error. When you attempt to do a group
by (or a sort) on a column that has errors, you will get the "data type
mismatch" error.

You could try the following expression
End Date: IIF(IsDate([Provision End Date])=False, [Provision End Date],
Format(DateValue([Provision end date]),"yyyy mm"))

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

JT wrote:
Dear all

I have a data field which contains a mixture of date entries and text
entries which read either "ongoing" or "missing".

I use the following function to convert the date entries into the "yyyy mm"
format in a query:

End date: IIf([Provision end date]="missing","missing",IIf([Provision end
date]="ongoing","ongoing",Format(Val([Provision end date]),"yyyy mm")))

In a subsequent query I then attempt to group data based on this End date,
but this results in a "Data type mismatch in criteria expression" error.

I have no idea why I cannot group data in this way and haven't been able to
find a solution elsewhere in this group.

Thanks

John
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Type Mismatch 5
Problems displaying dates 5
Running Yearly Sum 1
How to SELECT with date criteria by SQL? 1
Date search query 0
Data type mismatch in criteria expression 4
Data Type Mismatch in Criteria Expression 4
Data type mismatch in criteria expression 1

Top