Calculate one year back, Access and Oracle

[Jean]

Hi,

I am using an Access 2000 front-end to an Oracle 9 backend.

I want to write a query that returns all records that are not older
than one year for Column "Status_30" (which is a Date).

When I look at the ODBC Datasource in Table DWHADMIN_V_PROBLEM , the
Date formatting looks normal to me, like #05/07/2005#. However, when I
try using the following in my Access Query :

SELECT PROBLEMNR, STATUS_30
FROM DWHADMIN_V_PROBLEM
WHERE (((STATUS_30)>Year(Now())-1));

I get the message:

ODBC-call failed:
[Oracle][ODBC][Ora]ORA-00932: inconsistent datatypes; expected DATE got
NUMBER (#932)

How do I implement this functionality in my Access Query? Is the WHERE
clause of my query correct?

Thanks in advance

[Keith]

"Jean" <je**********@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Hi,

I am using an Access 2000 front-end to an Oracle 9 backend.

I want to write a query that returns all records that are not older
than one year for Column "Status_30" (which is a Date).

When I look at the ODBC Datasource in Table DWHADMIN_V_PROBLEM , the
Date formatting looks normal to me, like #05/07/2005#. However, when I
try using the following in my Access Query :

SELECT PROBLEMNR, STATUS_30
FROM DWHADMIN_V_PROBLEM
WHERE (((STATUS_30)>Year(Now())-1));

I get the message:

ODBC-call failed:
[Oracle][ODBC][Ora]ORA-00932: inconsistent datatypes; expected DATE got
NUMBER (#932)

How do I implement this functionality in my Access Query? Is the WHERE
clause of my query correct?

Thanks in advance

You need to format your field as a year too. Try this:

WHERE (((Year([STATUS_30]))>Year(Now())-1))

Regards,
Keith.
www.keithwilby.com

[Wayne Morgan]

I'm not sure which functions work in Oracle, but the Where clause is not
correct. You are currently asking for "Year(Now())-1". What this will return
is

Now(): Today's Date (i.e. 7/6/2005), subtract 1 (this gives 7/5/2005), then
get the year of this date. This will yeild a value of 2005. As you can see,
this is just a number, not a date. The DateAdd function should do what you
want.

DateAdd("yyyy", -1, Date())

The difference between the Date() and Now() functions is that Date() returns
the current date and Now() returns the current date AND time.

While Access can evaluate these functions, I don't know if Oracle can. So it
will depend on how you are using them. If this is a "pass through" query, it
won't work unless Oracle can evaluate the functions. However, judging by the
error message, I think making the change above will help.

--
Wayne Morgan
MS Access MVP
"Jean" <je**********@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Hi,

I am using an Access 2000 front-end to an Oracle 9 backend.

I want to write a query that returns all records that are not older
than one year for Column "Status_30" (which is a Date).

When I look at the ODBC Datasource in Table DWHADMIN_V_PROBLEM , the
Date formatting looks normal to me, like #05/07/2005#. However, when I
try using the following in my Access Query :

SELECT PROBLEMNR, STATUS_30
FROM DWHADMIN_V_PROBLEM
WHERE (((STATUS_30)>Year(Now())-1));

I get the message:

ODBC-call failed:
[Oracle][ODBC][Ora]ORA-00932: inconsistent datatypes; expected DATE got
NUMBER (#932)

How do I implement this functionality in my Access Query? Is the WHERE
clause of my query correct?

Thanks in advance

[Jean]

Hi Keith,

I ended up using:

WHERE (((([STATUS_30]))>Date()-365))

Your version only takes Dates not older than 1 Jan 2005.

But your solution provided some valuable insight into how things must
be done - Thanks!

[Jean]

Hi Wayne, thanks for your input too! Got it working now.

Perhaps you could help me on the next issue:

So now I have the data as follows:

ID STATUS_30 STATUS_50 STATUS_100
-- --------- --------- ----------

01 10/04/2005 12/05/2005
02 11/03/2005 19/03/2005
03 23/11/2004 09/05/2005 13/06/2005
etc.

I would like to build a query that would look at each second calendar
week in the past year, and then determine how many STATUS_30's,
STATUS_50's and STATUS_100's there were for that particular calendar
week.

For example, I might take calendar week 26 of 2005 (18 - 24/04/2005)
and determine that ID 1 was (highest)STATUS 30, ID 2 was (highest)STATE
50, and ID 3 was (highest)STATUS 30. So there were 2 x STATUS 30, 1 x
STATUS 50 and 0 x STATUS 100 for this particular calendar week.
Therefore, the following (crosstab) query should be possible:

STATUS CW26/2005 CW28/2005
30 2 etc.
50 1 etc.
100 0 etc.

This just looks so complicated to me at the moment, but it must be
possible (or not?). After all, all manager's want the same thing, don't
they? :)
Please help me solve this or tell me what approach I should take to put
me into the right direction.

Kind Regards,
Jean

[Wayne Morgan]

Jean,

I had to get some help on this one. The main problem is that the data isn't
in a format that readily allows for doing this. The suggestion I received
was to use two queries. The first one to reorder the data and the second to
do the transform, using the first query as the source for the second one.

SELECT 30 As Status, Status_30 As TheDate FROM tblStatus
UNION ALL
SELECT 50, Status_50 FROM tblStatus
UNION ALL SELECT 100, Status_100 FROM tblStatus;

TRANSFORM Nz(COUNT(*),0) AS theValue
SELECT Status
FROM Query29
GROUP BY Status
PIVOT "CW" & Format(2*((1+Datepart("ww",TheDate))\2)-1,"00") & "/2005";

Adjust the table and query names as appropriate for your file.

--
Wayne Morgan
MS Access MVP
"Jean" <je**********@hotmail.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

Hi Wayne, thanks for your input too! Got it working now.

Perhaps you could help me on the next issue:

So now I have the data as follows:

ID STATUS_30 STATUS_50 STATUS_100
-- --------- --------- ----------

01 10/04/2005 12/05/2005
02 11/03/2005 19/03/2005
03 23/11/2004 09/05/2005 13/06/2005
etc.

I would like to build a query that would look at each second calendar
week in the past year, and then determine how many STATUS_30's,
STATUS_50's and STATUS_100's there were for that particular calendar
week.

For example, I might take calendar week 26 of 2005 (18 - 24/04/2005)
and determine that ID 1 was (highest)STATUS 30, ID 2 was (highest)STATE
50, and ID 3 was (highest)STATUS 30. So there were 2 x STATUS 30, 1 x
STATUS 50 and 0 x STATUS 100 for this particular calendar week.
Therefore, the following (crosstab) query should be possible:

STATUS CW26/2005 CW28/2005
30 2 etc.
50 1 etc.
100 0 etc.

This just looks so complicated to me at the moment, but it must be
possible (or not?). After all, all manager's want the same thing, don't
they? :)
Please help me solve this or tell me what approach I should take to put
me into the right direction.

Kind Regards,
Jean

[Jean]

Hi Wayne,

Thanks, that worked! I really appreciate you sharing your knowledge

Regards,
Jean