dafault value for date not working with Date() but Now() is workin

Got a table with existing data, with simple date field on a form that I want 
to default to todays date.  I have tried inputting the Date() into the table 
default value for the date, but I get an error that says "Unknown function in 
'Date' in validation expression or default value on 'Data.date'.  Data is the 
table name.  If I enter Now() into the default value for date, it works just 
fine.  But, the reports that are generated, don't show the records with the 
date and time in them.  I can go back to the table and manually delete the 
time from those records, and they then show up in the report.  Any ideas?  I 
just want to use the default date of today in the date field.
0
Utf
2/26/2010 9:20:01 PM
access.forms 6864 articles. 2 followers. Follow

5 Replies
1220 Views

Similar Articles

[PageSpeed] 5

The correct usage in Access is Now().  Date is a reserved word and really
shouldn't be used for a field name.  Try changing it if possible.

Bonnie
http://www.dataplus-svc.com

Dirty70Bird wrote:
>Got a table with existing data, with simple date field on a form that I want 
>to default to todays date.  I have tried inputting the Date() into the table 
>default value for the date, but I get an error that says "Unknown function in 
>'Date' in validation expression or default value on 'Data.date'.  Data is the 
>table name.  If I enter Now() into the default value for date, it works just 
>fine.  But, the reports that are generated, don't show the records with the 
>date and time in them.  I can go back to the table and manually delete the 
>time from those records, and they then show up in the report.  Any ideas?  I 
>just want to use the default date of today in the date field.

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

0
bhicks11
2/26/2010 10:25:19 PM
On Fri, 26 Feb 2010 13:20:01 -0800, Dirty70Bird
<Dirty70Bird@discussions.microsoft.com> wrote:

>Got a table with existing data, with simple date field on a form that I want 
>to default to todays date.  I have tried inputting the Date() into the table 
>default value for the date, but I get an error that says "Unknown function in 
>'Date' in validation expression or default value on 'Data.date'.  Data is the 
>table name.  If I enter Now() into the default value for date, it works just 
>fine.  But, the reports that are generated, don't show the records with the 
>date and time in them.  I can go back to the table and manually delete the 
>time from those records, and they then show up in the report.  Any ideas?  I 
>just want to use the default date of today in the date field.

Two possible issues:

first, is your fieldname Date? If so, you're getting Access confused about
whether you mean the fieldname or the builtin Date() function. Avoid using
reserved words like this as the names of fields or controls.

Second, if that's not it, you may have a bad reference. Date() seems to be
especially vulnerable to this. To see, open any module in design view, or open
the VBA editor by typing Ctrl-G. Select Tools... References from the menu. One
of the .DLL files required by Access will probably be marked MISSING. Uncheck
it, recheck it, close and open Access.

If none are MISSING, check any reference; close and open Access; then uncheck
it again. This will force Access to relink the libraries.

-- 

             John W. Vinson [MVP]
0
John
2/26/2010 11:54:27 PM
On Fri, 26 Feb 2010 22:25:19 GMT, "bhicks11 via AccessMonster.com"
<u44327@uwe> wrote:

>The correct usage in Access is Now(). 


