DateSerial Function in VBA for PCOMM Emulator

[MCTampa]

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

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

 

[MCTampa]

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

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.

 

[xenou]

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?

 

[JonXL]

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):

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

 

[JonXL]

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

?#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:

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.

 

[MCTampa]

I should add that if you are using this with your code, you'd need DateValue. Something like:

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.

 

[JonXL]

Glad to hear. Appreciate the feedback.