Error with query 1

[Niebotel]

thread701-1691734
UPDATE Ontvangsten
SET ID = DCount("*","Ontvangsten","IDIn<=" & [IDIn])
did work in the past but now it fails on another table:
UPDATE [Kopie van Passanten]
SET ID = DCount("*","[Kopie van Ontvangsten]","IDIntern<=" & [IDIntern]);

When the query runs it dais that it could not chage the field because of:
I translate:
BECAUSE OF ERROR IN CONVERTING THE TYPE
Because I do not completely undertsnd the query Ii is difficult for me to find a solution.

Can you explain or help?

 

[dhookom]

What are the data types of the ID and IDIntern fields. Your expressions suggest they are both numeric.

Duane
Hook'D on Access
MS Access MVP

 

[Niebotel]

Yes that is correct I alraedy had a look because I assumed that that is the problem:
IDIntern; Autonumbering; long integer; indexed
ID; Numeric; double precision;

 

[PHV]

ID should be Long instead of Real.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Niebotel]

Hi, thanks for your answer.
Can tou tell me why or where to find an explanation?
I would like to learn a bit.

Gr. W.

 

[Niebotel]

I tried it but I get the same error.

 

[dhookom]

Open [Kopie van Ontvangsten] in datasheet view and make sure:
[ol 1]
[li]IDIntern is right-aligned[/li]
[li]ID is right-aligned[/li]
[li]ID is not an autonumber[/li]
[/ol]

Duane
Hook'D on Access
MS Access MVP

 

[Niebotel]

I did change as you say (the field were general aligned), but error message is the same.

 

[dhookom]

I don't understand "general aligned". In datasheet view, they should either be aligned left or right. Is [Kopie van Ontvangsten] an updateable Access table?

Duane
Hook'D on Access
MS Access MVP

 

[Niebotel]

I had a look in design view and the dutch word is "Algemeen" : General, but anyhow they are now right-aligned.
Yes it is an updatable table.

I tried with another simple query in which I set the field ID to 1; no problem.

 

[dhookom]

Does this work:

UPDATE [Kopie van Passanten] 
SET ID = DCount("*","[Kopie van Ontvangsten]","IDIntern<=""" & [IDIntern] & """");

Duane
Hook'D on Access
MS Access MVP

 

[Niebotel]

I exported the query and table to a small mdb, is it possible to sent it? If yes how?

 

[Niebotel]

No, your last suggestion gives the same error. unfortunately!

 

[dhookom]

Are any of the IDIntern fields Null?

What happens if you make a copy of the table and remove nearly all the records and try with the subset?

Can you try this and possibly post the significant fields/data for us to try?

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

You could send me a copy of the file. My email is noted at the bottom of the Hook'd on Access link in my signature.

Duane
Hook'D on Access
MS Access MVP

 

[Niebotel]

I sent you a copy in zip format.
Let me know if you do not receive, because sometimes it is difficult to sent Accees db's by mail

Greeting ans looking forward to your opinion.

Gr. W.

 

[dhookom]

Your database didn't have the table [Kopie van Ontvangsten]. If I replace that table name with [Kopie van Passanten] it works as expected.

Duane
Hook'D on Access
MS Access MVP

 

[Niebotel]

Terrible mistake; I overlooked it all the time!
I apoligize for my stupidness!

 

[dhookom]

No problem. I should have caught this earlier.

Duane
Hook'D on Access
MS Access MVP

 

[Niebotel]

Have a nice day/evening