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
Solved

FormatNumber

Posted on 2004-08-09
5
2,576 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
  • 3
5 Comments
 
LVL 18

Accepted Solution

by:
bonjour-aut earned 300 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 100 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 100 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

839 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