multiple criteria not taking

[Corticus]

Hi all,

This is a pretty generic problem I've had before and I've never really gotten a good explanation, just lots of workarounds...

Simple Query:

Book1
	A	B	C	D	E
1	Field	Name	Date_of_Birth	Service_Provided	Age:Year([Service_Provided]-[Date_of_Birth])-1900
2	Table	TblClient	TblClient	TblServices
3	Criteria		>#1/1/1940#	>#1/1/2001#	<"60"
Sheet1

The problem occurs when I have any criteria for a date. As long as there is only one criteria, the query returns the correct records. However, if I add additional criteria, as shown above, the query doesn't even acknowledge the additional criteria. Right now I am making one query to eliminate date_of_birth before 1940, and then another query that looks at the first query, and applies additional criteria to it, which works.
In addition, as long as the criteria aren't dates, I can use multiple conditions in one query. Once a date criteria has been added, however, no other criteria will affect the query. You can even put nonsense in the criteria and the query doesn't even seem to notice that its there.

Kind of strange, I hope I explained it well...

Thanks,
Corticus
This message was edited by Corticus on 2002-12-30 17:04

 

[Anne Troy]

Might help if you write your query in Access instead of Excel, Cort.

ROFL!! Just kidding. Cute trick.

Anyway...the only thing that I can see is <"60"

Does it *really* get the quotes? Is that what it really looks like? Anyway...that's all I can see.

Hey...I've got this lame-o email sitting in my drafts. I PROMISE to send it soon!

 

[Russell Hauf]

I agree with the DreamyOne. If you are calculating a field, then you don't need the quotes around 60.

Let us know if this doesn't work.

-Russell

 

[Corticus]

Ohhh...so what is this "Access" you speak of? Sounds useful!

