Writing to Table Issue

[Djblois]

I am using this code to log errors:

Sub LogError(strSub As String, lngErrCode As Integer, strErrDesc As String)

    Dim cnn As ADODB.Connection
    Dim strSQL As String
    
    Set cnn = CurrentProject.Connection
    
    
    strSQL = "INSERT INTO tblLog (ErrorNum, ErrMessage, UserName, ErrTime, BuildNum, CurrentSub) "
    
    strSQL = strSQL & "VALUES ( " & lngErrCode & ", '" & strErrDesc & "', '" & strUserLogin _
        & "', #" & Date & "#, " & DLookup("[VersionNum]", "tblVersion", "[VersionID] = 1") & _
        Format(DLookup("[VersionMinNum]", "tblVersion", "[VersionID] = 1"), ".00") & _
        Format(DLookup("[BuildNo]", "tblVersion", "[VersionID] = 1"), ".00") & ", '" & strSub & "' ) "
        
    cnn.Execute strSQL, , adExecuteNoRecords

End Sub

I am having one issue the BuildNum keeps going into the table as 0. I added this to a Watch Window and the right figure comes out in the Watch Window:

DLookup("[VersionNum]", "tblVersion", "[VersionID] = 1") & _
        Format(DLookup("[VersionMinNum]", "tblVersion", "[VersionID] = 1"), ".00") & _
        Format(DLookup("[BuildNo]", "tblVersion", "[VersionID] = 1"), ".00") & "

I have tried both Text and Integer as the Field types in the table. What am I doing wrong?

 

[pbaldy]

What type of thing will those return, and what is the data type of BuildNum? You're treating the field like a number, but with the concatenation, I suspect that returns a string, which would probably evaluate to 0. Maybe you want to add them together instead of concatenating?

 

[Djblois]

no I can't add them because the end result currently should be 1.52.02 The first dlookup pulls in 1, the second one pulls in .52 and the third one pulls in .02 . After they are concatenated together they create the 1.52.02. This is the version number of my program. I want to know what version somebody was using because it may be fixed in a newer version and I can also see what version an error started on.

 

[pbaldy]

Well, that is a text value, because a numeric value can't have 2 decimal points. You need to change the data type of the field to text, and add the single quotes in your code.

 

[Djblois]

Thank you i got it working now.