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

[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!

 

[Allen Browne]

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!

 

[Barry]

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!