Is there a way that Null works with 0 and >0

  • Thread starter Thread starter Jack Peyton
  • Start date Start date
J

Jack Peyton

I use Access 2000. I have a Form-FparkSpacAssignD with a recordsource that
is not updateable. On the form is a Frame that has two buttons. The Frame
values are 1 and 2. There is also a textbox [TotChkPrkSpc] that sums one of
the fields of the recordsource. There are three possible conditions for
[TotChkPrkSpc], IsNull, =0, >0. However, I cannot get Frame54=1 to work with
all of them. The IsNull(Me.[TotChkPrkSpc]) does not appear to be compatible
with Me.[TotChkPrkSpc] = 0 or Me.[TotChkPrkSpc] > 0. Is there a way that
Null works with 0 and >0? Any help will be appreciated.
Jack Peyton

Private Sub Frame54_Click()
If Me.Frame54 = 2 Then
DoCmd.Close acForm, "FParkSpacAssignD"
End If

If Me.Frame54 = 1 And Me.[TotChkPrkSpc] = 0 Then
DoCmd.GoToRecord , , acNewRec
ElseIf Me.Frame54 = 1 And Me.[TotChkPrkSpc] > 0 Then
MsgBox "Already Assigned."
ElseIf Me.Frame54 = 1 And IsNull(Me.[TotChkPrkSpc]) Then
MsgBox "Needs to be assigned"
End If
End If
 
Since you know that the control can be Null, you would first need to check
if the control is Null, since if the control is Null you can not check if it
is 0 or >0..
If it is Null, then you have one of the three cases.
Only if it is not, can you check if it is 0 or >0.to find which of the other
two cases you have.

In other words, you need to reorder your statements to check for the Null
first

If Me.Frame54 = 1 And IsNull(Me.[TotChkPrkSpc]) Then
MsgBox "Needs to be assigned"
Else
If Me.Frame54 = 1 And Me.[TotChkPrkSpc] = 0 Then
DoCmd.GoToRecord , , acNewRec
Else
If Me.Frame54 = 1 And Me.[TotChkPrkSpc] > 0 Then
MsgBox "Already Assigned."
End If
End If
End If

Ragnar
 
Ragnar,
Thank you for responding. I tried the code and it works for the "If
Me.Frame54 = 1 And Me.[TotChkPrkSpc] = 0" and "If Me.Frame54 = 1 And
Me.[TotChkPrkSpc] > 0" cases. When the case is "If Me.Frame54 = 1 And
IsNull(Me.[TotChkPrkSpc])" {the record set is empty when Me.[TotChkPrkSpc]
is null} I got the following Error Message:
Run Time Error '2427' "You entered an expression that has no value"
Any suggestions?
Again Thanks for responding.
Jack Peyton
 
Lynn,
Thanks for responding. The extra End If was an error on my part when I
posted the message.
I changed the Frame54 to command buttons as I was having problems with the
Frame working. Some additional information:
-The form is a continuous form.
-Me.[TotChkPrkSpc] in the form footer and is the sum of an expression in the
underlying query.
-The recordset is not updateable.
-The recordset is empty when Me.[TotChkPrkSpc] is null.
-The code now reads:
If Me.[TotChkPrkSpc] = 0 Then
DoCmd.GoToRecord , , acNewRec
ElseIf Me.[TotChkPrkSpc] > 0 Then
MsgBox "Already Assigned."
ElseIf IsNull(Me.[TotChkPrkSpc]) Then
MsgBox "Needs to be assigned"
End If
The form works okay when Me.[TotChkPrkSpc] = 0 or >0. When Me.[TotChkPrkSpc]
is null I get the following Error Message: Run Time Error '2427' "You
entered an expression that has no value". Any suggestions?
Thanks for your Assistance,
Jack Peyton


Lynn Trapp said:
The only reason i can see that your code might not work as written is that
you appear to have an extra End If at the end of the procedure.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Jack Peyton said:
I use Access 2000. I have a Form-FparkSpacAssignD with a recordsource that
is not updateable. On the form is a Frame that has two buttons. The Frame
values are 1 and 2. There is also a textbox [TotChkPrkSpc] that sums one
of
the fields of the recordsource. There are three possible conditions for
[TotChkPrkSpc], IsNull, =0, >0. However, I cannot get Frame54=1 to work
with
all of them. The IsNull(Me.[TotChkPrkSpc]) does not appear to be
compatible
with Me.[TotChkPrkSpc] = 0 or Me.[TotChkPrkSpc] > 0. Is there a way that
Null works with 0 and >0? Any help will be appreciated.
Jack Peyton

Private Sub Frame54_Click()
If Me.Frame54 = 2 Then
DoCmd.Close acForm, "FParkSpacAssignD"
End If

If Me.Frame54 = 1 And Me.[TotChkPrkSpc] = 0 Then
DoCmd.GoToRecord , , acNewRec
ElseIf Me.Frame54 = 1 And Me.[TotChkPrkSpc] > 0 Then
MsgBox "Already Assigned."
ElseIf Me.Frame54 = 1 And IsNull(Me.[TotChkPrkSpc]) Then
MsgBox "Needs to be assigned"
End If
End If
Click to expand...
Click to expand...
 
