Hi There i have created one Report in access 2010 and using the grouping option to see the groupwise report. I have used the detail section to get the data from other tables by using Dlookup. Following is the syntax - Jan1.Value = DLookup("[sumofhc]", "[q_mnthlyHCForAttrSU]", "[mainGroup]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![MainGroup] & "'" And [FortheYear] = "&[YearofAttr]&" And [forthemonth] = "&1&")
i am not getting the result of main group accurately. can anyone help here.
9  1418 
amitbgm11,
Welcome to Bytes.com. Always glad to see a new member.
It's always a good idea to form your SQL or criteria syntax before plugging into a function call. For example: - Dim strSQL as string
-
strSQL = "[mainGroup]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![MainGroup] & "'" And [FortheYear] = "&[YearofAttr]&" And [forthemonth] = "&1&"
-
DLookup("[sumofhc]", "[q_mnthlyHCForAttrSU]", strSQL)
-
That way, in debug mode we can stop before the Dlookup and examine strSQL to see if it is formed correctly. It's a very good programming habit to form, so start today.
Now, about your code, this looks wrong: & "'" And [FortheYear]...
You seehow you have restarted your quoted string to add the trailing ' but then you terminated the quoted string again immediately. But the And [FortheYear] should be part of your quoted string, it's not some variable. So, remove that quote and we have - strSQL = "[mainGroup]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![MainGroup] & "'" And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = "&1&"
At the end, after the [forthemonth]= you close your quoted string to put the month number you want and then you put another " instead of closing your function call with a )
Plus, if you are hardcoding a 1 there, why not just make the 1 part of the quoted string, like this - " And [forthemonth] = 1 ")
I've added the closing ) for you there also.
So your code should be like this - Dim strSQL as string
-
strSQL = "[mainGroup]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![MainGroup] & "' And [FortheYear] = "&[YearofAttr]&" And [forthemonth] = 1 ")
-
DLookup("[sumofhc]", "[q_mnthlyHCForAttrSU]", strSQL)
-
Give that a try.
Jim
amitbgm11,
Just so as not to consfuse, Jim's modifications look correct, but I think you are updating a text box on a report--rather than updating the field through VBA?
The Control Source for your Jan1 text box would then be: - = DLookup("[sumofhc]", "[q_mnthlyHCForAttrSU]", "[mainGroup] = '" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![MainGroup] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 1 "))
Great work, Jim, in evaluating the OP's complex expression, as well as providing good advice for evaluating those expressions. Good advice that I should follow more often when troubleshooting!
I've been seeing how zmbd does it :)
Thanks.
Jim,
I actually think your (Z's) method works better, especially for troubleshooting. It makes me re-look at how I assign values to text boxes through calculation. I typically don't have anything as complex as the OP's but I do have some reports that do some crazy calculations, which took me a long time to get right.
I think some Access users are "afraid" of VBA. I know I was when I first started. I've grown to love it. But, then again, I need it to work at my job....
I have appreciated your contributions to the site and hope to glean more from your experience.
Thanks guys for your Help. I tried doing the same thing as you suggested, but the output is coming different. i am pasting the complete code so that you can understand what i am looking for.
This code I am running on Some click of Refresh button - PrYr.Value = DLookup("[HC]", "[t_mnthlyHC]", "[Buname]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![BUname] & "' And [FortheYear] = " & [fyy] & " And [forthemonth] = 12")
-
Prjct.Value = DLookup("[sumofHC]", "[q_mnthlyHCForAttrSU]", "[MainGroup]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![MainGroup] & "' And [FortheYear] = " & [fyy] & " And [forthemonth] = 12")
-
LastYrEnd.Value = DLookup("[Lastyear]", "[tbl_Yr]", "[yrstext]=[fyy]")
-
Text173.Value = DLookup("[PrevLstDate]", "[tbl_Yr]", "[yrstext]=[fyy]")
-
Docmd.requery
-
-
Jan1.Value = DLookup("[sumofhc]", "[q_mnthlyHCForAttrSU]", "[mainGroup]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![MainGroup] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 1")
-
Feb2.Value = DLookup("[sumofhc]", "[q_mnthlyHCForAttrSU]", "[mainGroup]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![MainGroup] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 2")
-
Mar3.Value = DLookup("[sumofhc]", "[q_mnthlyHCForAttrSU]", "[mainGroup]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![MainGroup] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 3")
-
APr4.Value = DLookup("[sumofhc]", "[q_mnthlyHCForAttrSU]", "[mainGroup]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![MainGroup] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 4")
-
May5.Value = DLookup("[sumofhc]", "[q_mnthlyHCForAttrSU]", "[mainGroup]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![MainGroup] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 5")
-
Jun6.Value = DLookup("[sumofhc]", "[q_mnthlyHCForAttrSU]", "[mainGroup]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![MainGroup] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 6")
-
Jul7.Value = DLookup("[sumofhc]", "[q_mnthlyHCForAttrSU]", "[mainGroup]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![MainGroup] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 7")
-
Aug8.Value = DLookup("[sumofhc]", "[q_mnthlyHCForAttrSU]", "[mainGroup]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![MainGroup] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 8")
-
Sep9.Value = DLookup("[sumofhc]", "[q_mnthlyHCForAttrSU]", "[mainGroup]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![MainGroup] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 9")
-
Oct10.Value = DLookup("[sumofhc]", "[q_mnthlyHCForAttrSU]", "[mainGroup]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![MainGroup] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 10")
-
Nov11.Value = DLookup("[sumofhc]", "[q_mnthlyHCForAttrSU]", "[mainGroup]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![MainGroup] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 11")
-
Dec12.Value = DLookup("[sumofhc]", "[q_mnthlyHCForAttrSU]", "[mainGroup]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![MainGroup] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 12")
-
-
DoCmd.Requery
-
Jan.Value = DLookup("[HC]", "[t_mnthlyHC]", "[Buname]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![BUname] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 1")
-
Feb.Value = DLookup("[HC]", "[t_mnthlyHC]", "[Buname]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![BUname] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 2")
-
Mar.Value = DLookup("[HC]", "[t_mnthlyHC]", "[Buname]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![BUname] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 3")
-
Apr.Value = DLookup("[HC]", "[t_mnthlyHC]", "[Buname]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![BUname] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 4")
-
May.Value = DLookup("[HC]", "[t_mnthlyHC]", "[Buname]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![BUname] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 5")
-
Jun.Value = DLookup("[HC]", "[t_mnthlyHC]", "[Buname]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![BUname] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 6")
-
Jul.Value = DLookup("[HC]", "[t_mnthlyHC]", "[Buname]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![BUname] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 7")
-
Aug.Value = DLookup("[HC]", "[t_mnthlyHC]", "[Buname]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![BUname] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 8")
-
Sep.Value = DLookup("[HC]", "[t_mnthlyHC]", "[Buname]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![BUname] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 9")
-
Oct.Value = DLookup("[HC]", "[t_mnthlyHC]", "[Buname]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![BUname] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 10")
-
Nov.Value = DLookup("[HC]", "[t_mnthlyHC]", "[Buname]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![BUname] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 11")
-
Dec.Value = DLookup("[HC]", "[t_mnthlyHC]", "[Buname]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![BUname] & "' And [FortheYear] = " & [YearofAttr] & " And [forthemonth] = 12")
Perhaps you can find a more brief way to to describe to us what is happening and what you need. If there is a problem you're not telling us what it is.
Jim
Yes, what is happening in your code that it does not work? Are you getting an error or is the code returning the wrong values? What does your data table look like? What "should" the code return? The corrections to your code provided by Jim have the correct syntax, but if your code is looking for a number and somehow your table has the number saved as text, that can cause additional problems.
So far, we know that your code does not work. Please explain.
Hi Jim, I am getting the same value for all the MainGroup and Buname, which is effecting the calculation for each BU. The report is a grouped one (1st level Main Group, 2nd level Yearofattr, 3rd level BUname and 4th level TermStatus). All these codes calculations i am putting in Detail section, which i am keeping non visible.
Based on the code you provided, that is exactly what you are asking for, because the MainGroup adn Buname are values that are coming from your form: - "[mainGroup]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![MainGroup]
and - "[Buname]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![BUname]
These values will not change no matter how you group them.
There must be a better query for you to use as a record source for your Report and must be a better way to caclulate the groupings.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Ronny Sigo |
last post by:
Hello all,
I already put the same question, only now I have more to tell ...
Although I used this code before in the same routine (only the fieldname of
the table differs) ___ at this point in the...
|
by: Don Sealer |
last post by:
I've written this expression for a DLookup function. It works almost
alright.
What I'm trying to do is type in a description and the ID field (number)
populates automatically.
It works almost as...
|
by: Don |
last post by:
Can someone help me fix my DLookup problem. I'm far from proficiency with
Access. I've been creating databases for several years for work with the
help of many of you and trial and error.
I have...
|
by: jonvan20 |
last post by:
I have been having trouble with a simple Dlookup command that was Reccommended to me by a nice fellow named Vic, On the other hand I have statements like this that wont run they give me a run time...
|
by: musman |
last post by:
hey all,
I have tried to use the select statement instead DLookUp function as i have sql server at my backend and access as my front end.
But neither DLookUp function is working nor select state...
|
by: squiggly12 |
last post by:
Hello all, I have this interesting problem and it has me scratching my head. I have updated a few forms to use CDOSYS to email instead of using the doCmd.SendObject.
Dim objMessage As...
|
by: rleepac |
last post by:
This is a little complicated but I'll do my best to explain. In my db I have a table called L_AgeCorrection which has the following fields: Age, Sex, Frequency, AgeValue
This is a table used to...
|
by: Stoic |
last post by:
Hi ADezii,
I just want to thank you for your brilliant answer to my question on populating my reports to a combo box on my form. It works very well.
I also have a problem with my dlookup. I have...
|
by: Rinis |
last post by:
Edit #1 - I'm Using access 2010 with an access desktop database (.accdb). hopefully i'm referring to that correctly.
I'm working on a makeshift timeclock via VBA and access and I was never the best...
|
by: Rinis |
last post by:
I'm working on a timeclock system in a vba access database. I'm trying to get the system setup so when you hit the clock in button it checks to see if you have clocked in at some point that day. if...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
