Search Query

[rhernand]

I created a search form and query. If I do not enter anything I want to retrieve all the records. Otherwise I want to retrieve the records that meet the critertia I enter. Example, I want to enter, >500, <500 or =500. However, no matter what I enter, I always get the records equal to 0. This is my IIf statement.

IIf(IsNull([Forms]![frm_search]![KVA]),([tbl_oil_sample].[kva]) Like "* ",([tbl_oil_sample].[kva]) Like [Forms]![frm_search]![KVA])

 

[GumbyD]

Try the following:

Like & IIf(IsNull([Forms]![frm_search]![KVA]),"*",[Forms]![frm_search]![KVA])

The only other problem you may be having is that if the datatype is numeric than this may not work.

If it is numeric data you may want to create a fake value that you know will return all of the values in your recordset. For example:

IIf(IsNull([Forms]![frm_search]![KVA]),>-9999999999,[Forms]![frm_search]![KVA])

I am sure there are other ways too. Maybe someone out there can provide a cleaner solution.

GumbyD

 

[rhernand]

Thanks, I will try both. The data is numeric and I may have a problem with the "<" and ">" signs. Will let you know

 

[rhernand]

No Bueno

Since my data is numeric, I tried the second IIF, but it did not work for the TRUE. However, it does not for the FALSE. If I put a number it finds that data, but if I leave it blank, I get a null result, no records.

Helppp.

 

[GumbyD]

What are you typing in the text box on the form that is and is not working?

 

[rhernand]

In the form KVA Text Box, If I type 50, I get all the data KVA records equal to 50. However, If I skip over the KVA Text Box and not type anything, I get no records. I am expecting to see all KVA records.

 

[GumbyD]

OK that was a lot harder than I wanted it to be. I ended up having to build the query in code and then delete the existing query and build a new query def for with the criteria from the from in place. You will need to modify this with your information.

Private Sub Command2_Click()
Dim SQL As String
Dim QueryHolder As QueryDef

'If the field is null then use SQL statement with no criteria
If IsNull(Me.KVA) Then
SQL = "SELECT KVATable.ID, KVATable.NUMBER " & _
"FROM KVATable;"
Else
'If there is Criteria then put in the where clause
SQL = "SELECT KVATable.ID, KVATable.NUMBER " & _
"FROM KVATable " & _
"WHERE (((KVATable.NUMBER)" & Me.KVA & "));"
End If
'delete the existing query definition
CurrentDb.QueryDefs.Delete "kvaquery"
'Add the query with the new query definition
Set QueryHolder = CurrentDb.createquerydef("KVAQuery", SQL)
'Open the query and display the results
DoCmd.OpenQuery "kvaquery", acViewNormal

End Sub

GumbyD

 

[rhernand]

Working

Thanks for your help. Tried this suggested by a co-worker and it worked.

IIf([Forms]![frm_search]![KVA]<>" ",[Forms]![frm_search]![KVA],[kva])

 

[GumbyD]

Glad you are working now. Just because I am interested- what is the KVA field in the query that is used if the form field is an empty string?

GumbyD

 

[rhernand]

kva

The table contains electric transmission equipment data. kva is the size rating for transformers. Other data in the table is the equipment serial number, manufacturer, etc. The search form has several search text boxes; MFG, SERNO, KVA.