formula in Record Source

[Spidre23]

I have a form with a text box. I need the text box to show value_2 if value_2 exists, otherwise select value_1. Apparently, my Iif statement for the Record Source does not cut it:

IIf(IsNull([value_2 ]) Or [value_2 ] = "";[value_1];[value_2 ]). The formula itself appears to be wrong...

Fair enough, I made a query that does the select and used the query as the Control Source. It returns #name? for every record.

Any help is greatly appreciated!

S

 

[SteveH2508]

Try , (commas) as separators instead of ; (semi-colons).

 

[mahenkj2]

IIf(IsNull([value_2 ]) Or [value_2 ] = "";[value_1];[value_2 ])

Try

IIf(IsNull([value_2]) Or [value_2] = "",[value_1],[value_2])

 

[Spidre23]

Thanks for the replies guys. I wish it was that simple!

Access (2010) shows the structure of the formula as I type it and it definitely wants semicolons. I tried replacing it with commas but access then does not even recognize it as a formula at all. As far as I know, it should always be semicolons, but I am quite the n00b here...

 

[mahenkj2]

Copy altered code in my post and use. You have space [value_2 ] which should be [value_2]

 

[Spidre23]

Copy altered code in my post and use. You have space [value_2 ] which should be [value_2]

Damn...n00b indeed....

Ok, that fixed it a little (still have to use semicolons). Unfortunately, this:

=IIf(IsNull([value_2]) Or [value_2]=" ";[value_1];[value_2])

only returns value_2 if it exists and always shows #Error when value_2
does not exist and value_1 does exist. Note that both value_1 & value_2 columns are identically defined, just different values.

S

 

[mahenkj2]

Now, there may be something else may be happening when Value 2 is Null. Is this text box is stand alone or may have effect if Value 2 is null.

You may simply test this logic in a stand alone query with stand alone table.

 

[spikepl]

1. A TextBox has a Control Source - not Record Source (a form can have a Record Source)
2. Both sides of an OR expression are always evaluated - hence your error
3. To check if a textbox holds a value you can use the condition
Len(MyControl & vbNullString) >0
4. Semicolons are used to separate arguments in IIF, when country setting uses "," as decimal separator

 

[Spidre23]

1. A TextBox has a Control Source - not Record Source (a form can have a Record Source)
2. Both sides of an OR expression are always evaluated - hence your error
3. To check if a textbox holds a value you can use the condition
Len(MyControl & vbNullString) >0
4. Semicolons are used to separate arguments in IIF, when country setting uses "," as decimal separator

Thanks!

@Mahenk

Not sure what you mean there, but when I make simple select query based on the (linked) table with this:

name: IIf(IsNull([MapVendor].[vendor_name_we]) Or [MapVendor].[vendor_name_we]=" ";[MapVendor].[vendor_name];[MapVendor].[vendor_name_we])

where vendor_name_we = value_2 and vendor_name = value_1, it appears to work just fine.

@Spikepl

1: True, I confused them.
2: I see, but how does this give me an error? Does it not just check whether the columnvalue is empty (null) or has an empty space ("")? They can't both be true right?
3: As I n00b I don't know where to put this. I do know that all records have a value_1 and only some have also a value_2

S

 

[pr2-eugin]

I second Spikepl suggestion to use Len(), However the usage is as follows..

=IIf(Len([value_2] & "")<1,[value_1],[value_2])

NULL = vbNullString but an Empty String ("") is NOT equal to Null.. using Len() will help to capture both null and empty string and also will help you reduce computation...

 

[Spidre23]

Hi Guys,

thanks for all your help; it got me into the right direction. There was another problem that had not so much to do with the formula:

The text box's name was the same as the name of value_1. Apparently it caused some circularity problem. I simply renamed the textbox and now pr2-eugin's code works!

Cheers!

Case closed.