Count of records when no records are returned 2

[jamierobinson]

I am trying to have a field, on my form, display "No records found" when no records are found and the number of records when records are found. I have a query that asks for input from the operator and I have a field that counts [count(*)] the records returned, but when no records are returned, the field is blank. How do I report that no records where found, besides that fact that the form is blank

 

[GingerR]

instead of =count(*), put

=IIf(Count(*)=0,"No Records Found",Count(*))

 

[jamierobinson]

Thanks for the quick response. However, when no records are returned, the field with the IIF formula in it is blank. How can I get around that?

 

[missinglinq]

If you want to populate a given textbox with either a number (the number of records returned) or a message ("No records found&quot you have to populate it with a string variable; the message is already a string, so you would have to convert the NUMBER of records returned into a string representing that number. This can be done using the str function.

I think something like this will do what you need done. Under whatever proceedure you want to do this (such as Form Load, etc) try this:

If DCount(&quot;*&quot;, &quot;YourQuery&quot < 1 then
TxTTotalRecords= &quot;No records found&quot;
Else
TxtTotalRecords=str( DCount(&quot;*&quot;, &quot;YourQuery&quot)
End if

Hope this helps.

The Missinglinq

&quot;It's got to be the going,
not the getting there that's good!&quot;
-Harry Chapin

 

[GingerR]

hmmmm funny. in mine, when there are no records, it returns a zero.

try then iif(count(*) is null,&quot;blah&quot;,count(*))

 

[jamierobinson]

I still get a blank field. Could it be the fact that I am running a parameter query? Or could I have the field formatted incorrectly? I have the field in the header of the form. Should it be somewhere else. Everything works as long as there is data returned by the query.

 

[Rolliee]

what is your code to get the recordset? If it is

set rs = currentdb.openrecordset(SQLstring)

then

if rs.eof and rs.bof then msgbox &quot;There are no records.&quot;

will do nicely.

rollie@bwsys.net

 

[jamierobinson]

Rolliee-
No code... Just a query that asks the user for a search string as a criteria. The search field is a free form text field and I am using: Like &quot;*&quot;&[What do you want to query on?]&&quot;*&quot; to prompt for the search string. A text field, in the resulting form, displays the number of records found as long as any matches are found, but when no matches are found the text field is blank. I was using: =Count(*)&&quot; Records found&quot; as the control source of the text field, and wanted to use something like: =IIF(count(*)=0,&quot;No records found&quot;,count(*)). I am not very practiced working with Access. Any other suggestions?
-jamie

 

[GingerR]

how about this instead (i know this looks long but it is not hard):
is the user launching this process by like clicking a button? this would be cooler: instead of having the form opening blank with something in it that says there are no records, you just pop up a message box that says &quot;No Records Match!&quot;.

some questions: are the search results displayed in the form? or is the form just telling you how many records there are? how is the form being opened? is the user just opening the query from the database window, or is it being opened via a button? is the query the recordsource for the form?

Also, another idea would be instead of prompting the user for their search string, have a place in a form for them to type it: this way, say they search on &quot;BLOODHOUND&quot; and it doesnt get any matches, they can easily change it to &quot;HOUND&quot;.

make a form called frmSearch.
put on a text box called txtSearch.
put on a button called btnSearch. cancel the wizard when it comes up.

in the button's OnClick property:

Private Sub btnSearch_Click()
Dim strSearch, strWhere
strSearch = Me.txtSearch

'concoct a WHERE clause based on user input
strWhere = "[FieldName] like '*" & strSearch & "*'"

'If no records in the data table match, pop up a message and quit the subroutine
If DCount("ID", "TableName", strWhere) = 0 Then
    MsgBox "There are no records!", vbOkOnly
    Exit Sub
Else   'There is a match, so open the resulting data records
    DoCmd.OpenForm "FormName", acNormal, , strWhere
End If
End Sub

so the user can type in what they are looking for in the text box on the frmSearch, then hit a button.
you have to substitute: your table name for TableName, an ID field name from your table for ID, and the data field you are searching in for FieldName. also, at the end, my &quot;FormName&quot; will be a form whose record source is merely your data table, with whatever data you want displayed for the records that get returned. you can set it's properties to 'datasheet' so it looks like a table or query.

hope this gives you an idea of the choices you have.
g

 

[Rolliee]

jamie,

send me your email and the query as a txt file and I will send you back an example that works. I need to know if the user can search from more than one field also.

rollie@bwsys.net