Prev: How I can get a missing file "SHLWAPI.dll"
Next: Creating a Query based of two diffent data tables
From: IgorM on 21 Mar 2010 17:02
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

From: Douglas J. Steele on 21 Mar 2010 18:29
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 & ")"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"IgorM" <igorm(a)live.com> wrote in message
news:6D50EFC9-5C8A-44EE-9ED1-4B59AE3F621D(a)microsoft.com...
> 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

 | 
Pages: 1
Prev: How I can get a missing file "SHLWAPI.dll"
Next: Creating a Query based of two diffent data tables