469,315 Members | 1,959 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,315 developers. It's quick & easy.

need to check if field is empty

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
Nov 13 '05 #1
5 23566
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
Nov 13 '05 #2

"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.

Nov 13 '05 #3
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.


Nov 13 '05 #4
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.



Nov 13 '05 #5

"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.

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Paul Telco | last post: by
30 posts views Thread by S. van Beek | last post: by
1 post views Thread by Oleg Ogurok | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.