typemismatch.. possibly because its null?? 1

[wshs]

Private Sub List6_Click()

Me!txtbal = Me!List6.Column(6) - Nz(Me!txtAmount, 0)

end sub

when i run my update query, it seems like it leaves $0 as null. by that i mean when i look at the table, if the calculation is $0, then field doesnt return $0 but blank.

so i get an error message when i select something from the listbox which has null(column 6) box.

is there a way to make all null = $0?
or not make this error message pop up?

 

[SuryaF]

I see you're using the nz function already.
You can use:
Me!txtbal = nz(Me!List6.Column(6),0) - Nz(Me!txtAmount, 0)

This will still give you an error when the column is empty but you have an item in the list. At that moment the Column(6) property returns "" (0 length string).
See if this helps:

Me!txtbal = Val("0" & Me!List6.Column(6)) - Nz(Me!txtAmount, 0)

 

[SuryaF]

Or you can edit the control source quesry of the list and put the nz() on the 7th column. This way it will never be empty.

 

[wshs]

ty suryaf. it seems like that did the trick.

can u explain what u mean about ur last post?

it would be better to have them $0..

i have a query

UPDATE tbl_Fsource SET tbl_Fsource.Totex = [cashd]+[voupa]+[payrl]+[contp], tbl_Fsource.Balan = [fcast]-[totex];

i think its when i run this query, if fcast returns 0,
it returns balance as null or seomthing...

 

[SuryaF]

I don't understand the succession of actions but I assume you're running the update query that fills a table and that table is the source of the list.
I'm sure the process can be improved but looking simply at that part of the UPDATE statement I can only speculate that your update doesn't work because of the [totex] field.
If originally the [totex] field is empty then your UPDATE statement tbl_Fsource.Balan = [fcast]-[totex] will generate NULL, doesnt' matter that you include the tbl_Fsource.Totex = [cashd]+[voupa]+[payrl]+[contp] there.
If you want the update to run properly, see if doing tbl_Fsource.Balan = [fcast]- ([cashd]+[voupa]+[payrl]+[contp]) does the trick.
Cheers.