How to make an Access TextBox, with a Numberic Value, Equal to DLookup with the TextBox as the Criteria

Question

I really have no idea why this doesn't work, but I am trying to get the following to work:

If Me.Text1 = DLookup("Field1", "Test1_Table", "Test1_Table.Field1= _
     & Me.Text1") Then
   MsgBox "It works"
Else
End If

The above code is a test code for a larger project that I am working on, so the table and field names are just for testing. Also, Field1 is a numberic field. Basically, when I enter 1 within Text1, I would like the DLookup feature to find 1 within Test1_Table.Field1 and give me a message stating that it works. I am able to get this to work with string values such as:

If Me.Text1 = DLookup("Field1", "Test1_Table", "Test1_Table.Field1='" _
    & Me.Text1 & "'")Then

Likewise this works, when 1 is entered in the Text1:

If 1 = DLookup("Field1", "Test1_Table", "Test1_Table.Field1= _
    & Me.Text1") Then

However, I've tried:

If Me.Text1 = DLookup("Field1", "Test1_Table", "Test1_Table.Field1= _
    & Forms!TestSearch_Form!Text0)Then

and

If Me.Text1 = DLookup("Field1", "Test1_Table", "Test1_Table.Field1= _
    & Forms!TestSearch_Form!Text0)Then

etc...

I've tried many different combinations and it seems as though I cannot get the two to equal, when dealing with numeric values. Does anyone know what I am missing or have any ideas?

Thank you,

Damion

Answer 1

Dim sWhere as String
sWhere = "Test1_Table.Field1=" & Me.Text1
If CInt(Me.Text1)=DLookup("Field1", "Test1_Table", sWhere) Then         
     MsgBox "It works" 
Else
   'do something else
End If

Trying to do too much vba inside the DLookup could be an issue?

Answer 2

How about:

 If Not IsNull(DLookup("Field1", "Test1_Table", "Field1=" & Me.Text1)) Then

This will only work if Field1 is defined as a numeric field, you will need delimiters if it is a datetime or text field.

EDIT:

The above statement is either equal to the value of Me.Text1, or is null. Another way to use DllookUp would be to say:

 SomeVar=DLookup("Field1", "Test1_Table", "Field1=" & Me.Text1)

SomeVar will be either null, that is, not found, or return the value or Field1, which is equal to Me.Text1, because that is what we asked for in the Where statement. You can see from this that it is pointless to return the value of Field1, it is either found andequal to text1, or not found, and null. The olnly reason for getting the value of DlookUp is if you are looking up some other value or calculation in the table.

After this, it is important to remember that you are looking for an exact match and decimal values can be quite different far to the right of the decimal point, where you are unlikely to look.

Answer 3

If CInt(Me.Text1) = DLookup("Field1", "Test1_Table", "Test1_Table.Field1=" & Me.Text1) Then...

The above code works perfectly. Thanks to Guiness, I was able to utilize my original code and just add CInt to the if statement.

Also, thank you everyone for your input.

DFM