No Decimal Places option after Iff()

[hminh87]

Here's the code that I'm having trouble with:

Bet 1 mån: IIf ( [Betalningstidpunkt] < ( Now() + 30 ) ; [Skostnad] )

I've checked with VarType and they are 3:Long Floating type. Problem is that the default result numbers do not have thousand separators while "Standard" Format gives me decimals.
I don't want any decimal in the result and the field Decimal Places is nowhere to be found.

What I am suspecting is that Access is seeing the query as Text since there is a "Text Format" option available in the properties.

Any body has an idea how to fix this?

 

[raskew]

Hi -

Now() is a date/time data type which includes both date and time, e.g.

? now() + 30
7/21/2009 8:18:32 AM

To include just date, no time, use date()

? date() + 30
7/21/2009

[Betalningstidpunkt] should be stored as a date/time data type.

The Iif() statement must include both a True and False statement. See the Help file on this one.

HTH - Bob

 

[Galaxiom]

The Iif() statement must include both a True and False statement.

Actually the False statement is generally optional on IIF. The result is null if the test is false.
Though perhaps it is essential in this particular circumstance but unfortunately my svenska is very limited.

Betalningstidpunkt?!

 

[raskew]

Hi -

Actually the False statement is generally optional on IIF.

After taking a look at the Help File(s), please post a working example where the False portion of an Iif() statement can be successfullly omitted.

Bob

 

[jibbajabba]

SELECT IIf(1>0,Format(Int(4003.2),'#,###')) AS Expr1
FROM [enter a table];

Will return "4,003"

SELECT IIf(1<0,Format(Int(4003.2),'#,###')) AS Expr1
FROM [enter a table];

Will return null

Note there is no false part in the iif

 

[raskew]

Hmmm -

That's interesting.

When I attempt to test your posted solution, I get:

Compile Error

Argument Not Optional

Here's what the Help File says about the IIf() function:

Returns one of two parts, depending on the evaluation of an expression.

Syntax

IIf(expr, truepart, falsepart)

The IIf function syntax has these named arguments:

Part Description
expr Required. Expression you want to evaluate.
truepart Required. Value or expression returned if expr is True.
falsepart Required. Value or expression returned if expr is False.
Remarks

IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True

Bob

 

[jibbajabba]

I'm using the SQL editor in 2003 and it works perfectly. Maybe the functionality has changed? Or, are you trying to test this in a code module?

 

[raskew]

Hi -

Any other examples that don't include the Int() function? Think the potential structure of that further complicates the issue.

Bob

 

[jibbajabba]

SELECT IIf(1=1,1) AS Expr1, IIf(1=0,1) AS Expr2
FROM [enter a table]

Again, it does matter where you use the 'IIf' as Access essentially has two 'IIf' functions, VBA has one where the false part is not optional and a native jet IIf one where you can get away without the false part.

I never use iif in vba, as at runtime both true and false parts are always evaluated (leading to undesirable effects) and if/then/else or select case are much cleaner.

 

[Galaxiom]

Again, it does matter where you use the 'IIf' as Access essentially has two 'IIf' functions, VBA has one where the false part is not optional and a native jet IIf one where you can get away without the false part.

I'm glad I mentioned the IIf not needing the False statement. I never would have known this. Not that I use IIF in VBA but I might have one day and found out the hard way.

IIF is certainly very useful in SQL and I guess it should stay there.

 

[gemma-the-husky]

is this sorted?

iif stands for immediate if, and is just a method writing inline if then else statements

so

if a then
b
else
c
end if

becomes
iif(a,b,c)

-------------

Bet 1 mån: IIf ( [Betalningstidpunkt] < ( Now() + 30 ) ; [Skostnad] )

now, in your original example you are merely saying

iif(condition)

with no optional statmeents at all (you are using a ; as a statement separator, rather than a comma, and i am not sure if this is something in your language - but it is likely your statement is just written incorrectly.

so you want instead

iif(whateverdate < now()+30, result if TRUE, result if FALSE)

its just a matter of semantics whether you really want

iif(whateverdate < date()+30, result if TRUE, result if FALSE)

since now and date are slightly different


-----------
out of interest, in your header you used iff (a typo), rather than iif

iff is never right - its actually a logical assertion, meaning if and only if