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

re: need to check if field is empty

m.krechting@chello.nl (Krechting) wrote:
[color=blue]
> Somehow when a hyperlink field has been filled and
> emptied again something remains there, so it is not NULL.
>
> Can anyone help me out?[/color]

Have you tried

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

?

The field may contain a zero-length string.

Regards,
Keith.
www.keithwilby.com

re: need to check if field is empty

"Krechting" <m.krechting@chello.nl> wrote in message
news:a71c776d.0412200038.5cd3ab93@posting.google.c om...[color=blue]
> 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[/color]


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.

re: need to check if field is empty

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" <eric@schittlipz.com> wrote in message
news:cq69ns$8g0$1@titan.btinternet.com...[color=blue]
>
> "Krechting" <m.krechting@chello.nl> wrote in message
> news:a71c776d.0412200038.5cd3ab93@posting.google.c om...[color=green]
>> 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[/color]
>
>
> 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.
>
>
>
>
>[/color]

re: need to check if field is empty

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" <marcokrechting@zonnet.nl> wrote in message
news:c0267$41c6baa2$3e3af2ad$12318@news1.zonnet.nl ...[color=blue]
> 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" <eric@schittlipz.com> wrote in message
> news:cq69ns$8g0$1@titan.btinternet.com...[color=green]
> >
> > "Krechting" <m.krechting@chello.nl> wrote in message
> > news:a71c776d.0412200038.5cd3ab93@posting.google.c om...[color=darkred]
> >> Hi ALl,
> >>
> >> I have a code that checks if the documents in a hyperlink field are[/color][/color][/color]
still[color=blue][color=green][color=darkred]
> >> 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[/color]
> >
> >
> > 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[/color][/color]
strings[color=blue][color=green]
> > 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.
> >
> >
> >
> >
> >[/color]
>
>[/color]

re: need to check if field is empty

"Marco Krechting" <marcokrechting@zonnet.nl> wrote in message
news:c0267$41c6baa2$3e3af2ad$12318@news1.zonnet.nl ...[color=blue]
> 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
>[/color]

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.