How To Keep Null As Null

[taccoo73]

Hi All;

First of all, thanks for your time in advance. Got stuck on Null issue. The code I'm trying to use as follows;

Public Sub ReadItemEur(Item As ItemRecord)
Dim Db As Database, Rs As Recordset
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("RecordsEur")
Item.NoMatch = False
 
    With Rs
        .Index = "PrimaryKey"
        .Seek "=", Item.TrId
        If .NoMatch Then Item.NoMatch = .NoMatch: Exit Sub
        Item.TrId = !TrId
        Item.Mnt = IIf(IsNull(!Mnt), "", !Mnt)
        Item.RDate = !RDate
        Item.RTime = IIf(IsNull(!RTime), "", !RTime)
        Item.Category = !Category
        Item.SubCat = !SubCat
        Item.CustId = IIf(IsNull(!CustId), "", !CustId)
        Item.CustName = IIf(IsNull(!CustName), "", !CustName)
        Item.ForToId = IIf(IsNull(!ForToId), "", !ForToId)
        Item.ForToName = IIf(IsNull(!ForToName), "", !ForToName)
        Item.Explanations = IIf(IsNull(!Explanations), "", !Explanations)
        Item.Related = IIf(IsNull(!Related), "", !Related)
        Item.CatCode = !CatCode
        Item.SCCode = !SCCode
        Item.Chip = IIf(IsNull(!Chip), "", !Chip)
        Item.In = IIf(IsNull(!In), "", !In)
        Item.Out = IIf(IsNull(!Out), "", !Out)
        Item.Balance = !Balance
        Item.Mnth = !Mnth
        Item.RptDate = !RptDate
    End With
 
Rs.Close
Db.Close
End Sub

When I run the code, following line gives me the error 13 Type Mismatch. I have a hunch that it has something to do with the field being numeric;

Item.Mnt = IIf(IsNull(!Mnt), "", !Mnt)

Mnt field is in the table named RecordsEur and it's a combobox which is reading the records from another table. Data Type of the Mnt field in the table is Number.

Code above reads the selected record in a subform and fills the fields in another subform for editing. Actually, Mnt field of the selected record is Null both in the table and the subform. Code also recognise it as Null but during the code execution Null becomes 0. Item.Mnt shows 0 while (!Mnt) and !Mnt are showing Null when you hover on them during the codebreak view.

Any help will be greatly appreciated.

Best Regards;

tac

 

[sheetspread]

Does the Nz function help?

 

[taccoo73]

Hi sheetspread;

Thanks for your input. Nope, that does not help, it does exactly what I'm trying to get rid of, it transforms the Null to Zero.

 

[sheetspread]

If you change all the nulls to 0s does that help? Do you need the nulls at all?

 

[james_lankford]

(Item As ItemRecord)

what is an ItemRecord ?


"I have a hunch that it has something to do with the field being numeric;
Data Type of the Mnt field in the table is Number."

are you saying Item.Mnt is a numeric field ?

if it is then this
Item.Mnt = IIf(IsNull(!Mnt), "", !Mnt)

tries to assign an empty string to a numeric -- so type mismatch

I don't actually understand your post
your title is "How To Keep Null As Null"
but this
Item.Mnt = IIf(IsNull(!Mnt), "", !Mnt)
is not keeping a null as null
its checking to see if !Mnt is null and if it is it tries to assign an empty string to Item.Mnt
if you really want to keep null as null then Item.Mnt should be a variant and you should just assign !Mtn to Item.Mnt
that way if !Mtn is null then null will be assigned to Item.Mnt