[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

NZ in VBA using a date

Posted on 2010-03-25
24
Medium Priority
?
1,424 Views
Last Modified: 2013-11-27
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.
0
Comment
Question by:mtrussell
  • 6
  • 6
  • 5
  • +2
24 Comments
 
LVL 8

Expert Comment

by:Joe Overman
ID: 28571207
If Nz([First]) = "" Then

Should work or

If isnull([First]) = True Then
0
 
LVL 11

Expert Comment

by:Runrigger
ID: 28571224
if IsNull([First]) or IsEmpty([First]) then
do calc 2
else
do calc 1
End if
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 28571370
test this

If Nz([First],0) = 0 Then
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 11

Expert Comment

by:Runrigger
ID: 28571407
actually in Access SQL language its;

iif(or(IsNull([First]),[First]=""),"calcl1","calc2")
0
 
LVL 75
ID: 28575817
Gee ... how about:

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

End If

mx
0
 
LVL 11

Expert Comment

by:Runrigger
ID: 28577027
I knew that there was a reason for not developing anymore!
0
 
LVL 75
ID: 28580027
oops typo ... extra '

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

End If

mx
0
 

Author Comment

by:mtrussell
ID: 28581438
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
 
LVL 8

Expert Comment

by:Joe Overman
ID: 28581534
What is the error?
0
 
LVL 75
ID: 28581808
What is the calculation where this is used ... and did you try this @ http:#a28580027  ?

mx
0
 

Author Comment

by:mtrussell
ID: 28581837
I'm not being difficult... that is part of the problem... it says; #Error.

Not a lot to go on.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 28581847
mtrussell,

post the codes with the calculation..
0
 

Author Comment

by:mtrussell
ID: 28581961
yeah, I tried before responding -

oops typo ... extra '

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

End If
 
0
 
LVL 8

Expert Comment

by:Joe Overman
ID: 28582262
Try using
me.first or forms![formname].first
instead of
[first]
0
 
LVL 11

Expert Comment

by:Runrigger
ID: 28582409
#Error indicative of a value in a form control???
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 28582816
without the OP giving the complete scenarion we will all need a Crystal ball to figure out the problem... unsuscribing
0
 
LVL 11

Expert Comment

by:Runrigger
ID: 28582951
indeed, author please attach a copy of the DB, we may then be able to finalise the solution for you!
0
 

Author Comment

by:mtrussell
ID: 28583206
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
 
LVL 75
ID: 28583460
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 28583549
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
 
LVL 11

Expert Comment

by:Runrigger
ID: 28583871
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
 
LVL 8

Expert Comment

by:Joe Overman
ID: 28583897
Simply change to
if IsDate(First) = true then
0
 
LVL 75
ID: 28584630
Summarizing ...

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

mx
0
 

Author Closing Comment

by:mtrussell
ID: 31707131
Thanks!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question