Null value query results

[Courtman]

Hi again.

When I am compiling a query to look for a result, if it is null (no results found), it blanks the results form I am trying to display. I have tried running the NZ function on the form, this doesn't seem to work and I am unsure of how to run this in a query? Any ideas??

[This message has been edited by Courtman (edited 12-16-2001).]

 

[Rich]

I assume that you don't want the form to open if the recordset is empty in which case put the following code in the on open event
If (RecordsetClone.RecordCount = 0) Then
DoCmd.Close
Beep
MsgBox "There are no records recorded for that period.", vbInformation, "No Records Recorded"
End If

 

[Courtman]

I do want it to display the form, just with a null value in!! Sorry!

 

[jwindon]

I'm guessing here that you want to be able to ADD a record with the blank form?!?!

Modify Rich's code on the OnOpen event of the form (which should be based of the query) to something like this:

If (RecordsetClone.RecordCount = 0) Then

DoCmd.OpenForm "Form1", acNormal, , , acFormAdd
Else
Exit Sub
End If

 

[Courtman]

Sorry again, not very clear this one. I'll start again from the beginning.

I am running a query on a pilots logbook to count the number of landings in the last 90 days. The query may return 0, in which case I want it to display 0 in a box so the pilot can get off his ar*e and fly some sectors, thus preventing his license expiring!!! However, if the results of the query are blank (null), it won't display the form. For an example of what the forms look like, visit http://www.peanutwood.net and visit the Flying Page, then Logbook 2001 and scroll down to look at the Recency page. The fields I am struggling with are the number of landings in 90 days, especially if there have been none.

PS. The data in the screenshots are out of date, most of what seems not to work, now works ... but you get the idea

 

[Rich]

If your query returns 0 then you don't have a null entry. Use the Nz function in the query to return 0. ie. Nz([Somefield],0)

 

[Courtman]

Damn thing still won't work. I run a query to count the number of landings in 90 days in the logbook, this returns the value zero. I then run another query to group all the sub-queries together which I want to output to a text box on a form. Its the result of this query that returns null. I've tried "Group by" and all the other functions but it still doesn't want to play...