Combo Box, Check Box, Max Date, and Comments: Who Knows??

[Andy]

Hi,

We have a form developed with assistance form this site. It works great!
However, as usual, there is "database creep", meaning the requirements have
changed once again.

The new desire is to be able to select:
a) "x" number of most recent comments OR
b) all comments between certain dates OR
c) "x" number of most recent comments between certain dates

The form that works great and all related reports and queries have been
duplicated and renamed so we do not destroy the original working forms,
queries, and reports.

The form we want to modify has 2 calenders, 3 combo boxes (building, OPR,
status), and many command buttons that allow the report to be printed, the
form to be closed, and the calendars to be changed (change dates by either
the command button or by actually manually inputting dates on the calendars.
How we use the form: we select the start date, we select the end date, we
select the building #, we select the OPR, and we select the status. We then
click on the "Preview Report" button. Currently, the report that is returned
lists all comments ever entered for the building, OPR, and status that was
selected. We also have a separate report (not based on this form but based
on a parameter query) that brings back only the single most recent comment
entered for the building, OPR, and status typed when prompted.

How can we add a button, a box, or whatever to allow us to do a, b, or c
desires from above?

We can add the code for the form here, or email it to you, or whatever else
you need. We are stumped on how to even get going on this one.

Thanks for the help.

Andy

 

[xRoachx]

Hey Andy, scope screep is one of the most difficult things to deal with as a
BA or a programmer. At some point, you just have to say, "we'll release that
in the next version damn it!" Btw, that was one hell of a post...

Anyhow, what you want to do is not difficult but will require that you add a
text box for part a and modify the underlying SQL for a, b, and c. Since you
were able to get this far, I won't start out too detailed but will try to
give you some insight in how to proceed:

Part a:

Use TopValues property of SQL to return x number of most recent record. I
pulled this example from the MS help file and modified it to include a value
inputed by the user:

The following example assigns an SQL string that returns the top 10 most
expensive products to the RecordSource property for a form that will display
the ten most expensive products.

dimm strGetSQL As String
dim strTopValues as String

strGetSQL = "SELECT TOP " & strTopValues & " Products.[ProductName] " _
& "AS TenMostExpensiveProducts, Products.UnitPrice FROM Products " _
& "ORDER BY Products.[UnitPrice] DESC;"

Part b & c:

Use BETWEEN. Check the values inputed for the 1st & 2nd calendar. If the
values are not null, capture the values in variables and insert these in your
SQL's WHERE clause. IF the values are null, return all of the records.

I hope this points you in the right direction.