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

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

0
DoveArrow
8/1/2007 3:39:39 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
936 Views

Similar Articles

[PageSpeed] 50

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
> 


0
Jeff
8/1/2007 4:46:50 PM
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 -


0
DoveArrow
8/1/2007 5:19:40 PM
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.

0
DoveArrow
8/2/2007 12:01:27 AM
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.
> 


0
Dale
8/2/2007 2:22:56 AM
Reply:

Similar Artilces:

spin button value
Hi Does the value in spinbutton properties have to be numeric or can it be a day eg mon tues wed etc if so how do I dio this Thanks tina A Forms toolbar spinner control returns an integer. You can have another cell with a formula that references the cell linked to the spinner like =CHOOSE(A1,"Sun","Mon","Tues","Weds","Thurs","Fri","Sat") Here A1 is linked to the spinner. You'd want to limit the spinner's minimum value to 1 and maximum value to 7 in this case. -- Jim Rech Excel MVP "tina" <tina@...

How do I fit A4 text into A5 in publisher?
I have an A4 document in publisher which I want to convert to A5; that was easy but the text remains the same size and runs over the margins. How can i resize the text to fit the A5 pages? Select the text box, Format, AutoFit text... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "John" <John@discussions.microsoft.com> wrote in message news:AC06932C-D51B-4C83-80BC-DCEB4C9D0772@microsoft.com... >I have an A4 document in publisher which I want to convert to A5; that was > easy but the text remains the same size...

Null Is Null
I've got a text box in a report that's not returning properly. Here's its Control Source: =[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]! [SumWtg]+IIf(IsNull([Reports]![rptFGPackConfigsPKWeights]! [srptFGPKConfigsTPK_totalwt]![SumWtg]),Null,"") This returns #Error. It's definitely my IIf statement as it returns properly when removed however there are times when [srptFGPKConfigsTPK_totalwt].[SumWtg] will be null. In fact, this subreport will not appear if no records exist. I suspect that this is the problem. How can I write this Control Source t...

if A1=Null and B1=A1 why is result in B1=0 ??
if A1=Null and B1=A1 why is result in B1=0 ?? because of this I get wrong result for average calculations: average for (6,8,0) <> average for (6,8,null) Use =IF(A1="","",A1) -- HTH RP (remove nothere from the email address if mailing direct) "WGeorg" <WGeorg@discussions.microsoft.com> wrote in message news:09FCCF25-D0DC-4B1E-BFA1-CEF9A9327C46@microsoft.com... > if A1=Null and B1=A1 why is result in B1=0 ?? > because of this I get wrong result for average calculations: average for > (6,8,0) <> average for (6,8,null) Use this ...

RMS 1.2 New field for detailed sales report
I would like to print the tender type on the detail sales report.Could anybody tell me what table, field and syntax I need for the inner join of the report? Thanks Gerd I made some progress with the report. I found the tables I need and have linked those to the report. However, when running the report I get duplicate records for some transactions when the tender type was cash with some money back. Reason being that there are 2 records in table tenderentry. Here it the report definition with all the various joins: Begin ReportSummary ReportType = reporttypeSales ReportTitle = "...

Syntax for Is Not Null ?
When one of our forms is opened (Current event) I want a message box fired by certain conditions (that the Ref control does have a value, but the Country control is empty). I have tried various bits of code along the lines shown below, but keep getting runtime errors. Wot's wrong with the following, please? If [Me.Ref] is not null and [Me.Country] = "" Then MsgBox "Please enter the country!" Many thanks CW CW - Try this: If (not isnull([Me.Ref])) AND IsNull([Me.Country]) Then MsgBox "Please enter the country!" -- Daryl S ...

Rich Text Format
Hi, My problem is thus, I want to supply some text and a CHARFORMAT2 struct to a method and simply have it return the RTF, you can see below. I dont want to have to create a rich edit control etc. There must already be a method or class that will do this surely? Using a rich edit control you can take a string "this is a testing string". Create a CHARFORMAT2 struct and pass it to the metthod SetCharFormat to format that string. This produces some rtf text like this - although note its not the rtf for the "this is a test string" text. What i want to know is how...

