Full Version: Small syntax question: How to use the IN clause here
UtterAccess Forums > Microsoft® Access > Access Forms
MrSiezen
Hi! It seems that I need some more coffee, since I'm keep having these problems which seem to be so simple. I'm trying to use the IN clause here, but it that I'm not doing it right. What am I doing wrong?
If ELookup("Status", "TblData", "MAINID = " & IntDE & "") IN (KL, HL, JL) Then
dallr
I see you typed Elookup but shouldn't it be Dlookup?
You have not explained much about what you are doing but i assume the followoing.
1. You are looking up the STATUS value in the TBLDATA where the MAINID is quivelent to some variable (INTDE) and this variable is a numeric number.
2. But your IN clause comes in because I think you want to limit the records to (KL, HL, JL) in some field.

This is just my assumption from what you have posted. If this is the case then.
1. Create a query for your TBLDATA table and then filter the releveant field by placing IN (KL, HL, JL)
in the criteria.
2. Then use the following as your dlookup.
DLookup("Status", "YourNewQuery", "MAINID = " & IntDE )

PS: I hope i did not presume to much and when banging on the wrong door. Other wise provide some more feedback on what u are trying to accomplish

Dallr
MrSiezen
ELookup is a replacement module that simulates DLookup, but faster and with some more options. If you're interested in it, http://allenbrowne.com/ser-42.html)
think you misunderstood me. Sorry about the little information that was given. This is the part of the code where it is used:
If ELookup("Status", "TblData", "MAINID = " & IntDE & "") IN (KL, HL, JL) Then
MsgBox "Deze introductie is al verwerkt!"
Exit Sub
End If
MAINID is a unique value (PK). I want to check if the value in the field Status is KL, HL or JL. If so, exit the code. If not, just continue.
PS. Itt actually should check the other way around, but to keep things siple I left it like this. Adding NOT isn't that hard wink.gif
MrSiezen
Hmmm the longer I'm looking at it, the less logical it is what I did.
The IN clause can only be used in SQL, am I right? Is there any other way to make this more simple? (because that's all I'm actually trying to do...)
If ELookup("Status", "TblData", "MAINID = " & IntDE & "") = 'KL' Or ELookup("Status", "TblData", "MAINID = " & IntDE & "") = 'HL' Or ELookup("Status", "TblData", "MAINID = " & IntDE & "") = 'KL' Then
cheekybuddha
If ELookup("Status", "TblData", "MAINID In ('KL', 'HL', 'JL')")
rap your text criteria in quotes!
d
cheekybuddha
IIRC ELookup returns a value or null, so adjust:
im vResult as variant
vResult = ELookup("Status", "TblData", "MAINID In ('KL', 'HL', 'JL')")
If Not IsNull(vResult) then
' etc ...
cheekybuddha
Oops ! Mis-read the original bit!
Result = ELookup("Status", "TblData", "MAINID = " & IntDE & " AND Status In ('KL', 'HL', 'JL')")
hth,
d
MrSiezen
Again thank you Cheeky, you helped me out again! Had to adjust it a little bit (the inital criteria was left out), but now it works like a charm!
Result = ELookup("Status", "TblData", "Status In ('KL', 'HL', 'JL') AND MAINID = " & IntDE & "")
If IsNull(vResult) Then
etc...
cheekybuddha
Your welcome Mr Siezen,
You must imagine it like a Select statement:
Select Status
From TblData
Where MAINID = 999 AND Status In ('KL', 'HL', 'JL')
;
So you have the 3 parts of the DLookup/ELookup
"Status"
"TblData"
"MAINID = " & IntDE & " AND Status In ('KL', 'HL', 'JL')"
hth,
d
dallr
Sorry I stepped out and missed all the fun!!
Thanks for the link Mr.Siezen but i already know about the Elookup function and its usage. It is not a custom build in Access function and few persons might know about it. Also the letter D is close to E on the keyboard so these two things made me thought you made a typo.
I would still recommend what i originally posted except that you are using the Elookup, which according to AllenBrown is faster along with other benefits.
The reason why is because by using a query and filtering by (KL, HL, JL) the search should run faster becuase it is not a flat search.
The Elookup would have less records to run through using the Filtered Query than the entire table (tblData).
Just my two cents worth.
Dallr
cheekybuddha
Hi Dallr,
just saw your response to this and I wondered whether the method you suggest would be quicker. Effectively you are using a nested Select:
"Select Top 1 a.Status " & _
"From (Select b.MAINID, b.Status From TblData b Where b.Status In ('KL', 'HL', 'JL')) AS a " & _
"Where a.MAINID = " & IntDE
I'm guessing, but I reckon:
"Select Status From TblData Where MAINID = " & IntDE & " Status In ('KL', 'HL', 'JL')"
would be quicker.
shrug.gif
d
dallr
Cheeky, I did some test with some records using the following.
Result = DLookup("Status", "query1", "MAINID = " & intDE) 'Query1 is filtered by In ('abc', 'qwe')
vResult = DLookup("Status", "tbldata", "Status In ('abc', 'qwe') AND MAINID = " & intDE)
Ofound the speed times were basically the same.
Dallr
cheekybuddha
Hi Dallr,
Thanks for posting the results of your test - interesting to know that Jet isn't as bad with nested Selects as its reputation suggests.
thumbup.gif
d
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.