By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,854 Members | 869 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,854 IT Pros & Developers. It's quick & easy.

Data Conversion Error

P: n/a
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.

Dec 19 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a

"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
Dec 19 '05 #2

P: n/a
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?

Dec 19 '05 #3

P: n/a

"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

Dec 19 '05 #4

P: n/a
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.

Dec 19 '05 #5

P: n/a
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.

Dec 19 '05 #6

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

Dec 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.