Data Validation date field

I don't understand why this isn't working.  I just want to make sure that 
they don't put a future date in the polydate field.  Is me.polydate > now() 
an invalid expression?

code:
_________________________________________________________
Private Sub Form_BeforeInsert(Cancel As Integer)

If Me.PolyDate > Now() Then
    Cancel = True
    Me.PolyDate.SetFocus
    MsgBox "Please enter a date that falls prior to today's date"
    Exit Sub
End If

End Sub
0
Utf
1/31/2008 10:38:00 PM
access.formscoding 7493 articles. 0 followers. Follow

8 Replies
580 Views

Similar Articles

[PageSpeed] 37

It does not appear to be invalid.  It is always helpful to post the error you 
are getting and if it is a runtime error, point out the error number and 
description and the line on which the error occurs.
-- 
Dave Hargis, Microsoft Access MVP


"Jonathan Brown" wrote:

> I don't understand why this isn't working.  I just want to make sure that 
> they don't put a future date in the polydate field.  Is me.polydate > now() 
> an invalid expression?
> 
> code:
> _________________________________________________________
> Private Sub Form_BeforeInsert(Cancel As Integer)
> 
> If Me.PolyDate > Now() Then
>     Cancel = True
>     Me.PolyDate.SetFocus
>     MsgBox "Please enter a date that falls prior to today's date"
>     Exit Sub
> End If
> 
> End Sub
0
Utf
1/31/2008 10:57:00 PM
As Klatuu said, the code's valid. You do understand that the messagebox won't
appear until Access tries to save the record, don't you? If you want it to
appear immediately after entering the date, you need to move the code to
another event, like the BeforeUpdate event of the Polydate control itself. If
you do this, you'll need to drop the line:

Me.PolyDate.SetFocus

which you won't need anyway.

-- 
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1

0
Linq
1/31/2008 11:15:40 PM
I wasn't getting an error message at all.  It was allowing me to save the 
record no matter what date I put in there; past or future.

I decided to just go ahead and use the following:

if datediff("d", me.polydate, now()) < 0 then
etc, etc, etc.

It works if I do it that way.

"Linq Adams via AccessMonster.com" wrote:

> As Klatuu said, the code's valid. You do understand that the messagebox won't
> appear until Access tries to save the record, don't you? If you want it to
> appear immediately after entering the date, you need to move the code to
> another event, like the BeforeUpdate event of the Polydate control itself. If
> you do this, you'll need to drop the line:
> 
> Me.PolyDate.SetFocus
> 
> which you won't need anyway.
> 
> -- 
> There's ALWAYS more than one way to skin a cat!
> 
> Answers/posts based on Access 2000/2003
> 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1
> 
> 
0
Utf
1/31/2008 11:30:00 PM
On Thu, 31 Jan 2008 14:38:00 -0800, Jonathan Brown
<JonathanBrown@discussions.microsoft.com> wrote:

>I don't understand why this isn't working.  I just want to make sure that 
>they don't put a future date in the polydate field.  Is me.polydate > now() 
>an invalid expression?

Use the BeforeUpdate event of either polydate or of the form, instead of the
Form's BeforeInsert event. BeforeInsert executes the moment you dirty the
form, with the very first keystroke; polydate will not have any value at that
point.

             John W. Vinson [MVP]
0
John
1/31/2008 11:42:44 PM
Jonathan

It will never work because you are referring to Now() this is in the format 
date + time.

I fell into the same trap.

If you are testing for Me.PolyDate  is greater than the current date it 
should be

If Me.PolyDate > Date() Then
    Cancel = True
   Me.PolyDate.SetFocus
   MsgBox "Please enter a date that falls prior to today's date"
    Exit Sub
End If


Allan

"Jonathan Brown" <JonathanBrown@discussions.microsoft.com> wrote in message 
news:BACAD023-3342-4695-A55D-D8C8B92BDE53@microsoft.com...
>I don't understand why this isn't working.  I just want to make sure that
> they don't put a future date in the polydate field.  Is me.polydate > 
> now()
> an invalid expression?
>
> code:
> _________________________________________________________
> Private Sub Form_BeforeInsert(Cancel As Integer)
>
> If Me.PolyDate > Now() Then
>    Cancel = True
>    Me.PolyDate.SetFocus
>    MsgBox "Please enter a date that falls prior to today's date"
>    Exit Sub
> End If
>
> End Sub 


0
Allan
2/1/2008 8:03:05 AM
Not true, Allan, at least in ACC2000-2003! The code works fine for me, using
a date vs Now(). When entering data that only contains a "date" Access adds
the "time" component of 00:00:00. So the code is esentially comparing 

polydate 00:00:00

to 

Now()

-- 
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via http://www.accessmonster.com

