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

[greyscale]

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?

[June7]

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])

[greyscale]

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

[June7]

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.

[greyscale]

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