Date with null

[theinviter]

Hi Guys;

nee help

i have created a form and when i select from drop list the item name the exp date field will be updated based on the number with each drop list but if the number is null i got error " Type Mismatch",


the code is:
Private Sub Name_AfterUpdate()

BUD = name.Column(3)
exp_date = Date + BUD

End Sub

i wanna if the number is null then Exp. date will be only current date.

please advise me how to resolve this issue

 

[pbaldy]

Try

BUD = Nz(name.Column(3), 0)

Name is not a good field/control name as it is a reserved word. It can conflict with the Name property of an object.

 

[theinviter]

i changed to Item_Name but still same issue
i got this line highlighted yellow:
exp_date = Date + BUD

Error " Type Mismatch"

 

[pbaldy]

Did you try the Nz() function I suggested?

 

[theinviter]

yes i tried but the same issue

 

[theinviter]

this is the code

Private Sub Drug_Name_AfterUpdate()
Order_Date = Now


BUD = Nz(Item_name.Column(3), 0)
exp_date = Date + BUD

End Sub

 

[pbaldy]

What is BUD? I assume a variable, but it isn't declared here. What exactly does that combo column contain? If it's text, you might need to convert it

BUD = CInt(Nz(Item_name.Column(3), 0))

Do you know how to set a breakpoint and step through the code, seeing what all the objects contain at runtime?

 

[theinviter]

BUD its variable contain a number , once i select form drop list item name BUD will get update with number it represent .

please this modified code :
Private Sub item_Name_AfterUpdate()
Order_Date = Now

BUD = CInt(Nz(item_name.Column(3), 0))

exp_date = Date + BUD


End Sub

run time Error "13" type mismatch

 

[pbaldy]

On what line? Can you attach the db here?

 

[theinviter]

the error in this line

exp_date = Date + BUD

cant attached its big size.

 

[theinviter]

i changed the field to drug_name

 

[pbaldy]

From the image it appears the combo has a zero length string rather than Null. Perhaps something like

If Len(drug_name.Column(3) & vbNullString) > 0 Then
  BUD = CInt(drug_name.Column(3))
Else
  BUD = 0
End If

 

[theinviter]

thank you so much
solve successfully

 

[pbaldy]

Happy to help! There's a one-line solution along the lines of:

BUD = IIf(Nz(drug_name.Column(3), "") = "", 0, CInt(drug_name.Column(3)))