Convert string to int in VBA SQL query

Question

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)

Val(NR) support.office.com/en-gb/article/… alternatively can convert to a specific numeric type using one of the functions on this page: support.office.com/en-us/article/… — serakfalcon
– serakfalcon, Commented Sep 13, 2019 at 8:07
@serakfalcon I would say that your comment is a valid perfect answer to post and close this question :) — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Sep 13, 2019 at 12:45

APEALED · Accepted Answer · 2019-09-13 20:22:38Z

1

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.

answered Sep 13, 2019 at 20:22

APEALED

1,7231 gold badge14 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert string to int in VBA SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related