Results 1 to 8 of 8
  1. #1
    hockeyman9474 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2013
    Location
    Manalapan, NJ
    Posts
    45

    DLookup reference column according to variable

    I am creating a rolling 12 month report. I have a report with 12 text boxes whose value depends on the month we are in. i.e. current month data is March therefore first text box will be Feb, then Jan, then Dec....etc. I have a form where the user selects the Month to query and the selection is saved as a number 1 for January, 2 for February, etc. The table stores data in 12 separate fields with the field name 01,02,03,04...etc.



    because the text boxes display the previous month and then descend, I need to use a Dlookup to ensure that they are listed in the correct order depending on the month selected by the user. Will someone please provide me the correct verbiage for a Dlookup that follows this logic:

    =Dlookup("[column name]","TABLE","[Column Name] = " & comboBox)

    as a reference, I was successful with the labels by actually using text boxes and the code(the code was a bit more complicated but you get the idea):

    Dlookup("[MonthShort]","MOY", "[MonthNumber] =" & [FORMS]![FORM1]![monthCombo])

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the query controls the order. Im not sure what the Dlookup does.
    if user picks a month in the combo, say 7,
    then the query would use this to pull all the data starting with 7 and before.

  3. #3
    hockeyman9474 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2013
    Location
    Manalapan, NJ
    Posts
    45
    Ranman,

    If you have a table where the columns are 1,2,3,4,5......you can have a query return results in a different order according to a variable number select on a form? Didn't realize I could do that and that would be perfect. how do I write the query to do that?

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Have you tried using a query?
    Select yourfield1, yourfield2... from yourtable Order By yourfieldX ascending id default or use DESC to get descending order or some value from an Open form...

  5. #5
    hockeyman9474 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2013
    Location
    Manalapan, NJ
    Posts
    45
    I understand the concept you are implying, but each month the order changes. It's not a matter of 1-12 or 12-1. It's rolling, so I crosstab the information and create a table. so now I have a table with a few ID fields(columns) followed by 1,2,3,4,5....the report has a series of 12 text boxes that use a Dlookup to fill in the data. So for example, if the month selected on the form is March (3) I need to use the dlookup to find the field named 3 and use the data from it, the next textbox the same however it will look for formselection-1 which is 2. So it looks for the field named 2. Next month the first textbox will start with april...so on and so forth.

    I don't know that a query can provide that

  6. #6
    hockeyman9474 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2013
    Location
    Manalapan, NJ
    Posts
    45
    You know what? What I forgot to mention was that b/c it's a rolling month report, I query for feb and Jan of this year, and then march-dec of last year (12 months) another another query captures Jan and Feb of 2016/mar - dec of 2015 so i have 12 months to compare since it's also a comparison report. So asc or desc is tough when I have to sort by year and month.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Is it always current month - 11? There may be some way to do this with an algorithm/expression/function.

  8. #8
    HIolemis1 is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    May 2017
    Posts
    1
    Your posting is very useful.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 01-19-2016, 07:50 PM
  2. Variable to reference a spreadsheet from Access
    By lawdy in forum Programming
    Replies: 9
    Last Post: 03-03-2015, 11:31 AM
  3. How to reference data without using Dlookup()
    By HunterEngineeringCoop in forum Programming
    Replies: 7
    Last Post: 12-01-2010, 09:22 AM
  4. Variable within form control reference
    By Tyork in forum Programming
    Replies: 2
    Last Post: 10-13-2010, 09:55 AM
  5. Replies: 2
    Last Post: 05-09-2010, 04:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums