Have Geek Brain

How to create an Access textbox with a null value equal to DLookup with TextBox as criterion

I really don't know why this isn't working, but I'm 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 test code for a larger project I'm working on, so the table and field names are for testing purposes only. In addition, Field1 is a numeric field. Basically, when I enter 1 in Text1, I would like the DLookup function to find 1 in Test1_Table.Field1 and give me a message that it is working. I can get this to work with string values ​​like:

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

It works similarly when 1 is entered into Text1:

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

However, I 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 have tried many different combinations and it seems that I cannot get them to equal when dealing with numeric values. Does anyone know what I don't have or ideas?

Thanks,

Damion

0


a source to share


3 answers


What about:

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

This will only work if field 1 is defined as a numeric field, you need separators if it is a date or time field.

EDIT:



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

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

SomeVar will either be null, meaning not found, or it will return a value or field1 that is equal to Me.Text1, because that's what we asked for in the Where statement. You can see from this that it is pointless to return Field1, it is either found, or equal to text1, or not found and null. The olive oil reason for getting the DlookUp value is when you are viewing another value or calculation in the table.

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

+2


a source 


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 many vba inside DLookup could be a problem?

0


a source


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

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

Also, thanks to everyone for your input.

DFM

0


a source






More articles:


All Articles