Multiple Selections for Criteria Prompt

[andysgirl8800]

I keep thinking this should be pretty simple, but more than 2 hours searching through posts has revealed no answers, probably because the answer is too simple.

OK, I have an append query. I've created a prompt to enter the number of the record the user wishes to append, as it is usually one single record. However, I've recently run into a problem that now there is the possibility of needing to specify more than one record at the same time to be appended.

How do I change the criteria syntax to allow the user to enter two, three, or a dozen (if need be) different record numbers? They are not listed sequentially, so I can't use a "between" statement. I've tried separating the numbers by commas, quotes, and so forth, but can't get it to recognise the records.

Can anyone help please? Thanks in advance.

 

[Jon K]

If there are not too many numbers, you can use a multi-select list box on a form for the user to select the numbers from and you can build the SQL statement on the fly with VBA code. (Search for list box code examples on these forums.)


Alternatively, you can use the InStr() function in the query criteria. See the example in the attachment. You can open the form and run the queries. Note the leading and ending commas in the InStr expression.
.

 

[jsanders]

If there are not too many numbers, you can use a multi-select list box on a form for the user to select the numbers from and you can build the SQL statement on the fly with VBA code. (Search for list box code examples on these forums.)


Alternatively, you can use the InStr() function in the query criteria. See the example in the attachment. You can open the form and run the queries. Note the leading and ending commas in the InStr expression.
.

I have seen a multi-select example that creates a list of numbers in a text box could this be used to in conjunction with your example?

 

[andysgirl8800]

Thanks for your reply...
Unfortunately, my system won't allow me to download and open zipped files. I have tried to do it with this forum in the past, but due to the firewalls, I can't actually open the files. I'm hesitant to dramatically change the format of the form, mostly because it's an append query I'm running, and it needs to mirror the layout of the table I'm appending to.
As an alternative, can I program a button on the form that, when clicked, will append the record you are currently on? So that I can move to each record and append it as needed? Can that be done?

 

[andysgirl8800]

No suggestions? Can't be done? Been struggling with this for a while now, and still can't work it out. Any help would be appreciated. Thanks.

 

[neileg]

Add a checkbox field to your table. Allow the user to tick the checkbox and base the append query on those records with the tick box set.

 

[andysgirl8800]

thank you for your reply. Because this is an append query, should I also put the check box on the original table that it is being appended to? Don't they both have to mirror each other? And how might I build the code for the append including the tick box?

 

[Spam]

I am currently doing something similar to print out a list of specific orders for a despatch report.
Basically i have a select query with multiple OR statements for two fields (I have to match results from 2 linking tables).


This is a cut down version of the sql:

SELECT dbo_tbl_sales_orders.sales_order_no, dbo_tbl_sales_orders.customer_ref, dbo_tbl_sales_deliveries.date_printed, dbo_tbl_sales_deliveries.delivery_reference, 
FROM dbo_tbl_sales_orders INNER JOIN dbo_tbl_sales_deliveries 
ON dbo_tbl_sales_orders.id=dbo_tbl_sales_deliveries.sales_order_id
WHERE (((dbo_tbl_sales_orders.sales_order_no)=[Enter Sales order 1]) AND ((dbo_tbl_sales_deliveries.delivery_reference)=[Enter Delivery Number1])) OR (((dbo_tbl_sales_orders.sales_order_no)=[Enter Sales order 2]) AND ((dbo_tbl_sales_deliveries.delivery_reference)=[Enter Delivery Number2])) OR (((dbo_tbl_sales_orders.sales_order_no)=[Enter Sales order 3]) AND ((dbo_tbl_sales_deliveries.delivery_reference)=[Enter Delivery Number3]))

Basically it prompts for the sales order number then the delivery ref, then repeats a set number of times (3 in example above - if you only need to fill in 1-2 of the records, you can just leave entries blank in the prompt boxes).

However i'm looking for a more elegant way to do this myself if anyone has any better ideas?

 

[andysgirl8800]

Basically it prompts for the sales order number then the delivery ref, then repeats a set number of times (3 in example above - if you only need to fill in 1-2 of the records, you can just leave entries blank in the prompt boxes).

Thanks for speaking up. So, what if I just want it to prompt for the sales order number, not necessarily the delivery ref also? You're right, a more elegant way to do this would be helpful. Seems like it should be simple enough to be able to enter a series of numbers into one prompt box, separated by commas, or quotes, or something.

 

[neileg]

Why do you need to append this data anyway? In general, copying dat from one table to another should be avoided.

 

[Spam]

So, what if I just want it to prompt for the sales order number, not necessarily the delivery ref also?

Just remove the AND expressions from the query altogether.

eg:

SELECT dbo_tbl_sales_orders.sales_order_no
FROM dbo_tbl_sales_orders
WHERE ((dbo_tbl_sales_orders.sales_order_no)=[Enter Sales order 1]) OR ((dbo_tbl_sales_orders.sales_order_no)=[Enter Sales order 2]) OR ((dbo_tbl_sales_orders.sales_order_no)=[Enter Sales order 3])... etc.

Seems like it should be simple enough to be able to enter a series of numbers into one prompt box, separated by commas, or quotes, or something.

Have a look at the InStr() function example Jon K posted above - thats exactly what it's designed to do, however you will need to change your forms to integrate it.

 

[EMP]

Originally Posted by andysgirl8800
Seems like it should be simple enough to be able to enter a series of numbers into one prompt box, separated by commas, or quotes, or something.

Originally Posted by Spam
Have a look at the InStr() function example Jon K posted above - thats exactly what it's designed to do, however you will need to change your forms to integrate it.

You can run Jon K's append query without using a form. Just change the query from:-

INSERT INTO tblTemp
SELECT tblData.*
FROM tblData
WHERE Instr("," & [Forms]![Form1]![txtCategoryList] & ",","," & [tblData].[CategoryID] & ",");

to:=

INSERT INTO tblTemp
SELECT tblData.*
FROM tblData
WHERE Instr("," & [Enter numbers separated by commas] & ",","," & [tblData].[CategoryID] & ",");


Then you can run the query and type e.g. 1,2,33 when you are prompted for the numbers, though using a form control is more user-friendly and you can still see the numbers you have typed even after the query is run.

^

 

[andysgirl8800]

Finally got it worked out. I found the following link unbelievably helpful:

http://www.access-programmers.co.uk/forums/showthread.php?t=85466&highlight=InStr


or, in the Queries Forum, the thread titled "Need query to allow input of multiple numbers to look up/change"

 

[EMP]

Finally got it worked out. I found the following link unbelievably helpful:

http://www.access-programmers.co.uk/forums/showthread.php?t=85466

In that thread, Jon K didn't use the leading and ending commas in the InStr() expression because the banner numbers were all 4-digit numbers.

If your numbers are not of a fixed length, don't leave out the leading and ending commas in the expression.

^

 

[jsanders]

Why do you need to append this data anyway? In general, copying dat from one table to another should be avoided.

I have found a bunch of great reason to copy text
Love to show you

 

[neileg]

I have found a bunch of great reason to copy text
Love to show you

Someday, maybe, when we have world peace and an end to famine.