?
Solved

FormatNumber

Posted on 2004-08-09
5
Medium Priority
?
2,590 Views
Last Modified: 2012-06-27
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
Comment
Question by:romaniup
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 18

Accepted Solution

by:
bonjour-aut earned 1200 total points
ID: 11752352
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
 
LVL 16

Assisted Solution

by:Nestorio
Nestorio earned 400 total points
ID: 11752370
Try:

Format(output2, "MM/DD/YYYY")
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 11752374
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
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 11752455
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 400 total points
ID: 11752517


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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question