No value is making me nuts!!!!

[Sullaway]

I have a code based on whether we have a zip code on a customer or not. I have trapped for Null and "", which works perfect but only if the zip code has never been entered. Here's where my problem comes in. If someone deletes a zip code out of the customers record and leaves the field blank, I just can't seem to trap (whatever value this has become). I have tried Nz, IsNull, Len, "" but just can't seem to find any combination that will trap it.

Could someone please take a little time and give me a hand with this?

Thanks ahead of time,
Shane

 

[tlbroadbent]

What do you mean by trap the value? Are you running a query or doing something in VBA code?

You should be able to query the value in a Where clause.

Select * From tbl Where Trim(NZ(ZipCode,"&quot)=""

Or in the Select clause.

Select col1, col2, Trim(NZ(ZipCode)) As ZipCd, col4, ...
From tbl

In code you can use DLookUp.

sZipCd = Trim(NZ(Dlookup("[ZipCode]", "tbl", "NZ([ZipCode]))="&quot

Or check the value in a record set.

sZipCd = Trim(rs!ZipCode & "&quot Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Sullaway]

Terry,

Thanks for the help. I am trapping in code, to be more specific. Wondering if I could bother you a little further and ask you to explain why after a field has had a value entered then deleted, that IsNull or "" none of those work. Apparently there's some type of value. If you could take a little time to help me understand I would really appreciate it.

Thanks again,
Shane

 

[cwillsh]

You might also check to see if you are saving the formatting. There may be a '-'??
If your format is numeric, you may need to look for 0.
Just guessing!

 

[Sullaway]

Hey CWillsh,

No, I'm not saving any type of a format and the wierd thing is nothing shows in the text box (at least not that you can see on the screen). The Trim work around does work but I was just wondering if something happens behind the scene's when a user deletes the contents of a box that causes the box to no longer be Null or an empty string.

This now has become a curious question cause I now know how to make it work but I don't know why it's working.

Thanks for your time and have a good Sunday,
Shane

 

[amazing66]

why don't you check the contents of the database field via a message box. (Might this be a refresh issue?? - just guessing also!)

 

[JoeMiller]

You could also check the length of the string in the field:

If Len(MyField)<1 Then
MsgBox &quot;Fill it in sucka!&quot;
End If

Joe Miller
joe.miller@flotech.net