Solved

MS Access Date Criteria

Posted on 2007-04-04
71
384 Views
Last Modified: 2007-12-19
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??
0
Comment
Question by:StephenJD
  • 35
  • 30
  • 4
  • +1
71 Comments
 
LVL 75
ID: 18855551
DatePart()

mx
0
 
LVL 75
ID: 18855562
Sorry


datevalue(#4/4/2007 1:03:25 AM#) = 4-4-2007
0
 
LVL 4

Author Comment

by:StephenJD
ID: 18855571
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
 
LVL 75
ID: 18855574
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
 
LVL 75
ID: 18855577
"So I need this to be able to be static without ever having to change it again..."

DateValue([YourDateField])

mx

0
 
LVL 75
ID: 18855579
So, do you see how to use DateValue() ?
 

mx
0
 
LVL 4

Author Comment

by:StephenJD
ID: 18855592
*SIGH*

Apparently not......

[hangs my head]
0
 
LVL 75
ID: 18855594
ok ... can you post the query SQL where you want to use this .... we'll figure it out ...

mx
0
 
LVL 4

Author Comment

by:StephenJD
ID: 18855603
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
 
LVL 75
ID: 18855607
ook ... so,  which field do you want to filter on  -  SYSADM_PS_NE_PICK_BATCH.TODAY_DATE ?
0
 
LVL 4

Author Comment

by:StephenJD
ID: 18855611
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
 
LVL 75
ID: 18855616
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
 
LVL 4

Author Comment

by:StephenJD
ID: 18855649
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
 
LVL 75
ID: 18855655
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
 
LVL 75
ID: 18855665
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
 
LVL 75
ID: 18855670
Also, how exactly are you executing this SQL ?  It's a saved query in Access ?

mx
0
 
LVL 4

Author Comment

by:StephenJD
ID: 18855673
Yes, it is a saved query in Access....Or it will be when I get it to work... :)
0
 
LVL 75
ID: 18855678
OK ... but ... lets see ... you are running against, well ... so ... are you linked to some ODBC tables ?  or ??

mx
0
 
LVL 4

Author Comment

by:StephenJD
ID: 18855679
Still getting compile errors

Compile error . in criteria expression.

Then when I switch back to design view all criteria fields are blank.
0
 
LVL 4

Author Comment

by:StephenJD
ID: 18855682
Yes I am linked to ODBC tables.
0
 
LVL 75
ID: 18855684
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
 
LVL 75
ID: 18855690
Does it run without any date criteria?

0
 
LVL 4

Author Comment

by:StephenJD
ID: 18855692
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
 
LVL 4

Author Comment

by:StephenJD
ID: 18855695
Yes it runs without date criteria, so I know it is the date critieria that is killing me.
0
 
LVL 75
ID: 18855702
Maybe try

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

ie ... wrap CDate around

0
 
LVL 75
ID: 18855711
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
 
LVL 4

Author Comment

by:StephenJD
ID: 18855712
It still doesn't like it...

^$(#(&$&()(#$*$$ PEOPLESOFT!!!!!!!!!!!!
0
 
LVL 75
ID: 18855716
I'll wait for you to catch up, lol ...

0
 
LVL 75
ID: 18855729
does DateValue([SYSADM_PS_NE_PICK_BATCH].[TODAY_DATE])  ... extract just the date part ?

0
 
LVL 4

Author Comment

by:StephenJD
ID: 18855746
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
 
LVL 4

Author Comment

by:StephenJD
ID: 18855749
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
 
LVL 75
ID: 18855754

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
 
LVL 4

Author Comment

by:StephenJD
ID: 18855763
That wasn't it. Although I am glad you caught it! :)
0
 
LVL 75
ID: 18855765
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
 
LVL 75
ID: 18855770
What do you get if you put DateValue around just the field name ... leave out the table name?

DateValue([TODAY_DATE])
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 4

Author Comment

by:StephenJD
ID: 18855782
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
 
LVL 75
ID: 18855802
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
 
LVL 4

Author Comment

by:StephenJD
ID: 18855810
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
 
LVL 75
ID: 18855816
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
 
LVL 75
ID: 18855824
make that  "CAN'T Save it "  Cannot Save it ...

sorry
0
 
LVL 75
ID: 18855835
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
 
LVL 4

Author Comment

by:StephenJD
ID: 18855840
I'll try that.

I'm in Buffalo NY
0
 
LVL 4

Author Comment

by:StephenJD
ID: 18855845
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
 
LVL 75
ID: 18855861
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
 
LVL 4

Author Comment

by:StephenJD
ID: 18855887
CDate(CStr([SYSADM_PS_NE_PICK_BATCH].[TODAY_DATE]))  gives me a compile error as well...
0
 
LVL 75
ID: 18855898
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
 
LVL 75
ID: 18855956
ok ... time to zzzzzzzzzzzzz

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

back tomorrow ...

mx
0
 
LVL 4

Author Comment

by:StephenJD
ID: 18855969
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
 
LVL 4

Author Comment

by:StephenJD
ID: 18856039
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
 
LVL 39

Expert Comment

by:stevbe
ID: 18856852
do you have this query processing as a pass-thru query or just a straight up normal select?
0
 
LVL 4

Author Comment

by:StephenJD
ID: 18857377
A Select
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18857556
can you perform this?

MyDate:Format([SYSADM_PS_NE_PICK_BATCH].[TODAY_DATE],"mm/d/yyyy")
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18857580
sorry....I have a sticky "d"

MyDate:Format([SYSADM_PS_NE_PICK_BATCH].[TODAY_DATE],"mm/dd/yyyy")
0
 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 200 total points
ID: 18857608
<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
 
LVL 4

Author Comment

by:StephenJD
ID: 18858539
Ok, I was on conference call.....give me a couple of minutes to catch up with you guys.
0
 
LVL 75
ID: 18858585
I'm back !!!!!

mx
0
 
LVL 4

Author Comment

by:StephenJD
ID: 18858588
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
 
LVL 4

Author Comment

by:StephenJD
ID: 18858652
Oh, and the data in the linked table is formatted as date/time.....forgot to mention that.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 300 total points
ID: 18858732
0
 
LVL 75
ID: 18858744
Geeze ... I just noticed the Zone is MySQL ... why is that ?

mx
0
 
LVL 4

Author Comment

by:StephenJD
ID: 18858805
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
 
LVL 75
ID: 18858886
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18858916
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
 
LVL 75
ID: 18858968
jeff ... I'll almost BET it's Sandbox Macro (in)Security ...

mx
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18858974
I don't have a sandbox...I have a catbox though...same thing?
0
 
LVL 4

Author Comment

by:StephenJD
ID: 18859175
I just squashed Sandbox mode like a bug. Registry value is now set to 0. Let's see if this gets it...
0
 
LVL 4

Author Comment

by:StephenJD
ID: 18859202
You guys let me know if you ever run for President!!!
0
 
LVL 75
ID: 18859204
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
 
LVL 4

Author Comment

by:StephenJD
ID: 18859220
I'm allergic to cats....lol
0
 
LVL 75
ID: 18859272
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
 
LVL 4

Author Comment

by:StephenJD
ID: 18859320
LOL no problem on the interaction side of things.....My deadline is tomorrow!!! :-)
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now