Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-17-2019, 11:33 AM   #1
Jimg1976
Newly Registered User
 
Join Date: Apr 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Jimg1976 is on a distinguished road
Cool Update a field

Let me preface this by saying thank you in advance, I got stuck with a project at work and know next to nothing about Access programming and am trying to fumble through it.
My Tables are:

Badge
Date_Of_Incident
Description
Code
Comment
Points
Drop Date

Here is what I need.

The Drop Date field should be 90 Days from the date of Incident (I've got that working). UNLESS there is another incident in the last 90 days, then it has to be 1 year from the date of the latest incident.
I am lost with respect to the one year date. Any advice would help.

Jimg1976 is offline   Reply With Quote
Old 04-17-2019, 11:44 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,446
Thanks: 19
Thanked 362 Times in 354 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Update a field

Hi. Welcome to the forum! How are you calculating the 90 days part? If you're using the DateAdd() function, you should be able to use it for calculating the year as well.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 04-17-2019, 11:51 AM   #3
Jimg1976
Newly Registered User
 
Join Date: Apr 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Jimg1976 is on a distinguished road
Re: Update a field

Hello theDBguy,

I have a simple expression of [Date_of_Incident]+90. However, I need to see if another record in the table exists in the last 90 days, and if it does I need the Drop Date to be 1 year. I hope I am explaining this clearly.

Jimg1976 is offline   Reply With Quote
Old 04-17-2019, 11:57 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,446
Thanks: 19
Thanked 362 Times in 354 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Update a field

Quote:
Originally Posted by Jimg1976 View Post
Hello theDBguy,

I have a simple expression of [Date_of_Incident]+90. However, I need to see if another record in the table exists in the last 90 days, and if it does I need the Drop Date to be 1 year. I hope I am explaining this clearly.
Can you show us the SQL statement for your query? One possible approach is to use a subquery or use the DCount() function.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 04-17-2019, 12:03 PM   #5
Jimg1976
Newly Registered User
 
Join Date: Apr 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Jimg1976 is on a distinguished road
Re: Update a field

Hi theDBguy,

To be honest, I was just stumped on where to go next. Thanks for the tip. I will start looking into building an SQL statement.
Jimg1976 is offline   Reply With Quote
Old 04-17-2019, 12:15 PM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,446
Thanks: 19
Thanked 362 Times in 354 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Update a field

Quote:
Originally Posted by Jimg1976 View Post
Hi theDBguy,

To be honest, I was just stumped on where to go next. Thanks for the tip. I will start looking into building an SQL statement.
Oh, I thought you had a query already. I see now you were using a calculated field in your table. In your case, I think it would be better/easier to use a query instead.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old Yesterday, 11:34 AM   #7
Jimg1976
Newly Registered User
 
Join Date: Apr 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Jimg1976 is on a distinguished road
Re: Update a field

Hello theDBguy,

I have my general update query working. The field (Drop Date) generally should be 90 Days from the date of incident. (What I still need to figure out, however, is if there is a record for the Badge within 90 days of the Date Of Incident. If there is, I need the Drop Date field to update to 365 days. Am I explaining this well? I don't really feel I am.

Thanks for all of your advice so far!


This is works without checking for previous records in tbl_incident

UPDATE tbl_incident SET tbl_incident.[Drop Date] = DateAdd("d",90,[Date_of_Incident]);

Jimg1976 is offline   Reply With Quote
Old Yesterday, 11:56 AM   #8
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 1,446
Thanks: 19
Thanked 362 Times in 354 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Update a field

Quote:
Originally Posted by Jimg1976 View Post
Hello theDBguy,

I have my general update query working. The field (Drop Date) generally should be 90 Days from the date of incident. (What I still need to figure out, however, is if there is a record for the Badge within 90 days of the Date Of Incident. If there is, I need the Drop Date field to update to 365 days. Am I explaining this well? I don't really feel I am.

Thanks for all of your advice so far!


This is works without checking for previous records in tbl_incident

UPDATE tbl_incident SET tbl_incident.[Drop Date] = DateAdd("d",90,[Date_of_Incident]);
Hi. I am thinking something along these lines:
Code:
UPDATE tbl_incident 
RIGHT JOIN NewQueryNameHere 
  ON tbl_incident.Badge=NewQueryNameHere.Badge 
SET tbl_incident.[Drop Date] = DateAdd(IIf(IsNull([NewQueryNameHere].[Badge]),"d","yyyy"),IIf(IsNull([NewQueryNameHere].[Badge]),90,1),[Date_of_Incident]);
(untested)
NewQueryNameHere would be a new query you'll make to list the Badges with incidents within the last 90 days. It might look something like this:
Code:
SELECT DISTINCT Badge FROM tbl_incident WHERE Date_of_Incident >= DateAdd("d",-90,Date())
Hope it helps...


PS. I thought you wanted to make the due date 1 year from the incident. If you really want 365 days, you could try it this way instead:
Code:
SET tbl_incident.[Drop Date]=DateAdd("d",IIf(IsNull([NewQueryNameHere].[Badge]),90,365),[Date_of_Incident])

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Reply

Tags
access

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sql Update vba access query to update table field with Multivalue Field mukeshpiplai Forms 1 11-09-2016 09:42 AM
Update query criteria compare a field in another table and update accordingly AccessBee Queries 11 06-29-2015 03:34 PM
How to update a field in main form on double clicking a field in subform?? captgnvr General 1 07-17-2010 06:20 AM
how can you automatically update a field in the table using a prior field entry? nitinrao Tables 1 02-06-2010 07:10 AM
Update logic field with Update query staple Queries 1 11-08-2008 08:06 AM




All times are GMT -8. The time now is 01:09 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World