Aaron Goodwin
asked on
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
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
ASKER
SELECT [LName] & ", " & [FName] AS BOB
, Base
, tblBase_DATA.PCR_DATE
, tblBase_DATA.RESPONSE
, tblBase_DATA.NUMBER
, tblR.NUMBER
, CDate([tblMCMC_DATA.PCR_DA TE]) AS [Datetime]
FROM ((tblBase_DATA
WHERE (((tblR.NUMBER) Is Null))
and [Datetime] = Date()-1
ORDER BY [LName] & ", " & [FName], ;
I get prompted for Datetime
, Base
, tblBase_DATA.PCR_DATE
, tblBase_DATA.RESPONSE
, tblBase_DATA.NUMBER
, tblR.NUMBER
, CDate([tblMCMC_DATA.PCR_DA
FROM ((tblBase_DATA
WHERE (((tblR.NUMBER) Is Null))
and [Datetime] = Date()-1
ORDER BY [LName] & ", " & [FName], ;
I get prompted for Datetime
WHERE tblR.NUMBER Is Null
and CDate([tblMCMC_DATA.PCR_DA
ASKER
WHERE (((tblPCR.RESPONSE_NUMBER)
and CDate([tblMCMC_DATA.PCR_DA
ORDER BY [LName] & ", " & [FName];
Query Runs but Still Returns 0 records for some reason
ASKER
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?
if I put in a specific date
WHERE (((tblPCR.RESPONSE_NUMBER)
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?
ASKER
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.
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.
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_DA TE]) >= (Date() - 1) AND
CDate([tblMCMC_DATA.PCR_DA TE]) < Date()
>>if I put in a specific date
>>WHERE (((tblPCR.RESPONSE_NUMBER)
>>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_DA
CDate([tblMCMC_DATA.PCR_DA
ASKER
WHERE tblPCR.RESPONSE_NUMBER Is Null
AND CDate([tblMCMC_DATA.PCR_DA TE]) >= (Date() - 1)
AND CDate([tblMCMC_DATA.PCR_DA TE]) < Date()
still returns 0 records
AND CDate([tblMCMC_DATA.PCR_DA
AND CDate([tblMCMC_DATA.PCR_DA
still returns 0 records
OK. Are you quite sure that you have records for yesterday that meet all of your other criteria?
ASKER
yes, but even if I change the now()-1 to Now()-4.....0 records are returned
ASKER
like I say, if I specify the datetime
where datetime = #05/11/2008# I get records back, even with the other criteria specified
where datetime = #05/11/2008# I get records back, even with the other criteria specified
ASKER
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
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
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")
WHERE tblPCR.RESPONSE_NUMBER Is Null AND
Format(CDate([tblMCMC_DATA
ASKER
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
??
WHERE tblPCR.RESPONSE_NUMBER Is Null AND
Format(CDate([tblMCMC_DATA
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
??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
:)
ASKER
Wow Finalyy got it to work
WHERE (((CDate(Mid$([tblMCMC_Dat a.PCR_DATE ],4,3) & Left$([tblMCMC_Data.PCR_DA TE],3) & Right$([tblMCMC_Data.PCR_D ATE],4)))= Date()-1) AND ((tblPCR.RESPONSE_NUMBER) Is Null));
WHERE (((CDate(Mid$([tblMCMC_Dat
straight dates you would get no matches.
Please post the SQL you have now.