Seems silly -- but query not returning the right result... (1 Viewer)

[brosenfelt]

I feel dumb asking this -- but I've been banging my head against the wall all day. Here's the query:

SELECT pipeline_won_lost.Business, pipeline_won_lost.[Primary Officer Last Name], pipeline_won_lost.Status, pipeline_won_lost.[Actual Close Date]
FROM pipeline_won_lost
WHERE (((pipeline_won_lost.[Primary Officer Last Name])="Greene") AND ((pipeline_won_lost.Status)="Closed") AND ((pipeline_won_lost.[Actual Close Date])>="8/1/15" AND (pipeline_won_lost.[Actual Close Date])<="9/30/15"));

I have a record in this table with an "Actual Close Date" of 9/19/15. When I run this query, that record is not returned. I also verified that the other data elements in my WHERE clause was accurate. Single table - single query - no joins. Any thoughts???

Thanks!:banghead:

 

[llkhoutx]

[Actual Close Date] is probably not a string. Look into using dates is a query, they're not usually strings.

 

[jdraw]

Dates have to be enclosed in octothorpes (hashes/number signs)
Try this query

SELECT pipeline_won_lost.Business
, pipeline_won_lost.[Primary Officer Last Name]
, pipeline_won_lost.Status
, pipeline_won_lost.[Actual Close Date]
FROM pipeline_won_lost
WHERE (pipeline_won_lost.[Primary Officer Last Name]="Greene") AND
 (pipeline_won_lost.Status="Closed") AND 
 (pipeline_won_lost.[Actual Close Date] Between #8/1/15# AND #9/30/15#;

Good luck.

 

[brosenfelt]

Makes sense / will try when I log in later tonight

 

[brosenfelt]

Tried that - but same results. When I change the query to =#9/9/2015# it returns that value. Maybe need to check on the format of the field in the table?

 

[brosenfelt]

ARgh! I'm a dummy -- the field was formatted as text. Changed the field definition to DATE/TIME -- and works fine.