Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old December 6th, 2006, 06:13 PM
Authorized User
 
Join Date: Aug 2006
Location: Hoffman Estates, IL, USA.
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default Updating Dates and Changing two other fields

Hello everyone,
  I need help with something that is a bit more complicated than I am used to dealing with. It's three seperate components.

The first one is that I need to take a date field and make a couple of changes to it. If the date field is <=12/31/2006, I need to update it to m/1/2007. I think the formula I would use for it is, but I want to be sure:
Code:
DatePart("m",[Scheduled_Review_Date]) & "/1/2007"
The second is that if the date is <=12/31/2006, I need it to change a checkbox field to "True" and I need it to change a Date_Changed field to "12/28/2006".

I'm not sure how is the best way to do this.

Thanks,
Arholly

  #2 (permalink)  
Old December 7th, 2006, 08:41 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You are doing many things with the same date parameters. Is that right? You say in the first one, if the date is <=date, and in the second one, if the date is <=date. These are the same conditions. Did you mean one to be >=date?

That being said, you want to take the values in variables, and wrap the values in a conditional, like this:

Dim dtNewDate As Date

If Me.Scheduled_Review_Date <= #12/31/2006# Then
   dtNewDate = DatePart("m",[Scheduled_Review_Date]) & "/1/2007"
   Me.CheckBox = True
   Me.DateChanged = #12/28/2006#
End If

Me.Scheduled_Review_Date = dtNewDate

Is that what you want? This code will run on some event on a form. If you want to loop through all of your records and make these changes, then let me know.



mmcdonal
  #3 (permalink)  
Old December 7th, 2006, 09:19 AM
Authorized User
 
Join Date: Aug 2006
Location: Hoffman Estates, IL, USA.
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Mmcdonal,
  Thanks for the help, but yes, I wanted to run it as an update so that I can update all the records.
  To answer your questions though, yes, I am using the same parameters for all the variables. If the "Scheduled_Review_Date" is <="12/31/2006", then do all of the stated.

Thanks for your help with this, I appreciate it.

  #4 (permalink)  
Old December 7th, 2006, 09:34 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I would do something like this then. Create a form, and put a single button on the form. Put this sort of code on the On CLick event of the button:

Dim rs As ADODB.Recordset
Dim dtNewDate, dtChange As Date
Dim sSQL As String

dtNewDate = DatePart("m",[Scheduled_Review_Date]) & "/1/2007"
dtChange = "12/28/2006"

sSQL = "SELECT * FROM myTable WHERE [Scheduled_Review_Date] <= #12/31/2006#"

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do Until rs.EOF
   rs("Scheduled_Review_Date") = dtNewDate
   rs("CheckBox") = True
   rs("DateChanged") = dtChange
   rs.Update
   rs.MoveNext
Loop
rs.Close

You may have to mess around with the SQL string syntax to get it to pull the proper records. So try this for your check:

Dim rs As ADODB.Recordset
Dim sSQL As String
Dim dtSample As Date

sSQL = "SELECT * FROM myTable WHERE [Scheduled_Review_Date] <= #12/31/2006#"

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

dtSample = rs("Scheduled_Review_Date")

MsgBox dtSample

rs.Close

This will let you tes the syntax and only pop up the first record in the recordset.

I bet one of the other posters will come up with a very elegant Update query though.

HTH



mmcdonal
  #5 (permalink)  
Old December 7th, 2006, 09:45 AM
Authorized User
 
Join Date: Aug 2006
Location: Hoffman Estates, IL, USA.
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Okay, I got the test code to work just fine, but when I go to run the real code, I get a run time '2465' error. It says it can't find the field '|' refered to in your expression and the line in question is:

Code:
dtNewDate = DatePart("m", [Scheduled_Review_Date]) & "/1/2007"

  #6 (permalink)  
Old December 7th, 2006, 09:49 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Dang *smacking forehead* of course you wouldn't get that because it hasn't pulled the date yet. Yeesh.

Add this:

Do Until rs.EOF
   dtNewDate = DatePart("m",rs("Scheduled_Review_Date")) & "/1/2007"

   rs("Scheduled_Review_Date") = dtNewDate
   rs("CheckBox") = True
   rs("DateChanged") = dtChange
   rs.Update
   rs.MoveNext
Loop

And remove the dtNewDate line
dtNewDate = DatePart("m", [Scheduled_Review_Date]) & "/1/2007"
from the top part of the code.

Dang.

mmcdonal
  #7 (permalink)  
Old December 7th, 2006, 09:55 AM
Authorized User
 
Join Date: Aug 2006
Location: Hoffman Estates, IL, USA.
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for all of your help. I realized I made a mistake in the information I gave you, but was able to fix it myself. I gave you the real date instead of the date_ID (because the tables are related), so it was throwing an error. Once I realized what I had done, it worked perfectly. Thanks for all of your help mmcdonal. I appreciate it.

Best Regards & Thanks again,
Arholly

  #8 (permalink)  
Old December 7th, 2006, 10:14 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

De nada. Anytime.

Hey, I got a post for that! =)

mmcdonal
  #9 (permalink)  
Old December 7th, 2006, 01:15 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

I'm curious as to why you're dealing with dates as strings:

dtNewDate = DatePart("m",rs("Scheduled_Review_Date")) & "/1/2007"

Instead of dates as DATES:

dtNewDate = DateSerial(2007, Month(Me.Scheduled_Review_Date), 1)


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
  #10 (permalink)  
Old December 7th, 2006, 01:19 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I can see the code is shorter...

dtNewDate = DateSerial(2007, Month(Me.Scheduled_Review_Date), 1)
versus
dtNewDate = DatePart("m",rs("Scheduled_Review_Date")) & "/1/2007"

No wait, its not! =)

That is the way the question was posed.


mmcdonal




Similar Threads
Thread Thread Starter Forum Replies Last Post
Forum Chapter 16 - changing the dates mr_badger BOOK: Beginning PHP5, Apache, and MySQL Web Development ISBN: 978-0-7645-7966-0 1 July 31st, 2008 07:50 PM
Updating Excel with Form fields nancy Classic ASP Professional 6 April 7th, 2006 02:18 PM
Changing textboxes to hidden fields - HELP!!! jroxit ASP.NET 1.0 and 1.1 Basics 4 February 10th, 2006 07:46 PM
Error when changing 'Link Child Fields' property JackNimble Access 3 October 25th, 2004 03:21 PM
Updating table fields ppenn Access VBA 2 September 20th, 2003 06:09 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.