How to get current date

[tedmi]

The code
Something = Date
produces error 2427, You entered an expression that has no value.
Substituting DATE() has no effect, because the editor removes the parens.
So how does one get the current date in VBA?
Strangely, when not executing, but in the VBA editor, typing ?date or
?date() in the immediate window displays correctly. But when stopped at a
breakpoint, the imm. window gives the same message.
Happens both in A2002 & 2007

 

[fredg]

The code
Something = Date
produces error 2427, You entered an expression that has no value.
Substituting DATE() has no effect, because the editor removes the parens.
So how does one get the current date in VBA?
Strangely, when not executing, but in the VBA editor, typing ?date or
?date() in the immediate window displays correctly. But when stopped at a
breakpoint, the imm. window gives the same message.
Happens both in A2002 & 2007

Moast likely a missing reference.
Open any module in Design view (or click Ctrl + G).
On the Tools menu, click References.
Click to clear the check box for the type library or object library
marked as "Missing:."

An alternative to removing the reference is to restore the referenced
file to the path specified in the References dialog box. If the
referenced file is in a new location, clear the "Missing:" reference
and create a new reference to the file in its new folder.

See Microsoft KnowledgeBase articles:
283115 'ACC2002: References That You Must Set When You Work with
Microsoft Access'
Or for Access 97:
175484 'References to Set When Working With Microsoft Access' for
the correct ones needed,
and
160870 'VBA Functions Break in Database with Missing References' for
how to reset a missing one.

For even more information, see
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

 

[tedmi]

Fred: Thanks for the quick response. The references in my Access
configuration are:
Visual Basic for Applications
MS Access 10 Object Library
MS DAO 3.6 Object Library
Ole Automation

None were marked as missing. Per suggestion from djsteele, I deleted all
references that Access would allow (the last two), then restarted Access and
restored them. No joy - same result.

If it is a reference problem, shouldn't it also appear in the VBA editor?
Date works when the application is not executing, but fails in execution.

 

[Dirk Goldgar]

The code
Something = Date
produces error 2427, You entered an expression that has no value.
Substituting DATE() has no effect, because the editor removes the parens.
So how does one get the current date in VBA?
Strangely, when not executing, but in the VBA editor, typing ?date or
?date() in the immediate window displays correctly. But when stopped at a
breakpoint, the imm. window gives the same message.
Happens both in A2002 & 2007

Do you have a field or variable with the name "Date"?

Also, you summarized the line of code as:

Something = Date

Could there be something wrong with the "Something", whatever it is? Can
you successfully resolve references to that?

 

[John Spencer]

As a test try

Something = DateValue(Now())

If that works, I suspect that somewhere you have managed to have a field
named date or a function named date or a variable named date.

Or you could have a missing reference, but you have already checked for
that.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[tedmi]

Actually, the statement is:
Something = SomeString & AnotherString & Date

It works just fine without the & Date

 

[tedmi]

Thanks John, that works. But I have searched the entire project for "Date"
and find it only in strings, not in names.

 

[John Spencer]

In that case, I might try Allen Browne's routine for recovering corrupt
databases. I'm not saying your database is corrupt, but there may be
something hanging around somewhere in the VBA code or in AutoCorrect that is
causing the problem.

Here is a standard sequence to try to rescue a corrupted mdb

0. Make a backup copy of the file.
00. Make a backup copy of the file.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why: http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file.
Decompile the database by entering something like this at the command
prompt while Access is not running. It is all one line, and includes the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, and reference ambiguities are resolved.

If it is still a problem, the next step would be to get Access to rebuild the
database for you. Follow the steps for the first symptom in this article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group,
rather than allenbrowne at mvps dot org.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Alan]

In that case, I might try Allen Browne's routine for recoveringcorrupt
databases.  I'm not saying your database iscorrupt, but there may be
something hanging around somewhere in the VBA code or in AutoCorrect that is
causing the problem.

Here is a standard sequence to try to rescue a corruptedmdb

0. Make a backup copy of the file.
00. Make a backup copy of the file.

1. Uncheck the boxes under:
     Tools | Options | General | Name AutoCorrect
       Explanation of why:  http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
     Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file.
     Decompile the database by entering something like this at the command
prompt while  Access is not running. It is all one line, and includes the quotes:
     "c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
         "c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
     http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, and reference ambiguities are resolved.

If it is still a problem, the next step would be to get Access to rebuild the
database for you. Follow the steps for the first symptom in this article:
     Recovering from Corruption
at:
     http://allenbrowne.com/ser-47.html

--
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users -http://allenbrowne.com/tips.htmlReply to group,
rather than allenbrowne at mvps dot org.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County




- Show quoted text -

Hi,

Thanks, the post is very useful. But sometime we can't solve the
recovery by ourselves, then we can try a tool called Advanced Access
Repair. I have used it to repair many corrupt Access MDB files on my
damaged disks successfully. Its homepage is http://www.datanumen.com/aar/
Hope this helps.


Alan