Can't we give conditions on Validation Rule? (1 Viewer)

prabha_friend

Prabhakaran Karuppaih
Local time
Today, 21:22
Joined
Mar 22, 2009
Messages
771
Can't we give conditions on Validation Rules? (Like IIf)

IIf(getusername()=[Log_Resource],Between DateAdd("d",-1,Date()) And Date(),Between DateAdd("d",-2,Date()) And Date())

?

Thanks in advance.
 

vbaInet

AWF VIP
Local time
Today, 15:52
Joined
Jan 22, 2010
Messages
26,374
I'm a bit concerned that a developer of your calibre would be using Validation Rules prabha ;)

From what I remember:
* If it's at table level you can't use User Defined functions like GetUserName()
* If it's at control level you, User Defined functions are valid
* Your expression isn't evaluating to True or False, IIF() is returning a value.
 

vbaInet

AWF VIP
Local time
Today, 15:52
Joined
Jan 22, 2010
Messages
26,374
Sorry one more thing:
* Your expression is also invalid with your use of BETWEEN in an IIF() function.
 

prabha_friend

Prabhakaran Karuppaih
Local time
Today, 21:22
Joined
Mar 22, 2009
Messages
771
I'm a bit concerned that a developer of your calibre would be using Validation Rules prabha ;)

Hi Inet. Thank you for your reply. I still believe we have to use as little code as possible. It's my humble view. opinion differs...

I even tried this:
IIf(getusername()=[Log_Resource],"Between DateAdd('d',-1,Date()) And Date()","Between DateAdd('d',-2,Date()) And Date()").
Not working :(

So. Can we conclude that we cannot use IIF conditions on Validation Rule?

Currently trying to replicate the same Validation Rule with AND and OR operators...
 

vbaInet

AWF VIP
Local time
Today, 15:52
Joined
Jan 22, 2010
Messages
26,374
Use as little code as possible if (and that's a big if) the alternative solution is scalable, robust and match the 'code-way' in performance.

Following my last point, "* Your expression is also invalid with your use of BETWEEN in an IIF() function.", a simple test in your immediate window will indicate that you can't use BETWEEN in that fashion.

I can already see at least 4 ways in which you can re-write your function to work, using AND, OR and BETWEEN, or using AND OR, >= and <=. Have a think.
 

prabha_friend

Prabhakaran Karuppaih
Local time
Today, 21:22
Joined
Mar 22, 2009
Messages
771
(getusername()=[Log_Resource] And [Log_Date] Between DateAdd("d",-1,Date()) And Date()) Or (getusername()<>[Log_Resource] And [Log_Date] Between DateAdd("d",-2,Date()) And Date())
 

vbaInet

AWF VIP
Local time
Today, 15:52
Joined
Jan 22, 2010
Messages
26,374
Yes, that's it. And to make it even shorter:

Code:
getusername()=[Log_Resource] AND [Log_Date] Between DateAdd("d",-2,Date())
Note that you can get rid of the DateAdd(-1) criteria because DateAdd(-2) covers that basis.
 

prabha_friend

Prabhakaran Karuppaih
Local time
Today, 21:22
Joined
Mar 22, 2009
Messages
771
No. I need two different validations. One for self entries and other for passive entries (through admin)
 

vbaInet

AWF VIP
Local time
Today, 15:52
Joined
Jan 22, 2010
Messages
26,374
Little typo which you should have picked up:
Code:
getusername()=[Log_Resource] AND [Log_Date] Between DateAdd("d",-2,Date()) [COLOR="Blue"]AND Date()[/COLOR]
But I think you're missing the point here and I'll explain.

DateAdd(-2) = 2 June 2015
DateAdd(-1) = 3 June 2015
BETWEEN DateAdd(-2) AND Date() = 2 June 2015 AND 4 June 2015
BETWEEN DateAdd(-1) AND Date() = 3 June 2015 AND 4 June 2015

Can you now see that DateAdd(-2) covers both -2 and -1? Either way, DateAdd(-2) alone will cover both basis because your criteria is an OR and BETWEEN.#

If you know about Logic gates:
TRUE OR TRUE = TRUE
TRUE OR FALSE = TRUE
FALSE OR TRUE = TRUE
FALSE OR FALSE = FALSE

You only need one condition to make it true and in this case the condition with the widest range meets that criteria.

Does that make sense?
 

Users who are viewing this thread

Top Bottom