How to get the Dlookup working in the VBA

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • amitbgm11
    New Member
    • Jun 2014
    • 3
    #1

    How to get the Dlookup working in the VBA

    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

    Code:
    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.
    Last edited by Rabbit; Jun 12 '14, 03:13 PM. Reason: Please use [code] and [/code] tags when posting code or formatted data.
    Tags: None
    • jimatqsi
      Moderator Top Contributor
      • Oct 2006
      • 1293
      #2
      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:
      Code:
      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
      Code:
      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
      Code:
      " And [forthemonth] = 1 ")
      I've added the closing ) for you there also.
      So your code should be like this

      Code:
      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

        Comment

        • twinnyfo
          Recognized Expert Moderator Specialist
          • Nov 2011
          • 3665
          #3
          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:

          Code:
          = 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 !

            Comment

            • jimatqsi
              Moderator Top Contributor
              • Oct 2006
              • 1293
              #4
              I've been seeing how zmbd does it :)

              Thanks.
              Last edited by zmbd; Jun 13 '14, 08:35 PM. Reason: [z{no edit - Just a Smile (^_^) }]

                Comment

                • twinnyfo
                  Recognized Expert Moderator Specialist
                  • Nov 2011
                  • 3665
                  #5
                  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.

                    Comment

                    • amitbgm11
                      New Member
                      • Jun 2014
                      • 3
                      #6
                      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
                      Code:
                      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")
                      Last edited by NeoPa; Jun 29 '14, 08:13 PM. Reason: Second Warning. Please use [code] and [/code] tags when posting code or formatted data.

                        Comment

                        • jimatqsi
                          Moderator Top Contributor
                          • Oct 2006
                          • 1293
                          #7
                          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

                            Comment

                            • twinnyfo
                              Recognized Expert Moderator Specialist
                              • Nov 2011
                              • 3665
                              #8
                              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.

                                Comment

                                • amitbgm11
                                  New Member
                                  • Jun 2014
                                  • 3
                                  #9
                                  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.

                                    Comment

                                    • twinnyfo
                                      Recognized Expert Moderator Specialist
                                      • Nov 2011
                                      • 3665
                                      #10
                                      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:

                                      Code:
                                      "[mainGroup]='" & [Forms]![frm_SuperUserDB]![R_AttrReportSU]![MainGroup]
                                      and

                                      Code:
                                      "[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.

                                        Comment

                                        Previous template Next
                                        Working...