Need query to allow input of multiple numbers to look up/change

[amgray]

Hi all,

We run a database of proposals we review here at my University. We use Banner as an accounting system, so the records in our Access database have a column that reference these numbers. When a group of proposals is up for award, I send them to our Grant and Contract people for set up. I've created an update query so I can enter the date forwarded and my initials, plus I was able to make it so input boxes keep popping up for entry of about 15 of these Banner numbers to update, and then I just hit enter for Null if I don't have 15 to update. Instead of having to have 15 boxes come up, could I make one box for the Banner Number part pop up and make it so that I could just enter, for example, the numbers separated by commas or spaces - 4543,5456,3435, etc. for as many proposal records I need to update that date and initials on? I'm stumped.

Here's what it looks like right now:

UPDATE [Proposal Log] SET [Proposal Log].[Date Sent to G&C] = [Enter Date Sent to GCS], [Proposal Log].[Sent to G&C By] = [Enter PAA Name]
WHERE ((([Proposal Log].[Banner Number])=[Enter Banner Number 1] Or ([Proposal Log].[Banner Number])=[Enter Banner Number 2] Or ([Proposal Log].[Banner Number])=[Enter Banner Number 3] Or ([Proposal Log].[Banner Number])=[Enter Banner Number 4] Or ([Proposal Log].[Banner Number])=[Enter Banner Number 5] Or ([Proposal Log].[Banner Number])=[Enter Banner Number 6] Or ([Proposal Log].[Banner Number])=[Enter Banner Number 7] Or ([Proposal Log].[Banner Number])=[Enter Banner Number 8] Or ([Proposal Log].[Banner Number])=[Enter Banner Number 9] Or ([Proposal Log].[Banner Number])=[Enter Banner Number 10] Or ([Proposal Log].[Banner Number])=[Enter Banner Number 11] Or ([Proposal Log].[Banner Number])=[Enter Banner Number 12] Or ([Proposal Log].[Banner Number])=[Enter Banner Number 13] Or ([Proposal Log].[Banner Number])=[Enter Banner Number 14] Or ([Proposal Log].[Banner Number])=[Enter Banner Number 15]));


Cheers, and thanks,
Andrew

 

[amgray]

Anyone? Any ideas?

 

[amgray]

bump. someone *has* to know this. anyone? pretty please?

 

[Pat Hartman]

I just posted a sample database that should help you. Sample Multi-Select ListBox

 

[amgray]

I just posted a sample database that should help you. Sample Multi-Select ListBox

Maybe I just don't understand the example and how to work with it, I'm still kind of new to Access. So, I wish it did help me... I have a table of over 8000 records. One field is a column with a record number. I need to run the query so that I can search numbers, always different ones each time, instead of looking up one at a time and updating it. I need to click the query and be able to enter the relevant numbers (perhaps separated by comma or space?) and have those records be updated without doing it one at a time. The way I have it set up now, as I pasted above, works, but I have to enter one number at a time. If I have more than 15 records to update, I have to do the query again. If I have less than 15, I have to hit enter through 15 until the query loads the records. 15 is arbitrary, I could make it more or less, but it'd be nice to punch all the record numbers I need to update into one query box and have it update them all at once. Would it help if I posted a sample of my database? Thanks for your help, Andrew

 

[Pat Hartman]

If Banner number is numeric, try this:

Where [Proposal Log].[Banner Number] In([Enter Banner Numbers separated by a comma]);

The entry should look like 45, 9887, 145, 86

If Banner Number is text, it is more complicated since each entry needs to be enclosed in quotes and separated by a comma.
The entry should look like "0045", "9887", "0145", "0086"


BTW, column names should not contain special characters or embedded spaces. They should also not duplicat property or function names such as "Name" or "Date".

 

[amgray]

If Banner number is numeric, try this:

Where [Proposal Log].[Banner Number] In([Enter Banner Numbers separated by a comma]);

The entry should look like 45, 9887, 145, 86

If Banner Number is text, it is more complicated since each entry needs to be enclosed in quotes and separated by a comma.
The entry should look like "0045", "9887", "0145", "0086"


BTW, column names should not contain special characters or embedded spaces. They should also not duplicat property or function names such as "Name" or "Date".

Dang, neither of those worked. Do I need to tell it somewhere that the comma is the separator, or does the In function let it know that?

