Hi everybody, I am trying to fin

[FCCC]

Hi everybody,

I am trying to find a duplicate number on Item_No field but my code always returns NOT NULL even there was no duplicates. Actually I only have 1-30 numbers on my Item_No field. Can someone tell me what is wrong with my code below?

Dim FindItemNo As Variant
NewItemNo = Item_No + 100
FindItemNo = DLookup("[Item_No]", "dbo_PMOption_History", NewItemNo)

If IsNull(FindItemNo) Then
Item_No = NewItemNo
Else
Item_No = NewItemNo + 50
End If

Do I need to Dim NewItemNo? Any ideas will be very much appreciated.

Thanks.

 

[thornmastr]

It is not necessary to post the same question twice. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[Junior1544]

Dim FindItemNo As Integer
NewItemNo = Item_No + 100
FindItemNo = DLookup("[Item_No]", "dbo_PMOption_History", NewItemNo)

If FindItemNo > 0 Then
Item_No = NewItemNo
Else
Item_No = NewItemNo + 50
End If
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[FCCC]

Thanks for your ideas.

I tried to change Variant to Integer but it still do the same as my code. Do you still have any more ideas?

 

[Junior1544]

make a duplicates query...

just another idea.

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[VanJack]

have you tried using:

msgbox FindItemNo

This will tell you whether your dlookup is returning nulls or zeros. That way you can then work out whether the isnull or >0 is the best bet....or have your if statement include both tests.

The msgbox function is a really helpful way to get an idea of what the variable contains.

Jack