adding date add to a field

shutzy

Registered User.
Local time
Today, 20:55
Joined
Sep 14, 2011
Messages
775
i have a tbl that stores items that have an expiry. in a child table i want to store the expiry. but as it is different for each item i need a way of doing this.

i thought that by having

DateAdd('m',3,Date()) in one of the fields this would work.

on click event i would like the date add to come into play but the way i am doing it comes up with an error. too few arguments.

this is what i have so far
Code:
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("tblClientCourse", dbOpenDynaset)
rs.AddNew
rs![OrdersItemsID] = [Forms]![frmClientSale-AddCourse]![OrdersItemsID]
rs![ClientID] = [Forms]![frmClientSale]![ClientDetailsID]
rs![CourseID] = [Forms]![frmClientSale-AddCourse]![txtCourseID]
rs![ItemsRemaining] = [Forms]![frmClientSale-AddCourse]![txtItemsRemaining]
rs![OrderID] = [Forms]![frmClientSale-AddCourse]![OrderID]
rs![DateExpiry] = [Forms]![frmClientSale-AddCourse]![lstCoursesAvailable].Column(5)
rs.Update
rs.Close
db.Close

this is the part that has the DateAdd
rs![DateExpiry] = [Forms]![frmClientSale-AddCourse]![lstCoursesAvailable].Column(5)

any ideas how i can do this?
 
DateAdd('m',3,Date())
Your equation contains single quotes but should be double quotes:
Code:
DateAdd("m",3,Date())
 
oh, if only that was it. thanks for pointing it out though. i not get an error of

data type conversion error

is what i am doing possible?
 
rs![DateExpiry] = [Forms]![frmClientSale-AddCourse]![lstCoursesAvailable].Column(5)

Then it is either DateExpiry or .Column(5) that is not a date data type. Are you sure .Column(5) contains your date? .Column(5) is actually the 6th column as the counting starts with 0.
 
yes i am sure that column 5 contains DateAdd("m",3,Date)). i have just doubled checked in the query builder of the list box and it definately shows it. are my " & correct. i havent used any because there are not any in the previous rs!.
 
If you take out the line:
rs![DateExpiry] = [Forms]![frmClientSale-AddCourse]![lstCoursesAvailable].Column(5)
does it then work?
 
Try
Code:
 = CDate([Forms]![frmClientSale-AddCourse]![lstCoursesAvailable].Column(5))
 
hi milhail, i have tried CDate and i now get a Type Mismatch. i dont know why im getting this as the field it is going to is Date/Time(Short Date).
 
If you could attach your DB I can take a look and see if I see anything preventing you from accomplishing your task.
 
Blaster, i get your point about calculations and i have stored calculations and duplicated data all over my database. the one simple reason is that if my item cost 10 today and i change it to 11 tomorrow then all the records that have the same data will automatically change. same with this instance really. if i want to change the expiry term of my item in the future then the clients that have had that item and has now expired may become valid again. i do need to store the value to each record.

i have thought about that this time. but cant do it.

thanks
 
ok ive stuck it into .mdb.

on open frmPasswordscreen will open. enter s4ut21 into the text box and press enter. ignore the error.

then open frmClientSale.
select a name and click 'Payment'
then on frmclientSalePaymentScreen click Add course. select a course item in the list box and click save. you should then get the error i am getting.

thanks
 

Attachments

You are misusing equations. You are attempting to store the equation as a text field in your table tblCourses as field ExpiryInterval. Equations are carried out in queries, forms, reports and VBA.
 
I have run this code for diagnostic:
Code:
Dim a As String
   a = [Forms]![frmClientsale-AddCourse]![lstCoursesAvailable].Column(5)
   Debug.Print a
   rs![DateExpiry] = CDate(" & [Forms]![frmClientsale-AddCourse]![lstCoursesAvailable].Column(5) & ")

where the last line is the line in trouble.
In the Immediate window the result is
DateAdd("m",6,Date())
So, in the column 5, your list contain this string, not a date.
You need to verify code that fill that lstCoursesAvailable list.
 
Last edited:
And the RowSource for that list box catch the field from table tblCourses.
So you need to check the code that fill the field ExpiryInterval in that table.
But, in that table, the field ExpiryInterval have a TEXT data type.
Is this OK ? I don't think so.
 
And I don't see any form that fill this table with data. Is it ?
 
sorry i havent responded for a couple of days. i know the rules for database build. not duplicating data, not storing calculated data. i have tried to follow them where ever possible but i have never understood why. i know that in effect, calculated data is duplicate data.( im expecting a raft of responses for this, i will not be offended).

milhail. the tbl is currently populated manually. we dont often make changes to these records and rarely add new to it so i thought i would do that some otherday when i have finished the fundamentals.

And the RowSource for that list box catch the field from table tblCourses.
So you need to check the code that fill the field ExpiryInterval in that table.
But, in that table, the field ExpiryInterval have a TEXT data type.
Is this OK ? I don't think so.

i didnt know any other way to get the expiry date of any given course from any given date. it seemed logical when i did it. i thought that if i referenced it it would work, but it seems a long way from that. i thought i would try having ExpiryInterval as "m" or "yyyy" and then in the next column have the numerical value. as you can see the numerical value is there in the next column but that didnt work either.

does anyone have any suggestions of how i can do this.

i have just thought about just storing the numerical value and do it all in days

so for one year it would be

DateAdd("dd"," & [Forms]![frmClientSale-AddCourse]![lstCoursesAvailable].Column(5) & ",Date())

im sure you guys know a better ways of doing this.
 
From what I see, the ExpiryInterval is always after 6 days.
If so, is no need to store this data. You can design a calculated field in the query/report to show this date.
On the other hand, if the interval is not constant (maybe 3 days, maybe 30) you must store THIS value in the table. Then use it (in query/form) to calculate the expiry date.
So, your ExpiryInterval field in the table must be of type NUMBER - Long
 
i know the rules for database build. not duplicating data, not storing calculated data. i have tried to follow them where ever possible but i have never understood why
Look to a real office. Think about of a lot of copies all around.
Seems to be OK because, if someone ask for some data you quickly find a copy, in a box of your desk, and you give to he.

But what if the original is changed ? And you forget to update all yours copies ? You will give to someone a wrong document, isn't it ?

On the other hand, if you keep only the original in only one place, when someone ask for data you use your xerox device and give that copy to him.
If the original is changed (even without your knowledge) you will provide the good data.

Think to queries as the xerox device.

Why to not store calculated data ? The same reason: If the original data are changed, yours calculated data will become out of date. Or you need to refresh all yours calculated data without forget one.

Hope this is a explanation for you.

Cheers.
 

Users who are viewing this thread

Back
Top Bottom