0
Linq
2/1/2008 3:18:26 PM
Another question on Date validation:

What if I want to make sure they don't put in a date prior to 1/1/1900?

I tried the following but I got a data type mismatch error.

if me.polydate < datevalue(1/1/1900) then
cancel = true
....etc.
end if

Is datevalue the wrong function too?  I'm apparently not very good with 
these date functions.  The PolyDate field in my clearances table is of the 
short date data type.

"Allan Murphy" wrote:

> Jonathan
> 
> It will never work because you are referring to Now() this is in the format 
> date + time.
> 
> I fell into the same trap.
> 
> If you are testing for Me.PolyDate  is greater than the current date it 
> should be
> 
> If Me.PolyDate > Date() Then
>     Cancel = True
>    Me.PolyDate.SetFocus
>    MsgBox "Please enter a date that falls prior to today's date"
>     Exit Sub
> End If
> 
> 
> Allan
> 
> "Jonathan Brown" <JonathanBrown@discussions.microsoft.com> wrote in message 
> news:BACAD023-3342-4695-A55D-D8C8B92BDE53@microsoft.com...
> >I don't understand why this isn't working.  I just want to make sure that
> > they don't put a future date in the polydate field.  Is me.polydate > 
> > now()
> > an invalid expression?
> >
> > code:
> > _________________________________________________________
> > Private Sub Form_BeforeInsert(Cancel As Integer)
> >
> > If Me.PolyDate > Now() Then
> >    Cancel = True
> >    Me.PolyDate.SetFocus
> >    MsgBox "Please enter a date that falls prior to today's date"
> >    Exit Sub
> > End If
> >
> > End Sub 
> 
> 
> 
0
Utf
2/1/2008 4:52:01 PM
On Fri, 1 Feb 2008 08:52:01 -0800, Jonathan Brown
<JonathanBrown@discussions.microsoft.com> wrote:

>Another question on Date validation:
>
>What if I want to make sure they don't put in a date prior to 1/1/1900?
>
>I tried the following but I got a data type mismatch error.
>
>if me.polydate < datevalue(1/1/1900) then
>cancel = true
>...etc.
>end if
>
>Is datevalue the wrong function too?  I'm apparently not very good with 
>these date functions.  The PolyDate field in my clearances table is of the 
>short date data type.

No. It's a Date/Time datatype, not a "Short Date" datatype. A date value -
regardless of format! - is stored as a Double Float number, a count of days
and fractions of a day since midnight, December 30, 1899. The format merely
controls how that number value is displayed; you could have the same value
displayed many different ways in different parts of your application if you
wish.

The DateValue function accepts a Text String as a value. You're feeding it
what looks to you like a date, but to Access it looks like a calculation - 1
divided by 1 divided by 1900. That will give you some very small number, not a
text string - hence the error message!

For a date constant, use # as the delimiter:

If Me.polydate < #1/1/1900# Then

The # character tells Access "I'm giving you a Date/Time value, translate it
to your wierd number depiction so you can use it".

             John W. Vinson [MVP]
0
John
2/1/2008 6:32:26 PM
Reply:

Similar Artilces:

Date in Access
Is it correct to use next formula: 12/14/1999+ 12/14/2001? I’ve got result = 11/28/2101. What is wrong? You might want to ask in an Access forum and explain the context in which you're using that formula. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "ER" <ER@discussions.microsoft.com> wrote in message news:69ABCC01-1D28-4A45-8217-10FACA32228D@microsoft.com... > Is it correct to use next formula: 12/14/1999+ 12/14/2001? > I&...

convert a date
I have a field Trans date with different formats, some are DD/MM/YYYY and some are DD/MM/YYYY/HH/MM/SS. I want to use the Between begin date and End date. How do I convert all the date to the format DD/MM/YYYY/HH/MM/SS Please help. pon Formatting has nothing to do with how the data are stored. It only presents the data in the specified format. It is possible some of your records may have date and time and others only time. It should not be that way, but it is. If you are trying to compare on date only when there may be time in the field, you can format the values in the query so you ...

How to replace PivotTable data fields...?
Hi, I'm progamming a pivot table using VBA. I have no problem manipulating column or row fields, but how can I replace a data field? Everything I try just adds the data field to the existing data field resulting in a mess. The PivotTable.addfields methods *replaces* whatever columns were previously designated as RowFields, ColumnFields, and PageFields with the new specifications, as in: ..AddFields RowFields:="Date", PageFields:="Location" But no matter what I try, I can't replace the data field.... I've even tried setting the Orientation property to xlHidden...

MS Project 2003 Clarity Integration Field Mapping
I am using MS project 2003 with full Clarity integration and noticed that there is a neat field available when you use the Workbench scheduling tool - Pending Time Entry. Does anyone know how I can get the field/column mappings between MS Project 2003 and Workbench when integrating with Clarity? ex. in Workbench ETC is the same field as Remaining Work in MS Project 2003. What about all the others??? ...

