how do i recode the value of a null field 4

[uscitizen]

i have a table which has thousands of records so manually if out of the question (any volunteers?).

anyway, there is a field which should have a combo box on it with yes, no and possibly also n/a -- and it will --however i would need also to assign values to all the records in the database that are currently set to a null value in this field (the actual value would belong to the members of the combo box).

if i were using SAS or something like that it would be very straightforward to code a simple 'if .... then .....' statement to handle this.

 

[JimInKS]

Create an Update query.

Check Access help for details.

 

[Bullschmidt]

And to create an update query just create a regular query and then from the query's design view do Query | Update Query.

Thus to change all rows of the field called MyField with just a B in it to be Black instead do this:
Field of MyField
UpdateTo of Black
Criteria of B


Best regards,
J. Paul Schmidt - Freelance ASP Web Developer

http://www.Bullschmidt.com

- Creating "dynamic" Web pages that read and write from databases...

 

[uscitizen]

this is kind of 'interesting'

here's some sql code that query generator wrote which returned one record having the value 'Yes_No' in a field called Eligible (out of 4961 something records) ?

SELECT [Copy MedRecord].Eligible
FROM [Copy MedRecord]
WHERE ((([Copy MedRecord].Eligible)="IsNull&quot);

now when i have the access query generator author the following sql code:

SELECT [Copy MedRecord].Eligible
FROM [Copy MedRecord]
WHERE ((([Copy MedRecord].Eligible)="Yes_No&quot);

it returns 1151 records satisfying that condition.

in case the alien arithmetic that's producing these results isn't clear to the reader yet -- there is only one record that 'IsNull' when I query the table, although the query should return 4961 - 1151 records which are clearly visible to the eye as one scrolls through the table !!

 

[JimInKS]

This is not properly formed, it is filtering on the string "IsNull":

SELECT [Copy MedRecord].Eligible
FROM [Copy MedRecord]
WHERE ((([Copy MedRecord].Eligible)="IsNull"));

If you want to find entries with null values it should look like this:

SELECT [Copy MedRecord].Eligible
FROM [Copy MedRecord]
WHERE ((([Copy MedRecord].Eligible) Is Null));

Note the removal of the quotes and the space between 'Is' and 'Null'.

 

[uscitizen]

you are quite right. your code seems to have worked! thanks

 

[Hap007]

Jim,

Here is a star for you!!

Enjoy,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[JimInKS]

Thanks Hap!

 

[uscitizen]

just following up with a-nother up-date query:

suppose your interest was adding a field to a table with thousands of pre-existing records into which a unique number would appear. i know autonumber provides unique numbers but what would you have to do to tell ms access to populate an empty field with an autonumber value?

 

[Craig0201]

Add an autonumber field and it will do it automatically.

Craig

 

[janeatwork]

Hi Hap007,

I have given you a star for having the acumen to spot this and also having the courage to point it out.

JAW

 

[Hap007]

Dear JAW,

Thank you much.

I know that when I see a question that I have some experience with, I try to answer. Sometimes it might be a code example or query such as what JimInks provided. When I provide an answer, 50 percent of the time I will open an access database and try the answer so I know that I am providing good information. Then I take a couple of minutes to type a response.

Well, If what I provided solves someone's problem, that is a great thing. Now, If that person was a customer, I would charge them. There is no payment involved here, simply a little check mark that indicates the post was helpful. So, If someone takes the time to solve a problem, you would think that the person being aided would take the time to say thanks.

Thanks Again,
Hap...



Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[uscitizen]

What would you do if the following arose:

You discover the absent person who created the database you've inherited created a numeric field. The numbers themselves have no intrinsic value however different numbers map to different hospital names which is all explained in a two column look-up table. The data entry person clicks the drop-down arrow when completing this field and selects the hospital's name which is displayed in the field in the table (s)he's completing --- however the name is not being stored in the field (because, remember it was created as a numeric field).

You think you'd like to get away from that and would like perhaps to create another field in the table which draws upon a look-up table with just a single column displaying the hospitals and define the stored value of the item chosen to be textual.