How to populate blanks in a querry

[clintthorn]

I am trying to create a query to summarize code data (e.g., group ch0, ch1, ch2, all under ch). There are some invalid and blank codes that I want to identify as NA. I created a table that takes all the codes and classifies them into their respective groups. However, I cannot classify the blank fields as anything.

Does anyone have any ideas on how to get around this?

 

[jzwp22]

I would run an update query to change the ch field to NA for any invalid or blank codes. Moving forward, I would put a combo box on your form so that your users can only select valid codes (using the table you set up)

An update query may go like this (you'll have to substitute your own table and field names)

Update yourtable set ch="NA"
WHERE ch not in (SELECT code FROM yourvalidcodetable) OR isNull(ch)

Out of curiosity, are the records that contain the blank or invalid codes of any value? If not, then just delete them. If they are good records then update the codes to something that is valid rather than NA.

 

[clintthorn]

I didn't even think of deleting them. Leave it to me to try and complicate things.

Do you know a way that I could delete all the rows that have blanks in a specified field? I import data daily into access, and there are 50k+ rows a day.

I can sort the column to have a the blanks together and then delete that section. Just wondering if there is an easier way.

Thanks,

 

[jzwp22]

You can run a delete query. I would recommend making a backup of your database just in case the delete query does not go as planned. The WHERE clause will be similar to that in the update query I provided previously. The following delete query will delete any records that do not have valid codes or where there is no code at all:

DELETE tblData.ch
FROM tblData
WHERE tblData.ch Not In (SELECT txtCodeID From tblCodes)OR IsNull([ch])

When you say the records that have blanks, do you mean there is nothing in the field (this is called a NULL) or do you mean that there is a blank space (an actual character that you cannot see)?

If you just want to get rid of just those records with no data in the ch field (ch field is null) then the query would be as follows

DELETE tblData.ch
FROM tblData
WHERE IsNull([ch])

or


DELETE tblData.ch
FROM tblData
WHERE [ch] is null