access of oracle9i data base with MFC
i m getting some problem while accessing the data base of oracle9i . whenever i m accessing the data base it is always asking password and name , i need to remove this so what should u include in my code that it will privent the same Please, add more details about your problem. It's difficult for us to guess how you are connecting to Oracle database since there are a lot of ways to do it. Jaime "hame" <hame@discussions.microsoft.com> wrote in message news:43299876-F05F-417E-84F0-F51A5A9AF3AC@microsoft.com... > i m getting some problem while accessing the dat...

Uniquely Identify Data for Charting with a List Box
Gang, The Internet is a great thing. I found a charting example that does nearly every thing I want to do. Amazingly enough, I was able to figure out how the thing works! The chart is fairly simple. It is a X*Y line chart with 2 series. I found the chart here: http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - The chart is called "ChartAgainstStandard.zip." One of the 2 series plotted is a "standard" and the other changes based on the selection made in the drop down list. Amazingly enough, I was able to follow the thing completely and I would like ...

help with spliting data cells
I am looking for assistance with splitting data between cells. I have a column of data (first (space) last name). I need to split that column into two seperate columns so each name value is in it;s own cell and i end with 2 seperate columns. (john smith) to (john) (smith) Please advise if this is easy and can be done. Thanks, Phil pcavalcanto@Aol.com -- pcavalcanto ------------------------------------------------------------------------ pcavalcanto's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32212 View this thread: http://www.excelforum.com/showthread...

sort by sum of a field
I have a table with several thousand records with the following fields: username, job, total pages What I want to do is group the records by username, then count the total of all grouped records of the total pages field to get a sum. Then I need to sort the results of the total of the "total pages field in decending order. I got a report to show me the summary of the user name and jobs, and to show the total from the totalpages, but how do you sort the report on the total pages by decending order? Darrell Eddy Use the report Sorting and Grouping. -- Build a...

How do I create a graph from an equation and not a set of data?
How do I create a graph from an equation and not a set of data? Hi, Stephen Bullen has an example. See ChtFrmla within the Charting section. http://www.oaltd.co.uk/Excel/Default.htm The example file is, http://www.oaltd.co.uk/DLCount/DLCount.asp?file=ChtFrmla.zip Also see Tushar Mehta's Plot manager, http://tushar-mehta.com/excel/software/plot_manager/index.html Cheers Andy ashemorry wrote: > How do I create a graph from an equation and not a set of data? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Using table field in Lookup Wizard
In a particular table I need to reference fields of that table to a Lookup Wizard in the same table. This is a genealogy database and there is a filed for parentMother and a field for parentFather, which should reference the unique key numbers of the proper entry, NULL is possible. However, the Lookup Wizard will not let me reference fields of the current table. I even tried writing a query on the ID and name fields, but the Lookup Wizard will not let me use a query that references the same table. This is with Access 2007 on a Windows XP installation. I must stress that I am n...

validation list in a range
I have a defined range (separate sheet from the validation, but can be moved), and would like for the range to serve as my list of values in a "list" validation. Suggestions? Boris select data/validation. In the drop down box title "Allow" select list and for the source select the range of values. "boris" wrote: > I have a defined range (separate sheet from the > validation, but can be moved), and would like for the > range to serve as my list of values in a "list" > validation. Suggestions? > > Boris > Right, but I ...

How to wipe out HQ and SO data without the setting and paramters
Hi. Im planning to wipe out my database without clearing out its parameters and settings. I have an existing Store running on HQ and SO. I plan to change the barcodes for all items as they are inconsistent. Therefore i need to wipe out everything without the parameters and configuration settings. Can This be done? If so i need a detailed steps in doing this. This sounds very dangerous. What if you changed all your items so they start with a 'Z-' and then make all those items InActive, that way will not lose any Sales History? Then delete everything from the Alias table if you ar...

Cannot edit data in forms but can in tables
I have a problem! I created a database where I can only edit the data in tables. In the forms I created I can see the data but cannot edit the data. I am using 2007 that I have set so the file can be opened in 2003. The data was imported to create tables from two Excel spread sheets using the wizard. The tables are linked together and the combined data is shown in a report. This all works without a problem. Now I need to edit the data, so I created several forms all of which I cannot add, delete or edit the data. I saw under the User and Group Permissions that u...

Phone Call Activity Recipient Field
When creating a new Phone Call Activity by itself (not from within a record) when you chose the lookup recipient field and change the Look for to Contact I cannot get it to search off a custom field, the custom field we added to the contact form is "Company". I have changed in the customization, contact, forms & views, Quick Find View and the Advanced Find View, to include in the Add Find Columns this custom "Company" field and still does not bring up the results. How can I get it to bring up search results? Thank you in advance for the help! You need to modify...

