Re: Troubles with variables and objects

Tech-Archive recommends: Fix windows errors by optimizing your registry

=?Utf-8?B?QW1kdWtl?= <Amduke@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
news:C6FA7B48-5052-40F9-95EA-32166B128095@xxxxxxxxxxxxx:

> 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.

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





.

Relevant Pages

  • CDO, Active Directory and Mail Enable
    ... I've added a comment indicating which line generates the type mismatch ... Function CreateDL(strPath As String, strGroupName As String, ... ' CreateDL(strPath As String, strGroupName As String, strSamAcctName As ... Dim iAdCont As IADsContainer ...
    (microsoft.public.exchange.development)
  • Re: Type mismatch calling a COM object developed in vb6
    ... a type mismatch error. ... public function test(a as string) ... It gives me a type mismatch. ...
    (comp.lang.basic.visual.misc)
  • CDO Active Directory and Mail Enabling
    ... Can anybody explain why we'd receive a "type mismatch" error in a mail ... Function CreateDL(strPath As String, strGroupName As String, ... ' CreateDL(strPath As String, strGroupName As String, strSamAcctName As ... Dim iAdCont As IADsContainer ...
    (microsoft.public.outlook)
  • Re: DTS Multiple Columns
    ... A type mismatch seems fairly straight forward as an explanation. ... Obviously change CStr if OKCUA1is not really a string, ...
    (microsoft.public.sqlserver.dts)
  • Re: CDO, Active Directory and Mail Enable
    ... I've added a comment indicating which line generates the type mismatch ... Function CreateDL(strPath As String, strGroupName As String, ... ' CreateDL(strPath As String, strGroupName As String, strSamAcctName As ... Dim iAdCont As IADsContainer ...
    (microsoft.public.exchange.development)