Solved Create a FilePath with Year at the end (1 Viewer)

[PatAccess]

Hello Guys,

I have create the following file Path which looks for a document in the form LastnameFirstname.pdf

    strFolderPath = CurrentProject.Path
    strFilePath = strFolderPath & "\States\" & Me.cboState.Column(0) & "\" & Me.cboName.Column(2) & Me.cboName.Column(3) & ".pdf"

However, I would like it in the form LastnameFirstnameYYYY.pdf with YYYY = any year (i.e.: SmithJohn2021.pdf)but I want to save YYYY into a variable to run a check on it later (Let's say: IF within this year or before THEN do something...)

1st - How do I create the LastnameFirstnameYYYY.pdf? then some advise on the IF check
(I know I need to first save the current year into a variable and then compare it with the year at the end of the FileName then...)

Thanks for the help

 

[theDBguy]

Hi. You can get the current year by using the expression:

Year(Date)

 

[PatAccess]

Hi. You can get the current year by using the expression:

Year(Date)

Ok but how can I get my filepath to read SmithJohn2021.pdf if the last 4 characters represent a year. I mean like

strFilePath = strFolderPath & "\States\" & Me.cboState.Column(0) & "\" & Me.cboName.Column(2) & Me.cboName.Column(3) & ".pdf"

what would come after Me.cboName.Column(3) &
if I declare the year as dim intYr As Integer,for instance?

 

[theDBguy]

Ok but how can I get my filepath to read SmithJohn2021.pdf if the last 4 characters represent a year. I mean like

strFilePath = strFolderPath & "\States\" & Me.cboState.Column(0) & "\" & Me.cboName.Column(2) & Me.cboName.Column(3) & ".pdf"

what would come after Me.cboName.Column(3) &
if I declare the year as dim intYr As Integer,for instance?

You could try:

strFilePath = strFolderPath & "\States\" & Me.cboState.Column(0) & "\" & Me.cboName.Column(2) & Me.cboName.Column(3) & Year(Date) & ".pdf"

 

[PatAccess]

You could try:

strFilePath = strFolderPath & "\States\" & Me.cboState.Column(0) & "\" & Me.cboName.Column(2) & Me.cboName.Column(3) & Year(Date) & ".pdf"

I did but if the document is SmithJohn2022.pdf it does not read it because we are in 2021 so how do I tell it to read any year?

 

[theDBguy]

I did but if the document is SmithJohn2022.pdf it does not read it because we are in 2021 so how do I tell it to read any year?

How exactly do you "read" the file? If you're using Dir() you can use wildcard characters.

 

[bastanu]

Dim sCurrentYear as string, sFileYear as string
strFilePath = strFolderPath & "\States\" & Me.cboState.Column(0) & "\" & Me.cboName.Column(2) & Me.cboName.Column(3) & Year(Date) & ".pdf"
sCurrentYear=Year(Date)
sFileYear=replace(right(sFilePath,8),".pdf","")
If sCurrentYear<>sFileYear Then
'do something
else
do something else
end if

 

[PatAccess]

How exactly do you "read" the file? If you're using Dir() you can use wildcard characters.

I have a pre-defined function here is the whole thing

Private Function FileExists(ByVal path_ As String) As Boolean
    'Function to check if a file exist or not
    FileExists = (Len(Dir(path_)) > 0)
End Function

Private Sub cboState_AfterUpdate()
    
    'Create the FilePath to look for
    Dim strFolderPath As String
    Dim strFilePath As String
    
    'The Folder resides in the same directory as the Database
    strFolderPath = CurrentProject.Path
    strFilePath = strFolderPath & "\States\" & Me.cboState.Column(0) & "\" & Me.cboName.Column(2) & Me.cboName.Column(3) & Year(Date) & ".pdf"
    
    'Then if it exist show the button
    If FileExists(strFilePath) Then
        Me.cmdCertificate.Visible = True
    Else
        Me.cmdCertificate.Visible = False
        MsgBox "No certificate Found!", vbCritical
    End If
End Sub

How do I correctly declare the following?
Dim intYear as Integer
intYear = ????

 

[bastanu]

To extract the year from the strFIlePath variable use intYear=Cint(replace(right(sFilePath,8),".pdf",""))

 

[theDBguy]

I have a pre-defined function here is the whole thing

Private Function FileExists(ByVal path_ As String) As Boolean
    'Function to check if a file exist or not
    FileExists = (Len(Dir(path_)) > 0)
End Function

Private Sub cboState_AfterUpdate()
  
    'Create the FilePath to look for
    Dim strFolderPath As String
    Dim strFilePath As String
  
    'The Folder resides in the same directory as the Database
    strFolderPath = CurrentProject.Path
    strFilePath = strFolderPath & "\States\" & Me.cboState.Column(0) & "\" & Me.cboName.Column(2) & Me.cboName.Column(3) & Year(Date) & ".pdf"
  
    'Then if it exist show the button
    If FileExists(strFilePath) Then
        Me.cmdCertificate.Visible = True
    Else
        Me.cmdCertificate.Visible = False
        MsgBox "No certificate Found!", vbCritical
    End If
End Sub

How do I correctly declare the following?
Dim intYear as Integer
intYear = ????

Okay, I get the impression the files were created using a different process where you add the year at the end of the file name. Is that correct? If so, where is that information for the year to add in the file name comes from?

 

[PatAccess]

To extract the year from the strFIlePath variable use intYear=Cint(replace(right(sFilePath,8),".pdf",""))

Thank you for this.
Does access VBA have a way to declare a variable and tell it to be only 4 digits? I tried

Dim intY as Integer
'Needs it to be any 4 digits
intY = ####

But it is not working....What does Dim intY as Integer = 4 do?

 

[PatAccess]

Okay, I get the impression the files were created using a different process where you add the year at the end of the file name. Is that correct? If so, where is that information for the year to add in the file name comes from?

Yes when the file is saved within the folder, it should have be LastnameFirstnameYYYY.pdf so those 4 numbers are already there so I want it to declare a variable whose value is any 4 digits like

Dim intY as Integer
'Needs it to be any 4 digits
intY = ####

 

[theDBguy]

Yes when the file is saved within the folder, it should have be LastnameFirstnameYYYY.pdf so those 4 numbers are already there so I want it to declare a variable whose value is any 4 digits like

Dim intY as Integer
'Needs it to be any 4 digits
intY = ####

How about?

Dim intY AS Integer

intY = 2022

strFilePath = strFolderPath & "\States\" & Me.cboState.Column(0) & "\" & Me.cboName.Column(2) & Me.cboName.Column(3) & IntY & ".pdf"

 

[theDBguy]

How about?

Dim intY AS Integer

intY = 2022

strFilePath = strFolderPath & "\States\" & Me.cboState.Column(0) & "\" & Me.cboName.Column(2) & Me.cboName.Column(3) & IntY & ".pdf"

If you wanted to provide the year at execution time, you could try something like:

intY = InputBox("Enter Year")

 

[PatAccess]

To extract the year from the strFIlePath variable use intYear=Cint(replace(right(sFilePath,8),".pdf",""))

Ok I have working on this for hours and finally just used your suggesting. Here is what I have

Private Sub cmdCertificate_Click()
    'Create the FilePath to look for
    Dim strFolderPath As String
    Dim strFilePath As String
    Dim intYear As Integer
    
    'The Folder resides in the same directory as the Database
    'strFolderPath = CurrentProject.Path
    'strFilePath = strFolderPath & "\States\" & Me.cboState.Column(0) & "\" & Me.cboName.Column(2) & Me.cboName.Column(3) & Year(Date) & ".pdf"
    
    strFolderPath = CurrentProject.Path
    strFilePath = strFolderPath & "\States\" & Me.cboState.Column(0) & "\" & Me.cboName.Column(2) & Me.cboName.Column(3) & Year(Date) & ".pdf"
    
    
    Dim sCurrentYear As Integer, sFileYear As Integer
    sCurrentYear = Year(Date)
    sFileYear = CInt(Replace(Right(strFilePath, 8), ".pdf", ""))
    If sCurrentYear <> sFileYear Then
        strFilePath = strFolderPath & "\States\" & Me.cboState.Column(0) & "\" & Me.cboName.Column(2) & Me.cboName.Column(3) & sFileYear & ".pdf"
    Else
        strFilePath = strFolderPath & "\States\" & Me.cboState.Column(0) & "\" & Me.cboName.Column(2) & Me.cboName.Column(3) & sCurrentYear & ".pdf"
    End If
 
        'Then if it exist show the button
    If FileExists(strFilePath) Then
        Application.FollowHyperlink strFilePath
    Else
        MsgBox "No certificate Found!", vbCritical
    End If
    

End Sub

However, The problem is that I need to have a way to read the 4 digits year within the file name before I assign it to sFileYear which is my problem. I just stepped through the program and by the time I get to sCurrentYear and sFileYear, they are both 2021 so it is not then my function FileExists(strFilePath) comes back false because the year is different. What is the best way to initialize sFileYear with a value that is a 4-digits number?
So I tried to use wildcards like

Dim intYear as Integer
intYear = 2###

but of course that did not work.
How do I initialize a variable to say intYear = any 4 digits? Then I would go to my if statement later

 

[theDBguy]

Did you try my suggestion in Post #14?

Also, you haven't answered my question in Post #10.

Where did the year info in the file name originally came from? How was it assigned to the file name in the first place?

 

[PatAccess]

Did you try my suggestion in Post #14?

Also, you haven't answered my question in Post #10.

Where did the year info in the file name originally came from? How was it assigned to the file name in the first place?

From #10 - Yes the year is added at the end of the document file name, which is the year the specific document expires. So that year is manually keyed with the file is save so the path is for example "\States\California\SmithJohn2022.pdf" so when I create the filePath, I say match LastnameFirstname????.pdf?

From #14, I tried entering the date at execution but it does not work for what I need. I need a variable that takes any four digits because the years go from 2021 to 2025 or how do I initialize the intYear variable to say intYear BETWEEN 2021 AND 2025, for instance?

 

[bastanu]

I believe I finally understand what you what, wouldn't this work:

strFilePath = strFolderPath & "\States\" & Me.cboState.Column(0) & "\" & Me.cboName.Column(2) & Me.cboName.Column(3) 'stop here'& Year(Date) & ".pdf"
 
    'Then if it exist show the button
    If FileExists(strFilePath & "????.pdf") Then
        Me.cmdCertificate.Visible = True
    Else
        Me.cmdCertificate.Visible = False
        MsgBox "No certificate Found!", vbCritical
    End If

Cheers,

 

[PatAccess]

I believe I finally understand what you what, wouldn't this work:

strFilePath = strFolderPath & "\States\" & Me.cboState.Column(0) & "\" & Me.cboName.Column(2) & Me.cboName.Column(3) 'stop here'& Year(Date) & ".pdf"

    'Then if it exist show the button
    If FileExists(strFilePath & "????.pdf") Then
        Me.cmdCertificate.Visible = True
    Else
        Me.cmdCertificate.Visible = False
        MsgBox "No certificate Found!", vbCritical
    End If

Cheers,

Thank you, I tried that but it not work. The file path reads with literal ???? and not as a wildcard. I've also tried to bring it out of the quote marks but nothing. HOWEVERY, I just used your last suggestion and got a work around for it to work. Here is the final

Private Sub cmdCertificate_Click()
    'Create the FilePath to look for
    Dim strFolderPath As String
    Dim strFilePath As String
    Dim currentYear As Integer, fileYear As Integer
    
    currentYear = Year(Date)
    
    '----Extract the date from this record
    Dim strSQL As String
    strSQL = "SELECT DateExpires FROM QryLicensedPE WHERE [Employee]='" & Me.cboName.Column(0) & "' AND [State]='" & Me.cboState.Column(0) & "'"
    Dim intExpYear As Integer
    Dim d As Date
    d = CurrentDb.OpenRecordset(strSQL).Fields(0).Value
    intExpYear = Year(d)
    'Debug.Print intExpYear
    
    'The Folder resides in the same directory as the Database
    strFolderPath = CurrentProject.Path
    If currentYear <> intExpYear Then
        strFilePath = strFolderPath & "\States\" & Me.cboState.Column(0) & "\" & Me.cboName.Column(2) & Me.cboName.Column(3) & intExpYear & ".pdf"
        'Debug.Print strFilePath
    Else
        strFilePath = strFolderPath & "\States\" & Me.cboState.Column(0) & "\" & Me.cboName.Column(2) & Me.cboName.Column(3) & currentYear & ".pdf"
    End If
    
    'Then if it exist show the button
    If FileExists(strFilePath) Then
        'Extract that year from the file path
        fileYear = CInt(Replace(Right(strFilePath, 8), ".pdf", ""))
        If fileYear <= 2021 Then
            MsgBox "This certificate expires within this current year please double check"
        End If
        
        Application.FollowHyperlink strFilePath
    Else
        MsgBox "A certificate for the current year was not Found!", vbCritical
    End If
    
End Sub

Thank you for your help guys

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Good luck with your project.