troubleshooting Question

Convert Logic From VBA to SQL

Avatar of mlcktmguy
mlcktmguyFlag for United States of America asked on
Microsoft AccessMicrosoft SQL Server
5 Comments1 Solution80 ViewsLast Modified:
I want to move a VBA process in my Access 2013 application to SQL.  The logic is pretty simple.  1. There is a loop, 2. left padding of a string and 3. checking  for the existence of a file.

I've done a little  T-SQL programming but nothing that had any of these 3 functionalities.

Can anyone help with the conversion?

This is the piece of logic I want to move into a SQL SP.
Dim wkMuniNum As Long
Dim wkMuniStr As String
'

SQHD_Prefix = "SQHD"
wkFileExtension=".txt"

For wkMuniNum = 1 To 1000
    '
    ' Any files for this muni?  Pick one of the six to see if it's there.
    '
    wkMuniStr = Trim(Str(wkMuniNum))
    If Len(wkMuniStr) = 4 Then
        '  nothing to do
    ElseIf Len(wkMuniStr) = 3 Then
        wkMuniStr = "0" & wkMuniStr
    ElseIf Len(wkMuniStr) = 2 Then
        wkMuniStr = "00" & wkMuniStr
    ElseIf Len(wkMuniStr) = 1 Then
        wkMuniStr = "000" & wkMuniStr
    End If
    '
    ' Look for the first file
    '
    wkSearchFile = wkTaxDataPath & SQHD_Prefix & wkMuniStr & wkFileExtension
    '
    If Len(Dir(wkSearchFile)) = 0 Then
        GoTo getNextMuni     '///////////////// get outof here and get next muni
    End If
.
. Logic if the file is found
.
.

getNextMuni:
Next wkMuniNum

  
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros