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_PROB LEM , 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_PROB LEM
WHERE (((STATUS_30)>Y ear(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 6 3923
"Jean" <je**********@h otmail.com> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.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_PROB LEM , 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_PROB LEM WHERE (((STATUS_30)>Y ear(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
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**********@h otmail.com> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.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_PROB LEM , 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_PROB LEM WHERE (((STATUS_30)>Y ear(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
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!
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,
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+Da tepart("ww",The Date))\2)-1,"00") & "/2005";
Adjust the table and query names as appropriate for your file.
--
Wayne Morgan
MS Access MVP
"Jean" <je**********@h otmail.com> wrote in message
news:11******** *************@g 43g2000cwa.goog legroups.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
Hi Wayne,
Thanks, that worked! I really appreciate you sharing your knowledge
Regards,
Jean This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Tencip |
last post by:
Hi everyone,
I'm trying to build a simple script that does the following. It
should find today's month and year, and then go into a DB query string
and look for all records that are from this month and the next three
months.
I've been playing around with the date() function for a while now, and
from that I can pull today's month and year, but I'm having problems
with the transition from December to January. For instance, when i
|
by: David |
last post by:
I am learning plsql. I would like to run a stored procedure to
calculate my bank account value by predicted 10% annual growth rate.
Below is my plsql that is having problems. Your help is highly
appreciated.
Thanks
declare
money number := 50000.00;
year number := 1;
|
by: David |
last post by:
Can someone help me with this. I need to calculate the week ending
date of the first week of the year based upon a year provided by the
user. Is there a simpler way other than writing my own UDF?
|
by: Jan Szymczuk |
last post by:
I'm creating an MS Access 2000 database where I have a number of people
entered using simple basic fields, Surname: SMITH
Forenames: John
DoB: 09/09/1958
Age: (this needs to be automated to show years e.g. 46)
I am trying to get the age to be automatically shown after a persons DoB
(date of birth) is entered into the DoB field. I have tried using this
formula in the Control Source of the Age field...
|
by: Rustan |
last post by:
Hi
Im using GregorianCalendar to find out the current years week numbers.
When the user chooses a week number in a dropdown i want to show that week
in a table with the corresponding dates.
For example : the user choses week43 (this week)
so somehow i must calculate what date is startdate that week (monday 18th).
How do i do this with c# ?
all i know is that its-
| |
by: robboll |
last post by:
When I try to use an append query from an oracle link it takes forever.
I am exploring the idea of doing an append action using a pass-through
query.
If I have an Oracle ODBC connection to server OraTest. User: User1
Password: password
and I am trying to append all records in table: tblTEST that are code:
"abc"
|
by: PamelaB |
last post by:
I am trying to calculate the year end cost basis of equities held. I have downloaded all the transactions (purchases and sales) for the year and have them in a table. I need to calculate the value of each equity at year end using a First in First Out method. Does anyone have experience with this and any advice as to how to begin?
|
by: dreadnought8 |
last post by:
I've worked with mdbs, and with SQL Server to a lesser extent, with
Access as a front end, on commercial-strength systems for quite a
while, starting with A97.
The last 8 months or so, I've been working on Access 2000/2002 with
pass through queries and linked tables to an Oracle 10g backend
(ODBC).
Access seems (still) to beat everything else hands down as a front
end/ prototyping tool for WIndows based systems. With my type of
small...
|
by: colmkav |
last post by:
Can someone tell me how I can access the return value of a function
called from Oracle as opposed to a store proc from oracle? my oracle
function is get_num_dates_varposfile. I am only used to using this
method with store procs that dont return a value back to Access. Hope
this makes sense.
Set Cmd = New Command
With Cmd
Set .ActiveConnection = get_XE_Conn 'makes a connection
Oracle XE
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
| |
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| | |