DateSerial Function in VBA for PCOMM Emulator

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
97
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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:
Upvote 0
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:
Upvote 0
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#
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
Glad to hear. Appreciate the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top