How do I resolve a null or blank field where the length is greater than zero

Use the Trim command to trim out the blank chars.
If that doesn't work just do a Replace(YourRow,JustCopy1Char,"")

Is the SQL Server field defined as char or some version of varchar?

You might need to use the Trim() function to remove any trailing spaces.

I agree with the others that Trim would be my first attempt to remedy the issue.

If that doesn't work, then I use a function like the one below, to figure out what is being passed and then use a replace function to eliminate the issue.

'Convert a string to its Ascii character value
Public Function ConvertTextToAsc(sInput As String, Optional sDelim As String = ",")
    For i = 1 To Len(sInput)
        ConvertTextToAsc = ConvertTextToAsc & Asc(Mid(sInput, i, 1)) & sDelim
    Next i
    If Len(ConvertTextToAsc) > 0 Then ConvertTextToAsc = Left(ConvertTextToAsc, Len(ConvertTextToAsc) - Len(sDelim))
End Function

Select allOpen in new window

If you set the Access table definition for text fields to Allow ZLS = no, the zero length string will be dropped and the field will be appended as null.

Personally, I always set the Allow ZLS to no because I want all fields regardless of type to be null if they are empty.  I don't want to have to test for or distinguish ZLS from null.  Also, I don't want to have to figure out the data type of every fields as I validate it since ZLS is only ever valid for text fields.  It is never valid for numeric fields (including dates which are stored as double precision numbers).

The first thing I tried before I posted my question was to set the table definition for this field to No zero length string allowed. The second thing I tried since the first had no effect was Trim. Neither resolved the issue.

The SQL Data type for this field is vChar(MAX). It's customer data so I can't do much with that but I believe the Access equivalent is Memo

I will try your code today Daniel.

First of all I think we are all assuming, from the way you've written your question that you are dealing with a SQL Server table linked to Access and running an Access query on it to insert into an Access table.

Try this on your field to help determine what works:
SELECT LEN(TRIM(NZ([FieldName],""))
If it indicates zero length, then this should put a null in your field if empty:
SELECT IIF(LEN(TRIM(NZ([FieldName],""))=0,Null,TRIM([FieldName]))

If your "invisible" characters are not spaces, then TRIM() won't work.  You'll need to find out what characters are "invisible" and fix them with a REPLACE() function or some other fix.

Ok. This saga keeps getting weirder and weirder.

I opened the table containing the Reminder field values and selected a "blank" record. As I drag my cursor left to right over that field, the text becomes visible similar to the other records. It disappears once I remove my cursor from the field.


???

The first thing I tried before I posted my question was to set the table definition for this field to No zero length string allowed.

 My suggestion was to set zls allowed to NO.

It is set to NO

And what happens when you append the data?  You should see an error message but as long as the target table is Jet/ACE, the row will append with the ZLS converted to null.  The Trim() solution should also work.  What happens when you use that method?

I'm looking at the appended table and no, it does not append as null because we obviously now know the field is not null or blank, nor does a Trim solution work - in the sense of making it null. The appended field has an invisible value. Nothing I've ever run into before.

That means that the contents of the field are some non printable character.  Do you have a hex editor you can use to look at the data.  Export some records to a .csv file and then look at the data to see what the actual ASCII values are.

It looks like you are not dealing with spaces.

Try this on your field to help determine what is in the field:
SELECT ASC(LEFT([FieldName],1))
It should tell you what character is in the 1st position

What is the data type of the SQL Server table field and what is Access showing with the linked table in design view?  Something is populating the field.  I've seen this before with things like BStr() which puts trailing characters to "pad" the field with characters out to the field size.

The ASC number returned is 13. The source SQL field type is VChar(MAX)

Have you tried the Replace ?
Just copy paste a row of blank data and examine what chars you have...it should be 1 or 2 most
so you could do

S = Replace(S,Chr(X1),"")
S = Replace(S,Chr(X2),"")

Select allOpen in new window

X1,X2 the chars you will find by analyzing the row

13 is the code for a carriage return.

I'm not sure I'm following your assumption of only 1 or 2 characters. As I mentioned earlier, There's an entire sentence of characters. Easily over 100. Just not displayed.

Odd.  In SQL Server, there is an nvarchar(), a varchar(), and a char() data type, but no "vchar".  Is the linked table a SQL Server table and "vchar" is the data type of the linked table field when viewed in design view?  ALIENS!

It's possible you could have multiple "filler" characters.  However, hard to say if they are all "13"s (Carriage Return).  I wonder if you have any "10"s in there (Line Feeds).  "10"s and "13"s usually go together to form what you get when you hit the "Return" or "Enter" key on your keyboard.  It is possible to have a 10 & 13 side-by-side in a database table field without causing a break to another line.  Have you tried copy-paste the entry into an Excel cell and see what it says?

It might be we need to analyze the whole line just to see what's in there.  It may give us a clue where it is coming from.  "vchar" doesn't sound like anything SQL Server or Access, and Google brings up nothing.  I'd like to solve that mystery first....

I apologize - mistyped. The SQL datatype is varchar(MAX) and the content as it appears in Excel is:

ALLERGY ALERT**CLIENT IS ALLERGIC TO SEAFOOD/SHELLFISH. SHE SWELLS UP AND HAS TROUBLE BREATHING.

Looks like you have an entry that might have a carriage return and/or line feed character at the beginning, so you don't see anything on the 1st line.  This commonly occurs when a process adds notes to the end of a field that contains other notes.  However, if there is only one note and the leading carriage return and/or line feed is not removed, you can get what you seem to be getting.

Try checking to see what characters are present in the beginning until you get to the first visible character, then you can run a Replace() or Mid() function on the beginning characters to get rid of them.

there are no visible characters

How about clicking in the row and copy - paste

there are no visible characters

The carriage return is not a visible character.  That is why you can't see it.

Thanks everyone!

Thanks, SCL.  By the way, I've run into this situation several times, especially when importing Excel files into Access tables.

Oh, how I wish Excel users would also become more knowledgable on database data design as well as spreadsheets.  Too many "Excel Nerds" write data that's more like a report format than a database -compliant data table format.  Screws too many things up and requires the use of a lot of formulas and such to do what you could do with a simple SQL query, even in Excel.  Too many Excel "Experts" are way too familiar with the old way of doing things and not up to speed on all the vast data handling improvements in Excel.