Thanks for all your help thus far! I think we'll get it here eventually! Andrew

 

[Pat Hartman]

I didn't think it would but it was worth a try. The problem is that the return value from the inputbox is being treated as a string. So the query ends up like:
Where SomeField In("45, 9887, 145, 86")
rather than
Where SomeField In(45, 9887, 145, 86)
The difference is subtle but critical. In the first case there is a single string which is not numeric and doesn't match anything in your table. In the second case, there is a liste of descrete numeric values.

It appears that you're going to need to hunker down and understand the example I posted. If you don't want to use a listbox, the code is trivial. Just give the user an unbound text box and let him type the value in the form:
45, 9887, 145, 86
You then just need to put the contents of the textbox into the In(). No loop is required.

 

[amgray]

I didn't think it would but it was worth a try. The problem is that the return value from the inputbox is being treated as a string. So the query ends up like:
Where SomeField In("45, 9887, 145, 86")
rather than
Where SomeField In(45, 9887, 145, 86)
The difference is subtle but critical. In the first case there is a single string which is not numeric and doesn't match anything in your table. In the second case, there is a liste of descrete numeric values.

It appears that you're going to need to hunker down and understand the example I posted. If you don't want to use a listbox, the code is trivial. Just give the user an unbound text box and let him type the value in the form:
45, 9887, 145, 86
You then just need to put the contents of the textbox into the In(). No loop is required.

It's not that I don't totally understand the example, I just don't understand how it relates in any way to what I'm doing. And I don't understand that entire last paragraph you wrote. I'm sorry, I'm just slow.

 

[Pat Hartman]

I'm sorry too. Perhaps you should take a day or two to try to translate my example to use your data. In the process, you should get an understanding of what is going on.

 

[amgray]

I'm sorry too. Perhaps you should take a day or two to try to translate my example to use your data. In the process, you should get an understanding of what is going on.

I'm sorry, but I'm rather frustrated. I've been working on this problem all week. As someone that has little experience with Access, I feel that to just stare at a file's code and setup and to see how it fits with my problem is not reasonable to expect from someone who is much more experienced such as yourself. I don't understand, otherwise I wouldn't keep asking. I've been intently studying your example you posted for a couple days now, and I just don't understand how to make it do what I want to do. This might be basic stuff to more experienced users, but I'm still learning. I'm not looking for a free handout, I have no problem working on this stuff, but I can't find any resources that describe a similar function I'm seeking anywhere, so I figured asking some experts would be a good start.

Maybe I need to explain better what I'm trying to do:

Sorry for all the periods, I couldn't get it to line up right otherwise.

These are some of the column labels in my table and an example of the data I'd see:

-------------------------------------------------------------------------------------------------------------------------------------------------------
Date Received.|.Date Needed By Unit.|.Date Returned.|.Title.|.Banner Number.|.Funding Agency.|.PI.|.Proposed Amount.|.Date Sent to G&C.|.Sent to G&C By
-------------------------------------------------------------------------------------------------------------------------------------------------------
09-Jan-01.....|.18-Jan-01...........| 15-Jan-01.....| Title |.5963..........|.NSF............|.PI.|.$1,232,245......| 02-May-02........|.amg...........
-------------------------------------------------------------------------------------------------------------------------------------------------------
10-Jan-01.....|.18-Jan-01...........| 15-Jan-01.....| Title |.5978..........|.NASA...........|.PI.|.$643,673........|..................|...............
-------------------------------------------------------------------------------------------------------------------------------------------------------
11-Jan-01.....|.18-Jan-01...........| 15-Jan-01.....| Title |.5999..........|.DOD............|.PI.|.$32,653.........| 06-June-03.......|.amg...........
-------------------------------------------------------------------------------------------------------------------------------------------------------
12-Jan-01.....|.18-Jan-01...........| 15-Jan-01.....| Title |.6024..........|.NIH............|.PI.|.$75,752.........|..................|...............
-------------------------------------------------------------------------------------------------------------------------------------------------------

Now, obviously each row (record) consists of all this information for one proposal. We file these records by the "Banner Number", which is a 4-digit numerical value assigned by our accounting system. What I want to do is have two queries. One will allow the user to look up multiple records based on this Banner number and have the results displayed, showing all fields. The second one will allow the user to enter the "Date Sent to G&C" and the "Sent to G&C By" and then the Banner numbers, and have the query update the relevant records tied to those Banner Numbers.

