Todays Date + Access Database Number To Get Use By Date Help! Follow

0
Avatar
Legacy Poster


Hi and thank you for taking the time to help me. I have
never tried to VS Script before so any help will do.

I have a field name in access called Use_By and that has number
of days to use the product and each product has a different use by date (so
trying to use the date offset function will not work) for example

Penne 365

What I am trying to do is (with not much luck) is write a VB
script that will give me todays date + the Use_By number and get a date. For example
Penne 28 days + now = 5/27/14


I have tried Event Control

Value = Now + Field(“<fieldname>”)

I am getting this 12387099.3677894  BUT NEED this 5/27/14

So what am I missing

Help!  

 

thanks 

 

Brad

10 comments

0
Avatar
Fernando Ramos Miracle
Moderator
Comment actions Permalink

Hello BradG,

 

In order to sum a value to a date you'll need to use the "DateAdd()" function. For example, if you wish to add 28 days to today's date you would write:

 

DateAdd("d", 28, Date)

 

Where:

  - The "d" represents the "day" part of a date (while "m" represents the month).

  -  Date is the refers to the system date.

 

If you wish to use the value coming from your database you simply substitute the numeric value with a reference to your field:

 

DateAdd("d", Field(“<fieldname>”), Date)

 

*Also note that you'll need enter this code in the "OnNewRecord" event so it's applied for each new record with its new value.

0
Avatar
Legacy Poster
Comment actions Permalink

Frenando thanks for the script but its not working. 

here is what i did

new VB script, event control script, OnNewRecord

DateAdd("d",Field("Label_Record_Shelflife"),Date)  i also tried Value = DateAdd("d",Field("Label_Record_Shelflife"),Date)

i keep getting Script Error Found

OnNewRecord(line4): : Invalid procedure call or argument: 'DateAdd'

 

what am i missing????? 

0
Avatar
Fernando Ramos Miracle
Moderator
Comment actions Permalink

Hello Brad,

 

You certainly need to assign the value from the DateAdd function. When using "Value = DateAdd(...)" you should get what you require.

 

Not being that the case, there is probably something else going on. Could you attach a simple example of your document so I can take a look at it?

0
Avatar
Legacy Poster
Comment actions Permalink

Here is the label and i will send the data base next

0
Avatar
Legacy Poster
Comment actions Permalink

It wont let me send the access file??? any suggestions? i 

0
Avatar
Legacy Poster
Comment actions Permalink

I put them in dropbox see if that works for you?

 

https://www.dropbox.com/sh/9o3imaq1qwvg2jh/MZrNjf42eJ

0
Avatar
Fernando Ramos Miracle
Moderator
Comment actions Permalink

For now I don't think I need the actual database so don't worry.

 

Taking a look at your document I see two date objects both with a VB script data source:

 

 a. The first one uses a single line VB Script.

 

 b. The second one uses an event based VB Script. But I don't see the code applied on the "OnNewRecord" event.

 

Could you enter the function on that event with the reference to your database inside the DateAdd() function and send me this document?

0
Avatar
Legacy Poster
Comment actions Permalink

Sorry try this one

0
Avatar
Fernando Ramos Miracle
Moderator
Comment actions Permalink

Testing with your last document and database I've been able to get a successful print out with the applied date offset.

 

Make sure that you are printing all your records from the "File>Print..." dialog (you had it configured only to print two copies of your first record).

 

Also please ignore the initial error you get when entering the code on your event based VB script, it's probably the case that at that moment no data from the data base has been loaded and at that exact moment the database field "variable" is empty (thus giving you the DateAdd function error).

0
Avatar
Legacy Poster
Comment actions Permalink

thank you. i never checked to print it as the data on the screen was showing <empty> so i thought that was what i would get when printing. so thank you!!!

 

Brad

Please sign in to leave a comment.