• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2595
  • Last Modified:

FormatNumber

Currently I am working on a project where I want the user type in a date in a text box. When the user types in that date I want another text box to display the end date of that month. So if someone types in 1/1/2004 in one text box the other text box would display 01/31/2004. So basically I will be adding 30/31 days to a starting date. I have written some code like this before in VB which is as follows:

Private Sub Text37_Change()
    Dim input3 As Date
    Dim output2 As Date
    input3 = Val(Text37.Text)
   output2 = input3 + 30
   Text39.Text = FormatNumber(output2, 2)
End Sub

Problem is when I use it in Access it tells me that FormatNumber procedure/function needs to be referenced. Any Idea what procedure should I refer to use this?
Or any other way I could solve the above problem?

Thank you so much in advance.
0
romaniup
Asked:
romaniup
  • 3
3 Solutions
 
bonjour-autCommented:
the problem is, that the _Change event fires at the first keystroke

use the _AfterUpdate event

further:

better method should be:

output2 = DateSerial(Year(Me.input3),Month(input3)+1,1)-1

(ouput 2 contol format should be some date format)

Regards, Franz

 
0
 
NestorioCommented:
Try:

Format(output2, "MM/DD/YYYY")
0
 
bonjour-autCommented:
so it should read

Private Sub Tet37_AfterUpdate()
  Me.Text39 = DateSerial(Year(Me.input3),Month(input3)+1,1)-1
End Sub

(Text39 set to some dateformat)

Regards, franz
0
 
bonjour-autCommented:
sorry typo

so it should read

Private Sub Text37_AfterUpdate()
  Me.Text39 = DateSerial(Year(Me.Text37),Month(Me.Text37)+1,1)-1
End Sub

(Text39 set to some dateformat)
this takes the first day of the following month and deducts one day

Regards, Franz

0
 
Rey Obrero (Capricorn1)Commented:


try this

Private Sub Text37_AfterUpdate()

Me.Text39 = DateSerial(Year(Text37), Month(Text37) + 1, 0)  ' gives you the last day of the month

End Sub
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now