SQL Where statement Error - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

SQL Where statement Error

Warren Lovegrove

3 years ago

Hi,
I'm trying to insert a where statement into a DSum equation within the Report_Load sub below.  It works when bringing it in through the CompnayExpBtn sub, however crashes with a Run-Time error 3464 when bringing it in through the PropertyExpBtn sub.  The only different is that i'm changing the = in the criteria sql statement to a <>. Any advise you can provide would be appreciated.

Private Sub CompanyExpBtn_Click()

    Dim StartDate As Date, EndDate As Date
    Dim YearDate As Integer, MonthDate As Integer
    Dim ExpType As String, W As String, W1 As String, Criteria As String

    W = "General - Company"
    W1 = "Bank"

    If MsgBox("Are you after a monthly report?", vbYesNo, "Monthly Report") = vbYes Then
        MonthDate = InputBox("Please specify month you wish to report!" & vbNewLine & vbNewLine & "Format as mm", "Month Report Period")
        YearDate = InputBox("Please specify which year monthly report is required!" & vbNewLine & vbNewLine & "Format as yyyy", "Month Report Period")
        Criteria = "[CostCategory] = '" & W & "' or [CostCategory] = '" & W1 & "'
        ExpType = "Company Expenses" & "|" & "Month" & "|" & MonthDate & "|" & YearDate & "|" & Criteria
        DoCmd.OpenReport "MonthlyExpenseR", acViewPreview, , Criteria, , ExpType
    End if

Private Sub PropertyExpBtn_Click()

    Dim StartDate As Date, EndDate As Date
    Dim YearDate As Integer, MonthDate As Integer
    Dim ExpType As String, W As String, W1 As String, Criteria As String

    W = "General - Company"
    W1 = "Bank"

    If MsgBox("Are you after a monthly report?", vbYesNo, "Monthly Report") = vbYes Then
        MonthDate = InputBox("Please specify month you wish to report!" & vbNewLine & vbNewLine & "Format as mm", "Month Report Period")
        YearDate = InputBox("Please specify which year monthly report is required!" & vbNewLine & vbNewLine & "Format as yyyy", "Month Report Period")
        Criteria = "[CostCategory] <> '" & W & "' or [CostCategory] <> '" & W1 & " '"
        ExpType = "Property Expenses" & "|" & "Month" & "|" & MonthDate & "|" & YearDate & "|" & Criteria
        DoCmd.OpenReport "MonthlyExpenseR", acViewPreview, , Criteria, , ExpType
End if

Private Sub Report_Load()

    Dim EntityArray() As String, SP As String, EP As String, YP As String, ExpType As String, PeriodType As String, Criteria As String

    EntityArray() = Split(openargs, "|")
    ExpType = EntityArray(0)
    PeriodType = EntityArray(1)
    SP = EntityArray(2)
    EP = EntityArray(3)
    YP = EntityArray(2)
    Criteria = EntityArray(4)

    If ExpType = "Property Expenses" Then
        ExpenseHeader.Caption = ExpType
        ExpenseTotal = Format(DSum("Expense", "BankRecordQ", Criteria), "Currency")
        IncomeTotal = Format(DSum("Income", "BankRecordQ", Criteria), "Currency")
    End If
    If ExpType = "Company Expenses" Then
        ExpenseHeader.Caption = ExpType
        Income.Visible = False
        IncomeLabel.Visible = False
        IncomeTotal.Visible = False
        RelatedPartyLabel.Caption = "Paid To"
        ExpenseTotal = Format(DSum("Expense", "BankRecordQ", Criteria), "Currency")
    End If

	Scott Axton @Reply 3 years ago
A search of the error you indicated, 3464, comes up as: "The error means that one of the data types in your values does not match the data type of the column where you're trying to insert it." Check out the values being returned and the data types of the fields they are going into.

	Scott Axton @Reply 3 years ago
Arrays can sometimes be tricky when it comes to data typing. You may need to use one of the Type Conversion to ensure the correct type.

Warren Lovegrove OP @Reply

3 years ago

Thanks Scott,
I've checked out the Type Conversion video but still can't work out why it would be a data type issue. Why i say that is that Criteria = "[CostCategory] = '" & W & "' or [CostCategory] = '" & W1 & "' works, while Criteria = "[CostCategory] <> '" & W & "' or [CostCategory] <> '" & W1 & " '", using the same W and W1 variables. As soon as i change to <>, the criteria crashes with that error. W, W1, and criteria are all strings defined within the code above.

	Kevin Robertson @Reply 3 years ago
Message box Criteria (or Debug.Print) to see what's being returned. Perhaps it's something unexpected.

	Warren Lovegrove OP @Reply 3 years ago
Hi Kevin, I've tried this and msgbox is returning what I would expect ([CostCategory] <> 'General - Company' or [CostCategory] <> 'Bank'). It definitely seems to be the use of <>, as the simply change to = runs fine. Is the use of <> with a string an issue? Thanks

	Kevin Robertson @Reply 3 years ago
Try changing the operator from Or to And. `[CostCategory] = 'General - Company' Or [CostCategory] = 'Bank'` `[CostCategory] <> 'General - Company' And [CostCategory] <> 'Bank'`

	Kevin Yip @Reply 3 years ago
Hi Warren, I don't know exactly why <> causes the error and = doesn't, but you can try using Not and = instead of <>: Criteria = "Not ([CostCategory] = '" & W & "') Or Not ([CostCategory] = '" & W1 & " ')" <> shouldn't cause such a problem, so maybe something else is going on. The error may be caused elsewhere. Use the debugger to debug line by line.

	Warren Lovegrove OP @Reply 3 years ago
Thanks Kevin, I've tried to substitute and for or, this allows routine to run through but for opens with no results. I'll try the not option and see if that works. Cheers

	Kevin Robertson @Reply 3 years ago
Could it be there is an extra space (probably not, just brain-storming). In your code above you have: `Criteria = "[CostCategory] <> '" & W & "' or [CostCategory] <> '" & W1 & " '"` I would write it like this (except I would use Double Double Quotes): `Criteria = "[CostCategory] <> '" & W & "' And [CostCategory] <> '" & W1 & "'"` Also, make sure there are records that meet the criteria.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View