?
Solved

Select yesterday only from CDATE values

Posted on 2008-11-05
17
Medium Priority
?
450 Views
Last Modified: 2013-11-28
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
0
Comment
Question by:Aaron Goodwin
  • 11
  • 6
17 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22889090
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
 

Author Comment

by:Aaron Goodwin
ID: 22889182
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22889470

WHERE tblR.NUMBER Is Null
and CDate([tblMCMC_DATA.PCR_DATE]) = Date()-1
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Aaron Goodwin
ID: 22895235

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
 

Author Comment

by:Aaron Goodwin
ID: 22895391
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
 

Author Comment

by:Aaron Goodwin
ID: 22895609
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22895704
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
 

Author Comment

by:Aaron Goodwin
ID: 22895770
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22895869
OK.  Are you quite sure that you have records for yesterday that meet all of your other criteria?
0
 

Author Comment

by:Aaron Goodwin
ID: 22895912
yes, but even if I change the now()-1 to Now()-4.....0 records are returned
0
 

Author Comment

by:Aaron Goodwin
ID: 22895928
like I say, if I specify the datetime
where datetime = #05/11/2008#  I get records back, even with the other criteria specified
0
 

Author Comment

by:Aaron Goodwin
ID: 22896055
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22896059
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
 

Author Comment

by:Aaron Goodwin
ID: 22897446
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
 

Accepted Solution

by:
Aaron Goodwin earned 0 total points
ID: 22898578
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22898874
:)
0
 

Author Comment

by:Aaron Goodwin
ID: 22904172
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question