0

My code gives the following error. How can I correct this?

Item cannot be found in the collection corresponding to the requested name or ordinal

ElseIf Me.chkItem.Checked = True Then
    Dim CheckNumber As String = ""
    Dim CheckRef As String = ""
    dsvoucheritem.Clear()
    DSVoucher_Expense.Clear()
    DSVoucher_Check.Clear()

    Try
        Me.lstCV.Items.Clear()
        strDiscount = Nothing
        rec.Open("select billpaymentcheckline.txnnumber, billpaymentcheckline.txndate" _
        & ", billpaymentcheckline.payeeentityreffullname" _
        & ", billpaymentcheckline.amount, billitemline.itemlineitemreffullname" _
        & ", billitemline.memo" _
        & ", billpaymentcheckline.appliedtotxndiscountamount" _
        & ", billpaymentcheckline.appliedtotxnrefnumber, billpaymentcheckline.bankaccountreffullname" _
        & ", billpaymentcheckline.appliedtotxndiscountaccountreffullname" _
        & ", billpaymentcheckline.appliedtotxntxndate, billpaymentcheckline.appliedtotxnamount" _
        & ", billpaymentcheckline.refnumber, account.AccountNumber from (billitemline inner join" _
        & " billpaymentcheckline on billitemline.refnumber=billpaymentcheckline.appliedtotxnrefnumber) left outer join" _
        & " account on billitemline.APAccountreflistid=account.listid where" _
        & " billpaymentcheckline.bankaccountreflistid='" &Me.lblBankID.Text & "' and" _
        & " billpaymentcheckline.refnumber between '" & CInt(Me.txtRefFR.Text)
        & "' and '" & CInt(Me.txtRefTO.Text) & "'", con, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly)
4444
  • 3,541
  • 10
  • 32
  • 43
Maine
  • 1
  • 1
  • 5

2 Answers2

0

It's saying it doesn't recognize one of your column names. Double check all of them. You can also try removing fields until you find the culprit.

FloatingKiwi
  • 4,408
  • 1
  • 17
  • 41
  • I remove the outer join and then im getting an error like this "[QODBC] Lexical element not found" – Maine Aug 10 '16 at 05:42
  • The element it will be looking for is account. Does anything reference account.something? – FloatingKiwi Aug 10 '16 at 05:44
  • Your joins are probably working fine. Check your column names instead. – FloatingKiwi Aug 10 '16 at 05:44
  • ok, how about trying just select * from that and seeing what comes back. Are you sure the database your connecting to is the exact schema you're expecting? – FloatingKiwi Aug 10 '16 at 06:25
  • i can't use the select * because i need to get the data in two tables . – Maine Aug 10 '16 at 12:20
  • when i use the table billexpenseline and billpaymentcheckline the system run . but when i use the billitemline and billpaymentcheckline im getting those error . i just change the billexpenseline to billitemline . i dont really get why im having those errors – Maine Aug 10 '16 at 12:22
0

memo is a reserved word in Access SQL.

Try billitemline.[memo]

Andre
  • 26,751
  • 7
  • 36
  • 80