mtrussell
asked on
NZ in VBA using a date
I am trying to do a pretty simple If statement in VBA and it is kicking out an error. I have researched it and it looks like it has to do with the fact the nz field is a date. Basically what I am trying to do is if a date is left null on the form to do one calculation and if there is a date do another calculation.
The code starts with: If Nz([First]) = True Then
[First] is defined as a Date in the Function. I was reading that the NZ returns a 0 value which VBA considers to be a date so I played around with the return value a bit without success. If I put in a date in the field then the calculation kicks out a result.
How do I write this first line of code when a date is involved?
Any assistance with this would be appreciated.
The code starts with: If Nz([First]) = True Then
[First] is defined as a Date in the Function. I was reading that the NZ returns a 0 value which VBA considers to be a date so I played around with the return value a bit without success. If I put in a date in the field then the calculation kicks out a result.
How do I write this first line of code when a date is involved?
Any assistance with this would be appreciated.
if IsNull([First]) or IsEmpty([First]) then
do calc 2
else
do calc 1
End if
do calc 2
else
do calc 1
End if
test this
If Nz([First],0) = 0 Then
If Nz([First],0) = 0 Then
actually in Access SQL language its;
iif(or(IsNull([First]),[Fi
iif(or(IsNull([First]),[Fi
Gee ... how about:
If IsDate([First') Then
' do this
Else
' Do dat
End If
mx
If IsDate([First') Then
' do this
Else
' Do dat
End If
mx
I knew that there was a reason for not developing anymore!
oops typo ... extra '
If IsDate([First]) Then
' do this
Else
' Do dat
End If
mx
If IsDate([First]) Then
' do this
Else
' Do dat
End If
mx
ASKER
Oddly enough... none of the solutions work... An error in the calculation continues to appear when the field is null but provides the right answer when a date is provided.
What is the error?
ASKER
I'm not being difficult... that is part of the problem... it says; #Error.
Not a lot to go on.
Not a lot to go on.
mtrussell,
post the codes with the calculation..
post the codes with the calculation..
ASKER
yeah, I tried before responding -
oops typo ... extra '
If IsDate([First]) Then
' do this
Else
' Do dat
End If
oops typo ... extra '
If IsDate([First]) Then
' do this
Else
' Do dat
End If
Try using
me.first or forms![formname].first
instead of
[first]
me.first or forms![formname].first
instead of
[first]
#Error indicative of a value in a form control???
without the OP giving the complete scenarion we will all need a Crystal ball to figure out the problem... unsuscribing
indeed, author please attach a copy of the DB, we may then be able to finalise the solution for you!
ASKER
Public Function ShortInterest(Compound As Long, Principal As Double, Spread As Double, bSet As Double, Dcount As Long, YearC As Double, First As Date) As Double
If IsDate(Me.First) = True Then
Select Case Compound
Case 1
ShortInterest = Principal * ((Interest / YearC) * Dcount) + Principal
Else
....
End if
If IsDate(Me.First) = True Then
Select Case Compound
Case 1
ShortInterest = Principal * ((Interest / YearC) * Dcount) + Principal
Else
....
End if
Pass First as a Variant .....
Public Function ShortInterest(Compound As Long, Principal As Double, Spread As Double, bSet As Double, Dcount As Long, YearC As Double, First As Variant) As Double
If IsDate(Me.First) = True Then
Select Case Compound
Case 1
ShortInterest = Principal * ((Interest / YearC) * Dcount) + Principal
Else
....
End if
Public Function ShortInterest(Compound As Long, Principal As Double, Spread As Double, bSet As Double, Dcount As Long, YearC As Double, First As Variant) As Double
If IsDate(Me.First) = True Then
Select Case Compound
Case 1
ShortInterest = Principal * ((Interest / YearC) * Dcount) + Principal
Else
....
End if
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
don't need to test this "me.first"
case test should also be as follows
Select case compound
case A
case B
case Else
End select
full function should therefore be structured as follows
if isdate(first)=true then
"calculation method 1"
select case
case a
case b
end select
else
"calculation method 2"
select case
case a
case b
end select
end if
case test should also be as follows
Select case compound
case A
case B
case Else
End select
full function should therefore be structured as follows
if isdate(first)=true then
"calculation method 1"
select case
case a
case b
end select
else
"calculation method 2"
select case
case a
case b
end select
end if
Simply change to
if IsDate(First) = true then
if IsDate(First) = true then
Summarizing ...
1) Pass 'First' as a Variant because it apparently can be Null.
2) Test using
If IsDate([First]) = True Then
mx
1) Pass 'First' as a Variant because it apparently can be Null.
2) Test using
If IsDate([First]) = True Then
mx
ASKER
Thanks!
Should work or
If isnull([First]) = True Then