Select yesterday only from CDATE values

Hello Experts,

I have an access database that I have inherited and looking for a little help.  I have a simple select statement where I need to pull only the records from yesterday.  The only problem is that the only date field that should be a date / time field is text....  So what I tried in the select in a new column was cdate(datefield)..Which appears to be working.   My hopes were to then put in the criteria Now()-1  But this returns 0 records, even though there are records from yesterday I tried the same scenario with the same results using the date() -1 in the criteria.  Is it possible to do this type of select with a CDATE field, or is there a better way to do this.
source datefield format is dd-mm-yyyy (01-10-2008) October 1, 2008
Aaron GoodwinAsked:
Who is Participating?
 
Aaron GoodwinConnect With a Mentor Author Commented:
Wow Finalyy got it to work

WHERE (((CDate(Mid$([tblMCMC_Data.PCR_DATE],4,3) & Left$([tblMCMC_Data.PCR_DATE],3) & Right$([tblMCMC_Data.PCR_DATE],4)))=Date()-1) AND ((tblPCR.RESPONSE_NUMBER) Is Null));
0
 
Patrick MatthewsCommented:
I suspect the reason you got no matches with Now()-1 is that Now() returns a time portion, so if you had
straight dates you would get no matches.

Please post the SQL you have now.
0
 
Aaron GoodwinAuthor Commented:
SELECT [LName] & ", " & [FName] AS BOB
, Base
, tblBase_DATA.PCR_DATE
, tblBase_DATA.RESPONSE
, tblBase_DATA.NUMBER
, tblR.NUMBER
, CDate([tblMCMC_DATA.PCR_DATE]) AS [Datetime]

FROM ((tblBase_DATA
WHERE (((tblR.NUMBER) Is Null))
and [Datetime] = Date()-1
ORDER BY [LName] & ", " & [FName], ;

I get prompted for Datetime
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Patrick MatthewsCommented:

WHERE tblR.NUMBER Is Null
and CDate([tblMCMC_DATA.PCR_DATE]) = Date()-1
0
 
Aaron GoodwinAuthor Commented:

WHERE (((tblPCR.RESPONSE_NUMBER) Is Null))
and CDate([tblMCMC_DATA.PCR_DATE]) = Date()-1
ORDER BY [LName] & ", " & [FName];
Query Runs but Still Returns 0 records for some reason
0
 
Aaron GoodwinAuthor Commented:
its strange,
if I put in a specific date
WHERE (((tblPCR.RESPONSE_NUMBER) Is Null) AND ((CDate([tblMCMC_DATA.PCR_DATE])) Between #1/11/2008# And #2/11/2008#))

that works fine.  But the Now() and Date() Functions don't seem to be working with the CDate values.  Anybody know if this is a restriction/caveate of the cdate function?
0
 
Aaron GoodwinAuthor Commented:
I even tried it as a temp table,
select * from qrttemp
where Datetime = date()-1

no records returned
what about a max date or something like that, is it possible to select only the CDATE Maximum (as the table is update everyday and is 1 day behind the actual date.
0
 
Patrick MatthewsCommented:
SMUNBEMS-DBA said:
>>if I put in a specific date
>>WHERE (((tblPCR.RESPONSE_NUMBER) Is Null) AND ((CDate([tblMCMC_DATA.PCR_DATE]))
>>Between #1/11/2008# And #2/11/2008#))
>>
>>that works fine.  But the Now() and Date() Functions don't seem to be working with the CDate values.  
>>Anybody know if this is a restriction/caveate of the cdate function?

That suggests that PCR_DATE has date and time portions.  Try this:

WHERE tblPCR.RESPONSE_NUMBER Is Null AND CDate([tblMCMC_DATA.PCR_DATE]) >= (Date() - 1) AND
    CDate([tblMCMC_DATA.PCR_DATE]) < Date()
0
 
Aaron GoodwinAuthor Commented:
WHERE tblPCR.RESPONSE_NUMBER Is Null
AND CDate([tblMCMC_DATA.PCR_DATE]) >= (Date() - 1)
AND  CDate([tblMCMC_DATA.PCR_DATE]) < Date()

still returns 0 records
0
 
Patrick MatthewsCommented:
OK.  Are you quite sure that you have records for yesterday that meet all of your other criteria?
0
 
Aaron GoodwinAuthor Commented:
yes, but even if I change the now()-1 to Now()-4.....0 records are returned
0
 
Aaron GoodwinAuthor Commented:
like I say, if I specify the datetime
where datetime = #05/11/2008#  I get records back, even with the other criteria specified
0
 
Aaron GoodwinAuthor Commented:
I think there is something wrong with the CDATE conversion that I did.  
I ran a select Max(datetime) in my query, which should have returned 5/11/2008.  As the numbers should format as dd/MM/yyyy

but I got
10/31/2008

I think I may have to force the formatting of the Cdate for the NOW() or Date() functions to work
0
 
Patrick MatthewsCommented:
OK, here's a longshot...

WHERE tblPCR.RESPONSE_NUMBER Is Null AND
    Format(CDate([tblMCMC_DATA.PCR_DATE]), "yyyy-mm-dd") = Format(Date() - 1, "yyyy-mm-dd")
0
 
Aaron GoodwinAuthor Commented:
nossir, still returns 0 records
WHERE tblPCR.RESPONSE_NUMBER Is Null AND
    Format(CDate([tblMCMC_DATA.PCR_DATE]), "yyyy-mm-dd") = Format(Date() - 1, "yyyy-mm-dd");

i am guessing that the format statement should have teken care of the CDATE missmatch I am seeing?

is there a way to do a sub query
select *
from table
where select Max(cdate([date]) = date()-1

??
0
 
Patrick MatthewsCommented:
:)
0
 
Aaron GoodwinAuthor Commented:
Wow Finalyy got it to work

WHERE (((CDate(Mid$([tblMCMC_Data.PCR_DATE],4,3) & Left$([tblMCMC_Data.PCR_DATE],3) & Right$([tblMCMC_Data.PCR_DATE],4)))=Date()-1) AND ((tblPCR.RESPONSE_NUMBER) Is Null));
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.