DLOOKUP STATEMENT PLEASE HELP 1

[zishan619]

HI Can anyone tell me where is the error in this statement Please.
DLookup("[NT]", "tblAssignment", "[NT] = " & Forms!MainMenu!WinUserName & &quot
Thank you
Z

 

[Maquis]

Try this instead:

DLookup("[NT]", "tblAssignment", "[NT] = '" & Forms!MainMenu!WinUserName & "'&quot


You need quotes around the WinUserName field. (I'm assuming this is a text field and not a number.)

Maq
<insert witty signature here>

 

[zishan619]

Thank you Maq But now I am getting a Type Mismatch error. It is highlighting the first row:
If DLookup(&quot;[NT]&quot;, &quot;tblAssignment&quot;, &quot;[NT] = '&quot; & Forms!MainMenu!WinUserName & &quot;'&quot Then
Me.Start_Record = Me![tblAssignment].Form![Start_Record]
Else
msgbox &quot;No work assign For You&quot;
End If
WinUserName is a textbox.

 

[zishan619]

Actually I figured it out. I needed a space between the ' &quot; & Forms!MainMenu!WinUserName. Cool Thank you

 

[Poteen]

zishan619
IMPORTANT ADDENDUM....ref your original line:your syntax is still wrong!!!!The effect will be unpredictable.

DLookup(&quot;[NT]&quot;, &quot;tblAssignment&quot;, &quot;[NT] = &quot; & Forms!MainMenu!WinUserName & &quot

SHOULD BE:

DLookup(&quot;[NT]&quot;, &quot;tblAssignment&quot;, &quot;[NT] = &quot; & Forms!MainMenu!WinUserName)

Maquis: Your irk-dirk method ('&quot is only used in when coding VBA SQL insert commands and such like.

Regards,
john

 

[Maquis]

Sorry, I have to disagree. If NT is a text field then it needs the quotes around it. However, if NT is a number then Poteen is correct and putting the quotes around it will hurt the results.

But, an added note. I am using Access97. It's possible that the syntax has changed with Access2000 and beyond and what Poteen said may be correct for those versions. I know a lot of the syntax did change with the Access2000 upgrade.

Maq
<insert witty signature here>

 

[zishan619]

Hi By doing it your way I am getting a Runtime Error 64479...Which States The eexpression you enter as a query parameter product this error : 'The object doesn't contain the automation object &quot;ZRAZZ&quot;
And Then I tried Mar's way and it skips over the statement and think that it is false when instead it is true.
I do not know what to do.
I tried
If DLookup(&quot;[NT]&quot;, &quot;tblAssignment&quot;, &quot;[NT] = &quot; & Me!WinUserName) Then
OR
If DLookup(&quot;[NT]&quot;, &quot;tblAssignment&quot;, &quot;[NT] = '&quot; & Me.WinUserName & &quot;'&quot Then
This statements is the one that makes it think its false when it is true.

 

[Maquis]

What exactly is the &quot;NT&quot; field? Is it a text field? If so, why are you using it as a boolean? If it is a boolean then don't try to match it to a text field on your form.

If all you need is to determine whether or not a record exists where NT = the field on your form, then I would recommend using the DCount function instead.

If DCount(&quot;[NT]&quot;, &quot;tblAssignment&quot;, &quot;[NT] = '&quot; & Me!WinUserName & &quot;'&quot > 0 Then

or if you prefer using the Dlookup function then:
If Not(IsNull(DLookup(&quot;[NT]&quot;, &quot;tblAssignment&quot;, &quot;[NT] = '&quot; & Me!WinUserName & &quot;'&quot)) Then

And again the examples above assume that &quot;NT&quot; is a text field.



Maq
<insert witty signature here>

 

[zishan619]

You Get a Star Thank you for explaining it to me and it worked. Thanks