asked on

Access 2016 - Blank, Null, or empty string?

I'm running this on an Access 2016 database. (via ADO from Excel)

   sSQL = "Update qryUpdateAlwaysButWithExceptions "
    sSQL = sSQL + "Set tblManualData.RefCheck1Status = tblTracImport.RefCheck1Status "
    sSQL = sSQL + "WHERE tblManualData.RefCheck1Status <> 'Success'"

Open in new window

It should update the field RefCheck1Status when the WHERE is not 'Success'.

I'm finding this!

It works as it should when the field has 'Success' in it.
BUT the WHERE seems to return FALSE when the field has nothing in it - ie. when a new record has been created.
(I am expecting <> 'Success' to include when the field is blank)

Any help please?
Thanks

I have tried these ...
AND tblManualData.RefCheck1Status Is Null
AND tblManualData.RefCheck1Status = ''
AND tblManualData.RefCheck1Status LIKE ''

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

I'm totally confused. How you are updating fields in tables that are not referenced in your query?

YOu have not referenced tblManualData or tblTracImport in a FROM clause in the query.

hindersaliva

ASKER

Sorry. I should have mentioned that the query is already setup in Access. It joins the two tables.

Dale Fye

@hindersaliva,

I've never seen a query of any kind in Access where you can reference the individual tables that contribute to a saved query.

hindersaliva

ASKER

Hi Dale. It is possible cos it works. Except, there's the problem I'm trying to solve.

Rey Obrero (Capricorn1)

@hindersaliva,

did you try the post from http://#a41981227 ?

Rey Obrero (Capricorn1)

did you try the post from http:#a41981227 ?

hindersaliva

ASKER

Ray and Pat, OMG I'm such an idiot!!!!
I had AND instead of OR.

So, Is Null works as it should.

Thanks all.