Results 1 to 10 of 10
  1. #1
    CodeLiftSleep is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Posts
    48

    using a UDF in a query that takes column values? Is this possible?

    So for instance I have a column in a query that has a "Score" in it and I want to take the values from that row for columns "EmpName" and "ID" to pass in as parameters to the function. Is this possible?

    I tried just doing it via calling getScore([EmpName], [ID], "Jan") in the criteria and it didn't work.

    Is there a way to get this working or is that not possible?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That should work as a new field, presuming the parameters are valid. Not sure it would work as a criteria; which record's values would it pass? What's the SQL of your query?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    You can use functions in a query, but I don't think you can put them in the criteria. What is it you are trying to do, and when you say "it doesn't work", what do you mean? Do you get an error message? no results? wrong results?

    More details, please.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    FYI, you can call a function from the criteria.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Thanks, Paul - that clears that up. When I think about it, it makes sense, since the criteria are in the SQL where clause.

  6. #6
    CodeLiftSleep is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Posts
    48
    Well, I just realized its not going to work the way I need it to doing it this way. The score can be dependant on previous months because they have caps on them in some calculations.

    So I dont think that would work because I can only return one value, I would need to return the values for all 12 months. Or at least unless I want massive amounts of needless calculations.

    Hmm...just thought of something that might work better. Currently I have the scores pulling from the DB in the query. Since these score are being calculated every time the user loads it makes little sense to even keep these scores in the DB.

    Instead what I think might be better is if I had a column that contained a udf where I could pass in the empName and ID and then have that target the textboxes directly...hmm...no I dont that would work either now that I think of it...the report is going to load all query data not one at a time right?

    Ok...here is what I want to do. For each row in the query, calculate the score for each month of the year and use that score in a report that pulls its data from the query. Since each month score and data factors into the next months score and data, the scores all need to be calculated at once. Is there a way to write values directly to the querydef from the function? That would probably be the most ideal method.

    Unless someone has an easier way of doing this...

  7. #7
    CodeLiftSleep is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Posts
    48
    Hmm..suppose I could create an array of values on the initial function call passing the first one back and create another function for the other columns passing the array index I need to get back and have it return that index of the array in the function...

  8. #8
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Since each month score and data factors into the next months score and data,
    If by that you mean cumulative scores, then you can use the report to do it - reports can do running sums of values.

    If the scores factor in in some other way, then you might be better off writing a VBA procedure to populate a temporary table, and then base your report on the table.

  9. #9
    CodeLiftSleep is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Posts
    48
    Quote Originally Posted by John_G View Post
    If by that you mean cumulative scores, then you can use the report to do it - reports can do running sums of values.

    If the scores factor in in some other way, then you might be better off writing a VBA procedure to populate a temporary table, and then base your report on the table.
    I was thinking of using a temp table but the issue is these scores are also based on the employee. Its complicated but for instance if 2 employees have the same account for different months, the scores only calculate the values each employee had individually in their scores...its not just cumulative...its about 150 lines of code with a bunch of conditional checks to calculate a score for a month so its not something I could just easily add together, etc

    For the employee scores this is easy I can just use the current user. For the managers it becomes difficult because the report groups them so I need to pull the group heading off the report to determine who the score is for and use that in the score calculations to check against who had the account for that month.

  10. #10
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    It looks like you will have to use VBA to populate a table, one record at a time, since each record is dependant on what happened before, and the calculations are complex. The temporary table will have to contain all the data the report will need for grouping, meaning that there will be a lot of repeated data (i.e. it is not normalized). That's OK - it's one time you don't want to normalize. If you do that, be sure to include a sequential counter to number the records as you create them (don't use autonumber), so that you can maintain the order (not needed if you have other data to keep the sequence).

    For the managers it becomes difficult because the report groups them so I need to pull the group heading off the report
    I don't think you can do that, but why would you need to? If the report can generate headers based on the data, then VBA can extract the same information if you give it the same data.

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

Similar Threads

  1. Replies: 8
    Last Post: 09-21-2017, 12:53 PM
  2. Replies: 4
    Last Post: 12-29-2015, 03:25 PM
  3. Replies: 14
    Last Post: 07-13-2015, 12:47 PM
  4. Percentages of Column Values in a Query
    By kheneasar in forum Queries
    Replies: 1
    Last Post: 01-28-2015, 09:40 AM
  5. Replies: 5
    Last Post: 07-17-2014, 09:16 AM

Tags for this Thread

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