Update Query

[dbmanalser84]

I'm building car shop database and in one of my tables (Cars) I have dteManufacturingDate field. I want to update dteWarrantyDate field which should be calculated as dteManufacturingDate + 5 years. Now I did it like this: in the query builder my Field has value of: dteWarrantyDate: [dteManufacturingDate]+[Year(5)], Append has value dteWarrantyDate. When I run the Update Query I'm being prompted for the Year(5) value. No mater what I type the query runs and it doesn't update anything, it says something about 10 records (I have them in this table) but when I open the table my Dates are not updated the field is empty. I tried looking in Access Help for Update Query but couldn't find answers. Can someone explain this better on my example here, and say something else that I would need to know when I build Update queries?

I preffer to know what value Field must have (what should syntax look like, which brackets and how I use when I put fields that contain text and numbers), and what Append To reffers to, also when to use Criteria field, etc.? Thanx.

 

[boblarson]

If it is always 5 years, you don't need to store the calculated value. In fact, that would be discouraged due to normalization rules, since it can be calculated at any time using the DateAdd function.

 

[dbmanalser84]

Thnx

If it is always 5 years, you don't need to store the calculated value. In fact, that would be discouraged due to normalization rules, since it can be calculated at any time using the DateAdd function.

Thanx for your answer but you din't really answered my question. I tried to add it as default value but it didn't work so I want to do this. Can you also explain this DateAdd functin, how do I use it and where to find it?

 

[boblarson]

The date add function has the syntax of:

DateAdd(DatePartAsString, QtyOfDateParts, DateToAddTo)

So, for example if you used this in a query you can use:

MyNewDateField:DateAdd("yyyy", 5, [YourDateField])

As a control source on a text box it would be:

=DateAdd("yyyy", 5, [YourDateField])

but you can't have the control name the same as the field name in this instance or it will generate a #NAME error.

 

[dbmanalser84]

what

What should be the value of "yyyy" (which year)?

 

[boblarson]

No, you don't replace "yyyy" you use it as shown to tell Access that it is 5 YEARS that you are adding. You can also use:

"d" to mean days
"m" to mean months
"q" to mean quarter
"h" to mean hours
"n" to mean minutes (yes, note that I used an N)
"s" to mean seconds

 

[dbmanalser84]

Can...

In which type of query do I use the date add function Update or Append? Can MyNewdateField correspond to an existing field in database (where the value will be stored). Which field should I put in YourDateField (because I have several in my table) the one I wish to store the date or the one I'm pulling the date from and adding 5 Years to that date?

 

[boblarson]

Again I will say it. You do not need an Update query because you should NOT be storing this value. You use the above function to present it when needed but you do NOT need to store it as it violates normalization rules and there doesn't seem to be any overriding need to actually store the calculated value. So you would use it in a SELECT query.

 

[dbmanalser84]

Again I will say it. You do not need an Update query because you should NOT be storing this value. You use the above function to present it when needed but you do NOT need to store it as it violates normalization rules and there doesn't seem to be any overriding need to actually store the calculated value. So you would use it in a SELECT query.

Ok, thanx for answering. I'll present it in the SELECT query.