Can't have a null value if another field has text.

[DoveArrow]

I have tried a few different things to try and get this to work, but I
can't seem to figure it out. Basically, I have a form with some text
boxes linked to fields on a table, and I want one of two things to
happen (the first one would be preferred, but if that's not possible,
the second would be just as good):

1) If Field 1 is updated to a Null value and Field 2 has text in it,
move the text from Field 2 to Field 1 and change Field 2 to a Null
value.
2) If Field 1 is updated to a Null value and Field 2 has text in it, a
message box pops up that essentially says "You can't do that."

Is what I'm suggesting possible? I imagine it could be done with an
If...Then statement of some kind, but I can't figure it out. Here's
what I've tried.

If IsNull(Me![Field1]) And IsNotNull(Me![Field2]) :::I've also tried
Me![Field2] <> Null::: Then
MsgBox "You can't do that."
:::I've also tried the following:::
Me![Field1] = Me![Field2]
Me![Field2] = Null
End If

 

[Jeff Boyce]

Where are you trying this?

I would probably approach this using the AfterUpdate event of the control
that displays Field1 (by the way, you posted in a "queries" newsgroup--
you'll get more eyes/brains on this next time if you post in the "forms"
newsgroup).

I would use something like:

If IsNull(Me![Control1]) Then
If Len(Me![Control2])>0 Then
Me![Control1] = Me![Control2]
End If
End If

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[DoveArrow]

You know, I never realized this forum was just for queries. I stumbled
across it while scouring the web for information about Access, didn't
really read the title of the forum, and just assumed it was a catch
all for everything. Thanks for that clarification.

In the meantime, thank you for your advice. I didn't know you could
nest an If...Then statement inside of an If...Then statement. I'll
have to try that and let you know how it works out. Take care.

Where are you trying this?

I would probably approach this using the AfterUpdate event of the control
that displays Field1 (by the way, you posted in a "queries" newsgroup--
you'll get more eyes/brains on this next time if you post in the "forms"
newsgroup).

I would use something like:

If IsNull(Me![Control1]) Then
If Len(Me![Control2])>0 Then
Me![Control1] = Me![Control2]
End If
End If

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have tried a few different things to try and get this to work, but I
can't seem to figure it out. Basically, I have a form with some text
boxes linked to fields on a table, and I want one of two things to
happen (the first one would be preferred, but if that's not possible,
the second would be just as good):

1) If Field 1 is updated to a Null value and Field 2 has text in it,
move the text from Field 2 to Field 1 and change Field 2 to a Null
value.
2) If Field 1 is updated to a Null value and Field 2 has text in it, a
message box pops up that essentially says "You can't do that."

Is what I'm suggesting possible? I imagine it could be done with an
If...Then statement of some kind, but I can't figure it out. Here's
what I've tried.

If IsNull(Me![Field1]) And IsNotNull(Me![Field2]) :::I've also tried
Me![Field2] <> Null::: Then
MsgBox "You can't do that."
:::I've also tried the following:::
Me![Field1] = Me![Field2]
Me![Field2] = Null
End If- Hide quoted text -

- Show quoted text -

 

[Dale Fye]

My first question would be, why two text fields, why not a single memo field?

Instead of using ISNULL( ), you might want to consider testing for Null or
zero length string. Generally, if you have data in a text field, and delete
it, it does not make it null, it makes it a zero length string. You can test
for both circumstances like:

IF LEN(Me![Field1] & "") = 0 Then
'do something here
END IF

HTH
Dale

 

[DoveArrow]

My first question would be, why two text fields, why not a single memo field?

Instead of using ISNULL( ), you might want to consider testing for Null or
zero length string. Generally, if you have data in a text field, and delete
it, it does not make it null, it makes it a zero length string. You can test
for both circumstances like:

IF LEN(Me![Field1] & "") = 0 Then
'do something here
END IF

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.

I have tried a few different things to try and get this to work, but I
can't seem to figure it out. Basically, I have a form with some text
boxes linked to fields on a table, and I want one of two things to
happen (the first one would be preferred, but if that's not possible,
the second would be just as good):

1) If Field 1 is updated to a Null value and Field 2 has text in it,
move the text from Field 2 to Field 1 and change Field 2 to a Null
value.
2) If Field 1 is updated to a Null value and Field 2 has text in it, a
message box pops up that essentially says "You can't do that."

Is what I'm suggesting possible? I imagine it could be done with an
If...Then statement of some kind, but I can't figure it out. Here's
what I've tried.

If IsNull(Me![Field1]) And IsNotNull(Me![Field2]) :::I've also tried
Me![Field2] <> Null::: Then
MsgBox "You can't do that."
:::I've also tried the following:::
Me![Field1] = Me![Field2]
Me![Field2] = Null
End If- Hide quoted text -

- Show quoted text -

The reason that I have it looking for Null values is because I didn't
realize that it makes a zero length string when you delete the text. I
should probably change a lot of my programming.

As far as why I didn't use a memo field... I honestly don't know what
a memo field is, so there's a clue. Seriously, I'm very new at
using Visual Basic and even Access, so I make a lot of newbie mistakes.

 

[Dale Fye]

Keep using these newsgroups. They are your absolute best source of
information about Access.

A memo field is a field that allows a lot of text to be entered, preventing
you from having to have multiple 255 character text strings. I encourage
you to lookup "memo" in the Access help window. There is a good discussion
of when to use them.

We have all gone through this before, that's why we are here answering
questions. It took me a long time to realize that NULL <> "", but once you
realize that, it makes a huge difference in your coding.

Good luck.

My first question would be, why two text fields, why not a single memo
field?

Instead of using ISNULL( ), you might want to consider testing for Null
or
zero length string. Generally, if you have data in a text field, and
delete
it, it does not make it null, it makes it a zero length string. You can
test
for both circumstances like:

IF LEN(Me![Field1] & "") = 0 Then
'do something here
END IF

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.

I have tried a few different things to try and get this to work, but I
can't seem to figure it out. Basically, I have a form with some text
boxes linked to fields on a table, and I want one of two things to
happen (the first one would be preferred, but if that's not possible,
the second would be just as good):

1) If Field 1 is updated to a Null value and Field 2 has text in it,
move the text from Field 2 to Field 1 and change Field 2 to a Null
value.
2) If Field 1 is updated to a Null value and Field 2 has text in it, a
message box pops up that essentially says "You can't do that."

Is what I'm suggesting possible? I imagine it could be done with an
If...Then statement of some kind, but I can't figure it out. Here's
what I've tried.

If IsNull(Me![Field1]) And IsNotNull(Me![Field2]) :::I've also tried
Me![Field2] <> Null::: Then
MsgBox "You can't do that."
:::I've also tried the following:::
Me![Field1] = Me![Field2]
Me![Field2] = Null
End If- Hide quoted text -

- Show quoted text -

The reason that I have it looking for Null values is because I didn't
realize that it makes a zero length string when you delete the text. I
should probably change a lot of my programming.

As far as why I didn't use a memo field... I honestly don't know what
a memo field is, so there's a clue. Seriously, I'm very new at
using Visual Basic and even Access, so I make a lot of newbie mistakes.