Well... not really. There is a Now() function which returns the current date
and time accurate to the second; there is *also* a Date() function which
returns the current date without a time portion (equivalent to midnight at the
start of today's date). Both functions have their uses, just depending on
whether you want the time included or not.

You're quite right about not using Date as a fieldname, though.
-- 

             John W. Vinson [MVP]
0
John
2/26/2010 11:56:20 PM
Oh Johnny - I should have known you'd catch me!

John W. Vinson wrote:
>>The correct usage in Access is Now(). 
>
>Well... not really. There is a Now() function which returns the current date
>and time accurate to the second; there is *also* a Date() function which
>returns the current date without a time portion (equivalent to midnight at the
>start of today's date). Both functions have their uses, just depending on
>whether you want the time included or not.
>
>You're quite right about not using Date as a fieldname, though.

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

0
bhicks11
2/27/2010 1:27:02 AM
changed the field name from "date" to "dates", still same error message.  I 
also changed it from "date" to "chris" (my name), still same error message.

tools, references, shows "admin 1.0 type library" was missing.  Unchecked, 
then re-checked, closed access, re-added the date(), still same error 
message.  Then re-opened access, unchecked the "admin 1.0 type library" check 
box, saved, then exited.  Re-added the date(), now no error messages are 
present.  Appears to work without this "admin 1.0 type library"...

"John W. Vinson" wrote:

> On Fri, 26 Feb 2010 13:20:01 -0800, Dirty70Bird
> <Dirty70Bird@discussions.microsoft.com> wrote:
> 
> >Got a table with existing data, with simple date field on a form that I want 
> >to default to todays date.  I have tried inputting the Date() into the table 
> >default value for the date, but I get an error that says "Unknown function in 
> >'Date' in validation expression or default value on 'Data.date'.  Data is the 
> >table name.  If I enter Now() into the default value for date, it works just 
> >fine.  But, the reports that are generated, don't show the records with the 
> >date and time in them.  I can go back to the table and manually delete the 
> >time from those records, and they then show up in the report.  Any ideas?  I 
> >just want to use the default date of today in the date field.
> 
> Two possible issues:
> 
> first, is your fieldname Date? If so, you're getting Access confused about
> whether you mean the fieldname or the builtin Date() function. Avoid using
> reserved words like this as the names of fields or controls.
> 
> Second, if that's not it, you may have a bad reference. Date() seems to be
> especially vulnerable to this. To see, open any module in design view, or open
> the VBA editor by typing Ctrl-G. Select Tools... References from the menu. One
> of the .DLL files required by Access will probably be marked MISSING. Uncheck
> it, recheck it, close and open Access.
> 
> If none are MISSING, check any reference; close and open Access; then uncheck
> it again. This will force Access to relink the libraries.
> 
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
3/1/2010 6:11:01 PM
Reply:

Similar Artilces:

visual basic
Hi, I trying to retrieve values from a table to calculate the 14days average value of a stock closing price. However, i encounter some problem as stated beside the code as follows: Function DaysAvgs() 'Calculate the average value of a given value. Dim db As DAO.Database Dim rst As DAO.Recordset Dim varBookmark As Variant Dim numAve, numDaysAvg As Double Dim intA, intB, lngCount As Integer Set db = CurrentDb 'Open Table Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable) rst.MoveFirst Do While Not rst.EOF intA = 1 intB = 0 varBookmark = rst.Bookmark n...

Oldest date for Duplicate Cust. #
I'm trying to get the oldest date associated with a customer number, and in the Cust# column, i'll have many duplications of the same customer number. Let's say A is "Date", and B is "Cust#". (I won't be able to allow my users to sort the data, so i'll need a formula that returns either the oldest date, or the cell which contains the oldest date.) Any help is much appreciated! Nevermind. I found it using Google/Groups. {=MIN(IF($B$1:$B$10=B1,$A$1:$A$10))} >-----Original Message----- >I'm trying to get the oldest date associated with a...

Dates #9
The problem of a date code... I need to address this so that fo example, 5/6/04 can be correctly entered as either 5th of June or 6t of May, depending from where the date emanted. regards -- Message posted from http://www.ExcelForum.com Couldn't you format the cell as mmmm dd, yyyy so that the user sees what date they entered in a non-ambiguous manner right away? Or maybe provide 3 inputs: Month, day, and year. You could combine them elsewhere. "adn4n <" wrote: > > The problem of a date code... I need to address this so that for > example, 5/6/04 can be c...

IsOutLookClient() returns wrong value
IsOutLookClient() returns wrong value when both web client of crm and outlook client are running on the same workstation It looks like the same cookie(used for determining what client is running) is used by the sessions of each client. Look for "LightClient" in IsOutlookWorkstationClient() in global.js Oeps...I seem to have made a wrong assumption... Between the to clients IsOutlookClient() seems to work ok... But in outlook client the IsOutlookClient() function gives false for me...after I have opened a page from the Microsoft Crm folder structure... On another workstation it...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Qrp Date functions
Where do I find the various functions to modify the Reports like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others and what they mean???? Barry Found the information at MSDN Transact-SQL Reference Barry "Barry L" <barryl@eryanjewelers.com> wrote in message news:usd3uP1CIHA.1188@TK2MSFTNGP04.phx.gbl... > Where do I find the various functions to modify the Reports > like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others > and what they mean???? > > Barry > ...

date tracking
I am entering clients into a 2007 Excel spreadsheet. How do I make the entry turn color when 14 days have passed? Gene This is a multi-part message in MIME format. ------=_NextPart_000_0018_01CAC8D4.5688AC60 Content-Type: text/plain; format=flowed; charset="UTF-8"; reply-type=original Content-Transfer-Encoding: 7bit As part of the "client" entries, do you enter the date the client was entered? This would be the key in doing this task. In a cell on the worksheet you could enter the formula for today's date like this =TODAY(). Then use the con...

Ctrl+D not working on outlook messages
Any idea why? alltimefav wrote: > Any idea why? And what are you expecting to happen? "alltimefav" wrote in message news:a95c52df-a387-4a8f-bf9d-269ff485451f@i29g2000prf.googlegroups.com... > Any idea why? So instead of hitting Ctrl+D to delete an item, what happens when the item is selected and you hit the Del key, or the "X" toolbar button, or right-click on the item and select Delete? Did you really expect a detailed response for such a vague question? You didn't even bother to say what "not working" means, like the item does not get delete...

how to make macro work even a sheet is hidden.
hello there I have macro assigned to a button in Sheet 1 which goes to sheet 2. Press a button to Refresh and then PRINT preview It does work in normal state. I do not want the user to see the Sheet 2. I hid sheets from Format-sheet-hide. but the macro is not working when sheet 2 is hidden. how to overcome this error "can't execute code in break mode" the macro code is Sub Print_Preview() Sheets("PaySlips2009-10").Select Application.Run "'Latest 2009Payslip.xls'!Sheet2.HURows" ActiveWindow.SelectedSheets.PrintPreview ...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

Short time vs short date
In my form I have a fldOutOfRoom which the user inputs a short time into the field, i.e, 0900. I have the following code in the open event of another form called frmRNnotes: If DateDiff("h", Forms!frmPtDemographicNew!frmVisitNewEdit.Form!OutOfRoom, Now) >= 1 Then Me.cmdRNnotesEdit.Visible = False This code gives the RN one hour to complete a note and then he/she can no longer edit the note. What I want to know is the Short Time format going to let the cmdRNnotesEdit button be visible everyday within one hour of the of the original time? That is, is the short time just a tim...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

Date Calculation
Good Afternoon, I have a DB which tracks training of employees. The grace periods allowed with the training is that new training can be completed within 90 days of the expiry date without changing the anniversary date (e.g. the training is due on 1 April 2010, the employee conducts the training on 2 January 2010 but gets to keep the 1 April anniversary date). The table I am working with is mainly based on the date of training and the training type (which determines whether the training expires on the 1st of the 13th, 25th or 37th months or if it keeps the same date); what I w...

why does my spell check not work?
My spell check has never worked. What can I do to set it up? In what program / version "karen" <karen@discussions.microsoft.com> wrote in message news:DA8883BD-0958-4AB0-93FF-F43E4FB9D176@microsoft.com... > My spell check has never worked. What can I do to set it up? ...

date modified
I have two sheets Data and Summary The "data" sheets macro extracts data from external file and paste into "Data" sheet Everytime the m acro is run to get latest data... The macro delete all contents of the "data" and then paste new data into the "data" sheet. Is there a way.. I can put a date on the "summary" sheet, when was the time the macro was run ( or in other words.. the data updated) This little macro records the date in the selected cell and formats it: Sub Macro2() Dim d As Date Dim s As String d = Now() s...

Date function quit working
Hi, I have an Access 2002 application that I have been running on Windows XP SP2 without issue. I just installed the application (running in Access Runtime) on a Windows Vista Home Premium machine. Now, anywhere I used the =Date() function, it fails and just shows #Name? I also have a subform on one of my forms that has now gone blank. It also uses the date function. I had this problem when I converted to Windows XP several years ago and updating the OWC10.dll to version 6619 fixed both issues. However, everything I have read says that reference file makes no difference to the Access...

How do I convert a concatenated value into a know value
Hi all I am trying to get the results of a multiple input table, which get concatenated, read out as usable values eg. If the concatenated values are for example *llbbt* , I need this t be read as Simon, or *lbttd* must result in Fred etc... I will attact the spreadsheet. Thanks Colli Attachment filename: book3.xls Download attachment: http://www.excelforum.com/attachment.php?postid=54116 -- Message posted from http://www.ExcelForum.com You are probably better off by describing your problem, most regulars won't open files.. -- Regards, Peo Sjoblo...

Value is BLANK
In a form i'm working on i've asked this question before and i'm unable to locate the replies, but in one cell I have a date to be enter and in the other cell it takes that date and add 5 days to the date to give me a due date. But if no date is enter then I want to to remain blank insted giving me a date. Say that the date is to be entered into A1, then enter this formula into the "other" cell: =IF(A1,A1+5,"") -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ==================...

Date format 04-11-06
Hi, Is there a possibility that the dates used in all the entities are not in the default format mm/dd/yyyy but in dd/mm/yyyy. I already adapted the Organisatonal settings, that only adapts the journal but nog the dates of an appointment. Does anyone have an idea? Thanks, ...

Word 2003: Will it work on Windows 7 Pro
The ads say there is virtual XP on Windows 7 that can run programs like Word 2003. In your experience is this true? On any Windows 7 machine? Does 64 versus 32 bit processor matter for either Word/Office 2003 or Office 2007? thanks. You don't need Virtual XP to run Office 2003 on Windows 7. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "removing all headers and footers" <removingallheadersandfooters@discussions.microsoft.com> wrote in message news:95298031-44F6-4E74-A608-C76987DFAB46@microsoft....

Selecting the Right Text Alignment for a edit box doesn't work
When I select right text alignment in the edit control properties, the text is still left aligned when I run the program. What am I doing wrong Thanks Dan Dan, "Dan" <anonymous@discussions.microsoft.com> a �crit dans le message de news:DECFE605-A130-416B-9924-60BA0C79D684@microsoft.com... > When I select right text alignment in the edit control properties, the text is still left aligned when I run the program. What am I doing wrong? > I've no idea :-))) You can open your RC-file as text, and make sure it has the ES_RIGHT style set, thus: EDITTEXT IDC...

Null value in form not trapped by beforeupdate event
I have a form in Access 2003 linked to a SQL Server 2005 table. When I clear the value in a textbox (bound field is varchar and is required), I want the before update event to run to tell the user the value cannot be null. When I press the tab button to move to the next field after clearing the texbox, the before update event is not triggering and instead I'm getting the following error: You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162) How can I prevent nulls before and this error from triggering? Thanks! ...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Select your range to copy edit|goto|special|c...

How to Identify Records with Overlapping Dates
I need to be able to systematically identify any instance where there are overlapping date ranges in a data set. I need to pull records like those listed below out of a larger data set. I previously posted a question similar to this and was advised to pull the same table in a query, match on Member Number, and qualify that the product code from TableA did not match product code in TableA_1 where the Begin Date TableA was < Term date TableA_1 and TableA_1 Begin Date < TableA End Date. Which worked great for me finding overlapping records where the product was different, bu...

y value vs x
In an xy scatter plot one can choose the type of line connecting the data points (smooth, straight, etc.). Once this is done, Is there a simple way of determining the y value of graph for a specific x value without doing successive approximations with 0 shifts. I'd rather not purchase a graphing program just for this simple task. You could find an equation that fits the data (see trendline) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ralph" <ralph@discussions.microsoft.com> wrote in message news:284B39DE-20C6-40CB-AB60-39B...