Preceed with zero and Concatenate

Posted by BeverleyB on November 08, 2001 7:16 AM

Hi Guys,

Could anyone check this code and see if you could assist me please. (Joe Chin provided it and I just had to make one correction)

private function convertdate(intmonth as integer,intyear as integer) as string
dim strnewdate asstring *4
dim intnewmonth as integer
dim strnewmonth as string *2
dim intnewyear as integer
dim strnewyear as string *2
dim intnewdate as integer

if intmonth >10 then
intnewmonth=intmonth +3
intnewyear=intyear
else
intnewmonth=intmonth -9
intnewyear=intyear+1
end if

strnewmonth=cstr(intnewmonth)
strnewyear=cstr(intnewyear)
strnewdate=strnewyear & strnewmonth
convertdate=strnewdate
end sub
Private sub commandbutton1_Click()
txtyearmonth.text=convertdate(txtmonth.text,txtyear.text)
end sub

Everything works well up to the point where the data goes into txtyearmonth. If the month is less than 9 it has to be preceeded with a zero but i am at a loss as how to get this done for both the month and year.

The data also has spaces separating the month and the year and they should be 4 numbers. If the date is 30/09/01 my data should be 0112 but it looks like 1 12, can anyone offer any assistance?

Thanks

Bev

Posted by Barrie Davidson on November 08, 2001 7:49 AM

Bev, try changing these lines:

strnewmonth=cstr(intnewmonth)
strnewyear=cstr(intnewyear)

to:

strnewmonth = Format(intnewmonth, "00")
strnewyear = Format(intnewyear, "00")

I think this will do the trick.

Regards,
BarrieBarrie Davidson

Posted by BeverleyB on November 08, 2001 9:36 AM

Re: Preceed with zero - Worked Splendidly but now another problem

Thanks Barrie,

It worked but now another problem has popped up.
Since I am not entering data into this textbox manually, how do I get the information from the form to the spreadsheet. The textbox is called TxtYearMonth. Take a look at this code and let me know what I am doing wrong.

Private sub cmd2_click()
dim lastrow as object
set lasttow=sheet1.range("a65536").end(xlup)
lastrow.offset.value=polno.text
lastrow.offset.value=fefno.text
lastow.offset.value=txtyearmonth (Do I include this)

msgbox "one record added"
response=want to add another
if response=vbyes then
polno.text=""
refno.text=""
txtyearmonth="" (Do i need this?)

I included them but when I add the record no data is shown for txtyearmonth column. Any idea why???
Is it because I am not entering the data manually?

Bev

Posted by Barrie Davidson on November 08, 2001 10:54 AM

Re: Preceed with zero - Worked Splendidly but now another problem

Okay, not really knowing what you are doing with this code, here's what I came up with.

Private Sub cmd2_click()
Dim lastrow As Range
Set lastrow = Sheets("Sheet1").Range("a65536").End(xlUp)
lastrow.Offset(0, 3).Value = polno.Value
lastrow.Offset(0, 4).Value = fefno.Value
lastow.Offset(0, 2).Value = txtyearmonth.Value

If MsgBox("one record added, want to add another", vbQuestion + vbYesNo) = vbYes Then
polno.Text = ""
refno.Text = ""
txtyearmonth.Value = ""
End If
End Sub

You'll need to change the offset lines to the appropriate numbers. Right now, the value from your textbox "txtyearmonth" will go in column C, the value from "polno" will go in column E, and the value from "fefno" will go in column D (I assumed that "polno" and "fefno" are also text boxes).

One note of caution, I didn't test this code.

BarrieBarrie Davidson

Posted by BeverleyB on November 09, 2001 4:31 AM

Re: Preceed with zero - Problem Solved - Thanks a lot Barrie!!!

All is well now!!
Thanks