Type Conversion Failure

[john in fl]

Hello Everyone,
I have an Append Query that worked fine in Access97 but now fails to
work in Access 2000. The error that occurs is that it puts null values
in a field due to a "Type Conversion Failure".

The field in question is set up as a Long Integer in the target table.

When creating values, the Append Query performs a "group by" on this
field, and, although it leaves the data format blank, only offers
date-related options for formatting. It will not let me format the
field as a long integer in the query.

The difference between the Long Integer format in the target table and
the blank/date format generated by the Append Query appear to be
causing the error.

I'm stumped as to how to correct this...thanks in advance for any
assisstance.

John

[Allen Browne]

John, can you typecast the field in your query?

For example, you have this in the Field row:
[Field1]
try replacing it with:
CLng(Nz([Field1],0))

The Nz() is also needed since CLng() can't handle nulls.

More info on typecasting:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"john in fl" <jo**********@medtronic.com> wrote in message
news:11*********************@v46g2000cwv.googlegro ups.com...

Hello Everyone,
I have an Append Query that worked fine in Access97 but now fails to
work in Access 2000. The error that occurs is that it puts null values
in a field due to a "Type Conversion Failure".

The field in question is set up as a Long Integer in the target table.

When creating values, the Append Query performs a "group by" on this
field, and, although it leaves the data format blank, only offers
date-related options for formatting. It will not let me format the
field as a long integer in the query.

The difference between the Long Integer format in the target table and
the blank/date format generated by the Append Query appear to be
causing the error.

I'm stumped as to how to correct this...thanks in advance for any
assisstance.

John

[john in fl]

Allen,
Thanks...I got it to work !

John

Allen Browne wrote:

John, can you typecast the field in your query?

For example, you have this in the Field row:
[Field1]
try replacing it with:
CLng(Nz([Field1],0))

The Nz() is also needed since CLng() can't handle nulls.

More info on typecasting:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"john in fl" <jo**********@medtronic.com> wrote in message
news:11*********************@v46g2000cwv.googlegro ups.com...

Hello Everyone,
I have an Append Query that worked fine in Access97 but now fails to
work in Access 2000. The error that occurs is that it puts null values
in a field due to a "Type Conversion Failure".

The field in question is set up as a Long Integer in the target table.

When creating values, the Append Query performs a "group by" on this
field, and, although it leaves the data format blank, only offers
date-related options for formatting. It will not let me format the
field as a long integer in the query.

The difference between the Long Integer format in the target table and
the blank/date format generated by the Append Query appear to be
causing the error.

I'm stumped as to how to correct this...thanks in advance for any
assisstance.

John