query using an expression

[jamccarley]

I have a query with a expression field that adds 90 days to a date field. I
use the search criteria "Between [Start Date] and [End Date]" and run the
query. I type in 03/31/08 (start) and 12/08/08 (end) and then the query
brings back data from all the way back to 2001.

 

[John W. Vinson]

I have a query with a expression field that adds 90 days to a date field. I
use the search criteria "Between [Start Date] and [End Date]" and run the
query. I type in 03/31/08 (start) and 12/08/08 (end) and then the query
brings back data from all the way back to 2001.

Sounds like you may have the data stored in a Text field. What is the datatype
of the relevant field? If it's Date/Time, please post the complete SQL view of
the query.

 

[jamccarley]

All of the field that use dates are the correct field type.

SELECT Claim.FLX_Claim_Num, Claim.Resp_Dept, Claim.Occ_Date, Claim.Cust_ID,
Claim.Product_Num, Claim.Product_Name, Claim.Customer_Prob_Desc,
[C/M-CustClaim].Description, [C/M-CustClaim].Type, [C/M-CustClaim].[Op/St],
[C/M-CustClaim].[Auditor initial], [C/M-CustClaim].Resp,
[C/M-CustClaim].Target, [C/M-CustClaim].Run, [C/M-CustClaim].[C/M Complete],
[C/M-CustClaim].Effective, [C/M-CustClaim].Comm1, [C/M Complete]+90 AS Expr1,
[C/M-CustClaim].[C/M confirmed 90day], [C/M-CustClaim].[Shift 90day],
[C/M-CustClaim].[Auditor 90day], [C/M-CustClaim].[Comments 90day]
FROM (Claim INNER JOIN [C/M-CustClaim] ON Claim.FLX_Claim_Num =
[C/M-CustClaim].Flx_Claim_Num) INNER JOIN Cust_Assy_Claim ON
Claim.FLX_Claim_Num = Cust_Assy_Claim.Flx_CA_ClaimNum
GROUP BY Claim.FLX_Claim_Num, Claim.Resp_Dept, Claim.Occ_Date,
Claim.Cust_ID, Claim.Product_Num, Claim.Product_Name,
Claim.Customer_Prob_Desc, [C/M-CustClaim].Description, [C/M-CustClaim].Type,
[C/M-CustClaim].[Op/St], [C/M-CustClaim].[Auditor initial],
[C/M-CustClaim].Resp, [C/M-CustClaim].Target, [C/M-CustClaim].Run,
[C/M-CustClaim].[C/M Complete], [C/M-CustClaim].Effective,
[C/M-CustClaim].Comm1, [C/M-CustClaim].[C/M confirmed 90day],
[C/M-CustClaim].[Shift 90day], [C/M-CustClaim].[Auditor 90day],
[C/M-CustClaim].[Comments 90day]
HAVING (((Claim.Resp_Dept) Like "*cast*") AND (([C/M-CustClaim].[C/M
Complete])>#3/31/2008#) AND (([C/M Complete]+90) Between [Start Date] And
[End date]) AND (([C/M-CustClaim].[Auditor 90day]) Is Null))
ORDER BY Claim.Product_Name;

Thanks

I have a query with a expression field that adds 90 days to a date field. I
use the search criteria "Between [Start Date] and [End Date]" and run the
query. I type in 03/31/08 (start) and 12/08/08 (end) and then the query
brings back data from all the way back to 2001.

Sounds like you may have the data stored in a Text field. What is the datatype
of the relevant field? If it's Date/Time, please post the complete SQL view of
the query.

 

[John W. Vinson]

All of the field that use dates are the correct field type.

