How do I use a where statement in a query to return one value from a column in a tbl?

[Sathia1]

Hello,

Here is my code and below is an explanation of what I think may be the reason why I am getting the error message "Invalid Column Name 'MONTH_ID'". Thanks for helping. I have no clue how to fix this.

sql = "SELECT ACCUM_PLCY.MONTH_ID, PLCY_MASTER.INT_STRUCTURE_NBR, PLAN_CODE.PRODUCT_ACTUARIAL_CODE, ACCUM_PLCY.PLCY_DTE, ACCUM_PLCY.PLCY_ID, " & _
      "EQUITY_FUND.FUND_ID, EQUITY_FUND.FUND_ALLOCATION_PCT, EQUITY_FUND.PWV_PRM_BON_RTE, " & _
      "EQUITY_FUND.EQTY_ERN_INCR_RTE, EQUITY_FUND.CAP_RT, EQUITY_FUND.ANNUAL_SPREAD_AMT " & _
      "FROM DBO.ACCUM_PLCY ACCUM_PLCY, DBO.PLCY_MASTER PLCY_MASTER, DBO.PLAN_CODE PLAN_CODE, DBO.EQUITY_INDEX_PLCY_FUND EQUITY_FUND " & _
      "WHERE PLAN_CODE.PLAN_CODE_ID = PLCY_MASTER.PLAN_CODE_ID AND " & _
      "PLCY_MASTER.PLCY_ID = ACCUM_PLCY.PLCY_ID AND " & _
      "ACCUM_PLCY.PLCY_ID = EQUITY_FUND.PLCY_ID AND " & _
      "ACCUM_PLCY.MONTH_ID = " & newmth & " AND " & _
      "PLCY_MASTER.INT_STRUCTURE_NBR = " & newplcy & " AND " & _
      "PLAN_CODE.PRODUCT_ACTUARIAL_CODE IN (" & stringProd & ") AND " & _
      "(ACCUM_PLCY.ANNUITIZATION_VALUE_AMT * EQUITY_FUND.FUND_ALLOCATION_PCT / 100 <> 0)"

I believe the problem is with the variable newmth which I previously defined as

Public newmth as long
newmth=206

I would appreciate any ideas. Thank you!

[code green]

It means there is no MONTH_ID column in the table being referred to

[Sathia1]

Thanks code green for your response. I did double check though and there is a field called month_id. I was earlier referencing the wrong database. My problem now is that once I run the query, I'm getting the error message "Operation is not allowed when the object is open". Any ideas about this one?? I would really appreciate any help I could get

[ck9663]

This is an error on your front-end application. Try posting it on your front-end's forum and they might be able to help you more...

Good Luck!!!

~~ CK

[Sathia1]

Thanks CK for this idea! I'll try, fingers crossed.