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

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" <DoveArrow@gmail.com> wrote in message 
news:1185982779.366901.200430@i38g2000prf.googlegroups.com...
>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
> 

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.

On Aug 1, 9:46 am, "Jeff Boyce" <nonse...@nonsense.com> wrote:
> 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" <DoveAr...@gmail.com> wrote in message
>
> news:1185982779.366901.200430@i38g2000prf.googlegroups.com...
>
>
>
> >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 -

On Aug 1, 12:54 pm, Dale Fye <dale....@nospam.com> wrote:
> 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.
>
>
>
> "DoveArrow" wrote:
> > 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.

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.


"DoveArrow" <DoveArrow@gmail.com> wrote in message 
news:1186012887.944203.247700@x40g2000prg.googlegroups.com...
> On Aug 1, 12:54 pm, Dale Fye <dale....@nospam.com> wrote:
>> 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.
>>
>>
>>
>> "DoveArrow" wrote:
>> > 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.
>