Update a field (1 Viewer)

Jimg1976

Registered User.
Local time
Today, 10:02
Joined
Apr 17, 2019
Messages
47
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:02
Joined
Oct 29, 2018
Messages
21,357
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.
 

Jimg1976

Registered User.
Local time
Today, 10:02
Joined
Apr 17, 2019
Messages
47
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:02
Joined
Oct 29, 2018
Messages
21,357
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.
 

Jimg1976

Registered User.
Local time
Today, 10:02
Joined
Apr 17, 2019
Messages
47
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:02
Joined
Oct 29, 2018
Messages
21,357
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.
 

Jimg1976

Registered User.
Local time
Today, 10:02
Joined
Apr 17, 2019
Messages
47
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]);
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:02
Joined
Oct 29, 2018
Messages
21,357
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])
 

Jimg1976

Registered User.
Local time
Today, 10:02
Joined
Apr 17, 2019
Messages
47
theDBguy,

I can not thank you enough. I am going to try this now. I had the last day off work and swore not to open the computer. :D
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:02
Joined
Oct 29, 2018
Messages
21,357
theDBguy,

I can not thank you enough. I am going to try this now. I had the last day off work and swore not to open the computer. :D

Hi. No worries. Good luck. Please let us know how it goes...
 

Jimg1976

Registered User.
Local time
Today, 10:02
Joined
Apr 17, 2019
Messages
47
Hello theDBGuy,

Your code does work. Though, I only need it to update the last record inserted, in its current iteration it changes all Drop Date fields to one year. Where as I only need it to change the last one entered. I thought maybe adding the ID field from the table would help me with narrowing it, like so

SELECT tbl_incident.INCIDENT_ID, tbl_incident.Badge
FROM tbl_incident
WHERE (((tbl_incident.Date_Of_Incident)>=DateAdd("d",-91,Date())));


However, what I've tried keeps throwing errorswhen running the second query. The closest thing I have found is WHERE INCDIENT_ID = (max(INCIDENT_ID)). I continue to get "The Specified Field INCIDENT_ID could refer to more than one table listedn in your From clause of your SQL Statement. Thanks very much for all your help!
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 07:02
Joined
Oct 29, 2018
Messages
21,357
Hi. It’s late here now. I’ll take a look tomorrow.
 

Jimg1976

Registered User.
Local time
Today, 10:02
Joined
Apr 17, 2019
Messages
47
Hello theDBguy,

I think I may have figured this out.

Code:
UPDATE tbl_incident RIGHT JOIN qry_90Days ON tbl_incident.Badge=qry_90Days.Badge SET tbl_incident.[Drop Date] = DateAdd(IIf(IsNull([qry_90Days].[Badge]),"d","yyyy"),IIf(IsNull([qry_90Days].[Badge]),90,1),[Date_of_Incident])
WHERE Date_of_Incident=(SELECT MAX(Date_of_Incident) FROM tbl_incident);

This seems to work. Now, I am on to another issue! Thanks for all your help
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:02
Joined
Oct 29, 2018
Messages
21,357
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom