Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

if then statement

Discussion in 'Information Technology' started by LouD, Jul 28, 2006.

Tags:
  1. LouD

    LouD
    Expand Collapse
    Guest

    i have an If Then statement on the close event of a form, which is based on a
    query.
    if the form/query doesnt find anything i get an error "you entered an
    expression that has no value".
    what code would a i use to keep from getting this error?
    thanks in advance.
     
  2. Loading...


  3. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Hi Lou,

    You can likely use the Nz function, but it's kind of difficult to say with
    any certainty. Please post the code so that we can see exactly what is going
    on.


    Tom Wickerath, Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "LouD" wrote:

    > i have an If Then statement on the close event of a form, which is based on a
    > query.
    > if the form/query doesnt find anything i get an error "you entered an
    > expression that has no value".
    > what code would a i use to keep from getting this error?
    > thanks in advance.
     
  4. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Mon, 8 May 2006 18:02:01 -0700, LouD
    <LouD@discussions.microsoft.com> wrote:

    >i have an If Then statement on the close event of a form, which is based on a
    >query.
    >if the form/query doesnt find anything i get an error "you entered an
    >expression that has no value".
    >what code would a i use to keep from getting this error?
    >thanks in advance.


    Care to post your code?


    John W. Vinson[MVP]
     
  5. LouD

    LouD
    Expand Collapse
    Guest

    If Forms![frm_report].[snumber] = Forms![frm_verify].[check] Then
    Forms![frm_report].[snumber] = Forms![frm_report].[snumber] & " (R)"
    Else
    DoCmd.CancelEvent
    End If

    "John Vinson" wrote:

    > On Mon, 8 May 2006 18:02:01 -0700, LouD
    > <LouD@discussions.microsoft.com> wrote:
    >
    > >i have an If Then statement on the close event of a form, which is based on a
    > >query.
    > >if the form/query doesnt find anything i get an error "you entered an
    > >expression that has no value".
    > >what code would a i use to keep from getting this error?
    > >thanks in advance.

    >
    > Care to post your code?
    >
    >
    > John W. Vinson[MVP]
    >
     
  6. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Hi Lou,

    I think you have a few possibilities. I'm assuming that "check" is the name
    of a checkbox control. Is this correct?

    The first possibility would be to use the On Click event procedure for the
    checkbox to run code that validates that a value has been entered into the
    snumber field, if this checkbox is true (or not false, ie <>0). That should
    prevent the error from occuring in the code that runs in Form_Close.

    The other possibility might be to use the Nz function, as I first alluded to:

    If Forms![frm_report].[snumber] = Forms![frm_verify].[check] Then
    Forms![frm_report].[snumber] = Nz(Forms![frm_report].[snumber], "") & "
    (R)"
    Else
    DoCmd.CancelEvent
    End If

    I don't know if you would consider the second possibility an acceptable
    alternative, since, in the example above, you would be concatenating R to a
    zero-length string. You can use the Nz function to convert nulls to other
    values, such as 0 (zero), or "Unknown" or....just about anything you want.


    Tom Wickerath, Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "LouD" wrote:

    > If Forms![frm_report].[snumber] = Forms![frm_verify].[check] Then
    > Forms![frm_report].[snumber] = Forms![frm_report].[snumber] & " (R)"
    > Else
    > DoCmd.CancelEvent
    > End If
    >
    > "John Vinson" wrote:
    >
    > > On Mon, 8 May 2006 18:02:01 -0700, LouD
    > > <LouD@discussions.microsoft.com> wrote:
    > >
    > > >i have an If Then statement on the close event of a form, which is based on a
    > > >query.
    > > >if the form/query doesnt find anything i get an error "you entered an
    > > >expression that has no value".
    > > >what code would a i use to keep from getting this error?
    > > >thanks in advance.

    > >
    > > Care to post your code?
    > >
    > >
    > > John W. Vinson[MVP]
    > >
     
  7. LouD

    LouD
    Expand Collapse
    Guest

    Thanks for replying Tom. Turns out it wasn't my If..Then statement that was
    the problem. It was the way I was opening the second form (frm_verify) that
    was producing the error. I had a command button to run a macro to open and
    close the form. Instead I used the DoCmd.OpenForm and DoCmd.Close to open
    and close the form on the click event. And also, just so you know I wasn't
    concatenating
    a zero length string, I was adding it to the end of the value that was there
    already.
    Sorry if I didn't make that clear.
    Thanks again, I appreciate your response.

    > Hi Lou,
    >
    > I think you have a few possibilities. I'm assuming that "check" is the name
    > of a checkbox control. Is this correct?
    >
    > The first possibility would be to use the On Click event procedure for the
    > checkbox to run code that validates that a value has been entered into the
    > snumber field, if this checkbox is true (or not false, ie <>0). That should
    > prevent the error from occuring in the code that runs in Form_Close.
    >
    > The other possibility might be to use the Nz function, as I first alluded to:
    >
    > If Forms![frm_report].[snumber] = Forms![frm_verify].[check] Then
    > Forms![frm_report].[snumber] = Nz(Forms![frm_report].[snumber], "") & "
    > (R)"
    > Else
    > DoCmd.CancelEvent
    > End If
    >
    > I don't know if you would consider the second possibility an acceptable
    > alternative, since, in the example above, you would be concatenating R to a
    > zero-length string. You can use the Nz function to convert nulls to other
    > values, such as 0 (zero), or "Unknown" or....just about anything you want.
    >
    >
    > Tom Wickerath, Microsoft Access MVP
    >
    > http://www.access.qbuilt.com/html/expert_contributors.html
    > http://www.access.qbuilt.com/html/search.html
    > __________________________________________
    >
    > "LouD" wrote:
    >
    > > If Forms![frm_report].[snumber] = Forms![frm_verify].[check] Then
    > > Forms![frm_report].[snumber] = Forms![frm_report].[snumber] & " (R)"
    > > Else
    > > DoCmd.CancelEvent
    > > End If
    > >
    > > "John Vinson" wrote:
    > >
    > > > On Mon, 8 May 2006 18:02:01 -0700, LouD
    > > > <LouD@discussions.microsoft.com> wrote:
    > > >
    > > > >i have an If Then statement on the close event of a form, which is based on a
    > > > >query.
    > > > >if the form/query doesnt find anything i get an error "you entered an
    > > > >expression that has no value".
    > > > >what code would a i use to keep from getting this error?
    > > > >thanks in advance.
    > > >
    > > > Care to post your code?
    > > >
    > > >
    > > > John W. Vinson[MVP]
    > > >
     

Share This Page