0

I know how completely wrong it is to store calculated data in a table! But, in this case it is very necessary for what I need.

I am using this statement:

UPDATE BusinessStreets
SET BusinessesPerStreet
= DCount("*","Businesses","StreetName='" & Replace(StreetName,"'","''") & "'");

It goes to update the correct records in the table by asking if I am sure I want to continue as this cannot be undone, but then gives me an error:

Microsoft Office Access can't update all the records in the update query.
Microsoft Office Access didn't update 9 field(s) due to a type conversion failure

When it says "field(s)" I am sure it means records as I have 9 records in this table at the moment, which once I have this working, I will then increase to over 100.

The field which will be updated is a Long Integer field, which should be fine as the Dcount function will yield numeric results.

What is the problem with this statement?

Thank you for giving me your time to help me with this. It's really really appreciated!!

theWolf
  • 37
  • 2
  • 10
  • You can try running just the select portion of the query to see what the result will by, something like SELECT DCount("*","Businesses","StreetName='" & Replace(StreetName,"'","''") & "'") FROM BusinessStreets; this way you can see the output from the DCount function and maybe see which rows are causing trouble. – Roger Harvest Dec 12 '12 at 23:24

1 Answers1

0

Without seeing the data, one can't be sure what the conversion failure is about but maybe some of the data already contains a double quote? Or maybe you have null data for some of the records?

PmanAce
  • 4,000
  • 2
  • 24
  • 29
  • I do have null data for some of the records already yes. How do I work around this? Thanks – theWolf Dec 12 '12 at 19:29
  • I have just deleted the records which contain null data and the problem still persists unfortunately – theWolf Dec 12 '12 at 19:32
  • This question is in reference to this article [link](http://stackoverflow.com/questions/13810848/how-can-i-count-the-records-from-one-table-and-populate-a-record-with-the-value) – theWolf Dec 12 '12 at 19:42