When is a Null not a Null?
I have a form which is used for adding and entering data depending on where it is called from. When called in add mode (acFormAdd) a blank form is openned. When the cursor enters the text box [Name] on the form field I am trying test whether it is empty so that a search form can be openned if a new name is to be added. (I don't want the search form to open in cases when the form already has data in and I am editing rather than adding data). The form is based on a query and the data displayed in [Name] is the result of a calculated field in the query which concatenates firstname and surn...

Rearranging fields after hidding them
With simple Javascript we are able to hide fields from a form. However, the fields which are still shown on the form are not realligned. For example we might have the following scenario: Field 1 Field 2 Field 3 Field 2 should not be shown on certain conditions. So, in the onLoad() event of the form we hide this field. This will result in the following: Field 1 Field 3 With an empty space between Field 1 and Field 3. Is there a way to bypass this issue ? Thanks Nicos Papaioannou There's no automatic way of doing this. You can try this: var td2 = document.getElementById(crmForm.a...

Calculated Fields
Hi, Recently I started using calculated fields in word extensively. I write a lot of technical documents with a lot of calculation in them. Since all calculations are chain-like I am assigning values to variables and then use them. At the end of the document I end up with more than 100 variables which are difficult to track. So my question is how can I track my variables in Word (something like a panel where I can see them)? Is there any other efficient, or cleverer, way to complete my task? The following macro list all the docvariables with their values Sub ShowVariables(...

retrieving the XmlEnumAttribute and the enum-values (2)
This post is a followup to the thread "retrieving the XmlEnumAttribute values for an Enum" in this same forum earlier this month, since my last query in that thread went unanswered. I have an enum defined (DotNet v2) as public enum velocityUom { [System.Xml.Serialization.XmlEnumAttribute("m/s")] ms = 21, [System.Xml.Serialization.XmlEnumAttribute("cm/a")] cma, ... } My original post was to ask how I could retrieve the XmlEnumAttribute values for this Enum so that I could create a combo box with values like "m/s", &qu...

What is the Null character?
What is the character sequence that Excel uses to represent an empty cell? I am using a formula and when it returns false, I would like for it to represent the cell as if it was empty. I have tried using "" as an empty cell, but excel recognizes that this is a value, not an empty cell. You can't do what you want to do. A formula always returns *something*. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Quinn Ramsey" <Quinn Ramsey@discussions.microsoft.com> wrote in message news:D67F6311-0CCA-48B5-A...

Passing Values between forms
I am trying to link to forms. I have a button on the main form (frmCompanies) that needs to open another form when a button is clicked. The new form that opens is frmScheduledActivities. When it open it needs to check to see if there is an existing record (scheduled activity) for the company and if there is no scheduled activity be set to add a new record. When the button is clicked on the main company form (on the on click event) this code runs: Private Sub cmdCallBack_Click() On Error GoTo Err_cmdCallBack_Click Dim stDocName As String Dim stLinkCriteria As String...

Charting null values
I have a chart that is plotting null values. I tried the formula =if(C25="",N/A(),Sum(C22:C25)). I've also deleted the formula to create a completely null cell. I've been to Tools, Options, Chart and selected Plot empty cells as Not plotted, zero and Interpollated As well as turning Plot Visible Cells only (turned it on and off) Nothing seems to make a difference. Any suggestions? Stephanie, what kind of chart are you using? Can you give an example of how your data table looks like? br, Henk "StephanieH" wrote: > I have a chart that is plotting ...

How to Link a cell in an Excel spread sheet to a text value in Visio 2007
I would like to have a text value in Visio take the value of a cell in an Excell spread sheet. Is there a way to do this, at least on opening the Visio Document? Thanks in advance, Marco UCO Lick Observatory Laboratory for Adaptive Optics visio 2007 professional http://office.microsoft.com/en-us/visio/HA100518191033.aspx al "Marco" <null@null.net> wrote in message news:uCcafQkuKHA.1796@TK2MSFTNGP02.phx.gbl... > I would like to have a text value in Visio take the value of a cell in an > Excell spread sheet. Is there a way to do this, at leas...

NULL values
Working with a downloaded table form a Lotus Notes contact manager. Now in access call tblclients. I query certain info. to update the records. BUT...'Is Null' does not work. It comes back empty and there are definitely empty contents in the fields I chose. I know how to do this as it works in my other tables on other databases. The only difference I can see in this table as opposed to tables created directly in Access is the Field Design property called Unicode Compression saying No instead of the default Yes. I changed the Unicode to Yes in case this had something to do with it and ...

How can I check a combo box content or value or something
Hi. I need to check if my combobox has something on it, what I mean is if my users choose any value. I need something looked like: if combo1 then msgbox "you must choose an option on Combo1" exit sub end if How can I do something looked like? Regards, Marco Marco, In the AfterUpdate event of the combo type, if IsNull(combo1.text) then msgbox "you must choose an option on Combo1" End If "Marco" <Marco@discussions.microsoft.com> wrote in message news:92B57DBB-74BC-4177-AB68-A87153654F34@microsoft.com... > Hi. I need to check if my combobox h...

Border with text
I'm trying to make a border using text. I want to repeat the same phrase over and over again. Idealy, I'd like the words to make a rectangular border for a sign... and when it hit the corner of the page, the phrase would continue down the next angle, and so on, until it made a box. Does anyone have any ideas on how to do this? WordArt would be your only option. If you had a draw program... Serif has a free draw application. http://www.freeserifsoftware.com/ OpenOffice is free and has a draw feature. www.openoffice.org I have a method using WordArt on my web site that might be...

Taking the Mode based on cell value
Could you provide a few more details about what you want to do? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "sip8316" <sip8316@discussions.microsoft.com> wrote in message news:E3F4DE24-C5E8-4911-96E6-E61A1E56DEF0@microsoft.com... > ...

How to MATCH value up a column
I need to match/find the first matching cell up a column. Ideas? TIA! Hi I don't know what you are looking for, but see: MATCH(A2,A1:F1,0) or VLOOKUP(A3,Sheet2!A:B,2,FALSE) -- Please click "yes" if this post helped you! Greatly appreciated Eva "DevourU" wrote: > I need to match/find the first matching cell up a column. Ideas? TIA! Thankx for the reply. Here is my formula:=IF(A11>A10,C10,(IF(A11=A10,A10,"?"))). I need to have IF(A11<A10, search up column A for 1st match, example A4, display next column B4. Clear as mu...

Invisible or "missing" Outlook message text on reply or forward using Word as editor
We seem to have one Outlook 2003 SP2 user that seems to automatically delete or truncate parts of the message text when replying or forwarding. There may be a long thread of email replies but only the part of the most recent post is displayed. When you do a Select All/Ctr A to highlight the email, then paste it in notepad, all of the thread is displayed. When we deselect Word as the email editor, the problem thus goes away. Word seems to hide or make invisible the text. I just saw a similar post here from a while ago where the reply text was missing. But in my case just the history is &qu...

print selected text OL2003
hey folks been beating my head on wall for a few days now any one knows a way to set printing options/styles to print selected text in a message ? this is for MS Outlook 2003. thanks no, it's an all or nothing deal. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Subscribe to Exchange Messaging Outlook newsletter: ...

Code in a macro to branch to another macro?
I wonder if someone can help me with a little code for inclusion in a macro? It needs to do the following: “If cell in column D on this row = "Sunday", perform Macro “Sunday”, otherwise execute macro “Weekday”” Background info: I run an accounting system on a series of worksheets. The Cash Journal dynamically applies the week of the year and day of the week (based on the first date in the financial year) and conditionally formats rows accordingly to highlight weekends. Macros post the daily cashup sheet to the Cash Journal and General journal. I am combining the...

indicate identity between two fields
Probably a simple one for you Excel wizzards. Could someone explain me how I can, in an Excel, sheet automatically indicate whether two values (actually letters A, B, C,D) are identical or not? So I have column X with a long list of A's B's C's and D's (one per field) and column Y with a similar but different list and I would like to see where the diffrences are on the same row either by a symbol given in column Z or for instance by a color assigned to the different or similar values. Thanks in advance. =X1=Y1 will return TRUE if they are the same and FALSE if diffe...

Convert dates stored as text
I have Excel 2007 in English, but I sometimes receive data that comes from another applicationes, so dates are stored as text because they come in following format: dd/mm/yyyy. And I have the format mm/dd/yyyy. So, in the same column, I have dates stored as dates, and dates stored as text. Which is the easiest way to convert them all to date format? Thanks in advance. Regards, Emece.- Be careful. I'd bet that those values that come in as real dates aren't what the original data represent. For instance, if you have two values: 25/12/2010 and 01/02/2010 The...