stored procedures

[arjun.bhandari]

Hi,

I am switching from sql server to msaccess and cannot find an equivalent of a stored procedure in access.

The task I want to do is the following.

1. I have a table with many field.s
2. I want to loop through all the fields and replace records which have a value of 0 with NULL.

How could I do this without writing seperate update statements for each of the fields.

Thanks.

 

[statsman]

Use an update query.
You may run into a problem if the field with the zero is set to numeric. It won't accept a text "NULL".

 

[arjun.bhandari]

update query

I can use the update query but I want to use a generic query which gets the fieldnames and updates each of the fields. initially all the data is imported as text and hence i get around the problem of setting numeric values to null.

 

[oscarooko]

You may want to add a new column, set the field as text, then use an update query to populate the fields, setting NULL where the value is ZERO ie
you already have a table with a column A. This column is the one that has the data to be updated. Now Create another column B, set the data type to text, thenpupulate it with the corresponding values of A.
Now you are ready to run your update query on this new column B setting the ZEROs to NULL.
After you are throu, you will end up with two almost identical columns A and B. You can go ahead and delete A as and rename B to A if you have to keep tghe original column name.

 

[arjun.bhandari]

Hi,

I have many fields, so i need someway to get the names of the fields. in sql one could use the table name object and get the colids from there, i am not sure if there is an equivalent phrase in access.

Thanks
A

 

[Uncle Gizmo]

This Thread may give you a start.