Max Min Search 3

[susanlang]

Hi

I am trying to desing a search screen which searches fields on another form. The fields on the form are Minimum size and Maximum size. I need to be able to search for eg. a property is size 2000 to 5000 and I search for all properties of 1000 to 10000 I need it to be able to pick up all properties within that criteria. I can't seem to get it working quite right.

I appreciate all your help.

Thanks

Susan

 

[Frink]

Hallo,

Not sure exactly what you are trying to do, but if you want a form to display a set of records then you could set its recordsource, ie.

If your search results form is based on table tblData (which has a field called dblValue) and your Max and Min controls on your search form are called txtMax and txtMin then the code in your Search button click event (or whatever) could look like:
Forms!frmResults.Recordsource = &quot;SELECT * FROM tblData WHERE dblValue>=&quot; & me!txtMIN & &quot; AND dblValue<=&quot; & me!txtMAX

I hope this was something like what you were expecting,

- Frink

 

[JeremyNYC]

Susan,

Assuming you have a button that does the filtering, you'll want the click event to do something like this:

Dim strFilter As String
If Me!txtmin > 0 Then
strFilter = &quot;SizeField >= &quot; & Me!txtmin
End If
If Me!txtMax > 0 Then
If Len(strFilter) > 0 Then
strFilter = &quot; AND &quot; & strFilter
End If
strFilter = &quot;SizeField <= &quot; & Me!txtMax
End If
Call DoCmd.OpenForm(&quot;NameOfForm&quot;, , strFilter)

You'll have to change the name of the fields, of course, to match those in your database, but this should get you pretty close.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[Frink]

Hallo,

JeremyNYC's suggested method is another way of doing it, although I would make the code changes

Dim strFilter As String
strFilter=""
With Me!
  If IsNumeric(!txtMin) then
    If Len(strFilter) > 0 Then strFilter =  strFilter & " AND "
    strFilter = strFilter & "SizeField >= " & !txtMin
  Else
    !txtMin = ""
    Msgbox "Filtering value must be numeric",vbExclamation,"Filter Error"
    exit function
  End If
  If IsNumeric(!txtMax) then
    If Len(strFilter) > 0 Then strFilter =  strFilter & " AND "
    strFilter = strFilter & &quot;SizeField <= &quot; & !txtMax
  else
    !txtMax = &quot;&quot;
    Msgbox &quot;Filtering value must be numeric&quot;,vbExclamation,&quot;Filter Error&quot;
    exit function
  End If
End With
DoCmd.OpenForm &quot;NameOfForm&quot;, , strFilter

I don't mean to be picky, but...