SELECT Claim.FLX_Claim_Num, Claim.Resp_Dept, Claim.Occ_Date, Claim.Cust_ID,
Claim.Product_Num, Claim.Product_Name, Claim.Customer_Prob_Desc,
[C/M-CustClaim].Description, [C/M-CustClaim].Type, [C/M-CustClaim].[Op/St],
[C/M-CustClaim].[Auditor initial], [C/M-CustClaim].Resp,
[C/M-CustClaim].Target, [C/M-CustClaim].Run, [C/M-CustClaim].[C/M Complete],
[C/M-CustClaim].Effective, [C/M-CustClaim].Comm1, [C/M Complete]+90 AS Expr1,
[C/M-CustClaim].[C/M confirmed 90day], [C/M-CustClaim].[Shift 90day],
[C/M-CustClaim].[Auditor 90day], [C/M-CustClaim].[Comments 90day]
FROM (Claim INNER JOIN [C/M-CustClaim] ON Claim.FLX_Claim_Num =
[C/M-CustClaim].Flx_Claim_Num) INNER JOIN Cust_Assy_Claim ON
Claim.FLX_Claim_Num = Cust_Assy_Claim.Flx_CA_ClaimNum
GROUP BY Claim.FLX_Claim_Num, Claim.Resp_Dept, Claim.Occ_Date,
Claim.Cust_ID, Claim.Product_Num, Claim.Product_Name,
Claim.Customer_Prob_Desc, [C/M-CustClaim].Description, [C/M-CustClaim].Type,
[C/M-CustClaim].[Op/St], [C/M-CustClaim].[Auditor initial],
[C/M-CustClaim].Resp, [C/M-CustClaim].Target, [C/M-CustClaim].Run,
[C/M-CustClaim].[C/M Complete], [C/M-CustClaim].Effective,
[C/M-CustClaim].Comm1, [C/M-CustClaim].[C/M confirmed 90day],
[C/M-CustClaim].[Shift 90day], [C/M-CustClaim].[Auditor 90day],
[C/M-CustClaim].[Comments 90day]
HAVING (((Claim.Resp_Dept) Like "*cast*") AND (([C/M-CustClaim].[C/M
Complete])>#3/31/2008#) AND (([C/M Complete]+90) Between [Start Date] And
[End date]) AND (([C/M-CustClaim].[Auditor 90day]) Is Null))
ORDER BY Claim.Product_Name;

For one thing, since you're not using any GROUP BY or SUM or other totals
operators, I'd change the HAVING clause to WHERE (unselect the Totals icon).
It may also help to put

PARAMETERS [Start Date] Date/Time, [End Date] Date/Time;

before the SELECT to ensure that it's recognizing the parameter datatype.

It may also be necessary to use

DateAdd("d", 90, [C/M Complete])

instead of the [C/M Complete] + 90 expression, though I don't see why.

Are any of the "tables" in fact Queries? Might a date be wrapped in a Format()
expression (which converts it to a string)? What seems to be happening is that
it's treating "03/10/2008" as a string which is in fact less than the string
"03/11/1995".

 

[John W. Vinson]

I typed in "PARAMETERS [Start Date] Date/Time, [End Date] Date/Time; before
the select, but it gave me the error "Syntax error in the PARAMETER clause".
And you wrote that 03/10/2008 is less than the string 03/10/1995, could you
explain how to convert them, I would like to know why the older date is
larger.

Sorry about the memory lapse - as John says, it should have been DateTime.

What I said was that "03/10/2008" as a string which is in fact less than the
string "03/11/1995". If the datatype of the field is Date/Time, and you also
declare the paramter to be DateTime, it will sort them chronologically; if the
field is Text, or if you're using something like the Format() function - which
converts its input to Text - you will get alpha sorting rather than date.

 

[jamccarley]

This has worked, Thank you so much

I typed in "PARAMETERS [Start Date] Date/Time, [End Date] Date/Time; before
the select, but it gave me the error "Syntax error in the PARAMETER clause".
And you wrote that 03/10/2008 is less than the string 03/10/1995, could you
explain how to convert them, I would like to know why the older date is
larger.

Sorry about the memory lapse - as John says, it should have been DateTime.

What I said was that "03/10/2008" as a string which is in fact less than the
string "03/11/1995". If the datatype of the field is Date/Time, and you also
declare the paramter to be DateTime, it will sort them chronologically; if the
field is Text, or if you're using something like the Format() function - which
converts its input to Text - you will get alpha sorting rather than date.