Bizarre Null error? (1 Viewer)

[songofsolon]

Greetings all,

I'll try to make this as simple as possible, but please ask for clarification if I'm unclear:

*I have one pass-through query to Informix that pulls all available class schedule information.
*I have another local query that uses information in a local table to restrict that query to a particular year and semester, chosen by the user.
*I have a function TimeConv that takes the numeric value used in the Informix db to store times (e.g., "1300") and converts it into a real time value (e.g., "1:00:00 PM").

The problem is this:
While the TimeConv function by itself always works in the local query, if I try to perform any comparisons on it, I get the error message "Invalid Use of Null". There are no null records in the restricted data. If I put the semester and year restriction directly into the pass-through query instead of the local query, the comparison operations work just fine. (But it's important that the user be able to change this without editing the SQL directly.) Only when that restriction is in the local query do I get the error message.

I've tried several ways of filtering out Null values from both queries, and it doesn't seem to make any difference. If I Nz() the fields before passing them to the TimeConv function, I instead get the error "Invalid procedure or argument call", I believe because it insists on reading "0000" as "0", which is too few digits for the function to work.

I get the same results if I try to run it through yet a third (local) query.

Relevant code and SQL below. I'm really stumped on this one. Thanks in advance for any suggestions!

Pass-through query (qryPassJoin1):

SELECT t3.days, t3.room, t2.crs_no, t3.bldg, t3.mtg_no, t1.sec_no, t3.beg_date,
t3.end_date, t3.beg_tm, t3.end_tm, t9.sex, t5.abbr_name, t1.yr, t1.sess

FROM informix.sec_rec t1, informix.crs_rec t2, informix.acad_cal_rec t7, 
outer (informix.secmtg_rec t8, outer informix.mtg_rec t3), outer 
informix.dept_table t6, outer informix.schd_comment_rec t4, outer 
(informix.fac_rec t5, outer informix.profile_rec t9)

WHERE t1.cat="UG93" and t1.crs_no=t2.crs_no and t1.cat=t2.cat and 
t1.crs_no=t8.crs_no and t1.cat=t8.cat and t1.yr=t8.yr and t1.sess=t8.sess and 
t1.sec_no=t8.sec_no and t8.mtg_no=t3.mtg_no and t1.crs_no=t4.crs_no and t1.cat=t4.cat 
and t1.yr=t4.yr and t1.sess=t4.sess and t1.sec_no=t4.sec_no and t1.fac_id=t5.id 
and t2.crs_no NOT IN 
("JFESSA", "FRESSA", "SOESSA", "JRESSA", "MAESSA", "MAORAL", "DEPO", "DEPO2", "SRESSA", "SRORAL") 
and t2.dept=t6.dept and t7.prog=t2.prog and t7.sess=t1.sess and t7.yr=t1.yr 
and t5.id=t9.id;

If I add this criteria, functionally equivalent to the first WHERE statement in the local query below, it all works fine:

 AND t1.yr=2005 AND t1.sess="SU"

Local Query (qryCatalog):

SELECT qpj1.days, qpj1.room, qpj1.crs_no, qpj1.bldg, qpj1.mtg_no, qpj1.sec_no, 
qpj1.beg_date, qpj1.end_date, qpj1.beg_tm, qpj1.end_tm, qpj1.sex, 
qpj1.abbr_name, DateDiff('h',TimeConv([beg_tm]),TimeConv([end_tm])) AS NoLongLab

FROM qryPassJoin1 AS qpj1, tblSettings

WHERE (((qpj1.beg_tm)>0) AND ((CInt([yr]))=CInt([tblSettings].[catyear])) AND 
((qpj1.sess)=[tblsettings].[catsess]));

The comparison criterion I would like to add here is:

 AND ((DateDiff('h',TimeConv([beg_tm]),TimeConv([end_tm])))<=2)

Function TimeConv:

Public Function timeconv(numtime As Variant) As Date
timeconv = CDate(Left([numtime], (Len([numtime]) - 2)) & ":" & Right([numtime], 2))
End Function

 

[songofsolon]

Maybe I should ask this another way: Is there any way to dynamically adjust pass-through queries based on the value of a control or a stored value in a local table? (When I can't write stored procedures to the Informix db)

 

[pdx_man]

Using VBA and reconstructing the SQL of the querydef is what you need to do. Something like:

Dim dbs As DAO.Database
Set dbs = CurrentDb
dbs.QueryDefs("MyQueryName").SQL = "MyCodeWithWhereClause " & Forms!MyForm!MyField

 

[songofsolon]

Brilliant

Perfect! Thanks so much.