Initialising variables is good programming practice (even though current versions of Access seem to initialise strings to &quot;&quot;. There's no guarantee that the next version of Access will.

I've put in some error handling, to allow for non-numeric values being entered in the filter fields. This is essential if users are to have any confidence (and therefore like) using your software.

I've made the code for processing each filter parameter identical. This makes it easier when you add a new one, and more importantly helps identify where a subroutine could be used to save replicating code. It does mean that the processing of txtMin contains an unnecessary 'if' statement, as strFilter will always be &quot;&quot; at this point, you could comment it out if you wanted, but I wouldn't remove it totally

The use of With will save Access having to determine Me all the time and therefore save a bit of time, although how much I don't actually know, but it's good practice.

I've also corrected the bug that if you specify both Min and Max values it only uses the Max one, but I suspect that was just a coding error.

Some of the above I think are important, some are personal preference, what do you think?

- Frink

 

[susanlang]

Hi

Thanks so much for your solutions. The code I have in just now for it is:-
Private Function TypeCriteria()
Dim TempCriteria As String
Dim ThisCrit As String
Dim TownCrit As String

TempCriteria = &quot;&quot;


'check the selection of the property types
CheckPropertyTypeSelection TempCriteria



If TempCriteria <> &quot;&quot; Then TempCriteria = &quot;(&quot; & TempCriteria & &quot&quot;

MinSalesValue = XNull(Me![Sales])
MaxSalesValue = XNull(Me![MaxSales])

If MinSalesValue > 0 And MaxSalesValue > 0 Then
ThisCrit = &quot;(((isnull([MIN SALES]) OR [MIN SALES] <= &quot; & CStr(MaxSalesValue) _
& &quot and (isnull([MAX SALES]) OR [MAX SALES] >= &quot; & CStr(MaxSalesValue) & &quot)&quot; _
& &quot; or ((isnull([MAX SALES]) OR [MAX SALES]>= &quot; & CStr(MinSalesValue) _
& &quot and (isnull([MIN SALES]) OR [MIN SALES] <= &quot; & CStr(MinSalesValue) & &quot))&quot;
ElseIf MinSalesValue > 0 Then
ThisCrit = &quot;([MIN SALES] >= &quot; & CStr(MinSalesValue) & &quot; Or [MAX SALES] >= &quot; & CStr(MinSalesValue) & &quot&quot;
ElseIf MaxSalesValue > 0 Then
ThisCrit = &quot;([MIN SALES] <= &quot; & CStr(MaxSalesValue) & &quot; Or [MAX SALES] >= &quot; & CStr(MaxSalesValue) & &quot&quot;
Else
ThisCrit = &quot;&quot;
End If

If Len(ThisCrit) > 0 Then
If TempCriteria = &quot;&quot; Then
TempCriteria = ThisCrit
Else
TempCriteria = &quot;(&quot; & TempCriteria & &quot and &quot; & ThisCrit
End If
End If

Not sure why this is not working.

thanks

Susan

 

[MichaelRed]

Hmmmmmmmm,

my thought is along the lines of picky.

If you are really going to formalize the process, it seems -to me- that you would make the snippet into a formal (and complete) procedure, this would need to have a procedure declaration line and an end statement.

Checking (verification and validation) would need to include that the &quot;min&quot; was actually less than (or perhaps equal to) the max. Although, there MIGHT be different business 'rules' which would permit a single parameter (min | max), Ms. Lang has not stated this, or shown an example.

A truly general purpose version of this would probably only check that the min and max were of the same type (if two were present), not that they were actually numeric. Futher, the general purpose routine would be less dependent on a specific form - either for the parameters or for the form to be opened. Personally, I would construct THIS routine to accept the min and max textbox contents, and return the filter string, leaving the specific form details to the calling procedure. This would minimize the maintenance, as well as encapsulating the form specific details in one place and the generic (filter) string construction in another, which I feel is more appropite.

On the other hand, Ms. lang has NOT responded to your first post, so some details of what is REALLY requested are not clear or complete, and your initial simple response is, perhaps, adequate to her needs. Further, Jermy's response is in line with common (if perhaps deplorable) practice of illustrating techniques with code fragments (often refered to as snippets in Tek-Tips) which concentrate on simple illustration of the VERY SPECIFIC issue of the request without the burden of formalization.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Frink]

Hallo,

I guess it was kind of picky. One of those Monday mornings

Susan, what I'm guessing is that you have a control called[Sales] which is the minimum search criteria, and a control called [MaxSales] which is the maximum search criteria.

Does your recordsource (table or query) have a [MIN SALES] and a [MAX SALES] field? Or does it just have a Sales field?

Can you give us any more information about what you're trying to do?


If you can, then why don't we all (and anyone else out there who wants to) have a go at writing a complete function (strGetFilter) to return the criteria string for you to use as a filter.
I reckon we could assume that Me!txtMinSales and Me!txtMaxSales will hold any filter criteria, and that a button on the same form will include the line:

DoCmd.OpenForm &quot;NameOfForm&quot;, , strGetFilter()

No prize for the quickest, but it would be interesting to see what people come up with.

- Frink

 

[susanlang]

Hi

Sorry I am relatively new to programming so I probably don't make much sense. I have designed a search screen which allows the user to enter various types of criteria one of which is a minimum and maximum sales fields. The records are held on a table and have the fields Min Sales and Max Sales. I then want it to give the results to the form. I have the above code in that particular section which does work for most scenarios but not for 1000 - 10000 as it doesn't pick up details for the company which is 2000 - 5000 for some reason and I can't see why.

Thanks so much again for your help it's much appreciated.

Susan