Access Query Confusion

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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??
 
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.
 

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.
 

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!
 
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.
 

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!
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top