How can I adjust the two statements to handle the null values? (1 Viewer)

[JGravesNBS]

Dim ProcAllowanceNew As Currency
Dim EffectiveDate As Date

The following code works OK if there are values in Column(13) & (14)

ProcAllowanceNew = Nz(Me.[strVADA].Column(13))
EffectiveDate = Nz(Me.[strVADA].Column(14))

If Column(13) & (14) are empty I'm getting Run-time error '13' Type mismatch

How can I adjust the two statements to handle the null values?

 

[plog]

How'd you come upon the Nz function?

You are on the right track, just check out the documentation for it: https://www.techonthenet.com/access/functions/advanced/nz.php

 

[NauticalGent]

Quick observation JGravesNBS,

The "Me." reference is used in Forms. For example, if you had a control on your form named Column(13), you would refer to it in this way:

Me!Column(13) or Me.Column(13) depending on whether it is a control or RS field. You code appears to me referenceing a filed in a recordset, in which case you would not use the Me.(!) reference.

Can you provide more detail as to what is going on?

 

[JGravesNBS]

Dim ProcAllowanceNew As Currency

Me.[strVADA] is a combo box on a form

I changed as follows and still get Run-time error '13' Type mismatch

ProcAllowanceNew = Nz(Me.[strVADA].Column(13), 0)

When I hover mouse over ProcAllowanceNew = 0

Where am I going wrong?

 

[plog]

Are you certain this line is causing the error:

ProcAllowanceNew = Nz(Me.[strVADA].Column(13), 0)

Seems if ProcAllowanceNew is assigned a value, it worked.

 

[JGravesNBS]

Yes, that is the statement that is highlighted yellow in Debug

When I change as follows:

ProcAllowanceNew = Nz(Me.[strVADA].Column(13), 1.25)

When I hover mouse over ProcAllowanceNew = 0

 

[CJ_London]

just to confirm - when you say 'If Column(13) & (14) are empty ' do you mean the columns are not included (i.e. columncount property<13 or your rowsource is not returning all the columns) or do you mean the columns are there but there is no value for the row selected.

And also to confirm, columns start from 0 - so you don't mean to reference columns 12 and 13?

When I change as follows:

ProcAllowanceNew = Nz(Me.[strVADA].Column(13), 1.25)

When I hover mouse over ProcAllowanceNew = 0

it won't be assigned a value until the line has been executed. If highlighted, the line has not been executed

What do you get if you enter

?Nz(Me.[strVADA].Column(13), 1.25)

in the immediate window?

 

[JGravesNBS]

The Column(13) & (14) are there but there is no value for the row selected.

The following code works OK if there are values in Column(13) & (14)

Compile error: Variable not yet created in this context on the following

?Nz(Me.[strVADA].Column(13), 1.25)

 

[CJ_London]

sounds like you might be trying to reference the combo before it has been properly populated - you are running this when the code is at a break point?

 

[JGravesNBS]

I had to move on a abandon my request above with the following solution:

If NewAllowance Then
    EffectiveDate = Nz(Me.[strVADA].Column(14), Null)     'dteEffectiveDate
    If CDate(Me.dteVDATE) >= CDate(EffectiveDate) Then
        ProcAllowanceNew = Nz(Me.[strVADA].Column(13), 0)  'curProcAllowanceNew
        Me.curVBEN = ProcAllowanceNew
    End If
End If

Thanks for your help