If Texbox1.value = "" does not work in access

[pedie]

Hi, i want to make sure if texbox field is filed out...
If Texbox1.value = "" does not work in access
this is a no field...
what are the ways to trap this and alert user to fil out the field...?


And one more small thing...I have email id in record like pedie@live.com when the email is trigered it is showing as '[EMAIL="pedie@live.com"]pedie@live.com'[/EMAIL] and wont triger the email...i am setting this records as string....

is that the reason why?

 

[pedie]

Mycurrent Code:

Dim xEmailID As String
Dim email_Reciv As String
Dim Re_Contac As String
Dim InfoRst As DAO.Recordset
Set InfoRst = CurrentDb.OpenRecordset("Info", dbOpenDynaset)
spocname = InfoRst!S_Name
spoccontantno = InfoRst!ContactNo
xEmailID = ""
InfoRst.MoveFirst
Do Until InfoRst.EOF
If InfoRst!Email_ID <> "" Then
xEmailID = xEmailID & InfoRst!Email_ID & ";"
End If
InfoRst.MoveNext
Loop
InfoRst.Close
Set InfoRst = Nothing
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
Dim approvedDeclinedv As String
Dim dlnamev As String
.To = "pedie.me@live.com"
.CC = xEmailID
.BCC = ""
.Subject = "Test Subject"
.Body = "Hi Test"
If Me.Att1.Caption <> "Att" Then
.Attachments.Add Me.Att1.Caption
End If
If Me.Att2.Caption <> "Att" Then
.Attachments.Add Me.Att2.Caption
End If
If Me.Att3.Caption <> "Att" Then
.Attachments.Add Me.Att3.Caption
End If
If Me.Att4.Caption <> "Att" Then
.Attachments.Add Me.Att4.Caption
End If
If Me.Att5.Caption <> "Att" Then
.Attachments.Add Me.Att5.Caption
End If
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing

 

[Montez659]

Don' know much about your second question, but the answer to the first would be to use the IsNull function, like this:

If IsNull(Texbox1.value) Then...

Not exactly sure what you meant by this:

this is a no field...

Was that supposed to mean a yes/no field? If that is correct, then there might not be a null value, but either true or false.

 

[pedie]

Montez659, thanks ....I mean if there is not data entered in textbox field, apologies for typp error

 

[boblarson]

The best way is to test for Null AND for empty strings by using:

If you want to identify nulls and/or empty strings and then do something:

If Len(Me.TextBoxNameHere & vbNullString) = 0 Then

If you want to EXCLUDE nulls and/or empty strings and then do something:

If Len(Me.TextBoxNameHere & vbNullString) > 0 Then

 

[pedie]

Bob, that works perfect!!!
Thanks alot!

about the second inquiry....do you have any idea why the record when refered to as string is showing up as 'pedie@live.com' with small quotes on sides.... i want to show up as it is entered in the record.


Thanks again,

 

[boblarson]

do you have any idea why the record when refered to as string is showing up as 'pedie@live.com' with small quotes on sides.... i want to show up as it is entered in the record.

Yeah, you probably have the field's datatype set to Hyperlink don't you? It should be TEXT instead. You can always use it as a hyperlink elsewhere by some formatting and property adjustments but if it is in the table as such then it's "TEXT" value is email compliant which has the single quotes because that is the DISPLAYED TEXT as opposed to the actual hyperlink.

 

[pedie]

Bob, thanks for the quick reply...currently it is formated text, do i change that to hyperlink?

 

[Montez659]

If you want to identify nulls and/or empty strings and then do something:

If Len(Me.TextBoxNameHere & vbNullString) = 0 Then

You know Bob, I have seen that snippet a million times but i never used it because I didn't understand the reasoning/logic behind it. Tell me if I am wrong, but does the vbNullString essentially do what an NZ() does - what I mean is that if there is a Null value then it reads it as a length of 0?

Also, another question regarding empty strings: How does one come about? A field would be null until someone wrote in it, at which point it wouldn't be. If they simply delete or backspace the field, does it not return to Null, or is it recognized as ""?

Just wanting to understand how the computer thinks!

 

[xenou]

As far as I know these will all work the same:
Len(IIF(IsNull(Me.Textbox1.Value),"",Me.TextBox1.Value))>0
Len(NZ(Me.Textbox1.Value, "")) = 0
Len(NZ(Me.Textbox1.Value)) = 0
Len(Me.Textbox1.Value & "") = 0
Len(Me.Textbox1.Value & vbNullString) = 0


If you just want to know if the textbox is empty you could also employ:
If NZ(Me.TextBox1.Value) = "" Then ...
(empty strings are the default return for NZ() when the first argument is Null).

NZ and vbNullString aren't precisely the same thing (one's a function and the other isn't ). But it happens that these two expressions work out the same in Access:
Len("" & "")
Len(Null & "")
I.e., they both evaluate to zero, which is the point. However, as far as I know vbNullString and "" are the same thing. In practice, I've always used them interchangeably in any case. Maybe at the level of bits and bytes there is some difference ... I'd be curious to know if that's so.

So pick your poison (I typically use one of the last three myself). My experience is that empty textboxes can start out Null, but if someone types in them and deletes them they are then empty strings - but I've never tried to confirm that for sure. Maybe if you typed in them and hit Escape it would return to Null. I use the same Len() test to handle Null or Empty Strings irrespective of the difference - so its a handy way to handle this.

Bob may have a better understanding - HTH.

Note that personally I thought it a little odd, when I first encountered this, to find that Null & "" comes out to "" rather than Null. I never really thought to do such a thing until I saw it. But I think in general it's a behavior that makes sense - though its best to keep in mind that all mathematical operations (sums, factors, etc.) that involve a Null will return Null - never zero or another number.