But yes, I agree that the quotes shouldn't be necessary, at least that's what my SQL book says, unfortunately, if I don't use them, I get this delightful error:
"ODBC--Call Failed
[Oracle][ODBC][Ora]ORA-01481: Invalid number format model (#1481)"
Which I don't quite get. The problem is with the date criteria, I believe. Even without criterion in the "age" field, once I put in a criteria for a date field, any other criterias fail to "take". Even if I put something in the field to make Access prompt the user for criteria, it just ignores it. Actually, multiple date conditions work, but if I have a date criteria, and any other criteria, then I have a problem. It's very strange. I just tried the thing again, and one more observation, if I specify a date criteria for Date_of_Birth, then specify a text criteria for program(I don't remember if that field was in my example, but its in my db) like "O3C1", the text criteria works, and Access ignore the date criteria. If I specify a date criteria as my only condition, then the query works. If I then run a query on that query and specify additional criterion I'm okay. I would like to have this criteria in one query, however, since not being able to do so would imply I'm doing something wrong, and I've been working around this problem for some time now.

Thanks for the input, and I'll definately check out this "Access" to which you refer!
-Corticus

edit: I wanted to add two things. The issue of the quotes on<"60" needs resolving. I believe my formula might be returning a format other than a number, and I just made up the formula real quick so maybe there's a better way. Assuming 2 date fields, I'm using this formula to determine a client's age when served:
Age: Year([Service_Provided]-[Date_of_Birth])-1900
maybe I should use datedif()?

ps. I'll be looking forward to the E-mail, Dreamboat, I'm ready ready for some Access madness!
This message was edited by corticus on 2003-01-02 08:14

 

[Russell Hauf]

I think I see something suspect. You have:

Age: Year([Service_Provided]-[Date_of_Birth])-1900

Not sure what you are trying to do here, but if you subtract one date from another, you should get the number of DAYS between the dates. If you then try to take the YEAR of the number of days you get, you should get a funky answer. What are you trying to arrive at in your YEAR calculation?

-rh

 

[Parra]

Hi Corticus, This might not be the "proper" way to do it, but if why not create a query that gives you your first criteria, and then another that runs from the first query.

There have been times when I have tried to create 1 query to give me the results that I want, but at the end I need to create 2 or more.

The ends justify the means.
Parra

 

[Corticus]

Thanks for the replies,

Russel: My formula is an attempt to find out the clients age, in years, when they received service. By finding the difference between the date of service, and the date of birth. This answer comes up as the number of days between the two dates, so I put Year() to convert the days to years, but I was getting their age at service + 1900, so I subtracted that out. I guess I should use datdiff or something, but the formula I used produced correct age differences. Even without that criteria specified, though, once I put in any date criteria, any other criteria, even simple text, are not filtered out of the dynaset (I just wanted to use that word).

Parra, that's exactly how I'm doing the query now, and it works, it just bugs me that I can't put it all in one query. I have done just what you suggested (query a query) in the past when this problem has arisen, I was hoping to find out what was wrong, though. Thanks, though!

edit: In case anyown wondered what I was trying to do here, I am retrieving data from an Oracle database with over a million records (which means testing the query can be a bit time-consuming!) that indicates what age clients were when they recieved service, and what service. Clients are not allowed to recieve services if they are under 60 years of age, and I have to find these exceptions, and correct them. Age at service must be calculated in a query, because it is determined from their date of birth and date of service. Whew.

edit2: The age() formula I used gives the correct age when served in the dynaset.

Thanks again guys!
Corticus
This message was edited by Corticus on 2002-12-31 14:34

 

[Bariloche]

Corticus,

Check the dates you're getting from the Oracle database and make sure they are coming across as dates. In the past I've had problems with data that I expected to be numeric coming through as text. Not quite the same as your problem, but ya never know (my solution to that was to multiply by 1 to convert the data to numeric; until they corrected the database). Also, if your ODBC driver is configurable you might want to check that it is resolving dates correctly. My experience with a DB2 driver has been that a particular "patch" was not selected in the driver configuration. After selecting it dates resolved properly. Again, not quite specifically your problem, but ... .

The DateDiff function would be the proper method of determining the age. The syntax is:

Age: DateDiff("yyyy",[Date_of_Birth],[Service_Provided])

You might want to try it. Again, ya never know.


enjoy

 

[Bariloche]

Another thing:

Try putting your criteria fields in twice. With the second set of fields not displayed. Use those fields to specifiy your criteria; don't put any criteria in the fields you want displayed. Access can sometimes create funky, ineffiecient, SQL. By using "dedicated" criteria fields it seems to create better SQL.


have fun

 

[Corticus]

Success!

Okay, let me see if I can explain how I got this thing to work, finally. First, the date formula provided by Bariloche solved the quote issue, now Access is recognizing this age as a date and filtering by it correctly. I was able to specify the date_of_birth, service_provided, and age all in one query and got the correct results. The last thing I wanted to specify was a text condition of ="OA3B" Or "O3C1" or "O3C2" for [program], and when I put them on separate lines (one for "OA3B", one for "O3C1" etc"), I started getting erroneous data returned, but when I put them all in one line like ="OA3B" Or "O3C1" or "O3C2", it worked perfect. Thanks for all the help, here's the SQL if anyone's interested (and Bariloche, I tried the redundant fields as you suggested, but it ended up not being necessary so I don't know if that would have helped or not):
SELECT QryClients.SSN, QryClients.LAST_NAME, QryClients.FIRST_NAME, QryClients.MIDDLE_INITIAL, QryClients.DATE_OF_BIRTH, QryServices.PROGRAM, QryServices.SERVICE, QryServices.SERVICE_PROVIDED, QryServices.PROVIDER, QryServices.LOCATION, DateDiff("yyyy",[Date_of_Birth],[Service_Provided]) AS [Age When Served]
FROM QryClients INNER JOIN QryServices ON QryClients.SSN = QryServices.SSN
WHERE (((QryClients.DATE_OF_BIRTH)>#1/1/1940#) AND ((QryServices.PROGRAM)="OA3B" Or (QryServices.PROGRAM)="O3C1" Or (QryServices.PROGRAM)="O3C2") AND ((QryServices.SERVICE_PROVIDED)>#1/1/2001#) AND ((DateDiff("yyyy",[Date_of_Birth],[Service_Provided]))<60));

Thanks everybody for all the help, the information will be appreciated by many beyond myself, since many users will be using this system...

Corticus