Query Criteria - Date question

[jwr]

I have a query with ship date as one of the fields.

I want the query to list all information where the ship date is blank (not
shipped yet). What do I use for date criteria that will allow report to
print only unshipped orders?

 

[tina]

try

WHERE ShippedDate Is Null

substituting the correct field name, of course.

hth

 

[jwr]

Tina - I placed the information in the criteria field beneath ShipDate. Now
I get no information. When I remove it, I get shipped information.
??

 

[tina]

are there currently any records in the table without a shipped date? if yes,
is the shipped date a date/time field, or a text field? if a text field, is
the Allow Zero Length property of the field set to Yes?

also, are there criteria applied to any other fields in the query - besides
the "Is Not Null" criteria applied to the date field? if yes, are there
currently any records in that subset that don't have a shipped date?

 

[jwr]

Yes, I have some shipped and some without a ship date.

I have no other criteria.

I can get the correct data if I put - in the total field - Where and in the
criteria field - Is Not Null - of the Ship Date field.

If I change this to Is Null, I get message - Invalid Use of Null.

If I change this to "Is Null" (using quotation marks), I get message
date/type mismatch.


On another report, I have a date problem also in which I have a pop up form
asking for beginning and ending dates. If I go beyond 8-5-05, I get error
message that it is typed incorrectly or too complicated to evaluate.

Many thanks for your assistance. I really am a novice at this.

Joy

The Ship Date field is a date/time field.

 

[tina]

well, a simple SELECT query should accept Is Null on a date/time type data
field. i just double checked it, in case i was having a stupid attack or a
memory lapse, and it worked fine for me. i'm thinking maybe there's more
going on in the SQL that's affecting the outcome.

please post your SQL statement: open the query in design view. on the menu
bar, click View | SQL View. in the SQL pane, highlight the *entire*
statement and copy. then paste into a reply to this thread (you can use
Ctrl+V to paste, if necessary).

 

[jwr]

I did not notice until I printed the SQL view that there is another query
included in this one. Both SQL statements are included below. THANKS.

SELECT DISTINCTROW Orders.ShipDate, Customers.CompanyName,
Orders.FreightCharge, Orders.SalesTaxRate,
Sum(CLng([Quantity]*[UnitPrice]*(1-[Discount])*100)/100) AS LineTotal, [Sum
Of Payments Query].[Total Payments]
FROM (Customers INNER JOIN (Orders LEFT JOIN [Sum Of Payments Query] ON
Orders.OrderID = [Sum Of Payments Query].OrderID) ON Customers.CustomerID =
Orders.CustomerID) LEFT JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
WHERE ((("WHERE ShipDate") Is Null))
GROUP BY Orders.ShipDate, Customers.CompanyName, Orders.FreightCharge,
Orders.SalesTaxRate, [Sum Of Payments Query].[Total Payments];

SUM OF PAYMENTS QUERY:
SELECT DISTINCTROW Payments.PaymentDate, Customers.ControlNum,
Payments.OrderID, Sum(Payments.PaymentAmount) AS [Total Payments]
FROM (Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) LEFT JOIN Payments ON Orders.OrderID = Payments.OrderID
GROUP BY Payments.PaymentDate, Customers.ControlNum, Payments.OrderID;

 

[tina]

WHERE ((("WHERE ShipDate") Is Null))

okay, well, i'm not exactly the query queen, but i've never seen anything
like that WHERE clause. in the query's SQL pane, try replacing it with

WHERE Orders.ShipDate Is Null

hth

I did not notice until I printed the SQL view that there is another query
included in this one. Both SQL statements are included below. THANKS.

SELECT DISTINCTROW Orders.ShipDate, Customers.CompanyName,
Orders.FreightCharge, Orders.SalesTaxRate,
Sum(CLng([Quantity]*[UnitPrice]*(1-[Discount])*100)/100) AS LineTotal, [Sum
Of Payments Query].[Total Payments]
FROM (Customers INNER JOIN (Orders LEFT JOIN [Sum Of Payments Query] ON
Orders.OrderID = [Sum Of Payments Query].OrderID) ON Customers.CustomerID =
Orders.CustomerID) LEFT JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
WHERE ((("WHERE ShipDate") Is Null))
GROUP BY Orders.ShipDate, Customers.CompanyName, Orders.FreightCharge,
Orders.SalesTaxRate, [Sum Of Payments Query].[Total Payments];

SUM OF PAYMENTS QUERY:
SELECT DISTINCTROW Payments.PaymentDate, Customers.ControlNum,
Payments.OrderID, Sum(Payments.PaymentAmount) AS [Total Payments]
FROM (Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) LEFT JOIN Payments ON Orders.OrderID = Payments.OrderID
GROUP BY Payments.PaymentDate, Customers.ControlNum, Payments.OrderID;

well, a simple SELECT query should accept Is Null on a date/time type data
field. i just double checked it, in case i was having a stupid attack or a
memory lapse, and it worked fine for me. i'm thinking maybe there's more
going on in the SQL that's affecting the outcome.

please post your SQL statement: open the query in design view. on the menu
bar, click View | SQL View. in the SQL pane, highlight the *entire*
statement and copy. then paste into a reply to this thread (you can use
Ctrl+V to paste, if necessary).


in
the