SQL query dropping parameter function...

JackNimble · March 8th, 2005, 05:02 PM

Hey everyone,

I have a query that references a function I built in VB to return the number of working days between today and 4 months future. For some reason the query will work fine 3 or 4 times and then it will suddenly erase the function name from the query and start asking me to manually enter working days... If I go back in and re-enter the sql statement, when I save it, it erases the function name again. the only things that fixes it is to do a compact & repair.

Any suggestions???

Oh, and here is the relevant bits of the SQL statement, both before and after...

BEFORE

Code:

PARAMETERS intNumWorkDays INTEGER = Get_NetWorkDays();
SELECT
  tblSCPFBRT_SCPSTAT.KTN_PN,
  tblSCPFBRT_SCPSTAT.WHS,
  tblSCPFBRT_SCPSTAT.VEN_NUM,
  tblSCPFBRT_SCPSTAT.BGN_AVAIL,
  ROUND(tblSCPFBRT_SCPSTAT.COST, 2) AS COST,
  ROUND(((tblSCPFBTS.FCST_TOTAL + tblSCPIPTS.DPNDT_QTY)
          /intNumWorkDays), 2) AS AVG_DMND,
  CINT(intNumWorkDays) AS WRKNG_DYS,
  ROUND(IIF(((tblSCPFBTS.FCST_TOTAL + tblSCPIPTS.DPNDT_QTY)
              /intNumWorkDays) = 0, 999,
        IIF(tblSCPFBRT_SCPSTAT.BGN_AVAIL > 0,
            tblSCPFBRT_SCPSTAT.BGN_AVAIL, 0)
            / ((tblSCPFBTS.FCST_TOTAL + tblSCPIPTS.DPNDT_QTY )
            / intNumWorkDays )), 0) AS DOH,
  CLNG(NZ(tblSCPDRPU.M0, 0)) AS MTH0,
  CLNG(NZ(tblSCPDRPU.M1, 0)) AS MTH1,
  CLNG(NZ(tblSCPDRPU.M2, 0)) AS MTH2,
  CLNG(NZ(tblSCPDRPU.M3, 0)) AS MTH3
INTO
  tblPIPELINE
FROM
  ((tblSCPFBTS INNER JOIN tblSCPIPTS ON tblSCPFBTS.SCP_UID = tblSCPIPTS.SCP_UID) INNER JOIN
  (tblSCPFBRT_SCPSTAT LEFT JOIN tblSCPDRPU ON tblSCPDRPU.SCP_UID = tblSCPFBRT_SCPSTAT.SCP_UID)
  ON tblSCPFBRT_SCPSTAT.SCP_UID = tblSCPFBTS.SCP_UID);

AFTER

Code:

PARAMETERS intNumWorkDays Long;
  ... same as above

ru1 · March 14th, 2005, 11:20 AM

Jack. It sounds like you have an Access Problem, not a function problem. Try this: Create a blank database, import all your objects into the blank database and then try running your function. If there are still problems, ask your system admin if he will take a look at your "MSACCESS.PIP" file.

Hope this helps.

RU1

JackNimble · March 14th, 2005, 04:56 PM

Quote:

quote:Originally posted by ru1
Jack. It sounds like you have an Access Problem, not a function problem. Try this: Create a blank database, import all your objects into the blank database and then try running your function. If there are still problems, ask your system admin if he will take a look at your "MSACCESS.PIP" file.

Hope this helps.

RU1

Hello RU1,

Thanks for the reply... Unfortunately, this solution did not work for me. I recreated the DB from scratch and I am still having the same problem.

As for the PIP file, I need a little more information before I can go to our IS support desk. What exactly would the personal menu file (PIP) have to do with my UDF problem and why should the system admin look at it?

Thanks!