Append query doesn't work after conversion from Access 97 to 2000

  • Thread starter Thread starter Barry
  • Start date Start date
B

Barry

I've recently converted a .mdb from Access 97 to 2000 and
now an Append query no longer works. I get the error
"Access can't append all the records in the append query".
It tells me that 9 fields are set to Null due to a type
conversion failure. Here is the SQL of the query:
INSERT INTO api_tblCurrentLbrPrices ( ID, [Size], PriceDate )
SELECT DISTINCT api_tblCurrentLbrPrices.ID,
api_tblCurrentLbrPrices.Size,
[Forms]![api_frmCurrentPrices]![textDate] AS Expr1
FROM api_tblCurrentLbrPrices
WHERE
((([Forms]![api_frmCurrentPrices]![txtDate])=[Forms]![api_frmCurrentPrices]![txtDate]));

I've determined that the date field is causing the problem,
but I can't figure out what the problem is. The textDate
field is formatted on the form as #M/D/YYYY#. The txtDate
field is formatted as MM/DD/YYYY. (The textDate field was
one of my attempts to fix this problem that gave the same
results, so it's not really needed.) Any help would surely
be appreciated!
 
Try setting the Format property of the text box on your form to:
Short Date
or similar. If it is unbound, this helps Access understand the intended data
type. As a bonus, it prevents users entering invalid dates.

You may need to explicitly typecast the value in your query, e.g.:
WHERE CDate([Forms]![api_frmCurrentPrices]![txtDate]) = ...

I didn't undersand the purpose of your WHERE clause.
Won't the text box always be equal to itself unless it is Null?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


I've recently converted a .mdb from Access 97 to 2000 and
now an Append query no longer works. I get the error
"Access can't append all the records in the append query".
It tells me that 9 fields are set to Null due to a type
conversion failure. Here is the SQL of the query:
INSERT INTO api_tblCurrentLbrPrices ( ID, [Size], PriceDate )
SELECT DISTINCT api_tblCurrentLbrPrices.ID,
api_tblCurrentLbrPrices.Size,
[Forms]![api_frmCurrentPrices]![textDate] AS Expr1
FROM api_tblCurrentLbrPrices
WHERE
((([Forms]![api_frmCurrentPrices]![txtDate])=[Forms]![api_frmCurrentPrices]!
[txtDate]));

I've determined that the date field is causing the problem,
but I can't figure out what the problem is. The textDate
field is formatted on the form as #M/D/YYYY#. The txtDate
field is formatted as MM/DD/YYYY. (The textDate field was
one of my attempts to fix this problem that gave the same
results, so it's not really needed.) Any help would surely
be appreciated!
 
Thanks, your suggestions helped! Not until I changed the
date in the Select clause back to txtDate and put the CDate
typecasting on it as you suggested, but it works now!
As for the WHERE clause... I don't really understand it
either, I "inherited" the support of this database, I
didn't write it.
Thanks again!
 
Back
Top