Senin, 03 Oktober 2016

Re: [MS_AccessPros] Simple Default value question

 

You are more than welcome!


John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Oct 3, 2016, at 9:29 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



Thank You John. 

I put =Year(Date()) in the Default value of the field on the table and put Long Integer for the Field size and it worked. 

Thank You for your help over all these years. 

 
Jim Wagner


On Friday, September 30, 2016 11:10 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


Jim-

Sometimes referencing an earlier calculated column alias in a query works, and sometimes it doesn't.  The SELECT part should be:

SELECT Date() As CurrentDate, Year(Date()) As CurrentYear

John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications

On Oct 1, 2016, at 01:02, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I just discovered something odd about this issue. I am taking this database over for someone that retired and there is a macro that has a delete query and then an append query that appends to a table. the SQL is below. Apparently the current year needs to be a number somewhere down the line for some reason or another. I have not gone that deep in the database yet. 

In the macro

Delete Query
the comment in the macro states the following
    deletes the LU table (LU_CurrentDateYear) of current date and current year field (this is so we don't have the date problem of coming in as text (Cdate thing)

DELETE LU_CurrentDateYear.*
FROM LU_CurrentDateYear;

Append query
the comment in the macro is
 populates the LU table (LU_CurrentDateYear) with today's date and the year part of that

INSERT INTO LU_CurrentDateYear ( CurrentDate, CurrentYear )
SELECT Date() AS CurrentDate, DatePart("yyyy",[CurrentDate]) AS CurrentYear;

 LU_CurrentDateYear table

the CurrentYear field has the following properties
Data Type: Number
Field Size: Long Integer




 
Jim Wagner


On Friday, September 30, 2016 3:32 PM, "luvmymelody@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


 
Hi all,

I am trying to make a field in a table to have a default value of the current year. I have tried a lot of variations like =Year(Now()) but I am getting 1905. 

Why is this happening?

Thank You

Jim Wagner







__._,_.___

Posted by: John Viescas <johnv@msn.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (5)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


.

__,_._,___

Tidak ada komentar:

Posting Komentar