Update Query Criteria - ensure source string is not zero length before update target

[davem05]

I have an update query, where by some of the data from Excel may contain zero length strings. When I run the query it overwrites any data in my access tables with the zero length strings.
I have tried various criteria in the query, but to no avail.

The query, updates from the imported table to the correct location in the target table, the criteria I have used are along the lines of
Like "*"
Is Not Null --- I now understand Null is not zero length string, but that should still check there is a value there?



I was thinking along the lines of the criteria checks there is some data in the source table before writing to the target table, is that right? Between the two above I fear I have something a bit more wrong than simply bad criteria?

[JoeM]

Can you post the SQL code of your Update query, along with the name of the field that is causing issues?

[davem05]

Can you post the SQL code of your Update query, along with the name of the field that is causing issues?

UPDATE ResultImport INNER JOIN [Test Details] ON ResultImport.labNumber = [Test Details].labNumber SET [Test Details].reqHypo = [ResultImport].[reqHypo], [Test Details].Test = [ResultImport].[resultHypo], [Test Details].dateProcessedHypo = [ResultImport].[dateProcessedHypo], [Test Details].qualityHypo = [ResultImport].[qualityHypo]
WHERE ((([Test Details].resultHypo) Not Like "*")) OR ((([Test Details].dateProcessedHypo) Not Like "*")) OR ((([Test Details].qualityHypo) Not Like "*"));


This is the query, it taks data from "ResultImport" to "Test Details", I am having trouble with "resultHypo", "dateProcessedHypo" and "qualityHypo", I have tried Like "*", Not Like"*", in case I was misunderstanding where I was aiming the reference.
I want to update Test Details fields, from those in ResultImport, if there is data in the fields in ResultImport.
Taking data from ResultImport to Test Details is no problem, it's just the zero length strings, I assume it is possible to be selective in the criteria about which data to transfer.

Admittedly I haven't really looked at sql before, this will probably now change.

Thanks a lot for looking through this.

[JoeM]

Since you are using OR conditions in your Criteria, if anyone of those fields is not blank, it will update all three. Which means, you could have two blank ones and it will still update.
If you only want it to update if all three are not blank, replace all your "OR"s with "AND"s.

Otherwise, if you want to handle each one separately, and only update the ones that are not blank, one way to do that is with three separate UPDATE queries, focusing on the one field at a time.

[davem05]

Okay, thanks for that, it was kind of what I was thinking I would have to do.

Thanks for your time!

[JoeM]

Conceivably, you might be able to do it in one query using the NZ function to set it equal to itself if the other value is blank.
Don't know if that would work, but it might be worth a try.

[davem05]

Thanks for looking at it, I ended up approaching it a little differently and have got the desired results.
Thanks

[JoeM]

Out of curiosity, what did you end up doing?