Vbscript Using Today And Db Month Field S’abonner

0
Avatar
Legacy Poster

I am trying to generate an "Expiration Date" field using today's date and a database field which shows "Months".  An example would be today (07/06/16) + DB Months Field (2)=09/06/16.  Can someone help me with this script?

10 commentaires

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

You need to use the DateAdd function as follows

 

DateAdd(interval, number, date)

 

so you would end up with something like

 

Value = DateAdd("m",[DATABASE FIELD - double click on field to add this in automatically], Date)

 

 

where "m" signifies add months to Date which is today's date as specified by the computer clock. 

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

I entered the following:  Value = DateAdd("m",("Sheet1$.ShelfLifeMonths"),Date)) and received the following error: <Line 1: Value = Value = DateAdd("m",("Sheet1$.ShelfLifeMonths"),Date)): Expected end of statement>.

 

This is the same error that I have been receiving when I tried this before posting.  I am running version 9.2.  The Excel File field Sheet1$.ShelfLifeMonths is set to a numeric number for the months (ex. 18).  Is there something else I need to do to this VBscript?

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

There is an extra closing bracket after the Date at the end so the full line should be

 

Value = DateAdd("m",("Sheet1$.ShelfLifeMonths"),Date)

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

Copied and pasted your VBscript shown above and it is still not working.  Keep getting same error.  Not sure what to do now, may have to abandon the Excel field and just use a constant number.  This is not what I prefer since the number of months for different part numbers is not the same.  Let me know your thoughts.

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

Not sure.

 

To test it just try

 

Value = DateAdd ("m",18,Date)

 

and see if that adds 18 months to today's date

 

Are you using the VB script data source type rather than the More Options (now Transforms) VB option? If not change it to the Data Source type.

 

What you may also need to do if set this up as an Event Controlled Script > On New Record so the VB is processed whenever a new database record selection is made.

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

The Value = DateAdd ("m", 18, Date) works fine.  I am using the VB script and have the type set to Event Control scripts.  It just does not like pulling the number from the Excel file for some reason.  I guess at this point I am stuck with using the set number of months and not using the Excel field as a trigger.  Thanks.

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

You should be able to do it. Two things you could try are:

 

Add the line at the start

 

value1 = ,double click on your database field to add it in here>

 

then next add

 

Value = DateAdd("m", Value1, Date)

 

and see if that works. If not close the label and dont save it then reopen it with the old script.

 

Alternatively add on a text field that picks up the your month field from the database and in the Sample Data (I think it was called) box replace the "Field..,...." with say 18. From the Advanced button (I think, I cant remember 9.2 exactly) give this field a Share Name of DBMonth. You can drag this field off the side of the label so it doesn't print but it will still be able to be referenced by the VB below.

 

Next go back to your VB script in the other field and replace the original Value = DateAdd....  with the following

 

Value = DateAdd("m", <select the DBMonth from the shared sub-strings list on the bottom left> , Date)

 

and then try that.

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

It appears that the version we are using will not support any of these.  Since we are running a lot of seats of BarTender it is not cost effective to upgrade all of them at this time.  I am going to abandon trying to do the DB script and just use a number that we decide on.  I appreciate all of the help you have given me and I don't want to take anymore of your time.  Thanks.

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

I will be in at one my customer's sites in a few days and I know they have a 9.X version on one of their standalone units so if I get a chance I will try and mock something up linked to an excel spreadsheet and see if Ia can get this working as I am sure you should be able to do this but it could be have forgotten where everything is screen and tab-wise.

 

You could try using Now() instead of Date and this will return the Date and Time that the label is printed and so you would need to then adjust/truncate the field to just show the date part. 

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

I appreciate the help.  Thanks.

Vous devez vous connecter pour laisser un commentaire.