Results 1 to 9 of 9
  1. #1
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100

    How to use dates in if statements?

    Hi all - the code below is what I've put together with web examples (1st vba code, i'm sure there is room for improvement). The second Elseif is where I need help.



    Code:
     
    Function Test(Role As String, SSN as String, Act_Open as Date, Close_Date as Date) As String
    
    If Role = "Customer" Then
        Test = "Test Test Test"
    
    'ElseIf Role = "DET" And SSN <> "999999999" And Act_Open < #5/24/2019# And Close_Date < #6/18/2021# Then
       ' Test = “Further Review Req.”
    End If   
    
    End Function
    When I run the code, nothing happens even though I know the scenario in the if statement did occur. I don't see where my mistake is, could anyone please give me any pointers?
    Last edited by MsAxes; 01-21-2023 at 10:49 AM.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Walk through your code line by line. Use breakpoints to examine the variable values.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Just curious what is the data type for the SSN; if it is a number (long integer) then that is your culprit, you should have SSN <>999999999 (without the double-quotes).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100
    Let me give that try - question.

    In the online example, I saw that inside the parenthesis they identified the strings, dates etc. then outside the parenthesis there is 'as String'. Does that final 'as string' make everything inside the parenthesis a string? not sure what that does. Also, everything inside the parenthesis should be labeled as it is in the table correct? meaning, a field that is a string should be a string, a date should be a date?

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    The function as you have it would return a string, but if its arguments are wrong (meaning the SSN is a number but you pass it as a string) then the result will be incorrect (= nothing)
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100
    The change worked, thank you.

    So should I have that “as String” outside the function? Doesn’t appear that it will do anything for what I’m doing.

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by MsAxes View Post
    The change worked, thank you.

    So should I have that “as String” outside the function? Doesn’t appear that it will do anything for what I’m doing.
    I would say yes, as you are returning a string?
    Even if it is not 'technically' needed, it does show what is being returned quickly, rather than having to go through code. Functions can be quite complicated and long.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Does that final 'as string' make everything inside the parenthesis a string?
    No, it defines the data type of the value returned by the function as a string. If you leave it off, it is as WGM says, open to interpretation. If you leave that off, IIRC the data type returned is determined by Access, so not the best approach to take.

    Also, everything inside the parenthesis should be labeled as it is in the table correct? meaning, a field that is a string should be a string, a date should be a date?
    Not always. If your field might contain nulls then the function will usually raise an error when it encounters them. In that case, you'd need to define the variable in the argument as a Variant. Also, you can pass objects to functions, so the variable type in those cases has nothing to do with table fields. Then there is the availability to pass user defined types to a function, which likely would never be the type of a table field.

    If you're thinking that the return type should match the table field in cases where you intend to enter the returned value in a table, then I think "no" again (where Decimal is concerned). AFAIK, Access still does not accept Decimal as a return type.
    Last edited by Micron; 01-21-2023 at 02:11 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100
    Thank you all for your explanations.

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

Similar Threads

  1. Replies: 21
    Last Post: 06-23-2020, 11:45 AM
  2. Replies: 2
    Last Post: 09-19-2017, 11:31 AM
  3. Replies: 8
    Last Post: 12-02-2013, 03:46 PM
  4. Comparing Dates in nested IIF statements
    By Purdue_Engineer in forum Programming
    Replies: 6
    Last Post: 10-01-2012, 12:36 PM
  5. Query input dates for range of dates in two fields
    By kagoodwin13 in forum Queries
    Replies: 3
    Last Post: 02-13-2012, 04:25 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
  •  
Other Forums: Microsoft Office Forums