Results 1 to 12 of 12

File By Date Criteria

  1. #1
    DMT Dave is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    295

    File By Date Criteria

    Hi Guy's, i have 2 combo boxes, one requeries the other, in the 2nd combo it lists all PDF files in a folder, if there is 1000 PDF files, it is a lot to requery and list them in combo 2.

    Can my following code add a criteria based on the last part of the file name, files are called
    for example:

    Joe 19-10-19.pdf
    Fred 19-10-19.pdf
    Peter 19-10-19.pdf
    Andrew 18-10-19.pdf
    Steven 17-10-19.pdf


    As it stands now, all files are added to the combo, if i had a date combo called something like cboDate and for example select 19 October 2019

    Combo 2 lists:

    Joe 19-10-19.pdf
    Fred 19-10-19.pdf
    Peter 19-10-19.pdf

    And doesn't list:

    Andrew 18-10-19.pdf

    Steven 17-10-19.pdf



    Code:
    Dim fso As Object, fld As Object, Fil As Object
    Dim I as Integer
    Code:
    
    
    Code:
       Set fso = CreateObject("Scripting.FileSystemObject")
        Me.cboViewPO.RowSource = ""
        Me.cboViewPO.Requery
        SubFolderName = "T:\Folder1\Folder2\"
        Set fld = fso.getfolder(SubFolderName)
        For Each Fil In fld.Files
            i = i + 1
        Me.cboViewPO.AddItem Fil.Name 'added
        Next Fil
        Me.cboViewPO.Requery

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,306
    What exactly are you trying to do in plain English?
    There is a DateCreated and DateModified property associated with files. You don't often (but sometimes) see files named so consistently as you have shown.
    You may get some idea from the Similar Threads at the bottom of this thread.
    Good luck.

  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,623
    You can try the Right() function on the file name. It would require consistent naming of course.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    DMT Dave is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    295
    Thank you for your reply, where in the code would i use the right function ?, i know it is simple to use right function for a date field in the database but never tried using the right function for external reasons ie; in he case file nmes in a folder

    Would I be correct in this ??

    Code:
      Set fso = CreateObject("Scripting.FileSystemObject")
        Me.cboViewPO.RowSource = ""
        Me.cboViewPO.Requery
        SubFolderName = "T:\Folder1\Folder2\"
        Set fld = fso.getfolder(SubFolderName)
        For Each Fil In fld.Files
            i = i + 1
        Me.cboViewPO.AddItem
    Code:
    Right(
    Fil.Name
    ,"dd-mm-yy") & ".pdf"
    will his work ? don't have the database on this machine to test Next Fil Me.cboViewPO.Requery




  5. #5
    DMT Dave is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    295
    Made a mistake in post 4, the 2nd set of code tags shouldt have been there!

    Right( etc shgould have been at the end of Me.cboViewPO.AddItem

    Kindest

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,623
    I was thinking along the lines of:

    Code:
    If Right(Fil.Name, 12) = "19-10-19.pdf" Then
      'Your add item line
    End If
    In production you'd probably build the string from user input.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    DMT Dave is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    295
    HI pbaldy so would i be right in trying


    Code:
    Me.txtDate shows 19th October 2019
    Dim PDFDate as Date
    PDFDate = Format(Me.txtDate,"dd-mm-yy")
    
    
    If Right(Fil.Name, 12) = PDFDate & ".pdf" Then
    'Your add item line End If
    Also would this go just before the I = I +1 or just after that line ??
    Last edited by DMT Dave; 10-20-2019 at 02:38 PM. Reason: Adjusted

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,623
    Doesn't matter since that variable isn't used for anything.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    DMT Dave is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    295
    THank you very much pbaldy, going to update and try today, just another one, that's bugging me as i cant see s problem ?

    I am getting a runtime error 3131 sytax error in FROM clause from this one

    strCustName is set to string and InvNo is set to integer and the record does exist

    Code:
    Set rs = CurrentDb.OpenRecordset("SELECT tblInvoices.tblInvoiceNumber, tblInvoices.InvoiceDate," _
    & "tblInvoices.Customer, tblInvoices.Add1, tblInvoices.Add2, tblInvoices.Town, tblInvoices.PostCode," _
    & "tblInvoices.InvoiceDetails, tblInvoices.Amount FROM tblInvoices" _
    & "WHERE Customer = '" & strCustName & "'" & " & InvoiceNumber = " & InvNo)

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,306
    It seems this
    " & InvoiceNumber = "
    should be
    " AND InvoiceNumber = "

    Good luck.

  11. #11
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    380
    Code:
    & "tblInvoices.InvoiceDetails, tblInvoices.Amount FROM tblInvoices" _
    & "WHERE Customer = '" & strCustName & "'" & " & InvoiceNumber = " & InvNo)
    You may also need a space between tblInvoices and Where

  12. #12
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,623
    This is a valuable debugging tool:

    http://www.baldyweb.com/ImmediateWindow.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 01-30-2019, 05:55 PM
  2. Replies: 14
    Last Post: 05-24-2017, 02:22 PM
  3. Replies: 2
    Last Post: 04-02-2015, 12:45 PM
  4. Import Excel file based on a date and time in file name
    By nhylan in forum Import/Export Data
    Replies: 4
    Last Post: 04-16-2013, 03:26 PM
  5. Replies: 3
    Last Post: 08-21-2012, 03:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums