Criteria based on user input

[Alsail77]

I am very new to access so please forgive me if this is an obvious fix.



I have a query which searches the database for records and outputs all matches to a continuous form. On Form "Main" there are 2 text boxes where the user can enter the name and the address and run the search. This works.

I added 2 more boxes to limit the search to a date range, DateMin and DateMax. This checks the "NextMaturity" field which is a calculated field which is equal to the earlier of two date fields. I got this to work with the below text as a criteria of the "NextMaturity" field in the query:

Between [Forms]![Main].[DateMin] And [Forms]![Main].[DateMAx]

The problem is the dates are not, and can not, be required and can have null values. Since the user will not always need to limit the search by a date I added a check box on the Form "Main", "Check40". When checked I want the query to include the date as a criteria, when not checked I do not want the date considered. I tried writing and IIF statment along the lines of IIF([Forms]![Main].[Check40]=true, Between [Forms]![Main].[DateMin] And [Forms]![Main].[DateMAx]) (as well as several variations) but this caused no records to ever be returned.

If this is possible and anyone can help it would be appreciated

[ssanfu]

See if this helps..

http://access.mvps.org/access/queries/qry0001.htm

[Alsail77]

Ssanfu - I looked at the link you provided but am at a loss as to how to carry that over to what I need to do. Essentially, if the checkbox at [Forms]![Main]![check40] = true (is checked), I want Between [Forms]![Main].[DateMin] And [Forms]![Main].[DateMAx] to be the criteria. If [Forms]![Main]![check40] = false (not checked), I do not want there to be any criteria filter for this field.

[Alsail77]

I think I fixed my own problem but am confused. I went into the SQL view and added the code in red below. This appears to work but I know nothing about SQL. In the design view a new column was created with the red text as the Field and <>False as a Criteria. Does this make sense? Or am I asking to break something down the road?

WHERE (((PropData.Property) Like "*" & [Forms]![Main].[PropSearch] & "*") AND ((PropData.Address) Like "*" & [Forms]![Main].[AddressSearch] & "*") AND (IIf([Forms]![Main]![Check40]=-1, ((PropData.NextMaturity) Between [Forms]![Main].[DateMin] And [Forms]![Main].[DateMAx]),"")))

[ssanfu]

OK, here is one way:
Create two more text boxes on the form "Main":

Name: DateMin1
Visible = No
Default = #1/1/1800#

Name: DateMax1
Visible = No
Default = #12/31/3000#


Add code:

Option Compare Database
Option Explicit

Private Sub Check40_Click()
   If IsDate(Me.DateMin) And IsDate(Me.DateMax) Then
      If Me.Check40 = True Then
         Me.DateMin1 = Me.DateMin
         Me.DateMax1 = Me.DateMax
      Else
         Me.DateMin1 = #1/1/1800#
         Me.DateMax1 = #12/31/3000#
      End If
   End If
   Me.Requery
End Sub

Private Sub Form_Load()
Me.Requery
End Sub

Change the criteria in the query to:

Between [forms]![main].[datemin1] And [forms]![main].[datemax1]


If the dates in Me.DateMin and Me.DateMax are valid dates, put them in Me.DateMin1 and Me.DateMax1. Otherwise use the default dates of #1/1/1800# and #12/31/3000#.
Both Me.DateMin and Me.DateMax must be valid dates..

[ssanfu]

I think I fixed my own problem but am confused. I went into the SQL view and added the code in red below. This appears to work but I know nothing about SQL. In the design view a new column was created with the red text as the Field and <>False as a Criteria. Does this make sense? Or am I asking to break something down the road?

WHERE (((PropData.Property) Like "*" & [Forms]![Main].[PropSearch] & "*") AND ((PropData.Address) Like "*" & [Forms]![Main].[AddressSearch] & "*") AND (IIf([Forms]![Main]![Check40]=-1, ((PropData.NextMaturity) Between [Forms]![Main].[DateMin] And [Forms]![Main].[DateMAx]),"")))

That is another way. But I would use

(IIf([Forms]![Main]![Check40]=-1, ((PropData.NextMaturity) Between [Forms]![Main].[DateMin] And [Forms]![Main].[DateMAx]),TRUE)))


So if Check40 is FALSE, the Where clause would evaluate to:

WHERE (((PropData.Property) Like "*" & [Forms]![Main].[PropSearch] & "*") AND ((PropData.Address) Like "*" & [Forms]![Main].[AddressSearch] & "*") AND TRUE


See if that makes any difference.