DLOOKUP Criteria as a StringName 1

[Phil4tektips]

I've got this code:

If ([Reported by] = ".") Then
[User Opened] = strUserName
[Project] = DLookup("[Project]", "Reported by List", "[Clock Number] = 's02506'")
End If

The code above is correct for looking up a text string and works. Trouble is I need the criteria of the DLOOKUP to be a string name called 'strUserName' which is actually assigned by looking at the users login details.

I tried changing criteria to:
"[Clock Number] = 'strUserName'"), but that fails because it just looking for the text strUserName rather than what is assigned to strUserName which is s02506.

I've also tried:
"[Clock Number] = '" & Forms![Main Data form]![User Opened] & "'")
In other words lookup up the text string from a field on the form. But the error is: "Syntax error in string in query expression '[Clock Number] = 's02506'.

Any ideas on how to code this correctly?

Thanks!!

-Phil4tektips-

 

[Remou]

Hi
[tt][Project] = DLookup("[Project]", "Reported by List", "[Clock Number] = '" & strUserName & "'")[/tt]

 

[Phil4tektips]

I've also tried this which is teh code for looking up a string name:

"[Clock Number] = '" & strUserName & "'")

But the error is: "Syntax error in string in query expression '[Clock Number] = 's02506'.

I'm bemused!

 

[Phil4tektips]

Thanks Remou,

Just tried that and as you can see i'm still getting that error!

...

 

[Remou]

A couple of questions
What happens when you debug.print
"[Clock Number] = '" & strUserName & "'"

Is [Clock Number] a text field?

 

[Phil4tektips]

If ([Reported by] = ".") Then
[User Opened] = strUserName
[Project] = DLookup("[Project]", "Reported by List", "[Clock Number] = '" & strUserName & "'")
Debug.Print
End If

Like this you mean?

[Clock Number] is a text field

 

[Remou]

No, I was not clear
Debug.Print "[Clock Number] = '" & strUserName & "'"

 

[Phil4tektips]

That causes a syntax error in VB:

If ([Status List] = "Closed") Then
[User Closed] = strUserName
End If
If ([Reported by] = ".") Then
[User Opened] = strUserName
[Project] = DLookup("[Project]", "Reported by List", Debug.Print "[Clock Number] = '" & strUserName & "'")
End If

The [Project] line of code goes red and highlights debug saying: "Compile Error: Expected Expression

 

[Remou]

Still not clear

If ([Status List] = "Closed") Then
    [User Closed] = strUserName
    End If
    If ([Reported by] = ".") Then
    [User Opened] = strUserName
    'commenting out line below
    '[Project] = DLookup("[Project]", "Reported by List", "[Clock Number] = '" & strUserName & "'")
    'Using debug.print to check contents due to error
    Debug.Print "[Clock Number] = '" & strUserName & "'")
End If

(You will need to see the Immediate Window (ctrl + G) for the results of Debug.Print)

If you get a syntax error in a line like the Dlookup code you are using, it is often because a field is being treated as numeric when it is text, or as text when it is numeric. I mocked up a form and table from your information, and your code worked for me. So I am trying to find out what I have missed.

 

[PHV]

The correct syntax:
[Project] = DLookup("Project", "[highlight][[/highlight]Reported by List[highlight]][/highlight]", "[Clock Number]='" & strUserName & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Phil4tektips]

Nothing came up in the immediate window.

I've called some code before this which retrieves the users username from their windows login. The username is assigned to 'strUserName'.

[Clock Number] is a text field.

An interesting thing I found was when i tried to write 'strUserName' to a text field called [User Opened] on the form it would fail saying there wasnt enough characters, I entered 255 characters and it still failed. So after that I made it a memo and this then showed the users clock number (S02506) on the form.

Could this bear some light on what is happening here. That the strUserName cannot be used as a criteria for the [Clock Number] since it cant even be written to a text field itself? The odd thing is when its written to a memo it just comes out as S02506 (six characters!!)

Does that make sense?

 

[Phil4tektips]

PHV - That still comes up with the same error. I proved that the other syntax worked by hardcoding in s02506, and that worked and produced the desired result. I have amended to the syntax you've suggested though.

Problem seems to be with the fact that s02506 is assigned to strUserName, which doesnt seem compatible with a text field?

 

[PHV]

And this ?
[Project] = DLookup("Project", "[Reported by List]", "[Clock Number]='" & Trim(strUserName) & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Phil4tektips]

The same error message!?!

 

[PHV]

And this ?
If InStr(strUserName, Chr(0)) Then
strUserName = Left(strUserName, InStr(strUserName, Chr(0)) - 1)
End If
[Project] = DLookup("Project", "[Reported by List]", "[Clock Number]='" & strUserName & "'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Phil4tektips]

EXCELLENT!!!

That works a treat!!

What was wrong then? I dont really understand what your code is doing there?

-Phil4tektips-

 

[Phil4tektips]

Thankyou Remou and thankyou PHV for figuring this one out!

Had me stumped for ages!

 

[PHV]

I've called some code before this which retrieves the users username from their windows login.
I guess you get the windows login by some API call and you forgot to strip the trailing null chars (Chr(0)).
If you don't have any win9x box you may simply use:
strUserName = Environ("UserName")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Phil4tektips]

Yeah that was what I had done!

Well spotted sir. You must have been doing this for a while!

-Phil4tektips-