Data Conversion Error

[Ecohouse]

I have a field in a recordset which is a string. It can look like
this: 13-15-67-56

I need to break out this string into numbers to run in queries to open
recordsets.

But when I try to open the recordset I get the data conversion error
3421.

I have tried using the format function to convert the string and it
doesn't work. But if I get the strSQL statement from the Immediate
window and paste it into a query it works fine with no errors.

Here's some of the code:

With rstSearchTable
If .RecordCount > 0 Then
.MoveFirst

strSubBrands = ""
srchPos = InStr(!SubBrandSK, "-")
'See if there is more than one subBrand
If srchPos > 0 Then
strSubBrands = Left(!SubBrandSK, srchPos - 1)

'Dim newsubbrand As Long
'newsubbrand = Format(strSubBrands, "General Number")
'newsubbrand = Format(strSubBrands, "standard")

Do Until .EOF
'Get subBrands
' strSQL = "SELECT * FROM Brand WHERE SK = " & newsubbrand
& ";"
strSQL = "SELECT * FROM Brand WHERE SK = " & strSubBrands
& ";"
'strSQL = "SELECT * FROM Brand WHERE SK = " &
Format(strSubBrands, "Standard") & ";"

Set rstSubBrands = .OpenRecordset(strSQL, dbOpenDynaset)

Loop
.MoveNext
Else
strSQL = "SELECT * FROM Brand WHERE SK = " & strSubBrands &
";"
Set rstSubBrands = .OpenRecordset(strSQL, dbOpenDynaset)
End If
End If
End With

I left some of the commented out code in there to show what I've been
trying.

Any help would be greatly appreciated.

[Arno R]

"Ecohouse" <vi*******@netzero.com> schreef in bericht news:11**********************@g49g2000cwa.googlegr oups.com...

I have a field in a recordset which is a string. It can look like
this: 13-15-67-56

I need to break out this string into numbers to run in queries to open
recordsets.

But when I try to open the recordset I get the data conversion error
3421.

I have tried using the format function to convert the string and it
doesn't work. But if I get the strSQL statement from the Immediate
window and paste it into a query it works fine with no errors.

Here's some of the code:

With rstSearchTable
If .RecordCount > 0 Then
.MoveFirst

strSubBrands = ""
srchPos = InStr(!SubBrandSK, "-")
'See if there is more than one subBrand
If srchPos > 0 Then
strSubBrands = Left(!SubBrandSK, srchPos - 1)

'Dim newsubbrand As Long
'newsubbrand = Format(strSubBrands, "General Number")
'newsubbrand = Format(strSubBrands, "standard")

Do Until .EOF
'Get subBrands
' strSQL = "SELECT * FROM Brand WHERE SK = " & newsubbrand
& ";"
strSQL = "SELECT * FROM Brand WHERE SK = " & strSubBrands
& ";"
'strSQL = "SELECT * FROM Brand WHERE SK = " &
Format(strSubBrands, "Standard") & ";"

Set rstSubBrands = .OpenRecordset(strSQL, dbOpenDynaset)

Loop
.MoveNext
Else
strSQL = "SELECT * FROM Brand WHERE SK = " & strSubBrands &
";"
Set rstSubBrands = .OpenRecordset(strSQL, dbOpenDynaset)
End If
End If
End With

I left some of the commented out code in there to show what I've been
trying.

Any help would be greatly appreciated.

strSubBrands is initialised as a string "" but you want to use a numeric value?
You could try: strSQL = "SELECT * FROM Brand WHERE SK = " & Clng(strSubBrands)

When formatting a string the string still remains a string...
With Clng() or CInt() you are converting...
Btw You don't need the ";" when building a SQL-string

HTH
Arno R

[Ecohouse]

Thanks for the help.

I've already tried that. I even tried the following:
strSQL = "SELECT * FROM Brand WHERE SK = 13;"
Set rstSubBrands = .OpenRecordset(strSQL, dbOpenDynaset)

This still returned the same error. So it's not that field. When I
try to look at the Brand table while debugging it says 'You can't open
the table 'Brand' for modification." It says the table is bound to a
form, etc.

Could this be causing the problem?

[Arno R]

"Ecohouse" <vi*******@netzero.com> schreef in bericht news:11**********************@g14g2000cwa.googlegr oups.com...

Thanks for the help.

I've already tried that. I even tried the following:
strSQL = "SELECT * FROM Brand WHERE SK = 13;"
Set rstSubBrands = .OpenRecordset(strSQL, dbOpenDynaset)

This still returned the same error. So it's not that field. When I
try to look at the Brand table while debugging it says 'You can't open
the table 'Brand' for modification." It says the table is bound to a
form, etc.

Could this be causing the problem?

No. Access just says that you can't modify the table.
You are still able to look at the table when that message comes up.

But I don't understand your problem anymore ...
You said 'it' worked when you pasted the SQL from the Immediate window...
What exactly did that SQL look like?
Did you try to set a breakpoint to debug the code?

Also: I see that besides your problem the Loop is endless...
I guess you need .MoveNext BEFORE the Loop.

HTH
Arno R

Arno R

[Lyle Fairfield]

Maybe:

********
Your code:

With rstSearchTable
<snips>
..OpenRecordset(strSQL, dbOpenDynaset)

*****************
DAO Object Documentation:

Function OpenRecordset([Type], [Options]) As Recordset
Member of DAO.Recordset

****************
Maybe a DAO recordset can open another DAO recordset with an SQL
string, I've never tried. But this would be outside the documentation.
The documentation (not the help documentation, the help file is mostly
just guesses) says the first argument is an optional Type which
probably must be numeric.

[Ecohouse]

Thanks for all the help. I was able to solve the problem. I
originally set the currentdb as a global field. And even though I was
using it in a with statement, the code wasn't seeing it. So once I
added that to where I was opening the recordset it worked.

The error was misleading because it didn't deal with any data
conversion. If the error had said that there was a db connection
problem I could have quickly solved this problem.

[Lyle Fairfield]

You're one hundred per cent right. Only a genius could have recognized
what was wrong.