Well, I don't think the problem is with your IF statement, but with your
attempt to have a Null value in your calculated field.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Jack Peyton said:
Lynn,
Thanks for responding. The extra End If was an error on my part when I
posted the message.
I changed the Frame54 to command buttons as I was having problems with the
Frame working. Some additional information:
-The form is a continuous form.
-Me.[TotChkPrkSpc] in the form footer and is the sum of an expression in
the
underlying query.
-The recordset is not updateable.
-The recordset is empty when Me.[TotChkPrkSpc] is null.
-The code now reads:
If Me.[TotChkPrkSpc] = 0 Then
DoCmd.GoToRecord , , acNewRec
ElseIf Me.[TotChkPrkSpc] > 0 Then
MsgBox "Already Assigned."
ElseIf IsNull(Me.[TotChkPrkSpc]) Then
MsgBox "Needs to be assigned"
End If
The form works okay when Me.[TotChkPrkSpc] = 0 or >0. When
Me.[TotChkPrkSpc]
is null I get the following Error Message: Run Time Error '2427' "You
entered an expression that has no value". Any suggestions?
Thanks for your Assistance,
Jack Peyton


Lynn Trapp said:
The only reason i can see that your code might not work as written is
that
you appear to have an extra End If at the end of the procedure.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Jack Peyton said:
I use Access 2000. I have a Form-FparkSpacAssignD with a recordsource that
is not updateable. On the form is a Frame that has two buttons. The Frame
values are 1 and 2. There is also a textbox [TotChkPrkSpc] that sums
one
of
the fields of the recordsource. There are three possible conditions for
[TotChkPrkSpc], IsNull, =0, >0. However, I cannot get Frame54=1 to work
with
all of them. The IsNull(Me.[TotChkPrkSpc]) does not appear to be
compatible
with Me.[TotChkPrkSpc] = 0 or Me.[TotChkPrkSpc] > 0. Is there a way that
Null works with 0 and >0? Any help will be appreciated.
Jack Peyton

Private Sub Frame54_Click()
If Me.Frame54 = 2 Then
DoCmd.Close acForm, "FParkSpacAssignD"
End If

If Me.Frame54 = 1 And Me.[TotChkPrkSpc] = 0 Then
DoCmd.GoToRecord , , acNewRec
ElseIf Me.Frame54 = 1 And Me.[TotChkPrkSpc] > 0 Then
MsgBox "Already Assigned."
ElseIf Me.Frame54 = 1 And IsNull(Me.[TotChkPrkSpc]) Then
MsgBox "Needs to be assigned"
End If
End If
Click to expand...
Click to expand...
Click to expand...
 
Lynn,
Thanks for looking at this. I think I need to go back to the drawing board.
Jack Peyton
Lynn Trapp said:
Well, I don't think the problem is with your IF statement, but with your
attempt to have a Null value in your calculated field.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Jack Peyton said:
Lynn,
Thanks for responding. The extra End If was an error on my part when I
posted the message.
I changed the Frame54 to command buttons as I was having problems with the
Frame working. Some additional information:
-The form is a continuous form.
-Me.[TotChkPrkSpc] in the form footer and is the sum of an expression in
the
underlying query.
-The recordset is not updateable.
-The recordset is empty when Me.[TotChkPrkSpc] is null.
-The code now reads:
If Me.[TotChkPrkSpc] = 0 Then
DoCmd.GoToRecord , , acNewRec
ElseIf Me.[TotChkPrkSpc] > 0 Then
MsgBox "Already Assigned."
ElseIf IsNull(Me.[TotChkPrkSpc]) Then
MsgBox "Needs to be assigned"
End If
The form works okay when Me.[TotChkPrkSpc] = 0 or >0. When
Me.[TotChkPrkSpc]
is null I get the following Error Message: Run Time Error '2427' "You
entered an expression that has no value". Any suggestions?
Thanks for your Assistance,
Jack Peyton


Lynn Trapp said:
The only reason i can see that your code might not work as written is
that
you appear to have an extra End If at the end of the procedure.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



I use Access 2000. I have a Form-FparkSpacAssignD with a recordsource that
is not updateable. On the form is a Frame that has two buttons. The Frame
values are 1 and 2. There is also a textbox [TotChkPrkSpc] that sums
one
of
the fields of the recordsource. There are three possible conditions for
[TotChkPrkSpc], IsNull, =0, >0. However, I cannot get Frame54=1 to work
with
all of them. The IsNull(Me.[TotChkPrkSpc]) does not appear to be
compatible
with Me.[TotChkPrkSpc] = 0 or Me.[TotChkPrkSpc] > 0. Is there a way that
Null works with 0 and >0? Any help will be appreciated.
Jack Peyton

Private Sub Frame54_Click()
If Me.Frame54 = 2 Then
DoCmd.Close acForm, "FParkSpacAssignD"
End If

If Me.Frame54 = 1 And Me.[TotChkPrkSpc] = 0 Then
DoCmd.GoToRecord , , acNewRec
ElseIf Me.Frame54 = 1 And Me.[TotChkPrkSpc] > 0 Then
MsgBox "Already Assigned."
ElseIf Me.Frame54 = 1 And IsNull(Me.[TotChkPrkSpc]) Then
MsgBox "Needs to be assigned"
End If
End If
Click to expand...
Click to expand...
Click to expand...
 
Back
Top