Results 1 to 5 of 5
  1. #1
    greyscale is offline Novice
    Windows XP Access 97
    Join Date
    Aug 2010
    Posts
    5

    Access 97 query - 'grey out' drop down list if another list is selected

    Hi,



    I am very new to access and my question is 2 fold:

    1 - Is it possible to 'grey out' a drop down section of a form if another section has been selected?

    If not,

    2 - I need to write a query that counts the number of entries in column A, column B and shows the relevant dollar value. This is working fine except in cases where the user puts an entry in both A and B. In this case, we only want to count column A as per below:

    - if column A null and column B has an entry, return 1 count and dollar value for column B
    - if column B null and column A has an entry, return 1 count and dollar value for column A
    - if column A has an entry and column B has an entry, return 1 count and dollar value for column A only

    I can't attach the db as it contains customer sensitive information. Is anyone able to help based off this email?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,950
    1. I presume by 'drop down section', you mean a comboox. You can use code to disable controls on form. I only use VBA. In the AfterUpdate event of each, include a line like: Me.othercontrolname.Enabled = False

    2. This is two pieces of data. Construct fields in query with expressions.
    For the Count expression: IIf(Not IsNull([ColumnA]) Or Not IsNull([ColumnB]),1,0)
    For the Dollar Value: Nz([ColumnA],[ColumnB])
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    greyscale is offline Novice
    Windows XP Access 97
    Join Date
    Aug 2010
    Posts
    5
    Thanks for your reply, June7. Please excuse my ignorance here. This is someone else's database and I am just trying to help them with this. I will get them to try the VBA code you have provided.

    With regards to Question 2. I was not able to get your suggestion to work. It would not accept an excel attachment so I have put the layout of the query result that the owner of this db is using, along with the SQL query below. Are you able to modify your suggestion to accommodate their layout?

    Query
    SELECT [table].[Yes Outcome], Count([table].[Yes Outcome]) AS [CountOfYes Outcome], [table].[No Outcome], Count([table].[No Outcome]) AS [CountOfNo Outcome], Sum([table].Balance) AS SumOfBalanceFROM [table]WHERE ((([table].Date) Between [From Date] And [To Date]))GROUP BY [table].[Yes Outcome], [table].[No Outcome];

    Query result
    COLUMN 1 = Name of yes outcome
    column 2 - count of yes outcome
    column 3 = name of no outcome
    column 4 = count of no outcome
    column 5 = dollar value
    We are getting entries in both column 2 & 3 when it should only be one or the other
    Last edited by greyscale; 06-16-2011 at 06:01 PM. Reason: Will not accept lauout in body of post

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,950
    This is an aggregate query. Need to group on some identifier such as Customer ID. I suggest you not do this in the query but instead build a report using report Grouping & Sorting features.

    Do you mean should be either column 1 or 3 OR 2 or 4?

    Where are the from and to date criteria coming from - a form?

    If I am understanding your needs, in query designer build query that looks like this (you can copy/paste this into the SQL View of query designer, replace IDfield with your field name and fix the date criteria references):

    SELECT IDfield, IIf(Not IsNull([Yes Outcome]),"YesOutcome", IIF(Not IsNull("NoOutcome"), "NoOutcome", "None")) AS OutcomeType, IIf(Not IsNull([Yes Outcome]) Or Not IsNull([No Outcome]),1,Null) AS OutcomeCount, Nz([Yes Outcome],[No Outcome]) AS OutcomeAmount FROM [table] WHERE [Date] Between Forms!formname![From Date] And Forms!formname![To Date];

    Now use this query as the RecordSource for a report. If form is source for the date criteria, it would have to remain open.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    greyscale is offline Novice
    Windows XP Access 97
    Join Date
    Aug 2010
    Posts
    5
    Thanks June7. Much appreciated. Ooops. Yes, I did mean should be either column 1 or 3 and not both (typo). I know that was a messy question as I could not attach the db and it would not accept other documents I tried to mock up as attachments. This is out of my league but I will pass this on to the owner.

    thanks again

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

Similar Threads

  1. No drop down list in Access?
    By snorkyller in forum Forms
    Replies: 5
    Last Post: 03-21-2011, 03:28 PM
  2. Replies: 1
    Last Post: 03-15-2010, 02:52 PM
  3. drop down list
    By tceicher in forum Access
    Replies: 5
    Last Post: 08-13-2009, 05:41 AM
  4. Open form from a drop down list
    By ildanach in forum Forms
    Replies: 15
    Last Post: 05-21-2009, 05:40 AM
  5. I can't create a drop-down list box
    By cpuser in forum Access
    Replies: 4
    Last Post: 02-18-2008, 10:11 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 - Senior Forums