Date comparisons on data entry

I would like to compare two date fields on a form at the point that the 
second date is entered rather than wait until the user has entered all the 
fields and pressed the save button.
I have tried doing the comparison using On Change but the VB code sees the 
second field as NULL (when the second date is first entered) and throws a 
debug error.
Is there a way of coding this? I am using Access 2007
0
Utf
12/3/2009 12:16:01 PM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
746 Views

Similar Articles

[PageSpeed] 3

hi Tommo,

Tommo wrote:
> I would like to compare two date fields on a form at the point that the 
> second date is entered rather than wait until the user has entered all the 
> fields and pressed the save button.
> I have tried doing the comparison using On Change but the VB code sees the 
> second field as NULL (when the second date is first entered) and throws a 
> debug error.
You haven't posted any code, so I've to guess. You have sub like this:

Private Sub txtDateInput_OnChange()

   Dim myDate As Date

   myDate = txtDateInput
   ' or
   ' myDate = txtDateInput.Value
   ' or
   ' myDate = Me![DateField]

End Sub

In all cases the problem is, that the value (!) is changed later. To 
test it on the fly, you need to read instead of the value the Text property:


Private Sub txtDateInput_OnChange()

   Dim myDate As Date

   If IsDate(txtDateInput.Text) Then
     myDate = CDate(txtDateInput.Text)
     ' use it here.
   End If

End Sub



mfG
--> stefan <--
0
Stefan
12/3/2009 12:52:35 PM
Stefan,
I tried looking in three large Access manuals but couldn't find anything to 
help me.
I tried your code and my form now works. Thank you very much.

Tommo

"Stefan Hoffmann" wrote:

> hi Tommo,
> 
> Tommo wrote:
> > I would like to compare two date fields on a form at the point that the 
> > second date is entered rather than wait until the user has entered all the 
> > fields and pressed the save button.
> > I have tried doing the comparison using On Change but the VB code sees the 
> > second field as NULL (when the second date is first entered) and throws a 
> > debug error.
> You haven't posted any code, so I've to guess. You have sub like this:
> 
> Private Sub txtDateInput_OnChange()
> 
>    Dim myDate As Date
> 
>    myDate = txtDateInput
>    ' or
>    ' myDate = txtDateInput.Value
>    ' or
>    ' myDate = Me![DateField]
> 
> End Sub
> 
> In all cases the problem is, that the value (!) is changed later. To 
> test it on the fly, you need to read instead of the value the Text property:
> 
> 
> Private Sub txtDateInput_OnChange()
> 
>    Dim myDate As Date
> 
>    If IsDate(txtDateInput.Text) Then
>      myDate = CDate(txtDateInput.Text)
>      ' use it here.
>    End If
> 
> End Sub
> 
> 
> 
> mfG
> --> stefan <--
> .
> 
0
Utf
12/3/2009 4:07:02 PM
Reply:

Similar Artilces:

Publisher catelog merge- cannot open data source file
whether I try to open the source file or open a new one, I get the message: operation cannot be completed becasue of dialog or database engine failures. Please try again later. Troubleshooting advice suggestd to undate the engine from Windows update - can't find it. Does anyone have more specific troubleshooting advice? This article has a solution: You receive a "The operation cannot be completed because of dialog or data base engine failures" error message when you try to perform a mail merge by using an Outlook 2003 contact folder in Publisher 2003 http://support.mic...

Numerical entry for list boxes on a user form
I have a user form with 3 text boxes that inputs information onto a worksheet. What code do I need to make sure the textboxes only accept numerical entry. ie I only want a user to enter numbers, not letters ....and would I put the code inbetween this section: - Private Sub TextBox2_Change() End Sub TIA See reply from Nicke to your question in .programming, and don't multipost. -- HTH RP "Anthony Slater" <AnthonySlater@discussions.microsoft.com> wrote in message news:A87CD04E-5B58-46C4-B936-6B0433FCE8A6@microsoft.com... > I have a user form with 3 text boxes t...

