Results 1 to 3 of 3
  1. #1

    Pop Up Action to alter DB calculation field? (2000)

    Hi Folks

    I have created a Commission Report using Access and the calculations are based on historic sales as well as sales for the current month.

    In one of my fields I have a Control Source set to

    =IIf([dateBank] Between #01/07/01# And #31/07/01#,[comInitial],10)

    Now and again I need to change these dates to a different month. Is it possible to create a pop up box using VB that asks the user for the 'between dates' i.e. 01/07/01 and 31/07/01 as soon as the Report is opened and for these values to then be entered into the Control Source?

    It would be somewhat easier for the user as opposed to having to edit the Control Source in the Report directly. I realise that this is probably more of a VB question but I thought someone might simply know if this is possible or not?!

    Cheers for any help you can give me,


  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Crystal Beach, FL, Florida, USA
    Thanked 41 Times in 40 Posts

    Re: Pop Up Action to alter DB calculation field? (2000)

    Just replace your hardcoded dates with parameters, like this:
    Between [For date on/after] and [And for date on/before]

    Or any words you choose. When the report is selected, Access will pop an input box, using as a prompt the words you specified between the brackets.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Thanked 0 Times in 0 Posts

    Re: Pop Up Action to alter DB calculation field? (2000)

    You can create a dialog form (look this up in help: Using a form to collect report criteria) and then change your statement to read:
    =IIf([dateBank] Between [Forms]![yourDialogformname]![BeginDate] And [Forms]![yourDialogformname]!EndDate,[comInitial],10)

    BeginDate and EndDate will be the names of the textboxes on your dialog form.
    Set the OnOpen property of your report to open your dialog form and the OnClose property of your report to close the form.

    There are other ways, but this is fairly simple and always works for me.

Posting Permissions

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