Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Returns Null (A2k)

    Following on from the combo post but a different subject

    The following query from my combo still returns Null items from the table:

    SELECT tblVehicles.Model, tblVehicles.SubModel
    FROM tblVehicles
    GROUP BY tblVehicles.Model, tblVehicles.SubModel
    HAVING (((tblVehicles.Model)=[forms]![frmVehicleAdmin]![Model]) AND ((tblVehicles.SubModel) Is Not Null));

    I fear a value of " " may be populating the table, is there an additive to the query to trap this.
    I tried: Is Not """" but is obviously wrong.

    p.s, I have "group by" set for all the query columns.
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Returns Null (A2k)

    You can use <> "" as criteria. If that doesn't help, use Trim([SubModel]) instead of SubModel.

    To see what is behind this:

    Open the table underlying the query in design mode.
    Click in the SubModel field.
    Is the Allow Zero Length property set to Yes?

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Returns Null (A2k)

    Hans

    The <>"" has worked ok, and yes Zero length is set to yes.
    I set this to be so earlier because I didn't want the Access default error checking before the code
    from the other post was effected.

    Should I set it back to Allow Zero Length = No and will it effect code later on.?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Returns Null (A2k)

    I would start by creating an update query (interactively) that selects the records for which SubModel is "", and changes this to Null. Run this query. You can then set Allow Zero Length to No.

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Returns Null (A2k)

    Thanks Hans
    I've imported the original table from a backup which contains no empty values, this seems to have donethe trick.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •