Determine results based on form entries

[nosaj03]

Hey there! First post looking for some assistance. Here's my situation.

I have a form where users answer a series of questions through combo and check boxes that grade their experience. When they have made all of their selections and submit, I need to provide a result based on their answers.

The result varies based on the answers that are provided but only for specific questions ie some answers provide a positive or negative result.

I have a function that checks that answers for the specific questions and references a table with all of the possible answers, assigns a numerical value and then displays a result based on the lowest possible number.

Here's my issue, there are certain instances where the question does not have to be answered and is left blank therefore resulting in a null value. When my function runs, the blank answers result in a -1 which makes it the lowest value and giving the wrong result.

How do I account for null values returning a -1?

Any help would be greatly appreciated!

 

[sneuberg]

You can use the Nz function to assign an alternate value for null.

 

[nosaj03]

You can use the to assign an alternate value for null.

Ive tried using the NZ function as follows and it still shows as -1

Dim myIntarray(8) As Integer
Dim myString As String

myString = vbNullString
myString = Nz(Forms!frm_example!Combo0.Value, 2)
myIntarray(0) = intEffortAsInt(myString)

 

[sneuberg]

Check to see if the Combo0 combo box has a default values of "". The statement

myString = Nz(Forms!frm_example!Combo0.Value, 2)

should have raised an error when you didn't have the Nz function there so it probably isn't null when it's emply. Alternatively you could try,

myString = IIf(Len(Forms!frm_example!Combo0.Value & vbNullString) = 0, 2, Forms!frm_example!Combo0.Value)

which checks for both empty strings and nulls.

 

[arnelgp]

is it correct that myString is a string not numeric (integer)? what is function intEffortAsInt()?

 

[nosaj03]

is it correct that myString is a string not numeric (integer)? what is function intEffortAsInt()?

The function intEffortAsInt is a Dlookup that looks up the possible answers and the designated value.

 

[sneuberg]

If you upload your database perhaps I can figure out where the problem is.

 

[nosaj03]

If you upload your database perhaps I can figure out where the problem is.

Unfortunately my DB contains proprietary and confidential information. Is there another way for you to assist with the troubleshooting?

In general, Im having an issue with null values in a combo box returning a -1 value and the nz function and the other provided solutions are still returning a negative value.

There error Im also getting is that the value is not being found in my dlookup table. How do I put "null" in a table so that it can be accounted for by the dlookup?

 

[sneuberg]

For starts put the Debug.Print statement in the code as shown and let me know what you get in the Immediate Window when this code is run.

Dim myIntarray(8) As Integer
Dim myString As String

myString = vbNullString
myString = Nz(Forms!frm_example!Combo0.Value, 2)

Debug.Print "Forms!frm_example!Combo0.Value = " & Forms!frm_example!Combo0.Value
Debug.Print "myString = " & myString
myIntarray(0) = intEffortAsInt(myString)
Debug.Print "myIntarray(0) = " & myIntarray(0)

Please post the code for the intEffortAsInt function.


As far as the nulls in the table that's what normal gets entered when no values has been inserted and there is no default value. So what there is place of nulls and how did they get there?

If you ending up with empty strings in the field instead of nulls you first need to track down what's putting them there and correct that. You can fix any existing problems with update queries. Just use the query builder in the Update To put Null and in the Critieria put "". Please back up you system before doing this just in case or make copies of the tables.

If you have 0 values in fields that have had no value assigned that because Access gives you a default value of 0 for numbers. So if you don't want that change that in the table and you can update them to nulls the way i described above the only problem being some data might have legitimate zeros/

 

[nosaj03]

For starts put the Debug.Print statement in the code as shown and let me know what you get in the Immediate Window when this code is run.

Dim myIntarray(8) As Integer
Dim myString As String

myString = vbNullString
myString = Nz(Forms!frm_example!Combo0.Value, 2)

Debug.Print "Forms!frm_example!Combo0.Value = " & Forms!frm_example!Combo0.Value
Debug.Print "myString = " & myString
myIntarray(0) = intEffortAsInt(myString)
Debug.Print "myIntarray(0) = " & myIntarray(0)

Please post the code for the intEffortAsInt function.


As far as the nulls in the table that's what normal gets entered when no values has been inserted and there is no default value. So what there is place of nulls and how did they get there?

If you ending up with empty strings in the field instead of nulls you first need to track down what's putting them there and correct that. You can fix any existing problems with update queries. Just use the query builder in the Update To put Null and in the Critieria put "". Please back up you system before doing this just in case or make copies of the tables.

If you have 0 values in fields that have had no value assigned that because Access gives you a default value of 0 for numbers. So if you don't want that change that in the table and you can update them to nulls the way i described above the only problem being some data might have legitimate zeros/

I entered the code and this is the result in the immediate window "myIntarray(0) = -1"

I also have a debug print already in my code which displays
"The String value was not found in the table, returning -1" which upon looking I set values to -1 if it is not found in the reference table of the dlookup.

I do have "0" and "-1" in my table but it is for the checkboxes.

Ive been experimenting with the values of the answers and converted them to negative numbers. Since an answer that is blank doesnt necessary translate into a negative experience, Ive change the values to -1 through -3. This way the -1 will be a positive result and the -3 shows as a negative result.

 

[sneuberg]

I need a better understanding of the problem. Could you make a copy of your database, delete the data, and upload that? At this point I don't even know what questions to ask.