Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DateAdd to a field in access

Posted on 2013-01-02
17
Medium Priority
?
477 Views
Last Modified: 2013-01-04
I have a button in Access that copies one record and then saves it in the same table.


DoCmd.RunCommand acCmdSelectRecord
   DoCmd.RunCommand acCmdCopy
   DoCmd.RunCommand acCmdRecordsGoToNew
   DoCmd.RunCommand acCmdSelectRecord
   DoCmd.RunCommand acCmdPaste
   
   MsgBox "Re-Evaluation Created", vbInformation, ""

The table is called Initial_Eval, and the field is called Eval_date.

I want to change the date by 1, thus increasing the day. I'd like to add a command in the above script to change the date by 1 using the DateAdd.

What's the best way to do this?

So far I have:
Dim DateX As Date
DateX = Dlookup("Eval_date", "Initial_Eval","")
DateAdd("d",1, DateX)

Open in new window


But I'm not even sure that's right, and I know I need to re-store the value after modifying it.

Any ideas?
0
Comment
Question by:darantares
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
17 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38738790
Since you are already on the new duplicated record, you can just add this:
   Me.Eval_date = DateAdd("d", 1, Date)
   Dirty = False

Open in new window

0
 

Author Comment

by:darantares
ID: 38738938
Ok, so I tried the above and I have this:

 DoCmd.RunCommand acCmdSelectRecord
   DoCmd.RunCommand acCmdCopy
   DoCmd.RunCommand acCmdRecordsGoToNew
   DoCmd.RunCommand acCmdSelectRecord
   DoCmd.RunCommand acCmdPaste
   Me.Eval_date = DateAdd("d", 1, Date)
   Dirty = False

Open in new window


But instead of taking the last date (like if the Record was from 12 Dec), it takes the current date and adds 1 to it. So it copied the record and then the script put 3 Jan in there (and today is the 2nd of Jan) instead of it being 13 Dec.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38738968
Use this:
   Me.Eval_date = DateAdd("d", 1, Me.Eval_Date)
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

Author Comment

by:darantares
ID: 38739021
Brilliant!

So, if I have a number stored in a field, and the field type is set to number, if I want to add 1 to it I thought it would look like this

Me.Current_visit = [Me.Current_visit] + 1

Open in new window


But I'm getting an error with that too. Why wouldn't that work?
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38739025
What error message are you getting?
0
 

Author Comment

by:darantares
ID: 38742449
The MS Visual Basic for Applications opens up and then says

Compile error:

Method or data member not found.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38742513
Do you have a textbox named Current_visit in your form?
0
 

Author Comment

by:darantares
ID: 38742547
I don't, it's just called Visit
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38742548
If Visit is bound to a field in your table and you want to increment it by 1, you would use:
Me.Visit = [Me.Visit] + 1
0
 

Author Comment

by:darantares
ID: 38742563
Yea, it's still not going.

I've attached it here for you to look at if you want.

If you bring up a record (I was testing with Dora TheExplorer), if you click on Initial Evaluation, and then Create re-Evaluation, it copies the record and adds 1 to the date like it's suppose to.

I took that code (to add the date) and put it on the Daily Note Form/Tab, and that same code to copy the date and add 1 gets that Method error, as well as when I try to increment the visit +1

So, what should happen on the Daily Notes page, when you click "New Note/Copy Current" it should copy the note, add 1 to the date and increase the Visit by 1.

Any ideas what I'm doing wrong?
V3.accdb
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38742568
Can you save your database as 2007 version.  I don't have 2010.
0
 

Author Comment

by:darantares
ID: 38742637
I just tried and it won't let me because it says it uses features that are only available in access 2010
0
 

Author Comment

by:darantares
ID: 38742649
Do I need to declare the variables? I've been reading some stuff about that.

For the current form, the code is.
DoCmd.RunCommand acCmdSelectRecord
  DoCmd.RunCommand acCmdCopy
  DoCmd.RunCommand acCmdRecordsGoToNew
  DoCmd.RunCommand acCmdSelectRecord
  DoCmd.RunCommand acCmdPaste
  Me.Apt_Date = DateAdd("d", 0, Me.Apt_Date)
  Me.Current_visit = [Me.Current_visit] + 1
  Dirty = False
MsgBox "Re-Evaluation Created", vbInformation, ""

Open in new window

0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38742718
Go ahead and save a copy of your database in 2007.  Don't worry about the warning.  I just need to see what you have.
0
 

Author Comment

by:darantares
ID: 38742732
It won't even give me the option, unless there is a way I don't know how.

When I do a Save As, I can't change the file type or hit the drop down to select "All Files" and then change the extension.

If I do a Save & Publish, and Save Database As, and then Acess 2002-2003 .mbd, an error pop up comes and the only option is OK.

I've attached a screen shot of the error, let me know if it's any assistance at all.

ErrorError 2Error 3
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 2000 total points
ID: 38742749
I believe you gave the textbox control that holds Apt_Date a different name.  You need to use the name of the textbox control when using the Me keyword which is used to refer to controls on a form.  Or you can remove that keyword from your line of code.

Apt_Date = DateAdd("d", 0, Apt_Date)
0
 

Author Closing Comment

by:darantares
ID: 38746102
Brilliant! It's working perfectly now, for the changes to both variables. Thanks a ton, you've been a lifesaver.
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This collection of functions covers all the normal rounding methods of just about any numeric value.
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question