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.
mtrussellAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Joe OvermanEngineerCommented:
If Nz([First]) = "" Then

Should work or

If isnull([First]) = True Then
0
RunriggerCommented:
if IsNull([First]) or IsEmpty([First]) then
do calc 2
else
do calc 1
End if
0
Rey Obrero (Capricorn1)Commented:
test this

If Nz([First],0) = 0 Then
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

RunriggerCommented:
actually in Access SQL language its;

iif(or(IsNull([First]),[First]=""),"calcl1","calc2")
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Gee ... how about:

If IsDate([First') Then
   ' do this
Else
  ' Do dat

End If

mx
0
RunriggerCommented:
I knew that there was a reason for not developing anymore!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
oops typo ... extra '

If IsDate([First]) Then
   ' do this
Else
  ' Do dat

End If

mx
0
mtrussellAuthor Commented:
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.
0
Joe OvermanEngineerCommented:
What is the error?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
What is the calculation where this is used ... and did you try this @ http:#a28580027  ?

mx
0
mtrussellAuthor Commented:
I'm not being difficult... that is part of the problem... it says; #Error.

Not a lot to go on.
0
Rey Obrero (Capricorn1)Commented:
mtrussell,

post the codes with the calculation..
0
mtrussellAuthor Commented:
yeah, I tried before responding -

oops typo ... extra '

If IsDate([First]) Then
   ' do this
Else
  ' Do dat

End If
 
0
Joe OvermanEngineerCommented:
Try using
me.first or forms![formname].first
instead of
[first]
0
RunriggerCommented:
#Error indicative of a value in a form control???
0
Rey Obrero (Capricorn1)Commented:
without the OP giving the complete scenarion we will all need a Crystal ball to figure out the problem... unsuscribing
0
RunriggerCommented:
indeed, author please attach a copy of the DB, we may then be able to finalise the solution for you!
0
mtrussellAuthor Commented:
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



0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
And don't use Me.First

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([First]) = True Then
   
        Select Case Compound
       
        Case 1
       
        ShortInterest = Principal * ((Interest / YearC) * Dcount) + Principal

        Else

        ....


       End if
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RunriggerCommented:
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
0
Joe OvermanEngineerCommented:
Simply change to
if IsDate(First) = true then
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Summarizing ...

1) Pass 'First' as a Variant because it apparently can be Null.
2) Test  using
If IsDate([First]) = True Then

mx
0
mtrussellAuthor Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.