Vbscript Using Today And Db Month Field
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?
-
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 -
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 -
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 -
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 -
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 -
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 -
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 -
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 -
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 -
I appreciate the help. Thanks.
0
Vous devez vous connecter pour laisser un commentaire.
Commentaires
10 commentaires