Extract date to odd number string

Egg 'n' Bacon

Registered User.
Local time
Today, 16:56
Joined
Oct 19, 2006
Messages
14
I'm trying to change a standard date value into the date format used by our AS400 system (this is year - 1900 & mm & dd e.g. 1st Jan this year = 1060101). I'm using 'Datepart' but I'm running into a couple of problems;

Datepart doesn't give leading zeros, that I need & mid seems to do the same
Concatenating the results sometimes gives overflow.
Here's the code I'm trying to get to work;

Dim intEIStest As Integer
Dim sngEISyr As Single
Dim sngEISmnth As Single
Dim sngEISday As Single

sngEISyr = (DatePart("yyyy", txtDateFrom) - 1900)
sngEISmnth = DatePart("m", txtDateFrom)
sngEISday = DatePart("d", txtDateFrom)
intEIStest = sngEISyr & sngEISmnth & sngEISday

txtTest.Value = intEIStest


I've only just started using VBA so there's likely to be a lot I won't understand. However, I do want to learn.
 
because you haven't got a date that looks like a date, (presumably you are using a year 2k solution), you can't use date functions like datepart. Your date will either be treated as a number or text by your system. If you treat it as text, you will have to slice your string by using functions mid etc, to break out the day, month and year, and reassemble them to look like a date string. Then you can assign it to a date variable.
 
Cheers Gemma, I did try a couple of ideas, med, right etc, but the problem was more basic; I should've declared the values as LONG!
 

Users who are viewing this thread

Back
Top Bottom