Excel data query goes away.
I have a work book that gets information from an SQL database. About every two months the query goes away. Why? and how can I stop this from happening? ...

insert data from one excel file into another
Is there any way to insert data from one excel file into another without doing copy and pasting? I need to automate the process of inserting data but I cannot use copy-paste, because it puts data on a clipboard, and my Excel VBA program runs in a multi-user environment. So if one instance of a program is copying data into clipboard, and another instance is pasting it at the same time, it will paste incorrect data. Thank you Leonard. You'll need both spreadsheets open, but try this; Option Explicit Sub MyMacro() Dim MyVariable As String Windows("File2.xls").Ac...

How do I stop numbers from converting into dates?
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel I want to enter 4/1 or 4-1 into a cell but every time I press enter it converts it to 1-Apr. this is very irritating and despite looking in the the various preferences and format tabs I have yet to find a way to stop it. What am I doing wrong? First of all, any formulas need to be preceded by an equals sign (=) to let Excel know that what follows is an 'instruction' to be executed. Otherwise the content is interpreted as a text string or -- as in this case due to the operators you're using ("/&quo...

Automated changes of Outlook contact fields
What software / utilities / code etc. are available that would allow me to adjust some existing data in my Outlook 2003 contacts? For example, I have many "web page address" fields that need to be changed from e.g. http://subway.com to http://www.subway.com and also many "city" fields that need to be capitalized e.g los angeles to Los Angeles. Thanks in advance for any replies, Miner2049er. After some research, I believe I have to write a Visual Basic script to do this. Not knowing that language, does anybody know of a script that can do what I'm asking, or at l...

Pivot Table
I have 2 lists of 100 items (rows) with 5 data elements. Each list has a common element, which can tie the two lists together. How can I combine these 2 lists and use it for the source data of a pivot table? Todd ...

How can I import my data from Entourage for Macosx
I a moving from a Apple Mac computer and I want to move all my data from Entourage to Outlook Ken Allen <Ken Allen@discussions.microsoft.com> wrote: > I a moving from a Apple Mac computer and I want to move all my data > from Entourage to Outlook See if this helps: http://www.entourage.mvps.org/cross_platform/ -- Brian Tillman "Ken Allen" wrote: > I a moving from a Apple Mac computer and I want to move all my data from > Entourage to Outlook. Will these scripts move all my e-mails and attachments over to Outlook as well. Thank you for all your help. I h...

removing 0 value data labels
Hi All How would I remove 0 Value data labels from a chart using vb script? No need for VBA. You can make a custom number format for the labels. Select the whole set of labels, press CTRL+1 to format, and on the number format, select Custom in the left hand list, and add a format like one of these: 0;0;;@ 0.0;0.0;;@ There are four items in a custom format, by default the formats for positive, negative, and zero values, and for text. Leave off the format for zeros, and the label will not appear. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and C...

using two cells 1 with total time n 1 with date how can i put the.
I'm trying to put the amount of time spent in to a seperate sheet (Cell # 2 column E ) from a sheet that has the dates in one cell (column A) and the time spent in another (column K) and the reason for the time spent in column B. On the seperate sheet I have to put the amount of time spent in column E and match with the date I have in column A on the seperate sheet to populate the chart that I'm making. The only thing that I have been able to do is create a formula to show the amount of time spent and for what reason but i can't match it to the date because the dates ...

Data migration manager error
Hi, I am running migration manager for accounts, I am getting error on importing the file that - data migration manager cannot run furter, close and try again. I have tried several times , it is not functioning.Is there any service to be started. regards Ritesh ...

Re-Sizeable Field Display
Hi All, Explore have fields displayed in column such as File Name | Extension | Date |... User can re-size the field. What is that in VC++? How to make some thing like that in VC++? Do you have any ideal? Thank you, Ben Hi, The Windows Explorer window contains the ListView control. This control can provide several columns with titles in "Report" mode. The resizing functionality is also provided by this control (to be correct, by special Header control). You can use this control in your apps. -- WBR, Vitaly Brusentsev, AWinstall development team. http://www.awinstall.com ...

Cannot access or restore my Money2005 data
I have backed up to a memory stick religiously. Nevertheless, I keep getting a popup message: "Exception Processing message c000013 Parameters 75b6bf9c 4 75b6bf9c 75b6bf9c" Any help or ideas would be greatly appreciated. Many additional data points are necessary to help. Examples: What version/edition of Money? What O/S? Under what conditions (what sequence of steps?) do you get this message? What happened (Internet Update, OS patch installation, Money reinstallation) when it started presenting itself? Do you get the same message trying the same sequence of steps in a new da...