Data Migration manager setup fails.
I'm trying to instaal DMM on an XP machine. Everything goes fine until the installation is stopped. The message in my logfiles is: EVENT-Id:17188 MSCRMPERFCOUNTER. Sorry the rest is in Dutcht: Kan beschrijving voor gebeurtenis-ID (17188) in bron (MSCRMPerfCounters) niet vinden. De lokale computer beschikt wellicht niet over de benodigde registergegevens of DLL-berichtbestanden om berichten van een externe computer te kunnen weergeven. U kunt mogelijk de schakeloptie /AUXSOURCE= gebruiken om deze beschrijving op te halen, zie Help en ondersteuning voor details. De volgende gegevens z...

Cutting a line in a line chart when data series stops
I have a series like so: Jan 10 Feb 10 Mar 11 Apr 12 May Jun Jul And so on - the rest of the year is blank. When I draw a line chart for the full 12 months the series draws a line up to April, then plummets to zero in May. I want the line to just stop at April. If I use a series as above and genuine empty cells for the end of the year this ok, but since my May cell reads =IF($A11<=$D$17,G11,"") the "" does not seem to get recognised as a blank cell. Please help!! Change your formula to =IF($A11<=$D$17,G11,NA()) -- David Biddulph "DannyS" <Da...

Money 2007 -- QIF import
Good Evening! After a little trial and error, I am able to import account transactions from another system (not a bank statement, my actual register transactions) with little trouble except for the date format. (Before someone gets mad at me, I really don't think this has anything to do with my regional settings!) As recommended by an MVP from this forum, I open my CSV file with excel, then use a converter (add-in) called XL2QIF to create a qif output file. The actual "data" in a date field in my original csv file is "060106" (mm/dd/yy) which is June 1, 2006,...

Why does AutoFilter not show all the data in the pull down?
In an Excel spreadsheet with 12,000 lines, and autofilter on, all the possible selections are not displayed in the filter pull down. I can use custom filter in the pull down, but is more time consuming. I'd rather scroll in the pull down. Is there a limit on the amount of lines shown in the pull down? Or am I missing something? Go to Excel help and enter limits. Excel has dozens of limits. I was surprised not to find a limit on Autofilter which is 1000. Most irritating is only 1024 characters can display in a cell and only 256 characters can be copied to another worksheet. ...

Link a picture to data
When I select a group of cells and copy/paste as picture to another worksheet (in the same workbook), I want to have the picture linked to the source data so that the picture changes when the source data changes. I am pretty sure I have done this before, but cannot remember how. Thanks. ...

Remove All Bill Data
When I first upgraded to Money Plus, I was having performance issues. Nuking the bills to eliminate the multiple schemas that had built up since Money 1.0 seemed reasonable, so I performed the operation and rebuilt them. However, here I am, 4-5 months later, unable to balance my primary checking account without having Money hang and need to be killed twice during the process. I can't enter transactions that match scheduled bills without having Money hang and need to be killed. So, I nuked the bills again and started over with rebuilding them. Problem solved. Rather than cleaning up th...

prevent Excel from EVER automatically converting numbers to dates
in Excel v. X for Mac how do I permanently prevent Excel from EVER automatically converting numbers to dates. Formatting individual cells as text will not work in my applications. I need to disable this funtion. Not possible - there's no preference option to turn this off. Instead of preformatting as text, entries can be made with an apostrophe as a prefix: '3/4 You might be able to use an event macro to change it back retroactively, but that depends on knowing what format the user entered it. For instance, 3/4, 3-4, 4Mar, mar4, etc will all be interpreted as 4 Marc...

Data From A List
WHat I am trying to do is Type in a name of a Client in a cell and have the adjacent cell automatically enter a specific client number.... For example.... if on cell A2 I type in "Smith" I want A3 to read "100". I have a list of names and client numbers..... I have tried LookUp... but to no avail. Any help? Thanks Well, you need to show us your formula, for starters. Tyro "Steve" <Steve@discussions.microsoft.com> wrote in message news:E977F61D-B06C-4E81-9D1F-83094823B1F1@microsoft.com... > WHat I am trying to do is Type in a name of a Client in ...

Filtering data to different worksheets
Hi all, I'm sue this should be really simple to do but I'm struggling to get i to work. I have a list of account numbers and who has keyed each one on. I want to filter down the list so that I have a seperate worksheet fo each person with all of the account numbers (plus other columns of inf ie. address, phone number) that they have done. I've tried vlookup but that, obviously, only looks up the 1st accoun number. Can anyone point out what I'm missing? Thank -- jappe ----------------------------------------------------------------------- japper's Profile: http://www...

How can I data sort to more than 3 levels
Is there a technique or add-in utility that will let me do a data sort in Excel to 4 or more levels at once. Currently you can only do 3 levels, and then you have to separate the data to do more. Thanks for any help -- Pam LaDue ------------------------------------------------------------------------ Pam LaDue's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36773 View this thread: http://www.excelforum.com/showthread.php?threadid=564880 You have replies at your other post. Pam LaDue wrote: > > Is there a technique or add-in utility that will let me d...

Office 2004 German release date
Hey All, could someone inform me when the release date for the german Office 2004 = is? Although microsoft's mactopia site states that this was sometime = back in=20 July, on the german apple store one cannot yet purchase Office 2004?!? This is a bit confusing, especially because I was hoping to take = advantage of=20 the 'free' upgrade from Office X purchased at the beginning of this year = -=20 only catch being that this offer is time-limited to one month after the=20 release date. Any info much welcome! Thanks silvretta ...

Changing data orientation from one sheet to another
Hi, On sheet1 the data is oriented vertically as A1 A2 A3 I'd like on sheet2 the data be oriented horizontally A1 to A1 A2 to B1 A3 to B3 Can it be done? if so, how? TIA Olga Excell 07 Copy the data from sheet1 Select A1 of sheet2 and paste special>transpose. Note: cannot be done if you have more than 16384 rows of data in column A Also, I think you have a typo at A3 to B3.........maybe A3 to C1? Gord Dibben MS Excel MVP On Sun, 20 Apr 2008 19:55:12 +0200, "Olga" <noemail@hotmail.com> wrote: >Hi, >On sheet1 the data is oriented vertically as >A1 >A...

data-set too big for Excel
I am using Excel to extract data from a database, but when the extracted data is too extensive, Excel is stuck. Is there any way to expand the data-holding capacity of Excel? I am using Excel 2000. Any help would be appreciated. ...

Creating additional panes of data in CRM 1.2
When we select a Contact, I want to be able to bring up both the History and Activities associated with the contact. I'd like to create a 'window pane' on the right side and the bottom that displays this information - the question is can I do this without having to create a completely new ASP form? Thanks! David David, what you want is not currently possible with the out-of-the-box CRM. You have two options, build it yourself using the SDK, or purchase the functionality from an ISV that has already built it (www.salentica.com , www.c360.com). Dave <anonymous@discuss...

Representing times in excel without dates
Excel experts I need your help!! I am using Office XP excel and am trying to represent time in the format: AM/PM without seconds. When I put in the time [I have the cell number format set to time/"1:30PM"/Locale is English (United States)], it comes up with the date preceding the time. I have tried varying settings of time but they all come up with the date. So if I try to put in 1:00PM it would represent this as a date and leave the rest of the time as 12am. I am doing a time sheet that subtracts the hours from between two times and then adds the differences up. I want it to ...

Date Format #7
I want to display dates in the European format dd/mm/yy. When I enter dates they appear in the American format mm/dd/yy. Format Cells > Date in Excel only gives me the American format. Regional Options in Control Panel are set to English (Ireland) with the date format dd/mm/yy. How can I change the date to European format in EXCEL? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial...

Data Recovery Discussion
Hi! I am the Author of Book titled, �'Data Recovery with & without Programming' (http://www.datadoctor.biz/author.htm)�. I provide ONLINE ' data recovery training' (http://www.datadoctor.biz/DRCguide.htm) (Professional Courses ). If any forum member has any Computer Data Recovery related query or interested in Data Recovery knowledge sharing or You've any Data Recovery Software Programming related issues, I'll be happy to discuss the things. If you�ve any query or comments, feel free to mail me� Regards, 'Tarun Tyagi' (http://www.datadoctor.biz/author....

Help: pivot chart data in secured Access database wont show fields
Hello Is there a way for me to crate a pivot chart in Excel where the data is in a secured access database. By default (ie using system.mdw) the Access security allows a user view permissions on the tables. When connecting to the database the tables are visible, but the next step where the fields get shown doesnt happen, no fields can be selected. Is this a security issue? Is there a way around this? Thanks A ...

Need macro to allow me to go out into another excel file and copy and bring data back..
Hi, I'm looking to have a macro that I've started (below) to allow me to go out to a file that is always in the same location with the same name and copy information from it and bring it back into the file I was originally in. The file from which I want to get the information from is updated automatically every 24 hours from data dumped into it from our MRP system. The macro is to be stored in my "personal.xls" file. The problem is that the original file name is always different. So how can I make the macro come back to this file that has a different name each time to pas...

Importing xml Data into Excel 2002
It appears that there is a bug in Excel 2002 relating to importing xml files. If the xml file is empty for example: <?xml version="1.0"?> <SUA1500> </SUA1500> Excel 2002 will crash when trying to import this using Import Data, or New Web Query. However, if the file contains something inside these tags, like: <?xml version="1.0"?> <SUA1500> Something </SUA1500> Excel 2002 will not crash. I tried various combinations of this, and it appears that Excel crashes only if there is one empty tag. If there are other populated tags such as:...

How do I have a formula refer to the most recent entry in a row
I am using a spreadsheet to compile monthly results for my company. Some results are YTD and I would like to have the most recent month show up for the YTD total. Is there a formula I can input that will look for the last entry in a particular row and populate that? If you mean return the right most value in a row then try for row 4: =LOOKUP(2,1/4:4,4:4) -- HTH Sandy sandymann@mailinator.com Replace@mailinator with @tiscali.co.uk "sm7301" <sm7301@discussions.microsoft.com> wrote in message news:792756FA-6E13-4DE4-A522-6F621A8A8AAF@microsoft.com... >I am using a...

Partial transpose data problem
I am having trouble figuring out how to transpose data from one sheet to another. My original data [ 'Sheet1' (2 Columns, n number of rows)] in this format: File: file1.dgn Start: 0 Stop: 1 File: file2.dgn Start: 2 Stop: 3 File: file3.dgn Start: 4 Stop: 5 .... And I am trying to transpose it to a second sheet like this 'Sheet2' (3 columns) File Start Stop file1.dgn 0 1 file2.dgn 2 3 file3.dgn 4 5 .... Thus, the actual formulas I need on my 'Sheet2' should be similar to this. =Sheet1!...

Outlook 2007 + iPhone + iTunes = bad data file?
Hello, I'm running Vista 64-bit, Outlook 2007, and have an iPhone 3G. Outlook worked perfectly until my first sync with the iPhone (via iTunes 7.7). Ever since then I get the following error message ... "A data file did not close properly the last time it was used and is being checked for problems. Performance might be affected while the check is in progress." I've since tried disabling the iTunes add-in in Outlook (no success). Uninstalling iTunes altogether (no success). And running scanpst.exe on my .pst file (no success). Any tips on how I should move forward to re...