If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

[RESOLVED] Value in recordset not showing the same as in the DB query

Hi all,

I'm struggling with this one, it's quite a first for me! I have a rather complex query built, using MS Access (please hold the rotten tomatoes!) like all my DB's. Everything works well to display on a webpage and conditionally show/hide objects, except these two fields, which compute their value based on others also output in the query. Here they are, as defined in the MS Access query:

LTIRCase: IIf([Date_Of_Incident]<#1/1/2013 12:00:01 AM#,IIf([CasesWithDaysAway]=1 And [OSHA_Class]="Injury" And ([RecordableCasesForEHSIRByIncidentID].[Employee_Relationship] Like "Active*"),"Yes","No"),IIf([CasesWithDaysAway]=1 And [OSHA_Class]="Injury","Yes","No"))

...and...

LTIRCaseCount: IIf([LTIRCase]="Yes",1,0)

On MS Access the values show up correctly as "Yes" and 1 respectively for the records that meet the criteria defined, but when I pull the ADODB recordset on to my ASP page... MAGIC!! The field output doesn't show the same value!! It's like the entire recordset decides the value for LTIRCase has to be "No" and the LTIRCaseCount has to be 0 for all records... regardless of the values of other fields on which they are dependent!!

I am absolutely at a loss here... Why would the recordset object show values different from what's in the DB query? I tested out each individual output field mentioned in the conditions for "LTIRCase" and they all show up correctly to compute to "Yes" or "No" accordingly on the webpage. But the page still displays the wrong value for both "LTIRCase" and "LTIRCaseCount"!!

Your assistance and follow-up questions will be greatly appreciated!! I'm up against a deadline, and feeling the heat that this won't turn out very well... Might I once again play the damsel in distress card?

Figured it out!! In case anyone was wondering... The use of "LIKE" in MS Access was entirely acceptable, but ASP did not know what to do with it, so the conditions were failing because of that. I switched the definition of the field in MS Access to...

Which is a bit of a roundabout way, especially because they might add a new employee relationship and then I'm in trouble... but at least, for now, it works!! Plus, it saved my other aggregate query that was showing all zeros and had been driving me crazy for over a week...!