MS Access Date Criteria

I am trying to query a PeopleSoft database through ODBC using microsoft access and retrieve values specific to the current date. The table I am querying has a date field, but it contains data/time information. I have tried using the Date() function in the criteria, but it rejects it because the table contains date/time values. An example of the data in the table is: 4/4/2007 1:03:25 AM

Of course if I use Now() it pulls in the current system time so it bombs too. Is there any other criteria I can specify to get the record data for the current date only??
LVL 4
Stephen DaughertyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
DatePart()

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Sorry


datevalue(#4/4/2007 1:03:25 AM#) = 4-4-2007
0
Stephen DaughertyAuthor Commented:
I apologize, I did not mention that I need this criteria to work no matter what day I run it without modifying the criteria. This query will be nested in another query and I am needing to use one of the result fields from the query using the date criteria to specify criteria in the subsequent query.

So I need this to be able to be static without ever having to change it again...
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Also ... if you ever need it:

timevalue(#4/4/2007 1:03:25 AM#)  returns  01:03:25

and

datepart("yyyy",#4/4/2007 1:03:25 AM#) = 2007, for example

So ... DateValue([YourDateField]) will extract just the date ... use it to best fit your purpose ...

mx

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"So I need this to be able to be static without ever having to change it again..."

DateValue([YourDateField])

mx

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
So, do you see how to use DateValue() ?
 

mx
0
Stephen DaughertyAuthor Commented:
*SIGH*

Apparently not......

[hangs my head]
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
ok ... can you post the query SQL where you want to use this .... we'll figure it out ...

mx
0
Stephen DaughertyAuthor Commented:
Here is the SQL from the SQL view in Access without Date Criteria specified:

SELECT SYSADM_PS_NE_PICK_BATCH.BUSINESS_UNIT, SYSADM_PS_NE_PICK_BATCH.PICK_BATCH_ID, SYSADM_PS_NE_PICK_BATCH.TODAY_DATE, SYSADM_PS_NE_PICK_BATCH.DESCR
FROM SYSADM_PS_NE_PICK_BATCH
WHERE (((SYSADM_PS_NE_PICK_BATCH.BUSINESS_UNIT)="S02") AND ((SYSADM_PS_NE_PICK_BATCH.DESCR)="Credit Card Orders"));

I need to pull the PICK_BATCH_ID for the specified Buiness Unit with the Specified Description.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
ook ... so,  which field do you want to filter on  -  SYSADM_PS_NE_PICK_BATCH.TODAY_DATE ?
0
Stephen DaughertyAuthor Commented:
yes. I only want results for the current date regardless of the time, as this table captures this data everyday and the time value has the potential to be different every day.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Something like this maybe:

SELECT SYSADM_PS_NE_PICK_BATCH.BUSINESS_UNIT AS Expr1, SYSADM_PS_NE_PICK_BATCH.PICK_BATCH_ID AS Expr2, SYSADM_PS_NE_PICK_BATCH.TODAY_DATE AS Expr3, SYSADM_PS_NE_PICK_BATCH.DESCR AS Expr4
FROM SYSADM_PS_NE_PICK_BATCH
WHERE (((SYSADM_PS_NE_PICK_BATCH.TODAY_DATE)=DateValue(Now())) AND (([SYSADM_PS_NE_PICK_BATCH].[BUSINESS_UNIT])="S02") AND (([SYSADM_PS_NE_PICK_BATCH].[DESCR])="Credit Card Orders"));

This part I added:

WHERE (((SYSADM_PS_NE_PICK_BATCH.TODAY_DATE)=DateValue(Now()))

Yes / No ?

mx
0
Stephen DaughertyAuthor Commented:
I get an error:

Compile error . in query expression '(((SYSADM_PS_NE_PICK_BATCH.BUSINESS_UNIT)="S02") AND ((SYSADM_PS_NE_PICK_BATCH.TODAY_DATE)=DateValue(Now())) AND ((SYSADM_PS_NE_PICK_BATCH.DESCR)="Credit Card Orders"))'.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
OK .... probably this:

SELECT SYSADM_PS_NE_PICK_BATCH.BUSINESS_UNIT AS Expr1, SYSADM_PS_NE_PICK_BATCH.PICK_BATCH_ID AS Expr2, SYSADM_PS_NE_PICK_BATCH.TODAY_DATE AS Expr3, SYSADM_PS_NE_PICK_BATCH.DESCR AS Expr4
FROM SYSADM_PS_NE_PICK_BATCH
WHERE (((DateValue([SYSADM_PS_NE_PICK_BATCH].[TODAY_DATE]))=Date()) AND (([SYSADM_PS_NE_PICK_BATCH].[BUSINESS_UNIT])="S02") AND (([SYSADM_PS_NE_PICK_BATCH].[DESCR])="Credit Card Orders"));

This extracts the date part of [TODAY_DATE] and compares it  to the current date ...

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Here is again  ... with the 'AS expr 1'  etc removed ... that happens cuz I don't have your table.


SELECT SYSADM_PS_NE_PICK_BATCH.BUSINESS_UNIT, SYSADM_PS_NE_PICK_BATCH.PICK_BATCH_ID, SYSADM_PS_NE_PICK_BATCH.TODAY_DATE, SYSADM_PS_NE_PICK_BATCH.DESCR
FROM SYSADM_PS_NE_PICK_BATCH
WHERE (((DateValue([SYSADM_PS_NE_PICK_BATCH].[TODAY_DATE]))=Date()) AND (([SYSADM_PS_NE_PICK_BATCH].[BUSINESS_UNIT])="S02") AND (([SYSADM_PS_NE_PICK_BATCH].[DESCR])="Credit Card Orders"));
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Also, how exactly are you executing this SQL ?  It's a saved query in Access ?

mx
0
Stephen DaughertyAuthor Commented:
Yes, it is a saved query in Access....Or it will be when I get it to work... :)
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
OK ... but ... lets see ... you are running against, well ... so ... are you linked to some ODBC tables ?  or ??

mx
0
Stephen DaughertyAuthor Commented:
Still getting compile errors

Compile error . in criteria expression.

Then when I switch back to design view all criteria fields are blank.
0
Stephen DaughertyAuthor Commented:
Yes I am linked to ODBC tables.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Did my last version make sense ... ie  "This extracts the date part of [TODAY_DATE] and compares it  to the current date ..."

I take it that  the [TODAY_DATE] field contains a range of dates?

?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Does it run without any date criteria?

0
Stephen DaughertyAuthor Commented:
Yes it makes good sense. and Yes it is a range. Records are added daily from an App Engine that is automated to run in PeopleSoft.
0
Stephen DaughertyAuthor Commented:
Yes it runs without date criteria, so I know it is the date critieria that is killing me.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Maybe try

DateValue( CDate([SYSADM_PS_NE_PICK_BATCH].[TODAY_DATE]) )

ie ... wrap CDate around

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
ok ... next to ...SYSADM_PS_NE_PICK_BATCH.TODAY_DATE

just put this DateValue([SYSADM_PS_NE_PICK_BATCH].[TODAY_DATE])  as an extra expression and see if it IS just extracting the date part of TODAY_DATE  ... and temporarily remove Date() from the criteria cell.
0
Stephen DaughertyAuthor Commented:
It still doesn't like it...

^$(#(&$&()(#$*$$ PEOPLESOFT!!!!!!!!!!!!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I'll wait for you to catch up, lol ...

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
does DateValue([SYSADM_PS_NE_PICK_BATCH].[TODAY_DATE])  ... extract just the date part ?

0
Stephen DaughertyAuthor Commented:
It will not even accept it as a valid expression.

In the field name section of Design View in Access, I am entering:

Date:DateValue([SYSADM_PS_NE_PICK_BATCH].[TODAY_DATE])

And I am getting a compile error in that exact expression
0
Stephen DaughertyAuthor Commented:
Could this have anything to do with it? I got it from MSDN...

If the date argument includes time information, DateValue doesn't return it. However, if date includes invalid time information (such as "89:98"), an error occurs.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:

Date:DateValue([SYSADM_PS_NE_PICK_BATCH].[TODAY_DATE])

Date ... is a reserved word ... change that to say MyDate: or Expr1:

start with that.

0
Stephen DaughertyAuthor Commented:
That wasn't it. Although I am glad you caught it! :)
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Here is a simple example that works ... where [RecDtCr] = 04-04-2007 17:14:35
and DateValue( [RecDtCr]) returns 04-04-2007 ... and I get one record ... cuz there is just one RecDtCt with todays date.

SELECT tblEmp.EmpName, DateValue([RecDtCr]) AS Expr1
FROM tblEmp
WHERE (((DateValue([RecDtCr]))=Date()));

This "If the date argument includes time information, DateValue doesn't return it."
means that DateValue ONLY returns the Date, not the Time.  But ... if you have an invalid time ... you get an error.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
What do you get if you put DateValue around just the field name ... leave out the table name?

DateValue([TODAY_DATE])
0
Stephen DaughertyAuthor Commented:
Yeah, the code looks good.

And I guess I was grasping at straws on the invalid time thing...

I don't understand why it will not work. I am going to delete the query, then close and re-open the database and try it again.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Must be some odbc issue ... and I haven't worked with odbc for a while

ok ... back to basics ... do a Compact and Repair on the MDB, if you haven't already ?

0
Stephen DaughertyAuthor Commented:
It is strange though. I always run into this problem with Date/Time fields in PeopleSoft.

If I modify the tables Format properties for that field in the table's Design view will it effect the table in PeopleSoft directly since it is a linked table? Or only in Access?

I think if I can adjust the format it will work
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
You can't make any design changes to a linked table, if that's what you mean? I mean, you can go to design view and change something, but you can save it.  Besides, there doesn't appear to be anything wrong with the format ... as it looks anyway.  I went to the vba immediate window ... and did the DateValue on your data example ... and got just the date ...

So ... lets ... see

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
make that  "CAN'T Save it "  Cannot Save it ...

sorry
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
What happens if you just create an expression:

CDate([SYSADM_PS_NE_PICK_BATCH].[TODAY_DATE])  .  ???

No criteria ...

eg cdate(#4/4/2007 1:03:25 AM#)  returns 04-04-2007 01:03:25  

and DateValue(cdate(#4/4/2007 1:03:25 AM#)) returns 04-04-2007

Not supposed to be rocket science !!

Where are you located btw?  I'm in LA & San Diego ..

0
Stephen DaughertyAuthor Commented:
I'll try that.

I'm in Buffalo NY
0
Stephen DaughertyAuthor Commented:
It will not even accept CDate([SYSADM_PS_NE_PICK_BATCH].[TODAY_DATE]) with no criteria.

On the rocket science....tell me about it.....That's one of my favorite retorts.....now I get to sit in the idiot chair for a while it seems......LOL
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well ... I wasn't referring to you re rocket science ...
Damm ... there must be some way to convert that date field!!

OK ... try this:

CDate(CStr([SYSADM_PS_NE_PICK_BATCH].[TODAY_DATE]))

ie ... try converting it to a String first and then back to a date. IF ... that works, then add
DateValue ( CDate(CStr([SYSADM_PS_NE_PICK_BATCH].[TODAY_DATE]))  )

LOL
0
Stephen DaughertyAuthor Commented:
CDate(CStr([SYSADM_PS_NE_PICK_BATCH].[TODAY_DATE]))  gives me a compile error as well...
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
And what about just

CStr([SYSADM_PS_NE_PICK_BATCH].[TODAY_DATE])

Also ... when you say 'compile' ... what exactly is the error message?  Ie ... compile generally refers to issues in VBA Code ...

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
ok ... time to zzzzzzzzzzzzz

Don't forget me now ... :-)

back tomorrow ...

mx
0
Stephen DaughertyAuthor Commented:
Here is the latest error, verbatim:

"Compile error . in query expression 'CStr([SYSADM_PS_NE_PICK_BATCH].[TODAY_DATE])' "

This is the same error as always, except of course there is a different expresion in the quotes depending on what I try.
0
Stephen DaughertyAuthor Commented:
It seems that all these functions want an actual value plugged into them instead of a reference to a field where the value is stored. I think that is why it is bombing.
0
stevbeCommented:
do you have this query processing as a pass-thru query or just a straight up normal select?
0
Stephen DaughertyAuthor Commented:
A Select
0
jefftwilleyCommented:
can you perform this?

MyDate:Format([SYSADM_PS_NE_PICK_BATCH].[TODAY_DATE],"mm/d/yyyy")
0
jefftwilleyCommented:
sorry....I have a sticky "d"

MyDate:Format([SYSADM_PS_NE_PICK_BATCH].[TODAY_DATE],"mm/dd/yyyy")
0
stevbeCommented:
<Compile error . in query expression 'CStr(...  >
What version of Access?
Are you running in Sandbox mode?
Do you hacve safe expression on or off?

Can you tell us what the real data type is coming from PS? Is it text, a number that only looks like a date (like access does but obviously a different algorythm)?

Steve
0
Stephen DaughertyAuthor Commented:
Ok, I was on conference call.....give me a couple of minutes to catch up with you guys.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I'm back !!!!!

mx
0
Stephen DaughertyAuthor Commented:
OK...

MyDate:Format([SYSADM_PS_NE_PICK_BATCH].[TODAY_DATE],"mm/dd/yyyy") also gets a compile error...

Stevbe,

I am running Access 2003, I've never heard of Sandbox mode....I can do anything I want to in here as far as I know....I created this Access Database so no protections should be on.

Where can I check to see if "Unsafe Expressions" is turned on?
0
Stephen DaughertyAuthor Commented:
Oh, and the data in the linked table is formatted as date/time.....forgot to mention that.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Geeze ... I just noticed the Zone is MySQL ... why is that ?

mx
0
Stephen DaughertyAuthor Commented:
My bad. The tables these are pulling from were created in Oracle. I just spoke with the PeopleSoft consultant that created the table and he doesn't know why the time is even being posted in the field because he created it as a date field, not a timestamp. Unfortunately that's what PeopleSoft is outputting and he can't get a result when specifying the sysdate in SQL+.     I am reading up on Sandbox mode and I'll be reading all posts until we figure this out or give up....

I actually specified 3 zones when asking this question...Access, General Database, and MySQL.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Damm ... I totally forgot it might be frickin Sandbox mode.  Anyway ... after the 'reading'  just check to be sure Macro Security is set to Low ... and re-run some of the tests, with the various 'formatting' options we have already tried, etc.

mx
0
jefftwilleyCommented:
This sounds like a reference issue to me. IF you don't have your common functions like format, or Cdate...then something's amiss.

All other notes aside, open up the VBA and check for missing references.

A compact and repair...newDB import might be in order here.

J
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
jeff ... I'll almost BET it's Sandbox Macro (in)Security ...

mx
0
jefftwilleyCommented:
I don't have a sandbox...I have a catbox though...same thing?
0
Stephen DaughertyAuthor Commented:
I just squashed Sandbox mode like a bug. Registry value is now set to 0. Let's see if this gets it...
0
Stephen DaughertyAuthor Commented:
You guys let me know if you ever run for President!!!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
J .. I have TWO LARGE cat boxes and 4 indoor cats, 3 of which I have for 11 years ... and all came from Rockport !!!

0
Stephen DaughertyAuthor Commented:
I'm allergic to cats....lol
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
now THAT ... was a workout.  FRICKIN' sandbox ... I would LOVE to kick Microshafts butt over that stupid implementation ... especially since ... as I've eluded to several times in other posts ... Macro (in)Security is a FALSE sense of 'security'.

OK ... Stephen ... I DO appreciate the highly interactive nature of the thread, ie ... quick responses back by you, etc.  That really helped.  You can be an example for other who do NOT do that.

Take care ...
joe.mx
0
Stephen DaughertyAuthor Commented:
LOL no problem on the interaction side of things.....My deadline is tomorrow!!! :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.