How can I get this excel formula to work in access?

[Cayetano]

How can I get this excel formula to work in access I know that the field name replaces the cell reference?

Example: Clock In Clock Out = Total Time Worked

=IF((OR(H9="",G9="")),0,IF((H9<G9),((H9-G9)*24)+24,(H9-G9)*24))

[Delerna]

If you mean in a query then
excels
if(condition,true,false)
in access is
iif(condition,true,false)


so basically take you formula
change if to iif
change the cell references to field references
change the or part to (H9="" or G9="")

and your done

something like this (untested)

iif(H9="" or G9="",0,iif(H9<G9,(H9-G9)*24 +24,(H9-G9)*24)) 
 
 

you will need to change the cel refs to field refs of course

[Delerna]

If in vba
excels
if(condition,true,false)
in access is

if condition then
true statements
else
false statements
end if

so

if H9="" or G9="" then
   0
else
   iif H9<G9 then
      (H9-G9)*24 +24
   else
      (H9-G9)*24)
   end if
end if 
 

[NeoPa]

That's not right I'm afraid Delerna.

Access and Excel VBA are the same in this respect. Both have If constructs as well as the IIf() function available to them. Where they differ is as you suggested in your first reply - Excel formulas use IF(), whereas Access formulas use IIf() (just as in VBA). You can't have a line in Access (or any) VBA which just says 0.

I'm afraid the question is very basic in its information, leaving us very little to work from, but in general an Access IIf() function call can include more complex boolean expressions which must be handled using OR() or AND() function references in an Excel formula. This formula could then be written in Access (a formula reference rather than VBA) as :

=IIf([H9]='' Or [G9]='',0,IIf([H9]<[G9],([H9]-[G9]*24)+24,([H9]-[G9]*24))

Or a little less clumsily as :

=IIf([H9]='' Or [G9]='',0,24*([H9]-[G9]+IIf([H9]<[G9],1,0)))

Presumably [H9] & [G9] would have names, but we don't, so we'll just make do with the Excel references for now.

[Delerna]

That's not right I'm afraid Delerna.

Hi Neopa
I must admit I had always made the assumption that what I said was correct and after reading your post I had doubts so I fired up Access and Excel and Checked

I am sorry but I fail to see where what I said is not correct.

In my trial
IN EXCEL
iif(condition,true,false)
cannot be used in a cells formula.
Neither can it be used in VBA.
If condition then true else fales end
Also cannot be used in a cells formula.
It can be used within VBA of course but since the OP's question said excel formula I gave answers in reference to that.

In ACCESS
if(codition,true,false)
within a query generates an undefined statement error on if.
Again it cannot be used in VBA here since VBA in access or excel or word is just VBA.
It matters not, which application it is written within.


Actually, I don't see where I made any comment as to which constructs exist or don't exist within excel,access or VBA.

Within a query
Mine
iif(H9="" or G9="",0,iif(H9<G9,(H9-G9)*24 +24,(H9-G9)*24))

Yours
IIf([H9]='' Or [G9]='',0,IIf([H9]<[G9],([H9]-[G9]*24)+24,([H9]-[G9]*24))

Appart from the [] are identical


My syntax for VBA is incorrect because
I copied and pasted the code and forgot to change the iif in the nested if, which won't work in VBA.

Is that where you mean I was not correct?

[Delerna]

Oh I just noticed

Access and Excel VBA are the same in this respect

But as poted above
I made no comment about excel vba.
My answers were relating to the OP's excel formula and gave versions for both cases in ACCESS alone.
within a query and within VBA

[NeoPa]

Delerna:
In my trial
IN EXCEL
iif(condition,true,false)
cannot be used in a cells formula.
Neither can it be used in VBA.

The last line of the quoted text is not correct Delerna. Excel VBA handles the IIf() function quite happily.

In your post #3 you indicate that an Excel IF formula is equivalent to an If ... Then ... Else construct in Access VBA. There are many differences between these two I'm afraid, principal of which is that the VBA code is not usable within a query and no VBA line can return a simple value, as you have in line #2 of your code. That simply wouldn't compile or run.

Delerna:
Actually, I don't see where I made any comment as to which constructs exist or don't exist within excel,access or VBA.

No. I included my explanation of that in the hope that a single explanation would put the whole thing in perspective for anyone reading the question. A full and definitive answer, if you like. This was not about where your post was in error at all. It was part of my answer.

PS. I didn't see post #6 before I prepared this, but I have now of course. I think everything is still pertinent, but some may now be superfluous.

[NeoPa]

I forgot to include in my last post (#7) that your code was very similar to mine indeed. I wasn't implying that was wrong. I wanted to post a version that was as equivalent as possible to start with, before posting a tidier version. The only differences were the brackets, as you mentioned, but also the quotes used. These are harder to notice unless you use the [code] tags, but I used the single quotes (') which are SQL standard rather than the doubles (") which Access uses generally, but which are not standard (See Quotes (') and Double-Quotes (") - Where and When to use them).

The later version, which was somewhat different from the OP's code, was what I was really suggesting to use, as the logic is more straightforward, albeit somewhat less obvious than the OP's version.

[Delerna]

oops.......egg on face.

[Delerna]

corrected code

iif(H9='' or G9='',0,iif(H9<G9,(H9-G9)*24 +24,(H9-G9)*24))  
 

if H9="" or G9="" then 
   result=0  
else 
   iif H9<G9 then 
      result=(H9-G9)*24 +24 
   else 
      result=(H9-G9)*24) 
   end if 
end if  
 

and

iif does indeed exist in vba working similar to a fuction call, which I was not aware of.
A little research reveals that care in it's use needs to be taken because it actually doesn't work exactly like if then else.

For example in an attempt to cover division by zero errors

dim t
t=0
result=iif(t=0,0,10/t)
 

would still produce the error.
This is because both the true and the false statements are executed, returning the result based on the condition

Anyway, a new tool for my toolbox that I was not aware of before.

[NeoPa]

Delerna:
This is because both the true and the false statements are executed, returning the result based on the condition

This is something that many people get confused by. Actually, it's not so much about the function executing both of the statements, but in a very real way it's even simpler than that.

When the compiler creates the code, it is done in the standard way for passing values as parameters to a procedure. That is, the compiler ensures that the calculated values are determined and passed to the function, before the function is even instigated. In other words, all of the parameters, the boolean value as well as the True and False values, are worked out beforehand and the function itself merely returns one of the values it has been passed. The calculation that fails (division by zero in your example) will be done prior to the execution of the function.

I hope that helps. So many people get hung up on that and stay confused for ever.

[Delerna]

Well if we are going to that level of detail :)

I thought VBA was an interpreted language and not a compiled one.
In that case each instruction, immediately prior to execution is interpreted and both true and false conditions are determined immediately prior to the codes execution.
In effect, VBA's process of executing an instruction causes both versions of the truth to be calculated and therefore the error to be generated.

[Delerna]

Actually going down to this level could be best left to the people we are trying to help to discover for themselves, unless they ask for it directly.
It could be even more confusing for someone who doesn't have enough knowledge to understand it?

[NeoPa]

In response to post #12 :
VBA is semi-compiled. There are elements of compilation, but also of interpretation. Otherwise you're absolutely right in your understanding. That explains it well.

In response to post #13 :
We all have our opinions. In this case I don't believe this makes it harder to understand. We're not forcing anyone to read any comments. If they don't like what they read they can simply ignore it and go onto the next post, but if it's helpful and what they need, they can't read it if it's not posted. That said, if your opinion is otherwise, I can live with that too.