Retrieving Zero/All Records

[FrankMars]

I have written the following on the criteria line in the qbe grid of my query;[Forms]![zzfaCriteriaForm]![tbxStreetName]. I get zero records if nothing is filled out in the tbxStreetName field on my form. I would like to get all records if nothing is filled out in the tbxStreetName field. Is this possible? Thank you in advance.

 

[PHV]

=[Forms]![zzfaCriteriaForm]![tbxStreetName] OR [Forms]![zzfaCriteriaForm]![tbxStreetName] IS NULL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Use:

[Forms]![zzfaCriteriaForm]![tbxStreetName] Or [Forms]![zzfaCriteriaForm]![tbxStreetName] Is Null

Duane
Hook'D on Access
MS Access MVP

 

[FrankMars]

Thanks PHV and dhookom. Is there a way to accomplish this without using OR/IS NULL. Maybe something with Nz? The reason I am asking is because I have a number of OR/IS NULL statements in the query and it seems to be putting a drag on it.

 

[dhookom]

How are you using the query results? Is this a record source for a form or report? How much of a "drag"?

I often use code to modify the SQL property of a query or build a where condition when opening a form or report.

Duane
Hook'D on Access
MS Access MVP

 

[FrankMars]

The query results are going into a report. I have created a "criteria" form with about 15 fields in which I might fill in anywhere from 0 to 15 fields which query the table. When I refer to these fields as criteria in the qbe grid, many of them need the OR/IS NULL statement which causes the grid to become very large. This seems to cause a "not enough memory" error message at times. When I try to add a field to the qbe grid, the error message appears. My workaround for this has been to delete the grid, add the field and re-write all of the criteria statements for the grid.

 

[dhookom]

I would try code like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.tbxStreetName) Then
  strWhere = strWhere & " AND [StreetName] = '" & _
    Me.tbxStreetName & "' "
End If
If Not IsNull(Me.tbxCity) Then
  strWhere = strWhere & " AND [City] = '" & _
    Me.tbxCity & "' "
End If
If Not IsNull(Me.tbxZip) Then
  strWhere = strWhere & " AND [ZipCode] = '" & _
    Me.tbxZip & "' "
End If
DoCmd.OpenReport "rptReportName", acViewPreview, , strWhere

Duane
Hook'D on Access
MS Access MVP

 

[FrankMars]

Thanks Duane - I'll give the code a try.

 

[ARYankee]

I am having a similar issue. I have 3 criteria that can be used on a form for searching. Any or all can be used. My problem is that if my NSN criteria is NULL, it returns all results with a value in NSN. I want it to return results whether it is or isn't NULL. The other 2 criteria will never be NULL. Here is my query code:



SELECT [All].NSN, [All].Description, [All].Category, [All].[OD Iron #], [All].[Saturn Surplus #], [All].[NAPA #], [All].[Carquest #], [All].Notes, [All].[Autozone #], [All].[Eastern Surplus #], [All].[Memphis Equip #], [All].[O'Reilly's #], [All].[Bumper to Bumper #]
FROM [All]
WHERE (([All].NSN) Like [Forms]![Main Parts Search]![Label6].[Caption]) AND (([All].Description) Like [Forms]![Main Parts Search]![Label20].[Caption]) AND (([All].Category) Like [Forms]![Main Parts Search]![Label15].[Caption])
ORDER BY [All].Category;

 

[dhookom]

I am very confused. I have never seen any use of the "Caption" property in a query criteria. Everyone else that I am aware of uses the value of a bindable control.

Can we assume your captions have wildcards in them? Otherwise, using "Like" makes no sense and should be replaced by "=".

You can try:

WHERE (([All].NSN[b][red] & ""[/red][/b]) Like [Forms]![Main Parts Search]![Label6].[Caption]) ...

Duane
Hook'D on Access
MS Access MVP

 

[ARYankee]

Wow!!! That did it! The code I used I copied from another database that we had here that was similar that was obviously programmed by some one else. It had very few comments so it has been difficult to figure out what was happening. I'm still really green at this. Now it is all starting to make a little more sense to me. I appreciate you helping me out.

 

[ARYankee]

Oh I almost forgot.

Can we assume your captions have wildcards in them?

Yes I have wildcards in them.

 

[dhookom]

Can I ask how your users update the caption property? I expect there is some un-necessary work in the application.

Duane
Hook'D on Access
MS Access MVP