multiple dlookup criteria can't work

[jerry28ph]

I have 4 criteria to be pass thru my dlookup(), and it seems I coded it wrong, my level in access is not that high so I'm asking your help with this.
criteria1 = combo1 (string data type)
criteria2 = text38 (value)
criteria3 = <> "12:00:00 AM" (date)
criteria4 = between [start] AND [end] (date)

Tablogs = Table Name
tccec_pc = fieldname from Tablogs

Here's my script:

Text474 = DLookup("tccec_pc", "TabLogs","criteria='" & forms!frmLog!combo17 _
& " AND criteria = '" & forms!frmLog!Text38 & "'" _
& " AND criteria <> "'" & 12:00:00 AM "#" _
& " AND criteria = between #" & forms!frmLog!Text295 & "#" AND forms!frmLog!Text297 & "#")

it cames up the fonts into red while im editing this code.
Im not familiar with the syntax of dlookup.

Please need your help.
Thanks.

 

[dkinley]

I would guess that it's in the time field (you have # on one side, but not the other). I would further opine with so much criteria - why don't you just make a query to do all of this for you and simply use DLookup to get the record from the query?

-dK

 

[jerry28ph]

are my syntax correct? im confuse about the "fieldname" where should i get it from table that im using?

Im doing trial and error with this dlookup coz im really newbi with access.
I tried to use my query instead of dealing with so many criteria but the problem is I always getting Runtime Error '2001' - You canceled previous operation which I know the reason. That's why im trying to put all the criteria in dlookup and for me its so hard. If i will use my query in dlookup() i dont need the criteria inside the dlookup() right? coz it's already entered in my query. If you noticed my posting about "Enter Parameter Value" that my problem with it comes to dlookup using my query which I suppose to use my main form to enter all the value of my criteria. I dont know how to deal with this dlookup using my query.
Please if you an idea how to solve this I really appreciate it.

Thanks

I would guess that it's in the time field (you have # on one side, but not the other). I would further opine with so much criteria - why don't you just make a query to do all of this for you and simply use DLookup to get the record from the query?

-dK

 

[dkinley]

Let's start simple with only 1 then 2 criteria .... and you can add to that list ...

Me.txtControlName = Nz(DLookup("tecc_pc","TabLogs", "[SomeFieldName] = '" & Forms!frmLog!combo17 & "'),"Nothing Found")

If that works then expand it ...

Me.txtControlName = Nz(DLookup("tecc_pc","TabLogs", "[SomeFieldName] = '" & Forms!frmLog!combo17 & _
"' And [SomeOtherFieldName] = '" & Forms!frmLog!Text38 & "'),"Nothing Found")

If that works, then expand it to include another criteria ... etc

I put the Nz() on it so you won't get an error if there is no data found. The [SomeFieldName] will have to be filled in appropriately with the field name of the table that you want to run the criteria against. Like you want the criteria to be combo17 but against what? That is the field name you fill in.

-dK

 

[jerry28ph]

I created a sample table for me to try it in 1 criteria and it always gives me "Nothing Found"

Private Sub Command2_Click()
Me.Text0 = Nz(DLookup("stfee", "course", "sname" = "'& forms!form1!text3 & '"), "Nothing Found")
End Sub

i had 10 records of students, my fields are sno, sname, scourse, sdept, stfee, i want to get the stfee value, after i enter the criteria which is the sname from my form1.

Pls advise.

Let's start simple with only 1 then 2 criteria .... and you can add to that list ...

Me.txtControlName = Nz(DLookup("tecc_pc","TabLogs", "[SomeFieldName] = '" & Forms!frmLog!combo17 & "'),"Nothing Found")

If that works then expand it ...

Me.txtControlName = Nz(DLookup("tecc_pc","TabLogs", "[SomeFieldName] = '" & Forms!frmLog!combo17 & _
"' And [SomeOtherFieldName] = '" & Forms!frmLog!Text38 & "'),"Nothing Found")

If that works, then expand it to include another criteria ... etc

I put the Nz() on it so you won't get an error if there is no data found. The [SomeFieldName] will have to be filled in appropriately with the field name of the table that you want to run the criteria against. Like you want the criteria to be combo17 but against what? That is the field name you fill in.

-dK

 

[dkinley]

Private Sub Command2_Click()
Me.Text0 = Nz(DLookup("stfee", "course", "sname" = "'& forms!form1!text3 & '"), "Nothing Found")
End Sub

I would guess the quoted reference is incorrect - I can't tell because the type is small.

If you are refering to your criteria and it is text you use a single quote followed by a double quote ...

, "sname = ' " & Forms!Form1!text3 & " ' ")

If it's a number then no single quotes ...

, "sname = " & Forms!Form1!text3)

I typed that wrong previously - my apologies, but do you see? The system only literally reads stuff inside quotes. In the text one we have to let the system know it's text so showing that last quote is wrapped in double quotes. We don't need anything at the end of the number because it doesn't require them. Even better, dates require a # to let the system know a date is being used.

I would say the overwhelming majority of errors in DLookup is because of syntax of this nature.

This link should get you through most of it.

Hope that helps,
-dK