Query 1:

Something like this:

1. Click on the Query and an input box says "Enter Banner Numbers Separated by Comma|Space|etc."
2. Enter the relevant Banner numbers (from above) like 5963,5999
3. The query then shows this for the result:

-------------------------------------------------------------------------------------------------------------------------------------------------------
Date Received.|.Date Needed By Unit.|.Date Returned.|.Title.|.Banner Number.|.Funding Agency.|.PI.|.Proposed Amount.|.Date Sent to G&C.|.Sent to G&C By
-------------------------------------------------------------------------------------------------------------------------------------------------------
09-Jan-01.....|.18-Jan-01...........| 15-Jan-01.....| Title |.5963..........|.NSF............|.PI.|.$1,232,245......| 02-May-02........|.amg...........
-------------------------------------------------------------------------------------------------------------------------------------------------------
11-Jan-01.....|.18-Jan-01...........| 15-Jan-01.....| Title |.5999..........|.DOD............|.PI.|.$32,653.........| 06-June-03.......|.amg...........
-------------------------------------------------------------------------------------------------------------------------------------------------------


Query 2:

Something like this:

1. Click on the Query and the first input box asks you "Enter Date Sent to GCS"
2. Another input box comes up and asks "Enter PAA Name Who Sent to GCS"
3. Last input box asks ""Enter Banner Numbers Separated by Comma|Space|etc."
4. The query then updates the records with that information - for instance, note that the records with Banner Numbers 5978 and 6024 have not been sent to G&C yet. If I choose to update them with that info, they would now read that they've been sent to G&C on such and such date, by so and so person:

-------------------------------------------------------------------------------------------------------------------------------------------------------
Date Received.|.Date Needed By Unit.|.Date Returned.|.Title.|.Banner Number.|.Funding Agency.|.PI.|.Proposed Amount.|.Date Sent to G&C.|.Sent to G&C By
-------------------------------------------------------------------------------------------------------------------------------------------------------
10-Jan-01.....|.18-Jan-01...........| 15-Jan-01.....| Title |.5978..........|.NASA...........|.PI.|.$643,673........|.29-APR-05........|.amg...........
-------------------------------------------------------------------------------------------------------------------------------------------------------
12-Jan-01.....|.18-Jan-01...........| 15-Jan-01.....| Title |.6024..........|.NIH............|.PI.|.$75,752.........|.29-APR-05........|.amg...........
-------------------------------------------------------------------------------------------------------------------------------------------------------

As it stands now, I can do these 2 queries, but in a different fashion. An input box comes up for each Banner number I have to enter, and I made it so 15 can be entered.

Enter Banner Number 1
Enter Banner Number 2
Enter Banner Number 3
Enter Banner Number 4
etc. to 15 until my results are displayed or updated.

 

[Jon K]

Since the banner numbers are all 4-digit numbers, you can use the InStr() function in the query criteria.

Query1:-
SELECT [Proposal Log].*
FROM [Proposal Log]
WHERE instr([Enter Banner Numbers Separated by Comma],[Banner Number]);

Query2:-
UPDATE [Proposal Log] SET [Proposal Log].[Date Sent to G&C] = [Enter Date Sent to GCS], [Proposal Log].[Sent to G&C By] = [Enter PAA Name]
WHERE instr([Enter Banner Numbers Separated by Comma],[Banner Number]);


A better way would be to use two temporary tables (e.g. tblTempA, tblTempB) with a Banner Number field to hold the banner numbers and link the tables in the two queries:

Query1:-
SELECT [Proposal Log].*
FROM [Proposal Log] INNER JOIN tblTempA ON [Proposal Log].[Banner Number] = tblTempA.[Banner Number];

Query2:-
UPDATE [Proposal Log] INNER JOIN tblTempB ON [Proposal Log].[Banner Number] = tblTempB.[Banner Number] SET [Proposal Log].[Date Sent to G&C] = [Enter Date Sent to GCS], [Proposal Log].[Sent to G&C By] = [Enter PAA Name];
.

 

[amgray]

Holy crap, that worked! Thanks so much. I knew the answer had to be simple!!! Thanks again! Andrew