empty fields causing problems (1 Viewer)

Christos · Apr 28, 2003

The query below works, when there are no empty fields in the column Total. If there are empty fields is not working.

query = select INT( Total / 10 ), COUNT(*) from POINT
where Member_Title = 'Mr'
GROUP BY INT( Total / 10 )
ORDER BY INT( Total/ 10 )

Can please anyone tell me how can i do it to work when there are empty fields?

Nouba · Apr 28, 2003

It depends on your interpretation of Null values. With the Nz-function (see Access Online Help) you can define any value you want instead of a Null value by giving the function the second optional parameter. Keeping the optional parameter away a value of 0 is used on numerical field types and "" (vbNullString) is used on text fields.

Code:

Query = Query & "SELECT" & vbCrLf
Query = Query & "  Int(Nz(Total)/10)" & vbCrLf
Query = Query & ", Count(*) & vbCrLf
Query = Query & "FROM POINT" & vbCrLf
Query = Query & "WHERE Member_Title = 'Mr'" & vbCrLf
Query = Query & "GROUP BY Int(Nz(Total)/10)" & vbCrLf
Query = Query & "ORDER BY Int(Nz(Total)/10)"

or you can skip records, where Total is Null.

Code:

Query = Query & "SELECT" & vbCrLf
Query = Query & "  Int(Total/10)" & vbCrLf
Query = Query & ", Count(*)" & vbCrLf
Query = Query & "FROM POINT" & vbCrLf
Query = Query & "WHERE" & vbCrLf
Query = Query & "  Member_Title = 'Mr' AND" & vbCrLf
Query = Query & "  Total Is Not Null" & vbCrLf
Query = Query & "GROUP BY Int(Total/10)" & vbCrLf
Query = Query & "ORDER BY Int(Total/10)"

Christos · Apr 28, 2003

thanks

thanks for the help and the explanation

empty fields causing problems (1 Viewer)

Christos

Registered User.

Nouba

Registered User.

Christos

Registered User.

Similar threads

Users who are viewing this thread