Date Criteria Issue

  • Thread starter mattc66 via AccessMonster.com
  • Start date
M

mattc66 via AccessMonster.com

I have a fld that contains the date as text. I have using the below statment
converted it to a date.

=IIf(IsNull([yymmdd]),Null,DateSerial(2000 + Val(Left([yymmdd],2)),
Val(Mid([yymmdd],3,2)), Val(Right([yymmdd],2))))

However now when I set the criteria to > #12/31/05#
I get an error message. You tried to execute a query that does not include
the sepcified expression....as part of an aggregate function.

Any suggestions?
 
K

Ken Snell \(MVP\)

Post the SQL statement of the query that you're trying to run. Also post an
example of the "date string" that is in the textbox (it may be much simpler
to "convert" to a date than what you're using, depending upon what the
string is).
 
V

Van T. Dinh

It is like that the error is caused by non-total Field(s) in the SELECT
clause but not in the GROUP BY clause. If you can't find what's wrong, post
relevant Table details and the SQL String of your Query.

With your posted expression, you need to ensure that your data don't include
20th century date. You can take advantage of the default century settings
in your Windows OS in DateSerial(). Personally, I use:

=IIf(IsNull([yymmdd]), Null,
DateSerial(Left([yymmdd],2), Mid([yymmdd],3,2), Right([yymmdd],2)))
 
M

mattc66 via AccessMonster.com

Below is the SQL String for both queries. I get the error when I run
qryCustomerSalesSummary2006 when I have the "HAVING ((
(qryCustomerSalesNumbers.SHIPDATE)>#12/31/2005#));" If I remove this it runs
with no errors.

qryCustomerNumbers:
SELECT qryOEORD_All.ORDER_STATUS, qryOEORD_All.ORDER_NUM, qryOEORD_All.
CUSTOMER_ID, qryOEORD_All.BILL_NAME, qryOEORD_All.SALESPERSON, qryOEORD_All.
ORDER_DATE, qryOEORD_All.COMPANY, qryOEORD_All.DEPOSIT AS DEP_AMT,
qryOEORD_All.FREIGHT AS FRT_AMT, qryOEORD_All.TOTAL_TAX_AMT AS TAX_AMT,
qryOEORD_All.SHIP_QTY_DETAIL_TOT AS SALE_AMT, qryOEORD_All.SHIP_QTY_ORDER_TOT
AS INV_AMT, IIf(IsNull([SHIP_DATE]),Null,DateSerial(Left([SHIP_DATE],2),Mid(
[SHIP_DATE],3,2),Right([SHIP_DATE],2))) AS SHIPDATE
FROM qryOEORD_All
WHERE (((qryOEORD_All.ORDER_STATUS)="P"));

