ansaurus

Question

MS Access : How do I update a field using two other fields?

Answer 1

+6 A:

Do not use days, use DateAdd function or DateSerial

DateSerial(Year(PurchaseDate)+WarrantyTime, Month(PurchaseDate), Day((PurchaseDate))

DateAdd("yyyy", WarrantyTime, PurchaseDate)

See: http://www.techonthenet.com/excel/formulas/dateadd.php

Remou 2010-05-06 15:07:54

Yes. A much better solution - adding 365 days per year doesn't work if there is a leap year involved.

CodeSlave 2010-05-06 18:50:37

Answer 2

A:

Assuming that you have a form you can add the following code to the AfterUpdate events associated with the controls for PurchaseDate and WarrantyExpiry:

Me.WarrantyExpiry.Value = DateAdd("d", Me.WarrantyTime.Value * 365, Me.PurchaseDate.Value)

If you want to run a query you can use the following similar sql:

UPDATE Table1 SET Table1.WarrantyExpiry = DateAdd("d",[WarrantyTime]*365,[PurchaseDate]);

tchester 2010-05-06 15:10:13

Years and days are not an exact match. days should not be used to represent years.

Remou 2010-05-06 15:14:33

Thank you for the code samples. Being a bit of an access newbie - where should I actually put the code?, I will need to store it because people will need to run reports from it on a regular basis. Also, will the 'Warranty Expiry' field simply just get updated or will it need some kind of button trigger it ?

Scott Jackson 2010-05-07 06:49:40

You do not need to store the code, you use the notes I provided to create a query that includes Warranty expired as a calculated column. The query can then be used for reports. Furthermore, you can include a caluclated control on any forms that need to show Expired. This will also be in keeping with HansUp very good (and strongly voted) advice. I suggest you read http://r937.com/relational.html or any other article on relational database design before you get caught up in too much grief.

Remou 2010-05-07 08:27:42

Hmmm...ok thanks. I thought it would be much easier to do it in Access but I'll explain in a little more detail to see if there is an easier way. The access database is basically a front end to data coming from an SQL Server database. Would it be better to put something in place on the SQL side ?

Scott Jackson 2010-05-07 09:35:24

To make things a little more complicated, we have a helpdesk system which looks at the values of the database on a regular basis so the information needs to be there all of the time, it won't be able to rely on running a report about it as people won't have a clue that the data they are seeing is from the SQL database and not the helpdesk. I can cope with an sql query which needs to be manually started once a week or month as long as when it is run, the data stays in the 'Warranty Expiry' field.Hope this makes sense. Thank you for all your help.

Scott Jackson 2010-05-07 09:35:53

Last two comments go together....<limit>

Scott Jackson 2010-05-07 09:36:22

If you have a warranty expired field in an SQL Server database that needs to be updated, have you considered a trigger? I think it would be safest. Otherwise, is a view in SQL Server possible? Another possibility is a pass-through query in Access, but this is the least desirable solution.

Remou 2010-05-07 10:19:32

Could a query be made in SQL which would (when manually started) go and do everything you set it to do? It wouldn't need to be automatic. I could then change it if necessary on a monthly/ annual basis.

Scott Jackson 2010-05-07 10:52:38

If, by "everything", you mean update the expired field (column), certainly. If you have a larger definition of "everything", possibly.

Remou 2010-05-07 11:16:58

Yes, basically populate the 'Warranty Expiry' field with data from the other two (Warranty period and purchase date).

Scott Jackson 2010-05-07 12:24:45

The query above with a little change `UPDATE Table1 t SET t.WarrantyExpiry = DateAdd("yyyy",t.[WarrantyTime],t.[PurchaseDate])` should suit in either SQL Server or Access, AFAIK (http://msdn.microsoft.com/en-us/library/ms186819.aspx). You can either paste it into the query design window in Access or SSMS or such like in SQL Server, or you can run it through VBA or a macro in Access.

Remou 2010-05-07 13:02:10

What is the 't' for ?. Thank you for all this by the way.

Scott Jackson 2010-05-07 13:28:24

`t` is an alias and makes life easier, in a longer form you can say `FROM tablewithaverylongandinconvenientname AS a` which makes typing out the field (column) selection much more pleasant and it is simple to change the table with out messing with fields (columns).

Remou 2010-05-07 13:37:52

Sorry to be a pain (its all new to me), do I have to do something with alias first or should that statement just work as is ? Its coming up an error on the 't' when I've tried it (incorrect syntax).

Scott Jackson 2010-05-07 13:44:29

So if the field names are : [Purchase Date], [Warranty Period] (which is 1=365 days, 2=730, etc) and [Warranty Expiry]....What would the SQL query look like not using any alias's?

Scott Jackson 2010-05-07 13:59:23

Why does 1=365? What do you do in leap years? Why not store 365 if 1=365? What have you got against aliases?

Remou 2010-05-07 14:05:54

Please post the actual SQL you used and say whether it was in SQL Server or Access.

Remou 2010-05-07 14:06:38

I've got nothing against aliases - I just don't know how to use them :) The 1=365 is so that [Purchase Date] + [Warranty Time] = [Warranty Expiry]. So.... 01/01/10 + 1 (which would be 365 days) = 01/01/11, or 01/01/10 + 2 (which would be 730 days) = 01/01/12. I'm not too bothered about leap years as it only an approx we need.

Scott Jackson 2010-05-07 14:17:01

Please post your SQL. You should be adding years, as I have mentioned, not days.

Remou 2010-05-07 14:36:59

If you don't know how to use aliases, then you need to learn. In my humble opinion, if you don't know how to do something that basic, you don't know enough to query any database.

HLGEM 2010-05-07 17:54:37

@HLGEM: while what you say may be true of other databases, it's not true of Access, which provides a Query By Example interface that allows you to do aliases without knowing anything about SQL. While knowing SQL is incredibly useful in a bazillion different ways, it's not required to get useful work done in Access.

David-W-Fenton 2010-05-07 19:15:08

I've used Access and I would not allow anyone to touch any database of mine if they used the QBE form. If you don't know what you are doing, it is too dangerous to let you touch my database.

HLGEM 2010-05-07 19:43:25

Well, I'll know never to apply for a position with you, @HLGEM. Frankly, I just don't understand the hostility to tools that make things easier. This pseudo-macho tendency of so many to want to write their SQL by hand just makes me think these people have psychological problems.

David-W-Fenton 2010-05-09 00:40:26

@HLGEM....I can please only one person per day. Today is not your day. Tomorrow isn't looking good either. If you haven't got anything to say which would help me, kindly go away.

Scott Jackson 2010-05-10 06:31:18

Answer 3

+5 A:

Do you have a compelling need to actually store values for Warranty expiry?

The reason I asked is because storing derived values is generally considered bad practice. You could easily derive Warranty expiry from Purchase date and Warranty time whenever you need it. If you store Warranty expiry, you need to make sure the value gets updated whenever Purchase date and/or Warranty time change. You can use methods to minimize the risk the values can get out of sync, but you don't need any of those extra efforts if you're not storing the value in the first place.

HansUp 2010-05-06 17:30:41

ansaurus

tags:

views:

answers:

MS Access : How do I update a field using two other fields?

related questions