How to modify a Select clause to compare a nvarchar to an int value ? 1

[GarHeard]

How would you modify the following module to loop through replacing variable intBranch with
variable [Account Number] which is defined as nvarchar 10. The first 3 positions of [Account Number]
are similar to intBranch and contain the values '001' through '999'.

For ex:
[Account Number]
001 123456
001 234567
002 245678
002 344564

I am trying to create separate spreadsheets for every change in the first 3 positions of [Account Number].

Dim intBranch as Integer
Dim strSQL as string
Dim cnn as ADODB.Connection
Dim strFile as string


Set cnn = New ADODB.Connection
Set cnn = Currentproject.Connection
For intBranch = 1 to 6
strSQL = "DELETE FROM tblT"
cnn.Execute strSQL
strSQL = "INSERT INTO tblT SELECT * FROM tblA WHERE [Branch #]=" & intBranch
cnn.Execute strSQL
strFILE = "C:\BRANCH" & intBranch & ".XLS"
DoCmd.TransferSpreadsheet acExport, 8, "tblT", strFile, True, ""
Next
---------------------------------------------------------

Something similar to the following but I think I need to convert the intBranch :

strSQL = "INSERT INTO tblT SELECT * FROM tblA WHERE [Account Number] LIKE " & intBranch & "%"

 

[RoyVidar]

I think something like this could perhaps be used

[tt] strSQL = "INSERT INTO tblT SELECT * FROM tblA WHERE [Branch #] like '00" & cstr(intBranch) & "%'"[/tt]

But - this is a tad hardcoded, isn't it? I'd probably tried to do this a bit more dynamic - retrieving the actual branches and looping them?

Roy-Vidar