Help with WHERE statement

rinova

Registered User.
Local time
Today, 11:21
Joined
Aug 27, 2012
Messages
74
Help with parameter query that prompt users for input

Hi All,

I have a Form that is used to print a report and the report is based off of a query.

I don’t like the WHERE statement in the query. I want the user to enter the employer codes and not have to continue to press enter when they are done entering 5 employer codes.

Example: The want to send letters to 3 Employer codes but after they enter the 3rd employer code the query will continue asking for 21 more employer codes. How can I provide the user a way to not be asked for 21 more employer codes?

I guess what I'm trying to do is create prompts for report criteria.

Thank you,

Rich

The current code for the query is:
Code:
 SELECT Format([Enter Letter Date],"mmmm dd"", ""yyyy") AS [Letter Date], dbo_partfile.prt_employer_code, dbo_employer.mis_description, UCase([kn_key_name]) AS UID, Trim([prt_last]) AS [LAST], Trim([prt_first]) AS [First], dbo_partfile.prt_middle, dbo_partfile.prt_addr1, dbo_partfile.prt_addr2, Trim([prt_city]) AS City, dbo_partfile.prt_state, dbo_partfile.prt_zip_code, Format([prt_zip_ext],"0000") AS Zip4, Format([Enter Term Date],"mmmm dd"", ""yyyy") AS [Term Date], IIf([prt_sex]="F","Ms.","Mr.") AS Title, dbo_partfile.prt_status, dbo_partfile.prt_local_nbr
 FROM dbo_keyname_u INNER JOIN (dbo_partfile INNER JOIN dbo_employer ON dbo_partfile.prt_employer_code = dbo_employer.mis_number) ON dbo_keyname_u.kn_number = dbo_partfile.prt_ss_nbr
 WHERE (((dbo_partfile.prt_employer_code)=[Enter center number 01] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 02] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 02] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 03] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 04] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 05] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 06] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 07] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 08] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 09] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 10] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 11] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 12] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 13] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 14] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 15] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 16] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 17] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 18] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 19] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 20] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 21] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 22] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 23] Or (dbo_partfile.prt_employer_code)=[If needed enter center number 24]) AND ((dbo_partfile.prt_status)="A") AND ((dbo_partfile.prt_local_nbr)=205))
 ORDER BY dbo_partfile.prt_employer_code, Trim([prt_last]);
 
Last edited:
Time to scrap this setup. You cannot accomplish what you want using this method of data entry. Here's what you should do:

Create a new table to hold the center numbers the user wants to input.
Redesign your query to use that table, linking it to the prt_employer_code field.
Reconfigure your form to allow data entry/deletion from this table.
 
Yeah, that will help. Honestly, you won't have to start from scratch. Just need to set up a way to update/clear one new table. Report stays the same and your query just gets a few tweaks (new table brought in and linked to, delete current code criteria).
 
Hi Plog,

Here an update on editing prompts to only what I need. I decided to use a Multi-Select List box to Filter a Report and was able to get help from BaldyWeb.com here is the link http://www.baldyweb.com/multiselect.htm there is also a sample DB link on the web page. With his code I was able to accomplish what I wanted.

Thanks
 

Users who are viewing this thread

Back
Top Bottom