[Webinar] Streamline your web hosting managementRegister Today

x
Solved

# NZ in VBA using a date

Posted on 2010-03-25
Medium Priority
1,424 Views
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
Question by:mtrussell
• 6
• 6
• 5
• +2

LVL 8

Expert Comment

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

Should work or

If isnull([First]) = True Then
0

LVL 11

Expert Comment

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

LVL 120

Expert Comment

ID: 28571370
test this

If Nz([First],0) = 0 Then
0

LVL 11

Expert Comment

ID: 28571407
actually in Access SQL language its;

iif(or(IsNull([First]),[First]=""),"calcl1","calc2")
0

LVL 75

Expert Comment

ID: 28575817

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

End If

mx
0

LVL 11

Expert Comment

ID: 28577027
I knew that there was a reason for not developing anymore!
0

LVL 75

Expert Comment

ID: 28580027
oops typo ... extra '

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

End If

mx
0

Author Comment

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

ID: 28581534
What is the error?
0

LVL 75

Expert Comment

ID: 28581808
What is the calculation where this is used ... and did you try this @ http:#a28580027  ?

mx
0

Author Comment

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

ID: 28581847
mtrussell,

post the codes with the calculation..
0

Author Comment

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

ID: 28582262
Try using
me.first or forms![formname].first
[first]
0

LVL 11

Expert Comment

ID: 28582409
#Error indicative of a value in a form control???
0

LVL 120

Expert Comment

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

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

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

Expert Comment

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

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

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

ID: 28583897
Simply change to
if IsDate(First) = true then
0

LVL 75

Expert Comment

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

ID: 31707131
Thanks!
0

## Featured Post

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
Course of the Month8 days, 17 hours left to enroll