Access Help Requested

[kristi.daniel]

I have created a database based off of 1 table that holds all of my data. I need to create a form that queries off of the following columns from my table.

Acceptance Date
Sales Rep Name


Sales Region


I want to make a form which searches between a beginning and end date range, Sales Rep Name & Sales Region that can be pulled from these 3 queried selections all from the same form.

I am lost at how to do this and am also trying to build this while on chemotherapy which is making me even more clouded and confused as can be.

Does anyone feel patient enough to help a girl out?

Thank you for your help,
Kristi

[orange]

It would be helpful to readers if you showed us some sample records from your table.
Why exactly do you need a form?

General format of a select query with criteria -- Between Date1 and Date2

SELECT field1, field2, field3
FROM YourTableNameHere
WHERE  YourDatefIeld  Between Date1  AND  Date2

[CJ_London]

Welcome to the forum

on the forum, queries are communicated as SQL code - if you provide the real names for tables and fields, often you can then just copy and paste the code into your query builder which should make your life simpler. if you ask a vague question (remember we know nothing about what you are trying to achieve, or how your tables are constructed) then you will get a vague answer.

If you are new to access, resist the temptation to use spaces and non alphanumeric characters in your tables and field names - they will only cause you problems down the line. Also be aware of reserved words - using them can also cause problems, some 'favourite' ones are Name, Date, Type, Desc, Currency, Time, Month, Year. Here is a fuller list. If you need to use these characters/names on forms etc, use the caption property for the field in table design.

https://support.office.com/en-za/art...7-da237c63eabe

With regards your form, you haven't said what type of form, so I'll assume a continuous form (others are single form and datasheet)

You haven't said what your table is called so I'll assume it is myData
You also don't appear to have a primary key field but I'll assume you have and it is called EmployeePK. If you don't have one then you need to add one (otherwise what happens if you have two employees with the same name?). To add one, open the table in design view and add a new field called EmployeePK of type autonumber.

So to easily create this form, highlight the table, then click on the create tab in the ribbon, then click on 'more forms' and select 'multiple items'. Your form will be created instantly with the same name as your table. For clarity it is better to change the name of your form by adding frm to the beginning of the name, but up to you.

The form will be opened and displaying your data, so click on design view (in a dropdown on the top left button)

To create your 4 search options (salesrepname, salesregion, fromdate, todate)

To keep focused on your question, we'll put these in the form footer - it is not displaying at the moment, but hover over the lower area of the form footer grey bar and the cursor will change - drag down and some white space will appear - this is the form footer area where we'll put the search options.

the first two require a combobox. To do the first one, click and release on the combobox icon in the ribbon (8th along from the left), then move the mouse to the footer area, click and hold and drag to the size you require - suggest a similar width to the salesrepname control already created and about half the height. The wizards should be working so you will be prompted with some questions. Choose the following: '3rd option - find a record on my form...', next window select salesrepname, next window just click next and accept the given name for now. We'll come back to the coding required later

to do the second combobox, it is much the same as above, but with some changes - because you will see sales region repeated many times. To fix this once you have finished going through the wizard, open the properties window if not already open (you'll see it on the ribbon) do the following.

1. Ensure you have clicked on this new control - so click on the format tab and change number of columns from 2 to 1 and remove whatever is in the column widths property - leave it blank
2. now click on the edit tab and replace the rowsource with
SELECT DISTINCT salesregion FROM myData

This will then show each region just once

Now we move onto dates - both of these need a textbox, so as with the combo, this time click on the ab| button on the left and place where you want them in the footer. Now name one of them FromDate and the other ToDate - go to Properties>Other>Name. Whilst at it, check that the salesrep combo has been named salesrepname1 and the region combo as salesregion1. Either change them to these or modify the code below

At some point for all of these you are going to need to do other things such as change the label text to something relevant, ensure todate is greater than fromdate etc - but suggest you try these for yourself or make subject to a separate thread once you have the basics working.

So that this the basic look of the form finished, all that remains is some code to filter your list to what you require.

There are many ways this can be done but to do this we'll use a button - so drag on to the form footer a button control(3rd from left). Ignore the action options - they will not produce the code required - just click cancel. You can highlight the Command123 (number will vary) and type in what you want instead - such as Search.

Then in the properties window, select the event tab and on the On Click line click on the three dot carat on the far right and select code builder - the vba window will open up.

Click on the space between Private Sub.... and End Sub and type the following

me.filter=iif(isnull(salesrepname1),"","EmployeePK =" & salesrepname1 & " AND ") 'employee filter if used
me.filter=me.filter & (iif(isnull(salesregion1),"","salesregion ='" & nz(salesregion1) & "' AND " 'region filter if used
me.filter=me.filter & (iif(isnull(fromdate),"", "Acceptancedate>=#" & format(fromdate,"mm/dd/yyyy") & "# AND " 'from filter if used
me.filter=me.filter & (iif(isnull(todate),"", "Acceptancedate<=#" & format(todate,"mm/dd/yyyy") & "# AND " 'to filter if used
if len(me.filter>5) then ' some filters have been selected so filter the form
me.filter=left(me.filter,len(me.filter)-5)
me.filteron=true
end if

And that should be it.

Let me know how you get on