Using DLookUp in Access 2007

[samuelimtech]

Hi all,

for some reason this is beyond me and it really shouldnt be, ive tried everything so im resorting to just asking you guys to do it.
can somebody please just make me a function/sub that will return me the definition based on the paramater of my choosing and assign this value to a string.
you will see in the attached i have tried to do this with a dlookup but it will not work and when ive asked the answer seems to be well it should???

so please if you can look in the attached, do what ever to the sub/macro to make in the example result equal TS.

thanks

https://www.dropbox.com/s/ioqd2qknnkd3q3k/Test%20Bed.accdb?dl=0

[SamT]

If you will copy your sub in VBA, then click the # icon in the toolbr of our editor and press Ctrl+V, it will paste your sub into your post in a nicely formatted way. then we won't have to DL something from offsite.

[samuelimtech]

Hi Sam,
ive posted this a few times in various sites and no one can tell me whats wrong. I keep gettig the message wrong number of arguments or invalid property assignment.
I can confirm without doubt there is a table named Config with a column named Definition and a record in Parameter the equals Mail folder.
also that the record in Deinfition is a text string.


Private Sub boo()
Dim result As String
result = dlookup("Definition", "Config", "[Parameter] = 'Mail Folder'")
End Sub

[SamT]

Is DLookUp a User Defined Function. I can't find it in Access XP (2003) and Access XP doesn't recognize Access 2007 accdb file formats, so I can't open your file.

If it's User Defined, you will have to look at that function. If it's an Access 2007 or VBA 2007 Function then look in the Help file.

Or... wait for someone here with Access 2007. From your lack of success, I bet it is either a UDF or not a standard Access function. See if it is in Module1. :dunno

I am changing the title of this thread to indicate the Function name and Access version

[jonh]

It's a standard function.

If you paste this into a query does the query error?


select [Definition] FROM [Config] WHERE [Parameter]='Mail Folder'

[samuelimtech]

yes that fails, it says it cant find it but I can promise you with a doubt in my mind its there!

[jonh]

Can't find what?

[samuelimtech]

the error states microsoft office cant find the object select [Definition] FROM [Config] WHERE [Parameter]= 'Mail Folder'

[jonh]

select create on the menu
query design
click the close button on the Show Table dialog
click the SQL view button
paste the code
click run

does that work?

[samuelimtech]

Hi, thanks for your time on this btw.

Yes that works. what i need to do is find a way of taking that value and assigning it to a variable in VBA. which is why i thought Dlookup would be the answer.

[jonh]

Seems there's something up with your copy of Access.
You're better off learning sql anyway.

You could try using this but I wrote it quick and haven't tested it much...


Public Function DLookup2(table$, field1$, field2$, Optional value)
'On Error Resume Next
Dim sql$
Select Case VarType(value)
Case 0, 1, 9, 10, 12, 13, 36, 8192: value = Null
Case 2 To 6, 11, 14, 17
Case 7: value = "#" & Month(value) & "/" & Day(value) & "/" & Year(value) & "#"
Case 8: value = "'" & value & "'"
End Select
If IsNull(value) Then
sql = "select TOP 1 [" & field1 & "] from [" & table & "]"
Else
sql = "select [" & field1 & "] FROM [" & table & "] WHERE [" & field2 & "]=" & value
End If
Debug.Print sql
DLookup2 = DBEngine(0)(0).OpenRecordset(sql)(0)
End Function

e.g.

result = DLookup2("Config", "Definition", "Parameter", "Mail Folder")