Update query not working 1

[mraetrudeaujr]

I tried to create an Update Query to pull the data into a new field and the data didn't show up there. What happened was that I decided to replace the simple 'check boxes' with an 'option group' to avoid errors in selecting more than one check box. I already have 700 records in the database now, and I need have these three 'fields' consolidated under one field (option group). I was told that an update query could be used to replace the yes/no check box values with their respective option group values; 1, 2, or 3.

When I first tried out this Update Query a confirmation box said that it was going to populate 207 records into the new field. When I revisted the table, these records weren't in the newly created field. The good news is that I didn't lose the original information.

My plan was to experiment with just one of the populated fields and if I got the results that I wanted/needed, then I would create separate Update Queries for the other two fields. However, now the results are coming back as zero "0" for records being able to be updated. This would tell me that the update query worked, but like I stated above, this new field was not updated!

Here are the fields: Voluntary Return, WA/NTA, and TOT. These are the original 'check box' fields (yes/no). The new field is labeled DISPOSITION. This is the 'Option Group'. Here is the SQL that returned the empty results in the table:

 UPDATE tbl_reinstatement SET tbl_reinstatement.VOLUNTARY_RETURN = "DISPOSITION"
WHERE ((([tbl_reinstatement].[VOLUNTARY_RETURN])=True));

If I can straighten this out, I can duplicate it for the remaining fields and then delete them. I realize that this will also affect my queries and reports, but that's okay. Any help will be greatly appreciated.

 

[BNPMike]

Personally I can't see how that could work as you are reading a boolean field but loading a text value into it.

Are you trying to add a new field 'DISPOSITION'? If so you need to change the table in Table Design or run an SQL DDL ALTER statement.

 

[lupins46]

Your statement attempts to set the value of a field called Voluntary_Return to the literal text "Disposition".
This does not sound like what you want.

From your explanation, you should be setting the field Disposition to 1/2/3 (whichever is the option group value that corresponds to Voluntary_return = True.

For example:

UPDATE tbl_reinstatement SET tbl_reinstatement.Disposition=1
WHERE ((([tbl_reinstatement].[VOLUNTARY_RETURN])=True));

 

[PHV]

Something like this ?
UPDATE tbl_reinstatement
SET yourNEWfield = IIf([VOLUNTARY_RETURN],1, IIf([WA/NTA],2, IIf([TOT],3, Null)))
WHERE yourNEWfield Is Null;

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

 

[SJMcAbney]

mraetrudeaujr,

Always remember that if you arehand writing your SQL in Access that any table or field that you use wherein you unfortunately title with a space or other special character will require that you surreound the field and table name separately with square brackets.

Thus tbl_reinstatement becomes [tbl_reinstatement]

and reinstatement.VOLUNTARY_RETURN becomes [reinstatement].[VOLUNTARY_RETURN]

Stewart J. McAbney | Talk History

 

[mraetrudeaujr]

PHV,

The SQL statement that you gave me worked great! Obviously I had to substitute my information, but nevertheless it worked. The first time I ran it made me feel a little uneasy with updating 737 records. After re-running it, the total records to update changed to 455 records. This puzzled me, so I just let it rip. I didn't get the results I wanted so I deleted this 'DISPOSITION' field and started over. After letting the query do its job, I checked the results and this time it worked perfectly. Thanks again.

 

[mraetrudeaujr]

PHV,

The SQL statement that you gave me worked great! Obviously I had to substitute my information, but nevertheless it worked. The first time I ran it made me feel a little uneasy with updating 737 records. After re-running it, the total records to update changed to 455 records. This puzzled me, so I just let it rip. I didn't get the results I wanted so I deleted this 'DISPOSITION' field and started over. After letting the query do its job, I checked the results and this time it worked perfectly. Here is the final SQL:

UPDATE tbl_reinstatement SET DISPOSITION = IIf([VOLUNTARY_RETURN],1,IIf([WA_NTA],2,IIf([TURNED_OVER_TO],3,Null)))
WHERE DISPOSITION Is Null;

Thanks again.