Howto? Dlookup("field","SAVED_QUERY","Criteria='string'")

[Jndrline via AccessMonster.com]

Dlookup("field","SAVED_QUERY","Criteria='string'")

I think it's not liking either using a saved query as a table, or it's not
liking that my string/date/number is a calculated expression in the query.

Thanks for your help.

 

[undrline via AccessMonster.com]

According to the first couple of google results, it appears that the saved
query part shouldn't have a problem.
Opening the query, and typing in the string as the criteria manually, the
query runs fine.
So, I'm at a loss. Here's my script:

Private Sub Auditor_Change()
Dim username As String
Dim phone As String
Dim site As String

' formula_user is the following query:
' SELECT tbl_user.User_ID, [User_First]+" "+IIf([User_MI] Is Null Or [User_MI]
="" Or [User_MI]=" "," ",[User_MI]+" ")+[User_Last] AS User_Name, tbl_user.
User_Site, tbl_user.User_Phone, tbl_user.Active_Employee
' FROM tbl_user;

username = Me.Auditor.Value
phone = DLookup("User_Phone", "formula_user", "Criteria = '" & username & "'")

site = DLookup("User_Site", "formula_user", "Criteria = '" & username & "'")

MsgBox "Name: " & username & vbNewLine _
& "Phone: " & phone & vbNewLine _
& "Site: " & site

End Sub

 

[Jndrline via AccessMonster.com]

Oh, and the error message:

"run-time error 2001
You canceled the previous operation"

According to the first couple of google results, it appears that the saved
query part shouldn't have a problem.
Opening the query, and typing in the string as the criteria manually, the
query runs fine.
So, I'm at a loss. Here's my script:

Private Sub Auditor_Change()
Dim username As String
Dim phone As String
Dim site As String

' formula_user is the following query:
' SELECT tbl_user.User_ID, [User_First]+" "+IIf([User_MI] Is Null Or [User_MI]
="" Or [User_MI]=" "," ",[User_MI]+" ")+[User_Last] AS User_Name, tbl_user.
User_Site, tbl_user.User_Phone, tbl_user.Active_Employee
' FROM tbl_user;

username = Me.Auditor.Value
phone = DLookup("User_Phone", "formula_user", "Criteria = '" & username & "'")

site = DLookup("User_Site", "formula_user", "Criteria = '" & username & "'")

MsgBox "Name: " & username & vbNewLine _
& "Phone: " & phone & vbNewLine _
& "Site: " & site

End Sub

Dlookup("field","SAVED_QUERY","Criteria='string'")

I think it's not liking either using a saved query as a table, or it's not
liking that my string/date/number is a calculated expression in the query.

Thanks for your help.

 

[John Spencer]

The last section of the DLookup is basically a where clause without the word
WHERE. Any field you are querying against must be available in the query or
table.

DLookup("User_Phone", "formula_user", "User_Name = '" & username & "'")

That means that when the last section is evaluated it should become
User_Name = 'Holly GoLightly'
assuming that Me.Auditor.Value is "Holly GoLightly"

According to the first couple of google results, it appears that the saved
query part shouldn't have a problem.
Opening the query, and typing in the string as the criteria manually, the
query runs fine.
So, I'm at a loss. Here's my script:

Private Sub Auditor_Change()
Dim username As String
Dim phone As String
Dim site As String

' formula_user is the following query:
' SELECT tbl_user.User_ID, [User_First]+" "+IIf([User_MI] Is Null Or
[User_MI]
="" Or [User_MI]=" "," ",[User_MI]+" ")+[User_Last] AS User_Name,
tbl_user.
User_Site, tbl_user.User_Phone, tbl_user.Active_Employee
' FROM tbl_user;

username = Me.Auditor.Value
phone = DLookup("User_Phone", "formula_user", "Criteria = '" & username &
"'")

site = DLookup("User_Site", "formula_user", "Criteria = '" & username &
"'")

MsgBox "Name: " & username & vbNewLine _
& "Phone: " & phone & vbNewLine _
& "Site: " & site

End Sub

Dlookup("field","SAVED_QUERY","Criteria='string'")

I think it's not liking either using a saved query as a table, or it's not
liking that my string/date/number is a calculated expression in the query.

Thanks for your help.

 

[undrline via AccessMonster.com]

I figured it out probably right as you hit submit. I was going to cross-post
on UtterAccess, and realized my mistake. Silly me. "Criteria=" Ha!

Thanks.

The last section of the DLookup is basically a where clause without the word
WHERE. Any field you are querying against must be available in the query or
table.

DLookup("User_Phone", "formula_user", "User_Name = '" & username & "'")

That means that when the last section is evaluated it should become
User_Name = 'Holly GoLightly'
assuming that Me.Auditor.Value is "Holly GoLightly"

According to the first couple of google results, it appears that the saved
query part shouldn't have a problem.

[quoted text clipped - 34 lines]