Command Formula shows NO Data 1

[rwn]

I tested this in Access against the same Database/Tables and data is shon in access. However, when i run this as a Commnad in Crystal there is no data shown on the report.

SELECT Invoice_Header.Customer, Sum(Invoice_Header.Orig_Invoice_Amt) AS SumOfOrig_Invoice_Amt
FROM Invoice_Detail INNER JOIN Invoice_Header ON Invoice_Detail.Document = Invoice_Header.Document
WHERE (((Invoice_Header.Document_Date) Between 7/1/2014 And 6/30/2015))
GROUP BY Invoice_Header.Customer;

 

[kray4660]

Normally for access you have to surround dates with #'s. Everything else looks correct.

 

[rwn]

My question is for Crystal Reports for it works fine in Access. The data shows in Access with the following:
SELECT Invoice_Header.Customer, Sum(Invoice_Header.Orig_Invoice_Amt) AS SumOfOrig_Invoice_Amt, Invoice_Header.Document_Date
FROM Invoice_Detail INNER JOIN Invoice_Header ON Invoice_Detail.Document = Invoice_Header.Document
GROUP BY Invoice_Header.Customer, Invoice_Header.Document_Date
HAVING (((Invoice_Header.Document_Date) Between #7/1/2014# And #6/30/2015#));

The data does NOT show in Crystal with the following:
SELECT Invoice_Header.Customer, Sum(Invoice_Header.Orig_Invoice_Amt) AS SumOfOrig_Invoice_Amt, Invoice_Header.Document_Date
FROM Invoice_Detail INNER JOIN Invoice_Header ON Invoice_Detail.Document = Invoice_Header.Document
GROUP BY Invoice_Header.Customer, Invoice_Header.Document_Date
HAVING (((Invoice_Header.Document_Date) Between 7/1/2014 And 6/30/2015));

Maybe it is because the Invoice_Header.Document_Date field is a datetime field and I need to convert, but Im not sure how to convert to just a date in the above.

 

[BettyJ]

Try using

[tt]between to_date ('07-01-2014', 'mm-dd-yyyy') and to_date('06-30-2014', 'mm-dd-yyyy')[/tt]

instead of Between 7/1/2014 And 6/30/2015 and see if it works.

 

[rwn]

I changed, I beleive as advised. but it throws an error that to_date is not recognized bulit in function name.

SELECT Invoice_Header.Customer, Sum(Invoice_Header.Orig_Invoice_Amt) AS SumOfOrig_Invoice_Amt
FROM Invoice_Detail INNER JOIN Invoice_Header ON Invoice_Detail.Document = Invoice_Header.Document
WHERE (((Invoice_Header.Document_Date) between to_date ('07-01-2014', 'mm-dd-yyyy') and to_date('06-30-2014', 'mm-dd-yyyy'))
GROUP BY Invoice_Header.Customer;

 

[BettyJ]

Try the following and see if it works:

between to_date ('06-30-2014', 'mm-dd-yyyy') and to_date('07-01-2014', 'mm-dd-yyyy'))

if not try

SELECT Invoice_Header.Customer, Sum(Invoice_Header.Orig_Invoice_Amt) AS SumOfOrig_Invoice_Amt
FROM Invoice_Detail INNER JOIN Invoice_Header ON Invoice_Detail.Document = Invoice_Header.Document
WHERE (Invoice_Header.Document_Date >=#06-30-2014# and Invoice_Header.Document_Date<=#07-01-2014#)
GROUP BY Invoice_Header.Customer;

 

[BettyJ]

Sorry, In my previous post in the second line, I meant to write
between #06-30-2014# and #07-01-2014#

 

[rwn]

Crytal Reports doesnt accept # signs with Dates.

 

[BettyJ]

Crystal Reports does accept # signs with dates. I tested it with a Command using Access as database.

By the way, you are using an Acess database, right?

 

[rwn]

i think it is because in crystal that field is a date time, and need to be converted. any thoughts on how to convert this field to date Invoice_Header.Document_Date?


SELECT Invoice_Header.Customer, Sum(Invoice_Header.Orig_Invoice_Amt) AS SumOfOrig_Invoice_Amt
FROM Invoice_Detail INNER JOIN Invoice_Header ON Invoice_Detail.Document = Invoice_Header.Document
WHERE (((Invoice_Header.Document_Date) Between #7/1/2014# And #6/30/2015#))
GROUP BY Invoice_Header.Customer;

 

[rwn]

Thanks for the help Betty. The statement below is the SQL statement in the COMMAND in Crstal reports. I just wanted to be clear on what it is that is failing.

SELECT Invoice_Header.Customer, Sum(Invoice_Header.Orig_Invoice_Amt) AS SumOfOrig_Invoice_Amt
FROM Invoice_Detail INNER JOIN Invoice_Header ON Invoice_Detail.Document = Invoice_Header.Document
WHERE (((Invoice_Header.Document_Date) Between 7/1/2014 And 6/30/2015))
GROUP BY Invoice_Header.Customer;

 

[BettyJ]

I also used # in the SQL statement in the Command in Crystal Reports. I am using CR 11 and Access 2010.

In Field Explorer, when you right click on your Date field in the Command object and select Show Field Type, what is it displaying?

 

[rwn]

The field Invoice_Header.Document_Date displays as:
4/18/2015 12:00:00AM

 

[BettyJ]

Same in mine too. I am not getting any error. Are you getting any error message or nothing is being displayed in the report?

 

[rwn]

nothing shows on the report if i need to do a date range. if i just have the date filed in the command line, then the datetime show.

 

[kray4660]

I did an old trick that helps a lot when I am not sure of a specific database's syntax. I dropped in a table from a Access database. Filtered it by a date range and see what I get in the SQL.

So I am thinking this should work

WHERE Invoice_Header.Document_Date >= {ts '2014-07-01 00:00:00'} and Invoice_Header.Document_Date <= {ts '2016-06-30 00:00:00'}

 

[rwn]

That did the trick. thank you. I have Stared You on this!