Text value type is calculated on INSERT

[IgorM]

Hi

I think this is the right group for my question.
I have a macro in Excel VBA that inserts data into Access accdba database.
The code snippet that inserts the data is:

Public Function InsertPhotoMapNumbers(ByRef miMaxMapNo As Integer, ByRef
miApplicationYear As Integer) As Long
Dim msMapNo As String

'Some code here
msMapNo = CStr(miMapNo) + "/" + CStr(miMaxMapNo)
mobjCommand.CommandText = "INSERT INTO tblMaps(PhotoMapNo, ApplicationYear)
VALUES (" & msMapNo & "," & miApplicationYear & ")"

'Execute the SQL statement.
mobjCommand.Execute RecordsAffected:=mlRecordsAffected, _
Options:=adCmdText Or adExecuteNoRecords

'Some more code here
End Function


The data in PhotoMapNo column of the tblMaps table should look like:
"255/758", "256/758" etc. The PhotoMapNo column is of text type.
Unfortunately at some point the two values are treated as values and
calculated (divided) so they look like 0,33641, etc.
What should I do to get the correct data type into table column.

Kind regards
IgorM

 

[Douglas J. Steele]

Since PhotoMapNo is a text field, you need quotes around the value being
stored:

mobjCommand.CommandText = "INSERT INTO tblMaps " & _
"(PhotoMapNo, ApplicationYear) " & _
"VALUES ('" & msMapNo & "'," & miApplicationYear & ")"

Exagerated for clarity, that last line is

"VALUES ( ' " & msMapNo & " ' ," & miApplicationYear & ")"