[RESOLVED] .csv file select statement problem

[billboy]

This is similiar to the other problem I have posted , althought I think I have narrowed this one down a bit more.

It seems that if I use this statement and some of my Street Full Address fields do not have a "-" then I get an error, "Provider could not determine the string value"

If I remove the Left and InStr then I am good to go

So I examined one of my database files and discovered that in one of them there were some records of address with no postal code "-" ,I deleted them and it works

Dim comm As New OleDb.OleDbCommand("Select *, Left(([Street Full Address]), InStr([Street Full Address],'-')-1)  as StreetFullAddress From " & Me.OpenFileDialog1.SafeFileName & " Where Status  <> 'S'", con)
        Dim dalist As New OleDbDataAdapter(comm)

In summary I think what I need is to account for there maybe being records that dont have the "-" in the address?

[brucevde]

You need to ensure the Instr function returns a valid value to be used by the Left function. If Instr returns 0(ie, no '-' in the address) you are passing -1 to the Left function, which is obviously invalid.

MS Access has the IIF function, basically an If statement within a SQL statement.

Select *, 
Left([Street Full Address], IIF(InStr([Street Full Address],'-')>0, InStr([Street Full Address],'-')-1, Len([Street Full Address]))) as StreetFullAddress
From

[billboy]

You need to ensure the Instr function returns a valid value to be used by the Left function. If Instr returns 0(ie, no '-' in the address) you are passing -1 to the Left function, which is obviously invalid.

MS Access has the IIF function, basically an If statement within a SQL statement.

Select *, 
Left([Street Full Address], IIF(InStr([Street Full Address],'-')>0, InStr([Street Full Address],'-')-1, Len([Street Full Address]))) as StreetFullAddress
From

Thank you, thats exactly what I was looking for, I was struggling on how to write it.

I now have a situation where there is a "-" after the numbers in the address, so with this new statement iI lose the street name.

example: Data normally comes as

1313 Mockingbird lane, MyCity, CA 91302-0045

sometimes there is no "-0045"

now I discovered there is a record that has

1313-Mockigbird lane, MyCity, CA 91302-0045


I cant see a way to account for this?? Can you?

Thanks again for your help

[billboy]

Dim comm As New OleDb.OleDbCommand("Select *, Left([Street Full Address], IIF(InStr(7,[Street Full Address],'-')>0, InStr(7,[Street Full Address],'-')-1, Len([Street Full Address])))  as StreetFullAddress From " & Me.OpenFileDialog1.SafeFileName & " Where Status  <> 'S'", con)
        Dim dalist As New OleDbDataAdapter(comm)

Did the trick, so far

Thanks