Re: Type Conversion Failure

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" <john.caserta@xxxxxxxxxxxxx> wrote in message
news:1140924164.222851.67910@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Very Weird Date Format Problem
    ... Many, many, many thanks John. ... the date fields set format to d All but L1 display the day. ... The query uses a stored field which is the first of the month. ...
    (microsoft.public.access.reports)
  • Re: formatting a calculated number field on a form
    ... > A query in an MDB file CAN call a user-defined function. ... > function name is Age and it returns a double. ... The user will certainly "notice" if a patient was born on Jan ... I just want control over the *display format* of a number ...
    (microsoft.public.access.forms)
  • Re: date format inconsistent
    ... Many thanks John ... You're spot on I had changed the code but not the query! ... My query is flipping ambiguous dates to US format. ... strsql = "SELECT Jobdata.Labname" ...
    (microsoft.public.access.queries)
  • RE: Query count then average.
    ... What was happening in my database was the query returned the ... "Ken Sheridan" wrote: ... It probably depends where you set the Format property. ... "Robert F." ...
    (microsoft.public.access.queries)
  • RE: Cannot get code to work for API Save Dialog Box
    ... I got it working to format correctly now. ... that table in my other queries and union query. ... The code is actually working by giving me the totals I need. ... The first spreadsheet placed where I told it to be contains the incorrect ...
    (microsoft.public.access.forms)