Created new date fields, but not able to use "between" with the query design

[TAM]

I created two date fields: StartDate and Enddate. I backtested each new date field and they calculated and displayed correctly when displaying the detailed transactions. However, when I try to summarize in the query design tools transactions between StartDate and Enddate, I get an error message: Data type mismatch in criteria expression. Here's the SQL:

SELECT [SALESREP_12-22-2015].MBRID, [SALESREP_12-22-2015].FileCreationDate, CDate([FileCreationDate]-362) AS StartDate, CDate([FileCreationDate]+1) AS EndDAte, dbo_TransactionHistory.ReportDate, dbo_TransactionHistory.WklyTotalGDC AS SY_SALES INTO Tbl_Metrics_Test
FROM dbo_TransactionHistory LEFT JOIN [SALESREP_12-22-2015] ON dbo_TransactionHistory.AdvisorNo = [SALESREP_12-22-2015].MBRID
WHERE ((([SALESREP_12-22-2015].MBRID)=12345) AND ((dbo_TransactionHistory.ReportDate) Between "StartDate" And "Enddate"));SALESREP

What am I missing?

 

[Joe4]

You cannot use calculated field names (aliases) that you create in your SELECT clause in your WHERE clause. Just use the calculation itself, i.e.

SELECT [SALESREP_12-22-2015].MBRID, [SALESREP_12-22-2015].FileCreationDate, CDate([FileCreationDate]-362) AS StartDate, CDate([FileCreationDate]+1) AS EndDAte, dbo_TransactionHistory.ReportDate, dbo_TransactionHistory.WklyTotalGDC AS SY_SALES INTO Tbl_Metrics_Test
FROM dbo_TransactionHistory LEFT JOIN [SALESREP_12-22-2015] ON dbo_TransactionHistory.AdvisorNo = [SALESREP_12-22-2015].MBRID
WHERE ((([SALESREP_12-22-2015].MBRID)=12345) AND ((dbo_TransactionHistory.ReportDate) Between (CDate([FileCreationDate]-362)) And (CDate([FileCreationDate]+1))));

 

[TAM]

Excellent! This may need to be a new post, but after I added the change, while the query executes, before it finished, I got a message "Invalid use of Null".

You cannot use calculated field names (aliases) that you create in your SELECT clause in your WHERE clause. Just use the calculation itself, i.e.

SELECT [SALESREP_12-22-2015].MBRID, [SALESREP_12-22-2015].FileCreationDate, CDate([FileCreationDate]-362) AS StartDate, CDate([FileCreationDate]+1) AS EndDAte, dbo_TransactionHistory.ReportDate, dbo_TransactionHistory.WklyTotalGDC AS SY_SALES INTO Tbl_Metrics_Test
FROM dbo_TransactionHistory LEFT JOIN [SALESREP_12-22-2015] ON dbo_TransactionHistory.AdvisorNo = [SALESREP_12-22-2015].MBRID
WHERE ((([SALESREP_12-22-2015].MBRID)=12345) AND ((dbo_TransactionHistory.ReportDate) Between (CDate([FileCreationDate]-362)) And (CDate([FileCreationDate]+1))));

 

[Joe4]

Do you have NULL values in any of the fields you are doing calculations on or joining on?

 

[TAM]

Do you have NULL values in any of the fields you are doing calculations on or joining on?

I did. Thank you!

 

[Joe4]

You are welcome.

By the way, the NZ function is a nice tool for dealing with unwanted NULL values.
See: MS Access: Nz Function

 

[tiedied61]

However, becareful when using NZ ... A) it is MS Access specific function and more importantly B) NZ on dates you will want to supply a default value (2nd parameter) otherwise you get a Zero ... i.e. 12/31/1899 !!! which is probably not what you want

 

[Joe4]

NZ on dates you will want to supply a default value (2nd parameter) otherwise you get a Zero

I always supply the second value. No need to leave it up to chance!