DLOOKUP Function in Reports

[Shafique]

HI Everyone.
i create a daily production Report in my Report i want total month's Production in machine's Group Footer
i set a text box that show month number using report date value =Month([Forms]![IMPPRD]![cbodatesel])
here i want triple criteria DLOOKUP function
criteria=1 is month
criteria=2 is machine
criteria=3 is year

i am some confusing to aggregate the value with this formula

DLookUp("Totalpap","MONTHTOTAL","[MONTH]='Month([Forms]![IMPPRD]![cbodatesel])'" And "[machine]=[reports]![production]![machine]")

 

[Micron]

You have to get the concatenation correct, and that means knowing what is the data type returned by the references. That is something your post doesn't reveal. I'm going to assume that the date is date/time data type and the machine is text.

Maybe

DLookUp("Totalpap","MONTHTOTAL","[MONTH]=Month(#" & [Forms]![IMPPRD]![cbodatesel] & "#) And [machine]= '" & [reports]![production]![machine] & "'")

What goes in between start and end quotes is interpreted literally. So if the machine value should be "machine 1" then this
"[machine]=[reports]![production]![machine]"
is passing this to the expression
[machine]=[reports]![production]![machine] and not machine 1.

 

[welshgasman]

HI Everyone.
i create a daily production Report in my Report i want total month's Production in machine's Group Footer
i set a text box that show month number using report date value =Month([Forms]![IMPPRD]![cbodatesel])
here i want triple criteria DLOOKUP function
criteria=1 is month
criteria=2 is machine
criteria=3 is year

i am some confusing to aggregate the value with this formula

DLookUp("Totalpap","MONTHTOTAL","[MONTH]='Month([Forms]![IMPPRD]![cbodatesel])'" And "[machine]=[reports]![production]![machine]")

I think I have advised you before.
Put your criteria into a string variable, then you can debug.print it until you get it correct, then you can simply use it in the function.

 

[Shafique]

yes its true that you advised me before and i tried it same as you advising now. but i can't figure it out how to do that due lake of experience in VBA
believe me i don't know how to put a criteria into string variable and how to debug.print it. and what does it mean
i know that if i can understand it i will be a HERO
So please explain it (
Put your criteria into a string variable, then you can debug.print it until you get it correct, then you can simply use it in the function.)
i will follow as your instructions to be able helping peoples like you .......

 

[Micron]

I'm kind of surprised what I wrote works since machine values look like numbers in your post picture. You should know what the data types are for the criteria you use and reveal that in your future posts. You should also make it clear where you have such expressions. I think Welshgasman thinks it is in code, which could be why he's suggesting the debug thing. On the other hand, I now think it's in a query so debug would not apply. Also, if in a query I'll say that usually you should not have DLookup in a query field as it is inefficient and should not be necessary. Better to join the table you'd be doing the lookup on and use criteria against those fields. If you cannot join this table to other tables in the query, that suggests there is something wrong with the table design. Queries should be able to return records using only tables, joins and criteria against fields without doing lookups on other tables.

 

[Shafique]

You have to get the concatenation correct, and that means knowing what is the data type returned by the references. That is something your post doesn't reveal. I'm going to assume that the date is date/time data type and the machine is text.

Maybe

DLookUp("Totalpap","MONTHTOTAL","[MONTH]=Month(#" & [Forms]![IMPPRD]![cbodatesel] & "#) And [machine]= '" & [reports]![production]![machine] & "'")

What goes in between start and end quotes is interpreted literally. So if the machine value should be "machine 1" then this
"[machine]=[reports]![production]![machine]"
is passing this to the expression
[machine]=[reports]![production]![machine] and not machine 1

I'm kind of surprised what I wrote works since machine values look like numbers in your post picture. You should know what the data types are for the criteria you use and reveal that in your future posts. You should also make it clear where you have such expressions. I think Welshgasman thinks it is in code, which could be why he's suggesting the debug thing. On the other hand, I now think it's in a query so debug would not apply. Also, if in a query I'll say that usually you should not have DLookup in a query field as it is inefficient and should not be necessary. Better to join the table you'd be doing the lookup on and use criteria against those fields. If you cannot join this table to other tables in the query, that suggests there is something wrong with the table design. Queries should be able to return records using only tables, joins and criteria against fields without doing lookups on other tables.

machine's data type is SHORT TEXT
next time i will revel data types
i learn lot of things like rules and how much table designing is effective for future
i am very very thanks full to you for your valuable tips and priceless time

 

[welshgasman]

Yes, I did think it was in VBA, sorry.
Nevertheless, testing it in code until you get it correct would still help.

All too often people just write code without testing it bit by bit.
Now the experts here can do that, but I would still test my criteria as I advised?
So, with that said, try it in code first. Then when you do get it correct, copy the string variable contents into you control expression.

I have just done exactly that for the attempt below.

This works for me, so you need to build and test from that?

=DLookUp("Amount","Query9","Expr1 = " & Month([Forms]![frm2testtransactions].[Transactiondate]))

You take it from there.

 

[welshgasman]

I also tend to put any string criteria last, but that is just me.