Weird IIF problem

[NaderC]

Hi, I have a table (in an Access database) with two fields: CustomerName and OrderNumber.
Some order numbers have dashes, for example:
123-4, 123-5, 123-6 etc.
I wrote a query to strip those dashes from these ordernumbers, but only for 3 specific customers: Mark, Mike and Jane.
This is the query:

SELECT CustomerName, OrderNumber, 
    IIf(InStr(1,[OrderNumber],"-")<>0 And InStr(1,[OrderNumber],"-B")=0,
        IIf([CustomerName] Like '*mark*' Or [CustomerName] Like '*mike*' Or [CustomerName] Like '*jane*',
            CStr(Mid([OrderNumber],1,InStr(1,[OrderNumber],"-")-1)),
        CStr([OrderNumber])),
    CStr([OrderNumber])) AS NewOrderNumber
FROM Test
GROUP BY CustomerName, OrderNumber, 
IIf(InStr(1,[OrderNumber],"-")<>0 And InStr(1,[OrderNumber],"-B")=0,
        IIf([CustomerName] Like '*mark*' Or [CustomerName] Like '*mike*' Or [CustomerName] Like '*jane*',
            CStr(Mid([OrderNumber],1,InStr(1,[OrderNumber],"-")-1)),
        CStr([OrderNumber])),
    CStr([OrderNumber]));

This query works great when I run it from Access. However, if I run it from Visual Basic .NET, it does not strip any dashes for any customers. I am using the following code in VB.NET:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\test.mdb")
        conn.Open()
        Dim strSQL As String = "SELECT * FROM [Strip] ORDER BY NewOrderNumber"
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        Dim line As String
        While (reader.Read())
            line = reader("NewOrderNumber") & "        " & reader("CustomerName") & Environment.NewLine
            My.Computer.FileSystem.WriteAllText("C:\TheFile.txt", line, True)
        End While
        conn.Close()
    End Sub

Did anyone encounter a problem like this before?

Thank you,

Nader

 

[cjard]

From what I can see the query is unnecessarily wordy and complex. Here is a simpler version:

SELECT
IIF(CustomerName IN ('Mark','Mike','Jane'), Left(OrderNumber, Len(OrderNumber) - 2) & Right(OrderNumber, 1), OrderNumber) as NewOrderNumber,
CustomerNumber,
OrderNumber
From
Test

You perform no aggregation so the group by is 100% unnecessary

This query works great when I run it from Access. However, if I run it from Visual Basic .NET, it does not strip any dashes for any customers. I am using the following code in VB.NET:

Youre using .Net 2, data access is better done a different way. Read the DW2 link in my signature

 

[NaderC]

Thanks, cjard. The problem was with the LIKE keyword inside the IIF statement. It did not seem to work when accessed from .NET, even though it worked fine in Access.

 

[cjard]

Ah, also I should have mentioned that you should use:

LIKE '%' & text & '%'

In .NET.. * is only a wildcard in access front end