Another string manipulation question

[Afrosheen via AccessMonster.com]

I have this code:

If Me.Text2 < Me.EndDate Or Len(Nz(Me!Text2, "")) = 0 Then
msgbox "testing"
endif

What i'm trying to do is to check the string text2 for two variables.
1 is it less than the EndDate
2 is it empty or does it have something in it.

I keep getting the error invalid use of null.

I'm not sure the code is correct.

I thank you for checking this and reading this.

 

[Mark Andrews]

Lots of ways you could do it. Error is on the < compare when one side of
the equation is a null
How about

if (Nz(Me.Text2, Cdate("1/1/1810")) < Me.EndDate) then
msgbox "testing"
end if

or
'first check for empty date (in start date field)
' then check for empty date (in end date field)
'then check for end date >= startdate (or whatever you are trying to do?)

If using in BeforeUpdate I usually do individual checks and have different
messages to the user.

Hope that made sense,
Mark

 

[Afrosheen via AccessMonster.com]

Hi Mark and thanks for getting back to me so fast.
On the

If Me.Text2 < Me.EndDate Then

works. Its after that that it messes up.

I also had to put it OnExit in order to check and if not correct it stays
there.
I'm trying to do two checks before it leaves the field and if not then give
the message..

Correct me if I'm wrong.
if (Nz(Me.Text2, = 0 or < Me.EndDate) then

Lots of ways you could do it. Error is on the < compare when one side of
the equation is a null
How about

if (Nz(Me.Text2, Cdate("1/1/1810")) < Me.EndDate) then
msgbox "testing"
end if

or
'first check for empty date (in start date field)
' then check for empty date (in end date field)
'then check for end date >= startdate (or whatever you are trying to do?)

If using in BeforeUpdate I usually do individual checks and have different
messages to the user.

Hope that made sense,
Mark

I have this code:

[quoted text clipped - 11 lines]

I thank you for checking this and reading this.

 

[Afrosheen via AccessMonster.com]

Mark, I tried your code that I rewrote and no it didn't work.
Text2 is formated/masked for a date. I don't know if that makes a difference
or not.

Hi Mark and thanks for getting back to me so fast.
On the

If Me.Text2 < Me.EndDate Then

works. Its after that that it messes up.

I also had to put it OnExit in order to check and if not correct it stays
there.
I'm trying to do two checks before it leaves the field and if not then give
the message..

Correct me if I'm wrong.
if (Nz(Me.Text2, = 0 or < Me.EndDate) then

Lots of ways you could do it. Error is on the < compare when one side of
the equation is a null

[quoted text clipped - 20 lines]

 

[Paul Shapiro]

You have to account for everything which might occur. Try something like:

Dim isValidDate as Boolean
If IsDate(Me.Text2.Value) and Not IsNull(Me.EndDate) Then
isValidDate = (CDate(Me.Text2.Value) < Me.EndDate)
End If

Dim isEmptyString as Boolean
isEmptyString = (Len(nz(Me.Text2.Value, "")) = 0)

Dim isValid as Boolean
isValid = isValidDate Or isEmptyString

If you're having problems, put a breakpoint on the first line of code and
check each piece of the expression in the VBA editor's Immediate window.

You could put it all into a function like:
Private Function isValidText(strText As Variant, datEndDate As Date) As
Boolean
Dim isValidDate As Boolean
If IsDate(strText) And Not IsNull(datEndDate) Then
isValidDate = (CDate(strText) < datEndDate)
End If

Dim isEmptyString As Boolean
isEmptyString = (Len(Nz(strText, "")) = 0)

isValidText = isValidDate Or isEmptyString
End Function

which you would call as isValidText(Me.Text2.Value, Me.EndDate)

Mark, I tried your code that I rewrote and no it didn't work.
Text2 is formated/masked for a date. I don't know if that makes a
difference
or not.

Hi Mark and thanks for getting back to me so fast.
On the

If Me.Text2 < Me.EndDate Then

works. Its after that that it messes up.

I also had to put it OnExit in order to check and if not correct it stays
there.
I'm trying to do two checks before it leaves the field and if not then
give
the message..

Correct me if I'm wrong.
if (Nz(Me.Text2, = 0 or < Me.EndDate) then

Lots of ways you could do it. Error is on the < compare when one side of
the equation is a null

[quoted text clipped - 20 lines]

 

[Afrosheen via AccessMonster.com]

Thanks for your reply. Here's what I did. I copied and pasted the "Function"
and tried calling it with:

isValidText(Me.Text2.Value, Me.EndDate).

The thing is that when I put it in the OnExit control it showed up in red and
a Syntax error.

You have to account for everything which might occur. Try something like:

Dim isValidDate as Boolean
If IsDate(Me.Text2.Value) and Not IsNull(Me.EndDate) Then
isValidDate = (CDate(Me.Text2.Value) < Me.EndDate)
End If

Dim isEmptyString as Boolean
isEmptyString = (Len(nz(Me.Text2.Value, "")) = 0)

Dim isValid as Boolean
isValid = isValidDate Or isEmptyString

If you're having problems, put a breakpoint on the first line of code and
check each piece of the expression in the VBA editor's Immediate window.

You could put it all into a function like:
Private Function isValidText(strText As Variant, datEndDate As Date) As
Boolean
Dim isValidDate As Boolean
If IsDate(strText) And Not IsNull(datEndDate) Then
isValidDate = (CDate(strText) < datEndDate)
End If

Dim isEmptyString As Boolean
isEmptyString = (Len(Nz(strText, "")) = 0)

isValidText = isValidDate Or isEmptyString
End Function

which you would call as isValidText(Me.Text2.Value, Me.EndDate)

Mark, I tried your code that I rewrote and no it didn't work.
Text2 is formated/masked for a date. I don't know if that makes a

[quoted text clipped - 20 lines]

the equation is a null
[quoted text clipped - 20 lines]

 

[Afrosheen via AccessMonster.com]

Hello Paul and Mark. First of all I want to thank you for your help. After
further investigation I found the problem. It was the way I had the code. I
had to change it to this:

20 If Me!Text2 < Me!EndDate Then
30 MsgBox "Your End Date is to Low " + Str(Me.Text2) + ". It must be
higher. Please verify and correct."
40 Cancel = True
50 Text2 = Null
60 Me.cmdExecute.Enabled = False
70 Me.Command13.SetFocus
80 ElseIf IsNull(Me![Text2].Value) Then
90 Call MsgBox("System need an End Date.", vbExclamation, Application.
Name)
100 Cancel = True
110 Me.Command13.SetFocus
120 Else
130 Me.cmdExecute.Enabled = True
140 Me.cmdExecute.SetFocus
150 End If

Thanks for your reply. Here's what I did. I copied and pasted the "Function"
and tried calling it with:

isValidText(Me.Text2.Value, Me.EndDate).

The thing is that when I put it in the OnExit control it showed up in red and
a Syntax error.

You have to account for everything which might occur. Try something like:

[quoted text clipped - 33 lines]

the equation is a null
[quoted text clipped - 20 lines]

 

[John W. Vinson]

Thanks for your reply. Here's what I did. I copied and pasted the "Function"
and tried calling it with:

isValidText(Me.Text2.Value, Me.EndDate).

The thing is that when I put it in the OnExit control it showed up in red and
a Syntax error.

You'll need to create an Event Procedure and call it from that, or else put

=isValidText(Form.Text2, Form.EndDate)

in the On Exit event.