Dir not finding files

[GraeagleBill]

A folder with files like this:



A module that is looking for them with the Dir function:



Dir finds the 1st two files you see in the screenshot, but none of the others. (You'll see my Debug.Print in the IW) I'm in mystery as to why the other files aren't returned.



Each file name is comprised of 6 tokens separated by semicolons ";" with the ".mp3" file extension on the end. (periods appear where one would normally expect colons but one cannot use colons in file names.)

[June7]

I can only guess it has something to do with the semi-colons in file name.

Really should provide code as text, not image. Cannot copy/paste code from image.

I don't see anything that will print each filename to IW.

I would not expect colons in date values, only in time parts.

Are you saying records are not created?
Does SComments field allow empty string?
Is SDate a date/time field? "2024.11.10.0" is not a valid date structure.

[GraeagleBill]

We've been processing these mp3 files literately for years, but I can't say I ever remember processing folders with Dir like I'm trying to do now. I'll twiddle with one of the file names with substitutions for the semicolon to see if your suspicions reveal anything.

[Micron]

I agree with that response, unless maybe it's acceptable in some locales to use one semi colon in a file name instead of a dot/period. Then again, never 2 consecutive ones since the first of a repeated character usually escapes the second one, and who knows what shenanigans that will raise?

[GraeagleBill]

Semicolon not the issue. I changed the field delimiters with a simple rename to use underscore and re-ran the sub, but Dir still not finding the file.

Private Sub cmdImport_Click()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  This sub looks first at the sermon portal where audio files are passed
'  to the TMS host machine within the LAN.  Secondly, the sub will interrogate
'  the downloadsAdjusted to see if any audio files have been extracted from
'  Youtube videos and adjusted to the TMS format that also need to be
'  imported.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim strSerFldr As String
Dim strNewSer As String
Dim strTMSfmt() As String
Dim rsSermons As DAO.Recordset


strSerFldr = DLookup("InstSerLib", "InstProperties")


Set rsSermons = DBEngine(0)(0).OpenRecordset("QSermons")


With rsSermons
Debug.Print strSerFldr & "DownloadsAdjusted\" & "*.mp3"
strNewSer = Dir(strSerFldr & "DownloadsAdjusted\" & "*.mp3")
    Do While Len(strNewSer) <> 0
        strTMSfmt = Split(strNewSer, "_")
        If UBound(strTMSfmt) = 5 Then
            .AddNew
            !SDate = strTMSfmt(0)
            !SBook = strTMSfmt(1)
            !SReference = strTMSfmt(2)
            !SPresenter = strTMSfmt(3)
            !SComments = strTMSfmt(4)
            !SFileType = strTMSfmt(5)
            !SDuration = GetDuration(strSerFldr, strNewSer)
            !SFileName = strNewSer
            .Update
        Else
            MsgBox "Don't recognize format of '" & strNewSer & "'"
        End If
    strNewSer = Dir
    Loop
    
End With


rsSermons.Close
Set rsSermons = Nothing


End Sub

The renamed file: 2024.01.07.0_Psalm_95_Daniel Ransom__.mp3      (eliminating consecutive delimiters made no difference)

[June7]

I tested the code with a file of that same name (also tested with semi-colons). No problem finding.

What happens if you put Debug.Print strNewSer after Do While line?

Have you step debugged?

[GraeagleBill]

The adjusted code as you suggest:
The IW when I run the code:

Graeagle Community Church Live Stream-1.mp3
Graeagle Community Church Live Stream.mp3

Private Sub cmdImport_Click()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  This sub looks first at the sermon portal where audio files are passed
'  to the TMS host machine within the LAN.  Secondly, the sub will interrogate
'  the downloadsAdjusted to see if any audio files have been extracted from
'  Youtube videos and adjusted to the TMS format that also need to be
'  imported.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim strSerFldr As String
Dim strNewSer As String
Dim strTMSfmt() As String
Dim rsSermons As DAO.Recordset


strSerFldr = DLookup("InstSerLib", "InstProperties")


Set rsSermons = DBEngine(0)(0).OpenRecordset("QSermons")


With rsSermons
'Debug.Print strSerFldr & "DownloadsAdjusted\" & "*.mp3"
strNewSer = Dir(strSerFldr & "DownloadsAdjusted\" & "*.mp3")
    Do While Len(strNewSer) <> 0
    Debug.Print strNewSer
        strTMSfmt = Split(strNewSer, "_")
        If UBound(strTMSfmt) = 5 Then
            .AddNew
            !SDate = strTMSfmt(0)
            !SBook = strTMSfmt(1)
            !SReference = strTMSfmt(2)
            !SPresenter = strTMSfmt(3)
            !SComments = strTMSfmt(4)
            !SFileType = strTMSfmt(5)
            !SDuration = GetDuration(strSerFldr, strNewSer)
            !SFileName = strNewSer
            .Update
        Else
            MsgBox "Don't recognize format of '" & strNewSer & "'"
        End If
    strNewSer = Dir
    Loop
    
End With


rsSermons.Close
Set rsSermons = Nothing


End Sub

[June7]

Then I have no idea what is wrong. Code works for me.

[Welshgasman]

I am going to go with .mp3) is not the same as .mp3 ?

Which of course could be confirmed just by walking the code.

[CJ_London]

I was about to say the same thing - also think your date format of 2024.12.01.0 won't work if your sDate field is a date datatype.

Depends on your regional settings but

?cdate("2024.12.01.0") generates a datatype mismatch error

my regional setting use a -

cdate("2024-12-01") works

but with .0 or -0 or <space>0, they all generate the same mismatch error

[June7]

Oh, doh! So obvious! I did NOT test with paren.

But I did mention issue of date data type in post 2 as well as possible issue of empty string, which I never allow in fields.

[GraeagleBill]

I'm with June7, doh! If I had looked at my own post #1 sample screenshot of the file directory like our good friends in the UK did............ maybe I?

SDate is a string. Perhaps a more descriptive name would be more appropriate. (The string format is yyyy.mm.dd.n, where "n" is the day's file generation value, as there can be multiple sermons on the same date.)

GetDuration Function didn't care for the ".mp3)" file type either

!SDuration = GetDuration(strSerFldr, strNewSer)

Public Function GetDuration(ByVal p_FolderPath As String, ByVal p_FileName As String) As String