Leon Sivertson
asked on
Passing integers
MS Access 2003 sending list of record identifiers to be process in SQL 2008
''===== gather all selected Table_IDs a list box
Dim ptr As Variant
Dim tbl_IDs As Variant
tbl_IDs = "("
For Each ptr In Me.ActiveTimes.ItemsSelect ed
tbl_IDs = tbl_IDs & Me.ActiveTimes.ItemData(pt r) & ","
Next
tbl_IDs = Left(TimeIDs, Len(TimeIDs) - 1) & ")"
looks like ('139415, 139412' )
Conversion failed when converting the varchar value '139415, 139412' to data type int.
I've tried many ways of formatting the argument but it always throws an error
''===== gather all selected Table_IDs a list box
Dim ptr As Variant
Dim tbl_IDs As Variant
tbl_IDs = "("
For Each ptr In Me.ActiveTimes.ItemsSelect
tbl_IDs = tbl_IDs & Me.ActiveTimes.ItemData(pt
Next
tbl_IDs = Left(TimeIDs, Len(TimeIDs) - 1) & ")"
looks like ('139415, 139412' )
Conversion failed when converting the varchar value '139415, 139412' to data type int.
I've tried many ways of formatting the argument but it always throws an error
Hi @Leon.
The first thing you must have in mind is the integers range. The MSAccess integers goes from -32768 to 32767.
If you try to pass bigger values you must use the Long (integer) data type. Also, instead of declaring the variables as Variants, is preferable to use defined data types.
Try this code:
If you want to include an apostrophe at the beginning and end of the string, change the last line in the code with this one:
Best regards.
Antonio (Barcelona, Spain)
The first thing you must have in mind is the integers range. The MSAccess integers goes from -32768 to 32767.
If you try to pass bigger values you must use the Long (integer) data type. Also, instead of declaring the variables as Variants, is preferable to use defined data types.
Try this code:
Dim ptr As Variant
Dim tbl_IDs As String
For Each ptr In Me.ActiveTimes.ItemsSelected
tbl_IDs = tbl_IDs & ", " & Me.ActiveTimes.ItemData(ptr)
Next
tbl_IDs = Trim(Mid(tbl_IDs, 2))
tbl_IDs = "(" & tbl_IDs & ")"
If you want to include an apostrophe at the beginning and end of the string, change the last line in the code with this one:
'Commented to add apostrophes
'tbl_IDs = "(" & tbl_IDs & ")"
tbl_IDs = "('" & tbl_IDs & "')"
Best regards.
Antonio (Barcelona, Spain)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Antonio, I did not disagree with you at all :)
Just like you can't compare apples to bananas, you can't take an integer value and concatenate it with a string such as ",". The way around this is to convert the integer to a string. Try this..
>tbl_IDs & CStr(Me.ActiveTimes.ItemDa