Troubles with variables and objects

A

Amduke

Hello,

I'm a novice in VBA but I try to get a living out of it.
I want to append records in a table. But while doing it, an error came up.
There was a record with a null value.
To avoid problems I thought, maybe I can create a variable to check if the
value is realy null.
Now I'm facing an error telling me: "Object required" ;-(
Find here some code I've written. Can somebody help me out?

Dim V_mabc As String
Dim V_comp As String
Dim V_tcost As Single
Dim V_share As Single
Dim V_cuco As String
Dim V_perc As Single
Dim V_cumprc As Single

'Position on top of the table
rstTable.MoveFirst
'Run thru the table from begin to end
While Not rstTable.EOF

'For Each Field In rstTable.Fields
'First we fill up the variables
F_mabc = rstResult.Fields(0).Name
F_comp = rstResult.Fields(1).Name
F_tcost = rstResult.Fields(2).Name
F_share = rstResult.Fields(3).Name
F_cuco = rstResult.Fields(4).Name
F_perc = rstResult.Fields(5).Name
F_cumprc = rstResult.Fields(6).Name

'Values in variables
V_mabc = rstTable.Fields(0).Value
V_cumprc = "0"
'Check for null values

If rstTable.Fields(1).Value Is Null Then
V_comp = Space(0)
Else
V_comp = rstTable.Fields(1).Value
End If
'Insert into table all the records
AppStr = "insert into " + rstResult.Source + " ( " + F_mabc + _
"," + F_comp + "," + F_tcost + "," + F_share + "," + F_cuco + _
"," + F_perc + "," + F_cumprc + ")"
ValStr = " Select '" + V_mabc + "','" + V_comp + "','" + V_tcost + _
"','" + V_share + "','" + V_cuco + "','" + V_perc + "','" + V_cumprc + "'"

'Concatenate the complete string to append a record to rstResult
AppSql = AppStr + ValStr

'set warnings off
DoCmd.SetWarnings False
DoCmd.RunSQL AppSql
rstTable.MoveNext
Wend
 
T

Tim Ferguson

If rstTable.Fields(1).Value Is Null Then
Click to expand...

There is a number of problems with your code: the specific error presumably
comes from this line, because the Is operator in VBA compares two Objects
-- you are using it to compare two Variants. Do not confuse the IS NULL
clause in SQL with the IsNull() function in VBA, which is waht you probably
think you want here.


B Wishes


Tim F
 
A

Amduke

Tim,

thx, you're a great help to me.
Indeed, you pointed directly to the problem; sorry I didn't mension it.

Now i'm facing another problem.
When building a string to build up the SQL statement a "Type mismatch"
occurs on this line: ValStr = " Select '" + V_mabc + "','" + V_comp +
"','" + V_tcost + _
"','" + V_share + "','" + V_cuco + "','" + V_perc + "','" + V_cumprc + "'"

Due to concatenate, I put the numeric values between strings, otherwise the
"decimal comma" is interpreted as two values.
 
T

Tim Ferguson

Now i'm facing another problem.
When building a string to build up the SQL statement a "Type mismatch"
occurs on this line: ValStr = " Select '" + V_mabc + "','" + V_comp
+ "','" + V_tcost + _
"','" + V_share + "','" + V_cuco + "','" + V_perc + "','" +
V_cumprc + "'"

Due to concatenate, I put the numeric values between strings,
otherwise the "decimal comma" is interpreted as two values.
Click to expand...

See the thread in this newsgroup:-

Subject: how to change decimal symbol in VB / Access?


For another thing, it's not very clever to use + for string concatenation
because one Null value will (a) cascade through the entire expression and
(b) cause a type mismatch error when you try to put it into a string
variable.

In general, you need to handle all of these conversions explicitly:

Public Function JetNumeric(SomeValue as Variant) As String

If IsNull(SomeValue) Then
JetNumeric = "NULL"

ElseIf IsNumeric(SomeValue) then
JetNumeric = Replace(Format(SomeValue,"General Number"), _
",", ".")

Else
Raise ' some useful error number

End If
End Function

Public Function JetString(SomeValue as Variant) As String
If IsNull(SomeValue) Then
JetString = "NULL"
Else
JetString = Chr$(34) & _
Replace(SomeValue,Chr$(34),String$(2,34)) & _
Chr$(34)
End If
End Function

Public Function JetDate(SomeValue as Variant) As String
If IsNull(SomeValue) Then
JetString = "NULL"
Else
JetString = Format(SomeValue, _
"\#yyyy\-mm\-dd\#")
End If
End Function

then

jetInsert = "VALUES (" & _
JetNumeric(v_fieldOne) & ", " & _
JetNumeric(v_fieldTwo) & ", " & _
JetString(v_fieldThree) & ", " & _
JetDate(v_fieldFour) & ")"

and so on. Note that you really do have to think about the data types in
advance, or decode the .Type property of each field to work out what it's
expecting.

The bigger picture is that I cannot even guess why you are doing this.
Your original post is really only doing in longhand what a simple append
query would do: what is wrong with a straightforward

INSERT SecondTable (FieldOne, FieldTwo, FieldThree)
SELECT FieldA, FieldC, FieldB FROM FirstTable
WHERE Something = TRUE

and let the db engine worry about everything else. Even more, is there
are really good reason for shunting the records from one table to
another? Remember that having the same piece of information in two places
is just asking for the two items to disagree -- this is the underlying
Big Sin in R theory.

All the best


Tim F
 
A

Amduke

Tim,

thank so much for your help. It is great to get response on such a short
notice.
 
T

Tim Ferguson

thank so much for your help. It is great to get response on such a short
notice.
Click to expand...

Glad to help. I'm also glad that it coincided with the other thread, so I
didn't have to type in the whole answer again.

(thinks.... must see about setting up a database of these answers
sometime... who do I know who knows about setting up databases..?)

All the best


Tim F
 
M

Mike DiCanio

Tim,

Extremely helpful, the tip about not confusing the "Is Null" with the
IsNull() was priceless.

Thanks.

Mike
 
Top