MS Access Date Criteria

DatePart()

mx

Sorry


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

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...

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

"So I need this to be able to be static without ever having to change it again..."

DateValue([YourDateField])

mx

So, do you see how to use DateValue() ?
 

mx

*SIGH*

Apparently not......

[hangs my head]

ok ... can you post the query SQL where you want to use this .... we'll figure it out ...

mx

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.

ook ... so,  which field do you want to filter on  -  SYSADM_PS_NE_PICK_BATCH.TODAY_DATE ?

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.

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

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"))'.

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

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"));

Also, how exactly are you executing this SQL ?  It's a saved query in Access ?

mx

Yes, it is a saved query in Access....Or it will be when I get it to work... :)

OK ... but ... lets see ... you are running against, well ... so ... are you linked to some ODBC tables ?  or ??

mx

Still getting compile errors

Compile error . in criteria expression.

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

Yes I am linked to ODBC tables.

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?

?

Does it run without any date criteria?

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.

Yes it runs without date criteria, so I know it is the date critieria that is killing me.

Maybe try

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

ie ... wrap CDate around

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.

It still doesn't like it...

^$(#(&$&()(#$*$$ PEOPLESOFT!!!!!!!!!!!!

I'll wait for you to catch up, lol ...

does DateValue([SYSADM_PS_NE_PICK_BATCH].[TODAY_DATE])  ... extract just the date part ?

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

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.

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

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

start with that.

That wasn't it. Although I am glad you caught it! :)

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.

What do you get if you put DateValue around just the field name ... leave out the table name?

DateValue([TODAY_DATE])

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.

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 ?

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

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

make that  "CAN'T Save it "  Cannot Save it ...

sorry

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 ..

I'll try that.

I'm in Buffalo NY

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

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

CDate(CStr([SYSADM_PS_NE_PICK_BATCH].[TODAY_DATE]))  gives me a compile error as well...

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 ...

ok ... time to zzzzzzzzzzzzz

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

back tomorrow ...

mx

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.

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.

do you have this query processing as a pass-thru query or just a straight up normal select?

A Select

can you perform this?

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

sorry....I have a sticky "d"

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

Ok, I was on conference call.....give me a couple of minutes to catch up with you guys.

I'm back !!!!!

mx

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?

Oh, and the data in the linked table is formatted as date/time.....forgot to mention that.

Geeze ... I just noticed the Zone is MySQL ... why is that ?

mx

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.

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

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

jeff ... I'll almost BET it's Sandbox Macro (in)Security ...

mx

I don't have a sandbox...I have a catbox though...same thing?

I just squashed Sandbox mode like a bug. Registry value is now set to 0. Let's see if this gets it...

You guys let me know if you ever run for President!!!

J .. I have TWO LARGE cat boxes and 4 indoor cats, 3 of which I have for 11 years ... and all came from Rockport !!!

I'm allergic to cats....lol

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

LOL no problem on the interaction side of things.....My deadline is tomorrow!!! :-)