if statement, I think

[nigel brown]

Hi all, again!

I have taken the plunge and started to learn VB, early days yet!

However I have one for you.

how would I write this, and can I put it in a control source box on a form or report?

if [low] = true, then [Monitor1]=[first vet date] +56

I am asking if low field is true (yes/no) then take monitor 1 date and add 56 days to it.

I know what I want to do, I am slowly learning how to do it, with help form you guys.

Regards

Nigel

 

[JANR]

In the controlsource of an unbound control
=IIf([low], DateAdd("d", 56, [first vet date]),[Monitor1])

The bold part is the false argument, i took a guess on what you wanted there

JR

edit: changed my answer

 

[nigel brown]

In the controlsource of an unbound control
=IIf([low], DateAdd("d", 56, [first vet date]),[Monitor1])

The bold part is the false argument, i took a guess on what you wanted there

JR

edit: changed my answer

Thanks JR
Not sure what you mean by the false with Monitor1?

Nigel

 

[vbaInet]

I can see JANR has rightly given you a solution, but I wonder if this rewrite would be more safe:

=IIF(IsNull([first vet date]), Null, DateAdd("d", Abs(Nz([low], 0)) * 56, [first vet date]))

Just to avoid errors.

 

[JANR]

Thanks JR
Not sure what you mean by the false with Monitor1?

Nigel

What I ment was if low= False then what should the the control show. I assumed that you then wanted to show the monitor1 date??

JR

 

[vbaInet]

Plus the True and False parts of the IIF() function are required parameters.

 

[nigel brown]

Plus the True and False parts of the IIF() function are required parameters.

JR

If the return is false then the Monitor1 will be blank.

However, after asking if low is true, if it false then the same will aply to [medium] and that if true will return a value of adddate 21.

So I was having one if statement, if true then good but if false it moves onto another if statement.

There is probably an easier way of doing it, but in my logical-ish mind it seems feesable.

Regards

Nigel
PS is this:
=IIf([low], DateAdd("d", 56, [first vet date]),[Monitor1])

classed as VB or something else?

 

[nigel brown]

I can see JANR has rightly given you a solution, but I wonder if this rewrite would be more safe:

=IIF(IsNull([first vet date]), Null, DateAdd("d", Abs(Nz([low], 0)) * 56, [first vet date]))

Just to avoid errors.

Hi VBAInet

Is this statement better?

How does it work?

Looks technical, like most things like this.

Regards

Nigel

 

[nigel brown]

Ok so I have got this statement and it works:

=IIf([Risk Low],DateAdd("d",91,[1st Vet date]),[Learner 1st Monitor Due])

If it is false, then [Learner 1st Monitor due] is blank, good.

if it is false, how do I add to the end of it telling it to move onto the next if statement:

=IIf([Risk Low],DateAdd("d",91,[1st Vet date]),[Learner 1st Monitor Due])

else=IIf([Risk Medium],DateAdd("d",56,[1st Vet date]),[Learner 1st Monitor Due])

Tried changing the else,= and iff around but does not work.

Regards

Nigel

 

[vbaInet]

Putting into consideration [medium], you can use either one of:

=IIF(Not IsDate([first vet date]), Null, DateAdd("d", iif([low], 56, iif([medium], 22, 0)), iif(isnull([low] & [medium]), Null, [first vet date])))
 
=IIF(Not IsDate([first vet date]), Null, iif(nz([low], 0), dateadd("d", 56, [first vet date]), iif(nz([medium], 0), dateadd("d", 22, [first vet date]), null)))

If you break it down into a function you will be able to see the different parts of the IIF() function. A simple explanation would be:

If [first vet date] Is Not a Date Then 
     return Null
Else 
     If [low] Is True Then
          Add 56 days to [first vet date]
     Else If [medium] Is True Then
          Add 22 days to [first vet date]     
     Else
          return Null
     End if
End If

But bear in mind, this is a calculated value and should not be stored. Well, as it is it won't get stored in [Monitor1] anyway.

 

[nigel brown]

Putting into consideration [medium], you can use either one of:

=IIF(Not IsDate([first vet date]), Null, DateAdd("d", iif([low], 56, iif([medium], 22, 0)), iif(isnull([low] & [medium]), Null, [first vet date])))
 
=IIF(Not IsDate([first vet date]), Null, iif(nz([low], 0), dateadd("d", 56, [first vet date]), iif(nz([medium], 0), dateadd("d", 22, [first vet date]), null)))

If you break it down into a function you will be able to see the different parts of the IIF() function. A simple explanation would be:

If [first vet date] Is Not a Date Then 
     return Null
Else 
     If [low] Is True Then
          Add 56 days to [first vet date]
     Else If [medium] Is True Then
          Add 22 days to [first vet date]     
     Else
          return Null
     End if
End If

But bear in mind, this is a calculated value and should not be stored. Well, as it is it won't get stored in [Monitor1] anyway.

Hi and thanks for the work you put in to this, I have added the two above codes in to the control source on two unbound text boxes and it does not work. I do not want to amend the code as it is way above my understanding and would not do any good.

But thanks very much.

Nigel
VBA virgin

 

[nigel brown]

=IIf([Risk Low],DateAdd("d",91,[1st Vet date]),[Learner 1st Monitor Due])

The above code works, however if the risk in not low but medium, I have no way of understanding the additional or extra code required.

Regards

Nigel

 

[vbaInet]

We need more information. Is it showing #Error? Or just showing nothing?

 

[nigel brown]

We need more information. Is it showing #Error? Or just showing nothing?

Hi
It is showing #Name?

Nigel

 

[nigel brown]

do you want me to attach the database, it is small as using a test bed at the minute?

Regards

Nigel

 

[nigel brown]

Hold on, I just noticed a name [ ] was incorrect.

Just amended and it looks ok, will report back shortly

Nigel

 

[vbaInet]

#Name meaning one or more of the field names is/are incorrect.

E.g.

I have [first vet date] and you have [1st vet date]

Amend the field names in the code.

 

[nigel brown]

It is working after amending the field name on one area.

Just testing it with various dates, but it is looking good so far.

Will keep you posted.

Regards
Nigel
VBA Virgin