Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: DateSerial Function in VBA for PCOMM Emulator

  1. #1
    New Member
    Join Date
    Apr 2016
    Location
    Miami, Florida
    Posts
    41
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default DateSerial Function in VBA for PCOMM Emulator

    I am building a macro to change values in our IBM AS400.

    I need my macro to evaluate each line and determine if the date for that line satisfies a criteria.
    My dates are formatted as such: 01APR19
    These value are on row 7 starting with position 51 (the 0) and ending with position 57 (the 9)

    I have the date range that the AS400 value needs to satisfy in an Access Table.

    I am trying say "If the date in AS400 is less than or equal to the End Date in my table, do..."

    My code is as follows

    Code:
    Dim sdmonth as Integer
    If PS.GetText(7, 53, 3) = "APR" Then sdmonth = 4
    If Date(Serial(PS.GetText(14, 56, 2), sdmonth, PS.GetText(14, 51, 2) <= Fields.("End_Date") then....
    What I am trying to achieve is a DateSerial that utilizes the 19 for the year, the 4 for the month and the 01 for the day.
    However this is not working.
    Any help would be appreciated.
    Mike

  2. #2
    New Member
    Join Date
    Apr 2016
    Location
    Miami, Florida
    Posts
    41
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: DateSerial Function in VBA for PCOMM Emulator

    Edit to the above: I know I have a typo: It should be DateSerial() not Date(Serial(). It is correct in the code I'm working on.

    What I would prefer to do, although it is more cumbersome, is have the month portion of the DateSerial function evaluate the value on each line.

    Something like

    Code:
     
    
    DateSerial(PS.GetText(14, 56, 2), if(PS.GetText(7, 53, 3) = "APR" then 4, PS.GetText(7, 51, 2)
    But I can't get the month portion to work.
    Last edited by MCTampa; Apr 11th, 2019 at 04:53 PM.

  3. #3
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,300
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: DateSerial Function in VBA for PCOMM Emulator

    Hi,
    I think you should probably post more than half of the vba statements. As written, you have an IF ... but no idea whether it's good, bad, or indifferent. Also, would need to know what PS.GetText() does (probably a substring function? - is that the case?). Also would probably help to have some sample data. Also not sure what you mean by end end date when you are only checking for a single month. Is the end date "Apr" or is it a date in april? Does it matter what year it is? Why not just use a string search function since its a substring in a string?
    Last edited by xenou; Apr 12th, 2019 at 02:33 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  4. #4
    Board Regular JonXL's Avatar
    Join Date
    Feb 2018
    Posts
    189
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: DateSerial Function in VBA for PCOMM Emulator

    Quote Originally Posted by xenou View Post
    Hi,
    I think you should probably post more than half of the vba statements. As written, you have an IF ... but no idea whether it's good, bad, or indifferent. Also, would need to know what PS.GetText() does (probably a substring function? - is that the case?). Also would probably help to have some sample data. Also not sure what you mean by end end date when you are only checking for a single month. Is the end date "Apr" or is it a date in april? Does it matter what year it is? Why not just use a string search function since its a substring in a string?
    I think PS.GetText is the function pulling text off the AS400 screen (row, column, length).

    @MCTampa I don't understand the use of DateSerial.

    I use DateValue or ## when I need to convert text to a date.

    I just checked and confirmed that VBA has no problem returning the correct response to each of these checks (False, True):

    Code:
     
    ?#04-Apr-19# > #4/5/19#
    ?#04-Apr-19# > #4/3/19#

  5. #5
    Board Regular JonXL's Avatar
    Join Date
    Feb 2018
    Posts
    189
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: DateSerial Function in VBA for PCOMM Emulator

    Quote Originally Posted by JonXL View Post
    I just checked and confirmed that VBA has no problem returning the correct response to each of these checks (False, True):

    Code:
     
    ?#04-Apr-19# > #4/5/19#
    ?#04-Apr-19# > #4/3/19#
    I should add that if you are using this with your code, you'd need DateValue. Something like:

    Code:
     
    DateValue([AS400 Day] & "-" & [AS400 Month] & "-" & [AS400 Year])
    Where the [AS400 ...] represents the text extracted from the screen for that particular piece of the date.

    Note also that with this approach you don't need to convert the month to a number but can use the word format ("Jan", "Feb", etc.) as is.
    Last edited by JonXL; Apr 13th, 2019 at 09:36 AM.

  6. #6
    New Member
    Join Date
    Apr 2016
    Location
    Miami, Florida
    Posts
    41
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: DateSerial Function in VBA for PCOMM Emulator

    Quote Originally Posted by JonXL View Post
    I should add that if you are using this with your code, you'd need DateValue. Something like:

    Code:
     
    DateValue([AS400 Day] & "-" & [AS400 Month] & "-" & [AS400 Year])
    Where the [AS400 ...] represents the text extracted from the screen for that particular piece of the date.

    Note also that with this approach you don't need to convert the month to a number but can use the word format ("Jan", "Feb", etc.) as is.
    This worked perfectly! Thank you!
    I was using DateSerial because I had tried DateValue and couldn't get it to work, but your approach is much cleaner.

  7. #7
    Board Regular JonXL's Avatar
    Join Date
    Feb 2018
    Posts
    189
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: DateSerial Function in VBA for PCOMM Emulator

    Glad to hear. Appreciate the feedback.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •