1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access Query Confusion

Discussion in 'Business Applications' started by Erm, Sep 11, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    :confused:

    I am utterly confused! PLEASE HELP! I have two queries which are practically identical (one displays a few extra fields). When you run the query you specify a date range for the search. I specify the same date range for each query and have the queries sorting by a date field (the one searched on). The queries should show the data in the same order...but they don't. They have the same records, are sorted on the same column but have a different order????!!!!

    Any ideas??
     
  2. lora_3677

    lora_3677

    Joined:
    Aug 4, 2003
    Messages:
    1,228
    I would check the formatting of your date field, check in the query if you have them both ascending or descending order. Since you have additional fields in the one query, make sure they don't have an ascending or descending order selected.

    If you know SQL, it may help to look at the SQL statement to pinpoint the problem.
     
  3. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    These are the order by lines of the SQL from both queries. The queries display the exact same records, but order the null values in a different order. Each query always displays in the same order.

    ORDER BY [tblHousing.dtmDateServiced]+365 ;
    ORDER BY [tblHousing.dtmDateServiced]+365 ;

    Totally confusing or what????:confused:
    The date fields are formatted in exactly the same way also, within the table and the queries.
     
  4. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    By the way, I have tried adding ASC to the end of each statement just to force the sort even though it says ASC in the Design view!
     
  5. THoey

    THoey

    Joined:
    Feb 12, 2001
    Messages:
    3,420
    If the data is being sorted by the date field and the fields with possible NULL values, the data will sort differently cause a NULL is not similar to any character. You might want to test the NULL fields in your query and change them to a space character.

    From Access HELP:

    About working with blank fields in queries
    When a field contains no values, it contains a Null value or, for Text, Memo, or Hyperlink fields, a Null value or a zero-length string. If Null values exist in a field, they can affect the query's results. Following are some guidelines for working with Null values and zero-length strings in queries. (For information on the differences between Null values and zero-length strings, click .)

    This topic provides information about:

    Joining fields that contain Null values

    Searching for Null values or zero-length strings

    Understanding how Null values affect numeric calculations

    Converting Null values to zero

    Combining Text fields that contain Null values

    Sorting on fields containing Null values and zero-length strings

    Using the asterisk (*) wildcard character to return non-Null values

    Joining fields that contain Null values
    When you join tables in a query, the result includes only records that don't have Null values in the matching fields. For example, to list Suppliers and Customers who live in the same region, you create a query that includes the Suppliers and Customers tables, and the tables are joined on the Region field. When you view the results, you'll see only the values for records that contain a value in the Region field in both tables.

    Return to top

    Searching for Null values or zero-length strings
    If you're using a query to search for Null values or zero-length strings, type Is Null into the Criteria cell to search for Null values, or type two double quotation marks (" ") into the Criteria cell to search for zero-length strings (don't type a space between the quotation marks).

    Return to top

    Understanding how Null values affect numeric calculations
    If you use an aggregate function to calculate the sum, average, count, or another amount on a field's values, records with Null values in that field won't be included in the calculation. This is true whether you calculate the aggregate using the Total row in the query design grid, the Simple Query Wizard, or a custom expression. For example, if you use the Count function to count the number of values in a field, it will return a count of all the records with non-Null values. If you want to find the total number of records including those with Null values, use Count with the asterisk (*) wildcard character.



    If you use an arithmetic operator (such as +, -, *, /) in an expression (such as [UnitsInStock]+[UnitsOnOrder]), and one of the fields in the expression contains a Null value, then the result of the entire expression will be a Null value.

    Return to top

    Converting Null values to zero
    When you have fields that contain Null values, you can create an expression that converts the Null values to zero. You might do this if you want the records containing Null values to be included in an aggregate calculation, or if you want to prevent an expression from resulting in a Null value when a field that's referenced in the expression contains Null values. Use the Nz function, for example, to convert Null values to zero:

    Nz([Subtotal],0)+Nz([Freight],0)

    For more information about the Nz function, click .

    Return to top

    Combining Text fields that contain Null values
    If you're using an expression to combine two fields containing text values and one or both of the fields includes Null values, use the & operator instead of the + operator to combine the values. The & operator will combine the values even if they contain Null values, while the + operator will return a Null if either of the two values is a Null. For example:

    FullName: [LastName] & " " & [FirstName]

    Return to top

    Sorting on fields containing Null values and zero-length strings
    When you sort a field in ascending order, any records in which that field contains a Null value are listed first. If a field contains both Null values and zero-length strings, the Null values appear first in the sort order, immediately followed by the zero-length strings.

    Return to top

    Using the asterisk (*) wildcard character to return non-Null values
    If you use the expression Like "*" when defining query criteria for a field, the query results will include zero-length strings in that field, but not Null values.
     
  6. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    I think I have got the prob sorted guys and gals, thanks loads for all your help!
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/163930

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice