Update query-make variable=""

[jmj]

I have a query that sets a field to 0 if some conditions are met. I've been asked to change this to "" instead because they can not have 0 in that field. The field is a number field so I get an type conversion error when I use a query like this

UPDATE TableA INNER JOIN TableB ON TableA.STULID = TableB.STUID SET TableA.USERNo3 = ""
WHERE (((TableB.CODE)>=" "))

Is there any way to do this in access.
Thanks for any ideas.
J

 

[lespaul]

you are trying to set a number field to an empty string. Isn't going to work! You need to change it to a text field or keep the 0.

How do the users know what is being stored in the table?

You can change the value in a query if you have to:

SELECT iif(Stuid = 0, '', cstr(stuid)) from tblName

that way in the display it shows up as '', but you don't change the table structure.

HTH

leslie

 

[jmj]

I didn't think it was doable, but figured I'd check anyway.
This query is updating a dbIV database so I can't change the field type. When they run the reports against that database/export data they can't have a 0 in the reports.

Thanks for your response.
J

 

[PHV]

Have you tried this ?
UPDATE TableA INNER JOIN TableB ON TableA.STULID = TableB.STUID SET TableA.USERNo3 = Null
WHERE TableB.CODE>=" "


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244