qryCustomerSalesSummary2006:
SELECT qryCustomerSalesNumbers.CUSTOMER_ID, qryCustomerSalesNumbers.BILL_NAME,
Sum(qryCustomerSalesNumbers.SALE_AMT) AS SumOfEXT_PRICE,
qryCustomerSalesNumbers.COMPANY
FROM qryCustomerSalesNumbers
GROUP BY qryCustomerSalesNumbers.CUSTOMER_ID, qryCustomerSalesNumbers.
BILL_NAME, qryCustomerSalesNumbers.COMPANY, qryCustomerSalesNumbers.SHIPDATE
HAVING (((qryCustomerSalesNumbers.SHIPDATE)>#12/31/2005#));

It is like that the error is caused by non-total Field(s) in the SELECT
clause but not in the GROUP BY clause. If you can't find what's wrong, post
relevant Table details and the SQL String of your Query.

With your posted expression, you need to ensure that your data don't include
20th century date. You can take advantage of the default century settings
in your Windows OS in DateSerial(). Personally, I use:

=IIf(IsNull([yymmdd]), Null,
DateSerial(Left([yymmdd],2), Mid([yymmdd],3,2), Right([yymmdd],2)))
I have a fld that contains the date as text. I have using the below
statment
Click to expand...
[quoted text clipped - 8 lines]
Any suggestions?
Click to expand...
Click to expand...
 
J

John Spencer

Is QryCustomerNumbers really qryCustomerSalesNumbers? You have a different
title on the first query than the query name you used in the second query.

For your calculation in the first query, try checking for null and for
spaces (0 to many) in the IIF statement..

IIf(Len(TRIM([SHIP_DATE] & "")) <> 6,Null,
DateSerial(Left([SHIP_DATE],2),Mid([SHIP_DATE],3,2),Right([SHIP_DATE],2)))
AS SHIPDATE

AND to be even more paranoid wrap the dateSerial in a CDATE conversion.

IIf(Len(TRIM([SHIP_DATE] & "")) <> 6,Null,
CDate(DateSerial(Left([SHIP_DATE],2),Mid([SHIP_DATE],3,2),Right([SHIP_DATE],2))))
AS SHIPDATE

And finally instead of a HAVING Clause in the second query use a WHERE
clause - which should be faster

SELECT qryCustomerSalesNumbers.CUSTOMER_ID
, qryCustomerSalesNumbers.BILL_NAME
, Sum(qryCustomerSalesNumbers.SALE_AMT) AS SumOfEXT_PRICE
, qryCustomerSalesNumbers.COMPANY
FROM qryCustomerSalesNumbers
WHERE qryCustomerSalesNumbers.SHIPDATE > #12/31/2005#
GROUP BY qryCustomerSalesNumbers.CUSTOMER_ID
, qryCustomerSalesNumbers.BILL_NAME
, qryCustomerSalesNumbers.COMPANY

--
John Spencer
Access MVP 2002-2005, 2007

mattc66 via AccessMonster.com said:
Below is the SQL String for both queries. I get the error when I run
qryCustomerSalesSummary2006 when I have the "HAVING ((
(qryCustomerSalesNumbers.SHIPDATE)>#12/31/2005#));" If I remove this it
runs
with no errors.

qryCustomerNumbers:
SELECT qryOEORD_All.ORDER_STATUS, qryOEORD_All.ORDER_NUM, qryOEORD_All.
CUSTOMER_ID, qryOEORD_All.BILL_NAME, qryOEORD_All.SALESPERSON,
qryOEORD_All.
ORDER_DATE, qryOEORD_All.COMPANY, qryOEORD_All.DEPOSIT AS DEP_AMT,
qryOEORD_All.FREIGHT AS FRT_AMT, qryOEORD_All.TOTAL_TAX_AMT AS TAX_AMT,
qryOEORD_All.SHIP_QTY_DETAIL_TOT AS SALE_AMT,
qryOEORD_All.SHIP_QTY_ORDER_TOT
AS INV_AMT,
IIf(IsNull([SHIP_DATE]),Null,DateSerial(Left([SHIP_DATE],2),Mid(
[SHIP_DATE],3,2),Right([SHIP_DATE],2))) AS SHIPDATE
FROM qryOEORD_All
WHERE (((qryOEORD_All.ORDER_STATUS)="P"));

qryCustomerSalesSummary2006:
SELECT qryCustomerSalesNumbers.CUSTOMER_ID,
qryCustomerSalesNumbers.BILL_NAME,
Sum(qryCustomerSalesNumbers.SALE_AMT) AS SumOfEXT_PRICE,
qryCustomerSalesNumbers.COMPANY
FROM qryCustomerSalesNumbers
GROUP BY qryCustomerSalesNumbers.CUSTOMER_ID, qryCustomerSalesNumbers.
BILL_NAME, qryCustomerSalesNumbers.COMPANY,
qryCustomerSalesNumbers.SHIPDATE
HAVING (((qryCustomerSalesNumbers.SHIPDATE)>#12/31/2005#));

It is like that the error is caused by non-total Field(s) in the SELECT
clause but not in the GROUP BY clause. If you can't find what's wrong,
post
relevant Table details and the SQL String of your Query.

With your posted expression, you need to ensure that your data don't
include
20th century date. You can take advantage of the default century settings
in your Windows OS in DateSerial(). Personally, I use:

=IIf(IsNull([yymmdd]), Null,
DateSerial(Left([yymmdd],2), Mid([yymmdd],3,2), Right([yymmdd],2)))
I have a fld that contains the date as text. I have using the below
statment
Click to expand...
[quoted text clipped - 8 lines]
Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

In these cases, using the HAVING clause is unnecessary, and in fact will
make the query run longer. You can use the WHERE clause instead:


qryCustomerSalesSummary2006:
SELECT qryCustomerSalesNumbers.CUSTOMER_ID,
qryCustomerSalesNumbers.BILL_NAME,
Sum(qryCustomerSalesNumbers.SALE_AMT) AS SumOfEXT_PRICE,
qryCustomerSalesNumbers.COMPANY
FROM qryCustomerSalesNumbers
WHERE (((qryCustomerSalesNumbers.SHIPDATE)>#12/31/2005#))
GROUP BY qryCustomerSalesNumbers.CUSTOMER_ID, qryCustomerSalesNumbers.
BILL_NAME, qryCustomerSalesNumbers.COMPANY,
qryCustomerSalesNumbers.SHIPDATE;


The reason you got the error is because you were using SHIPDATE field in the
HAVING clause, but it wasn't in the SELECT clause.
--

Ken Snell
<MS ACCESS MVP>



mattc66 via AccessMonster.com said:
Below is the SQL String for both queries. I get the error when I run
qryCustomerSalesSummary2006 when I have the "HAVING ((
(qryCustomerSalesNumbers.SHIPDATE)>#12/31/2005#));" If I remove this it
runs
with no errors.

qryCustomerNumbers:
SELECT qryOEORD_All.ORDER_STATUS, qryOEORD_All.ORDER_NUM, qryOEORD_All.
CUSTOMER_ID, qryOEORD_All.BILL_NAME, qryOEORD_All.SALESPERSON,
qryOEORD_All.
ORDER_DATE, qryOEORD_All.COMPANY, qryOEORD_All.DEPOSIT AS DEP_AMT,
qryOEORD_All.FREIGHT AS FRT_AMT, qryOEORD_All.TOTAL_TAX_AMT AS TAX_AMT,
qryOEORD_All.SHIP_QTY_DETAIL_TOT AS SALE_AMT,
qryOEORD_All.SHIP_QTY_ORDER_TOT
AS INV_AMT,
IIf(IsNull([SHIP_DATE]),Null,DateSerial(Left([SHIP_DATE],2),Mid(
[SHIP_DATE],3,2),Right([SHIP_DATE],2))) AS SHIPDATE
FROM qryOEORD_All
WHERE (((qryOEORD_All.ORDER_STATUS)="P"));

qryCustomerSalesSummary2006:
SELECT qryCustomerSalesNumbers.CUSTOMER_ID,
qryCustomerSalesNumbers.BILL_NAME,
Sum(qryCustomerSalesNumbers.SALE_AMT) AS SumOfEXT_PRICE,
qryCustomerSalesNumbers.COMPANY
FROM qryCustomerSalesNumbers
GROUP BY qryCustomerSalesNumbers.CUSTOMER_ID, qryCustomerSalesNumbers.
BILL_NAME, qryCustomerSalesNumbers.COMPANY,
qryCustomerSalesNumbers.SHIPDATE
HAVING (((qryCustomerSalesNumbers.SHIPDATE)>#12/31/2005#));

It is like that the error is caused by non-total Field(s) in the SELECT
clause but not in the GROUP BY clause. If you can't find what's wrong,
post
relevant Table details and the SQL String of your Query.

With your posted expression, you need to ensure that your data don't
include
20th century date. You can take advantage of the default century settings
in your Windows OS in DateSerial(). Personally, I use:

=IIf(IsNull([yymmdd]), Null,
DateSerial(Left([yymmdd],2), Mid([yymmdd],3,2), Right([yymmdd],2)))
I have a fld that contains the date as text. I have using the below
statment
Click to expand...
[quoted text clipped - 8 lines]
Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with DateSerial 6
Help with sum of max values for a specific date field 1
Converting Date from a text field in Oracle 6
Expression Builder Help 1
Convert nvarchar to date 10
Date Compares 2
Access 2007,reports,VBA 2
Format Date Question 1

Top