Blank Field returning #Error

[imintrouble]

Hello All!



I have some code that is below!

Public Function DegreeChecker(AGShrs As Integer, ASBAhrs As Integer, ASCJhrs As Integer, AAhrs As Integer) As String
Dim Degree As String

If ((AGShrs = 0) And (ASBAhrs = 0)) Then
Degree = "ASBA"
ElseIf ((AGShrs = 0) And (ASCJhrs = 0)) Then
Degree = "ASCJ"
ElseIf (AGShrs = 0) Then
Degree = "AGS"
ElseIf (AAhrs = 0) Then
Degree = "AA"
Else
Degree = "Potential"
End If
DegreeChecker = Degree
End Function

The problem is, the information that is populating the query which is running this code may not be there. As in some of the fields, AA, AGS, ASBA, and ASCJ might be left blank. Unless these four fields are populated by something, it returns #Error instead of the specified number.

My question is then this: Is there a way to say, If variable is blank, ignore and continue on to check if the next thing is 0, or if none are 0, state Potential.

Also on a side note. The information in this query is to be exported out to excel to be used as a Mail Merge file. I don't know anything about mail merge so if someone who knows can point me towards a website or set of instructions for this.

Thanks!

Imintrouble

(Im in trouble, your in trouble, We're all in trouble)

[RuralGuy]

Look into the Nz() function.

[imintrouble]

This sounds like it could work, but how would it work with my code? I tried to just add another elseif into there but that didn't seem to work.

[RuralGuy]

((Nz(AGShrs,0) = 0) And (Nz(ASBAhrs,0) = 0))

[imintrouble]

Thanks I suppose, but where would I put this into the code that I already have? Would it just go into another Elseif? so that

Elseif ((Nz(AGShrs,0) = 0) And (Nz(ASBAhrs,0) = 0)) Then
Degree = "whatever"

[RuralGuy]

You would use the Nz() function any time you might encounter a Null.

[hertfordkc]

I'm guessing that your function arguments are supplied from a query. If so, perhaps you should use NZ in the query e.g. AGSHrs:NZ([AGSHrs],0) for each of the fields.

[imintrouble]

well the way i have it now is that I've created a coded module which is called into the field i created in design mode for the query which displays whatever degree is most prominant, AGS = AA, AGS & AA < ASCJ, AGS & AA < ASBA. or if none have 0 in their field just display Potential.

[imintrouble]

In order to help even more with this annoying problem, i have attached a the part of my database that we have been discussing. Now a little bit on what exactly is needed.

First of all I only require assistance where the query is concerned, not the table, unless the root of this is in the table which I highley doubt.

Next, the only parts of the query which is involved are the fields,

Hrs to AGS, Hrs to AA, Hrs to ASBA, Hrs to ASCJ, and the Degree field.

How this is supposed to work is by entering numbers into those first four fields, you get an answer in the Degree field. However most of the time the information which will be populating these four fields will be blank. So there are two ways to do this, although I dont know the specifics.

1. make it to where if their is a blank datafield, to instead of doing some fancy code work, simply make it N/A instead, this way their will be no null values to work with.

2. Make it so that the code ignores the blank datafield to get an answer in the Degree field.


Thanks! Hope this helps.