Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2017
    Posts
    3

    Answered: Access query results in a unbound Text box

    I have created a query that calculates the difference between 2 dates and then based on the results gives me a "Required" "Not required" result. My query works fine.

    Query code used:
    FTSW Check: DateDiff("yyyy",[adsd],[report_date])
    FTSWREQUIRED: IIf([ftsw check]<2,"FTSW REQUIRED","NOT REQUIRED")

    My problem is that I need the "Required" "Not required" to populate in my text box on my form.
    I have tried dlookup but it only works for the first record and then duplicates the response to all records.
    I think I need to create an AfterUpdate() but I'm struggling to figure out how to write the code in VBA.
    Can anyone help me.

  2. Best Answer
    Posted by weejas

    "If you've written the formulae in a query, can you use the query as the data source for the form? Then you could bind the text box to the query field, and it would always show the value for the current record.

    Otherwise you can use a formula for the value of the unbound control:
    Code:
    =IIf(DateDiff("yyyy", [adsd], [report_date]) < 2, "FTSW REQUIRED", "NOT REQUIRED")
    You might need to replace the field names with the names of the form controls that hold them."


  3. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,105
    Provided Answers: 18
    If you've written the formulae in a query, can you use the query as the data source for the form? Then you could bind the text box to the query field, and it would always show the value for the current record.

    Otherwise you can use a formula for the value of the unbound control:
    Code:
    =IIf(DateDiff("yyyy", [adsd], [report_date]) < 2, "FTSW REQUIRED", "NOT REQUIRED")
    You might need to replace the field names with the names of the form controls that hold them.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  4. #3
    Join Date
    Aug 2017
    Posts
    3
    Thank you that's what I was looking for.

  5. #4
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,105
    Provided Answers: 18
    Happy to help!
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •