Query to poplulate 3rd field on the fly based on 2 others. (1 Viewer)

spet

Registered User.
Local time
Today, 07:47
Joined
Oct 5, 2018
Messages
38
Good Afternoon,


I have a query that is outputting 2 fields, but I'm wanting to populate the 3rd [Expr1] based on the [PatronNo] and [Main].


If [PatronNo] is empty I was to display [Main] in [Expr1] or if [PatronNo] is equal to [Main] I want to display [Main] in [Expr1], otherwise display [PatronNo] in [Expr1].


Here is how I'm trying to attempt that:
Expr1: IIf(Val([AllPatrons].[PatroNo])=Val([PatronPayrollInfo].[Main]) Or Val([AllPatrons].[PatronNo])="",[PatronPayrollInfo].[Main],[AllPatrons].[PatronNo])


Can someone please help explain, why 103,104,105,etc are not showing in Expr1 and instead I'm receiving an error.



I've tried modifying this statement over and over and ready to pull my hair out! :banghead:


Thank you in advance.



PatronNo Main Expr1 103 #Error 104 #Error 105 #Error 107 #Error 108 #Error 109 #Error 111 #Error 112 #Error 113 113 113 1131 113 1131 114 #Error 115 #Error 120 #Error 1211 121 1211 121 121 121 124 #Error 129 #Error
 

spet

Registered User.
Local time
Today, 07:47
Joined
Oct 5, 2018
Messages
38
 

plog

Banishment Pending
Local time
Today, 07:47
Joined
May 11, 2011
Messages
11,613
First, you only have 1 logical condition to test for, not 2. In english your logic is this:

If [PatronNo] is empty I was to display [Main] in [Expr1] otherwise display [PatronNo] in [Expr1]

The part about [PatronNo]=[Main] is moot.

Second, "empty" is an ambigous word in coding. It can mean both an empty string ("") or NULL. There is a difference between those two, an empty string is not NULL. When you try and use NULL in the Val() function you will receive an error. That's my guess as to what is happening.

So my suggestion is to simply use the NZ(https://www.techonthenet.com/access/functions/advanced/nz.php) function instead of all the logic you have there:

Expr1: NZ([Main], [PatroNo])
 

Users who are viewing this thread

Top Bottom