need to check if field is empty

[Krechting]

Hi ALl,

I have a code that checks if the documents in a hyperlink field are still
where they should be. I use fileexist().
First I want to filter out all the hyperlink fields that are empty.
I open a recordset and browse through all the fields to check for the word
file. I cannot filter out the empty fields.

I have tried: If Len(.Fields("Link") <> 0 then
and: If Not IsNull(.Fields("Link")) then
and: If .Fields("link").Value <> 0 then

but nothing works fine. Somehow when a hyperlink field has been filled and
emptied again something remains there, so it is not NULL.

Can anyone help me out?

Regards

Marco

[Keith Wilby]

m.*********@chello.nl (Krechting) wrote:

Somehow when a hyperlink field has been filled and
emptied again something remains there, so it is not NULL.

Can anyone help me out?

Have you tried

If .Fields("Link") <> "" then

?

The field may contain a zero-length string.

Regards,
Keith.
www.keithwilby.com

[Eric Schittlipz]

"Krechting" <m.*********@chello.nl> wrote in message
news:a7**************************@posting.google.c om...

Hi ALl,

I have a code that checks if the documents in a hyperlink field are still
where they should be. I use fileexist().
First I want to filter out all the hyperlink fields that are empty.
I open a recordset and browse through all the fields to check for the word
file. I cannot filter out the empty fields.

I have tried: If Len(.Fields("Link") <> 0 then
and: If Not IsNull(.Fields("Link")) then
and: If .Fields("link").Value <> 0 then

but nothing works fine. Somehow when a hyperlink field has been filled and
emptied again something remains there, so it is not NULL.

Can anyone help me out?

Regards

Marco

There are a number of possible values which would look blank on the screen.
A null value, a zero-length string and, harder to accidentally enter via a
form, one or more spaces. To test for any of these in one line, try:

If Len(Trim(Nz(.Fields("Link"), "")))=0

Firstly any null values are turned to zero-length strings, and then the
spaces are trimmed so any of the three above-mentioned values all become
zero-length strings and so you can check the length.

By the way, if the difference between null values and zero-length strings is
of no use to you in this application, you could change the design of the
table so this field cannot contain a zero-length string.

[Marco Krechting]

Tnx,

This is working almost perfectly.
There is only one minor issue, the code does not work on the first record.
It'll say the file was not found although the field is empty.

Marco

"Eric Schittlipz" <er**@schittlipz.com> wrote in message
news:cq**********@titan.btinternet.com...

"Krechting" <m.*********@chello.nl> wrote in message
news:a7**************************@posting.google.c om...

Hi ALl,

I have a code that checks if the documents in a hyperlink field are still
where they should be. I use fileexist().
First I want to filter out all the hyperlink fields that are empty.
I open a recordset and browse through all the fields to check for the
word
file. I cannot filter out the empty fields.

I have tried: If Len(.Fields("Link") <> 0 then
and: If Not IsNull(.Fields("Link")) then
and: If .Fields("link").Value <> 0 then

but nothing works fine. Somehow when a hyperlink field has been filled
and
emptied again something remains there, so it is not NULL.

Can anyone help me out?

Regards

Marco

There are a number of possible values which would look blank on the
screen. A null value, a zero-length string and, harder to accidentally
enter via a form, one or more spaces. To test for any of these in one
line, try:

If Len(Trim(Nz(.Fields("Link"), "")))=0

Firstly any null values are turned to zero-length strings, and then the
spaces are trimmed so any of the three above-mentioned values all become
zero-length strings and so you can check the length.

By the way, if the difference between null values and zero-length strings
is of no use to you in this application, you could change the design of
the table so this field cannot contain a zero-length string.

[Douglas J. Steele]

I think we need to see more of your code in order to track that error down.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Marco Krechting" <ma************@zonnet.nl> wrote in message
news:c0***************************@news1.zonnet.nl ...

Tnx,

This is working almost perfectly.
There is only one minor issue, the code does not work on the first record.
It'll say the file was not found although the field is empty.

Marco

"Eric Schittlipz" <er**@schittlipz.com> wrote in message
news:cq**********@titan.btinternet.com...

"Krechting" <m.*********@chello.nl> wrote in message
news:a7**************************@posting.google.c om...

Hi ALl,

I have a code that checks if the documents in a hyperlink field are still where they should be. I use fileexist().
First I want to filter out all the hyperlink fields that are empty.
I open a recordset and browse through all the fields to check for the
word
file. I cannot filter out the empty fields.

I have tried: If Len(.Fields("Link") <> 0 then
and: If Not IsNull(.Fields("Link")) then
and: If .Fields("link").Value <> 0 then

but nothing works fine. Somehow when a hyperlink field has been filled
and
emptied again something remains there, so it is not NULL.

Can anyone help me out?

Regards

Marco

There are a number of possible values which would look blank on the
screen. A null value, a zero-length string and, harder to accidentally
enter via a form, one or more spaces. To test for any of these in one
line, try:

If Len(Trim(Nz(.Fields("Link"), "")))=0

Firstly any null values are turned to zero-length strings, and then the
spaces are trimmed so any of the three above-mentioned values all become
zero-length strings and so you can check the length.

By the way, if the difference between null values and zero-length strings is of no use to you in this application, you could change the design of
the table so this field cannot contain a zero-length string.

[Eric Schittlipz]

"Marco Krechting" <ma************@zonnet.nl> wrote in message
news:c0***************************@news1.zonnet.nl ...

Tnx,

This is working almost perfectly.
There is only one minor issue, the code does not work on the first record.
It'll say the file was not found although the field is empty.

Marco

When it does that, what is the length reported by the expression
Len(Trim(Nz(.Fields("Link"), "")))
I expect it is not zero, but for some reason the field looks blank. Perhaps
you have vbCrLf as the first characters so that you have two lines: a blank
one and one under it which you can't see. Make sure it really is null by
running an update query. If you're still having trouble, post you code like
Doug suggests.