dlookup headache 1

[scottian]

I have tried to search for a solution here but nothing seems to work
Im trying to validate a value in a text box on my form.

ive tried both of these:-
'If Nz(DLookup("[MyNumber]", "MyTable", "[MyNumber]=(Forms![MyForm]![MyFormNumber])")) Then
msgbox "good"
else
msgbox "bad"
end if

If IsNull(DLookup("[MyNumber]", "MyTable", "[MyNumber]=(Forms![MyForm]![MyFormNumber])")) Then
msgbox "good"
else
msgbox "bad"
end if

When i use Nz it tells me the number is in the table
whether it is or isnt.

When i use IsNull it does the exact opposite and tells me the number is not in the table .

does anyone know what im doing wrong?

"My God! It's full of stars...

 

[Remou]

The problem is with the Where statement:

If IsNull(DLookup("[MyNumber]", "MyTable", "[MyNumber]=" & (Forms![MyForm]![MyFormNumber]))) Then
'Is Null = Not found
msgbox "No Luck"

else
msgbox "Got It!"
end if

This is assuming that MyNumber is numeric, date and text fields need delimiters.

 

[scottian]

Using this code,
If the box is empty, i get a runtime error '3075' missing operator

if a number is supplied it still fails to find it in the table whether its in the table or not.

Sorry if i being a pain in the neck
Here is the pseudo code im using.

========================================
If IsNull(Me![MyNumber]) Then

MsgBox "Fill in the MyNumber"

Else

If IsNull(DLookup("[MyNumber]", "MyTable", "[MyNumber]= " & Forms![MyForm]![MyNumber]))) Then

MsgBox "MyNumber does not exist within this database. Please check the number is correct”

Else

EmailConfirm = MsgBox("You have chosen to accept this number " & Me![MyNumber], vbOKCancel

If EmailConfirm = vbOK Then

With ObjEmail

Send email to confirm number has been accepted

Else
MsgBox "Action Cancelled"
End If
End If
End If
Requery and refresh the form
End Sub


"My God! It's full of stars...

 

[Remou]

It sounds like MyNumber is text. Have a look in the table. If it is text, use single quotes:

[tt]If IsNull(DLookup("[MyNumber]", "MyTable", "[MyNumber]= '" & Forms![MyForm]![MyNumber] & "'")) Then[/tt]

You had an extra ) at the end, I wonder that Access did not complain. If this is running on the form, Me. will be enough.

Your first test could be improved. Try:
[tt]If Trim(Me![MyNumber] & "")<>"" Then[/tt]