Replies

Well, no, I’m still not there. Here is my latest sample (Not including the Macro).
I got this to work ONCE!! I have not been able to repeat the accomplishment. When it worked, I had typed something in the ON CLICK event for Combo6. I was attempting to requery my Select-accounts-parameter-experiment Query. I don’t know if you can even do that. But something did work once.
In this DB, the Query Select-accounts-parameter, is the one that is successful for typing in all information,
Select-accounts-parameter-experiment is the one where I’ve been playing around with a Combo Box.

Before you ask, I have a Make Table Query because the ‘real’ table I work from for accounts is huge. The query takes a long time to run. Sometimes we want to get further information from the same range of accounts. It’s just faster if I have smaller table to work from at that point.

Hi.
I’m about to look at what Patt sent me yesterday. Maybe that will solve the ‘show all when none are selected’ problem.
My question on macros is on a thread called Pauseing a Macro. I’d appreciate your taking a look.

Okay, I’m game to try.
Let’s start with Hans answer. This can’t be all you type. What -exactly – do I need to type in the Code window to 1. Turn off Warnings, 2. Open my form and wait for input, 3. Run my query, and 4. Export to Excel.
Yes, I know I could do a report. But this is what my supervisor wants. He likes to see the results in an Excel spreadsheet.

And if you don’t want to go VBA right now, what you can do is split your macro into two -(cut)

Will try this tomorrow. I really want to learn VBA, but, as usual, time is a factor.

Not too bad. I can get it to run correctly when I select a Department

I still get wierd results when trying to pull all departments (none selected) I just downloaded a sample from Patt that I will have to look at tomorrow.

Meanwhile — I have a second question up regarding how to get it to work in a Macro.

What?? Me greedy?

Thanks Pat,
I have downloaded it. Will look at it first thing in the morning.

Okay, but I don’t know VBA. Don’t know it at all. I’m starting to learn a bit. If there is a better way to do this I’m all ears, but I would have to be led in baby steps.

<>
What exactly do you mean by this.

I was trying to say that I want the query to return all the accounts in the requested range, with the Department that they are assigned to displayed in the Department number column. One of the results I’ve gotten is all of the accounts in the range, but the Department number blank.

<>
Your criteria for Account should be:
Between [Type beginning account] and [Type ending account] or [Type beginning account] Is Null

Sorry — It was time to leave and I was hurrying. I should have written
Nothing I have tried has gotten it to display all accounts if no Department is selected.
The account selection is working fine.

>
Again, what do you mean by this?

When I first started this project, I had [Type the Department number] in the criteria. It didn’t return anything if I didn’t type a number. I posted on this list, and you ( I am sure it was you) gave me the solution of adding OR Null[Type the Department number]. And it works beautifully.
Then my supervisor wanted a list of Department names to choose from. Thus the Combo Box.

I am frustratingly close to getting it to work. But it doesn’t.

Close – close.
Yes – I want to select a range of accounts, even if the range is only one
Sometimes I will want one Department that is in the range.
Sometimes I will want all the Accounts in the range with the Departments displayed.

Right now I have a query that Has ‘Between[Type beginning account] and [Type ending account]’ in the Account column
That works well for choosing the account.
One use of the query would be to learn if a given Department has any accounts in the selected range.
I can get my Combo box to select a department. — Thank you Drew.
Nothing I have tried has gotten it to display all accounts if none is selected.

Now — why wont it work with a Combo Box as elegantly as your solution worked with the Typed in Dept number?

My work day is thankfully over. I will see you all tomorrow. I am sure.

I often use the File Import function to import a spreadsheet into Access. I always give it the same name. (ok) Then run an append Query to attach it to the existing table.
Is this anything like what you are trying to do?

Well talk about frustrated! I can’t get the sample to work as well as my database is now. I’m going to send it anyway.
Things to be aware of;
I am actually linked to a read only database that has literally thousands of account numbers. Many are irrelevant to what we are doing. We will never call for all accounts. It will always be one account or a range of accounts.

There are many more departments than I have listed.

Maybe this will give a better idea of what I am trying to do.

Hi Pat,
I only have Access at work. I can check the board from home, but I can’t do anything in Access. I will build and send a sample database Monday.
Thanks.

Hi.
Well it sure does work in your sample. But I sure can’t get it to work in my database. I’m useing Access 2000. Could there be a slight difference in the way the Code has to be written? I won”t be able to work on this again until Monday. Maybe if I send a small database with my forms and queries as written, someone will spot where I have a colon intstead of a semi-colon, or something equally novice.
Thanks again for your help.

Well, the fog is clearing to some extent. This actually works now. Except — (Isn’t there always an “Except”?) the IS Null isn’t doing it’s thing properly. Worked fine when my criteria was “[Type in a Department number] – isn’t working when it references the combo box. If the combo box is empty, it just doesn’t return anything.

Can’t tell you how much help you’ve been. The tutorial is awesome. Can’t wait to have a chance to dig into it.

Yes — I think I’m going to need to see this. I keep getting lost between box one and box two and who gets what done when. My problem, not yours.