Check for no value

[jgelpi16]

I have a form that loads in the background (Form1) and I assign the value from one of the text boxes in Form1 to a variable (var1) in my main form (formMain). Form1 is based on a query that has the potential of returning nothing. When I try to do a simple var1 = form1.textbox.value I get the error "you entered an expression that has no value.". Is there a way to check for no value? If there is no value I simply want to make var1 = 0. I tried

if form1.textbox.value is null then
     var1 = 0
else
     var1 = form1.textbox.value
end if

However, that did not work and I got the same error.

[pbaldy]

I use:

If Len(Whatever & vbNullString) = 0 Then

which tests for both Null and a Zero Length String. Given what you're doing, you could simply use the Nz() function:

Var1 = Nz(Whatever, 0)

[jgelpi16]

Nz() function did not work. Still getting the error message "You entered an expression that has no value".

[pbaldy]

It would help to see exactly what you tried. If the form might have a ZLS, try the first test.

[jgelpi16]

This is the code I am using. It worked...The interesting this is if you'll notice this is copied directly from my code...I am not aware that "Whatever" is an operator...

If Len(Whatever & vbNullString) = 0 Then
        varAging = 0
    Else
        varAging = Form_SfrmARsAge.txtAgePercent.Value
    End If

[pbaldy]

There isn't; "Whatever" was intended to be replaced by your form reference. I said it that way because it could be a variable, form reference, etc.

[jgelpi16]

Ok, I feel pretty stupid right about now! So the code should read as below:

If Len(form.textbox.value & vbNullString) = 0 Then
        varAging = 0
    Else
        varAging = Form_SfrmARsAge.txtAgePercent.Value
    End If

[pbaldy]

Yes, presuming that's a valid way to reference the form. It doesn't look like it to me, but I know there are multiple ways. I use these:

Forms Refer to Form and Subform properties and controls

[jgelpi16]

Well...unfortunately this is not working now either....

    If Len(Form_SfrmARsAge.txtAgePercent.Value & vbNullString) = 0 Then
        varAging = 0
    Else
        varAging = Form_SfrmARsAge.txtAgePercent.Value
    End If

[pbaldy]

The name implies that's a subform; is it? If so, see the link for proper syntax. If that doesn't help, can you post the db?

[jgelpi16]

I tried the referenc you suggested:

    If Len(Forms!frmCollStats!SfrmARsAge.Form.txtAgePercent.Value & vbNullString) = 0 Then
        varAging = 0
    Else
        varAging = Form_SfrmARsAge.txtAgePercent.Value
    End If

Unfortunately I cannot post the DB. Let me see if I can explain my dilemma again.

I have a Query that returns results. It is possible for the query to return no results and therefore when the subform loads it loads blank (text boxes do not display or anything). When that occurs I want my variable to be assigned 0, otherwise I want my variable to be assigned the value of the text box on the form. I guess instead of using a form I could run my SQL out of VBA but I'm not well versed in that area. My SQL statement is below.

SELECT QryZZZOver2yrsOld.Emp, QryZZZOver2yrsOld.Balance AS Over2Yr, Sum(TblARReportYTDReport.EndBal) AS YTDEndBal, [Over2Yr]/[YTDEndBal] AS YTDPct
FROM QryZZZOver2yrsOld INNER JOIN TblARReportYTDReport ON QryZZZOver2yrsOld.Emp = TblARReportYTDReport.RACF_No
GROUP BY QryZZZOver2yrsOld.Emp, QryZZZOver2yrsOld.Balance
HAVING (((QryZZZOver2yrsOld.Emp)=[Forms]![FrmCollStats]![cboRVNum]));

[pbaldy]

Ah, the empty subform problem. I use this:

http://www.mvps.org/access/forms/frm0022.htm

[jgelpi16]

That was very helpful. Thanks a lot!