CODEWITHSUNDEEP
vbams-access
Moritz
Moritz

Reputation: 389

Convert string to int in VBA SQL query

I would like to import a dbf table (FoxPro) into a temporary MS Access table and copy 2 columns into a final one before deleting the temp one.

My code looks like this

'read all data into temp table
DoCmd.TransferDatabase acImport, "ODBC Database", DSNCONNECTIONSTRING, acTable, "BEL_PLZ", "Belegungsplaetze_Temp", False
'update table
With CurrentDb
    .Execute "INSERT INTO Belegungsplaetze (Belegungsplatznr,Bezeichnung) " & _
            "SELECT NR,BEZ FROM Belegungsplaetze_Temp t " & _
            "WHERE NOT EXISTS(SELECT 1 FROM Belegungsplaetze s " & _
            "WHERE t.NR = s.Belegungsplatznr) "
     .Execute "UPDATE Belegungsplaetze SET Belegungsplatznr = Trim([Belegungsplatznr]);"
End With

The problem I'm facing is that the column NR from Belegungsplaetze_Temp is a string and I would like to have it as an integer in my final table (final table has 2 columns Belegungsplatznr = Int and Bezeichnung = short text)

Upvotes: 1

Views: 3893

Answers (1)

APEALED
APEALED

Reputation: 1663

This answer's credit belongs entirely to serakfalcon, as they provide a completely valid anwser in the comments of the question.

Use the Val(NR) function to convert Text values into numbers. Microsoft documentation. And some Type Conversion Functions.

Upvotes: 1

Related Questions