Return the last day of the month by using database field Follow

0
Avatar
Zsolt Beregszaszi

Hi there,

I am trying to generate an "EXPIRY_DATE" field using today's date and an Oracle database field. The date parameter set by the user however depending on what the user set than I want to be returned always the last day of the month.

I'm trying to use the DateAdd(intervalnumberdate) however I always get an error.

I have got a database field type and field name is called "EXPIRY_DATE"

EXP = Field("sample_27-FEB-17.sample_27-FEB-17.EXPIRY_DATE")
Value = DateAdd("m",EXP, Date)

ERROR: OnProcessData (Line 1):  Illegal assignment: 'EXP'

Can someone help me with this script?

Thank you

9 comments

0
Avatar
Pete Thane
Comment actions Permalink

EXP is a function/command in VB and so you cannot use it to reference a field. Change the name to something else like EXPDate and that would be acceptable

0
Avatar
Zsolt Beregszaszi
Comment actions Permalink

I changed than I got another error:

EXPDate = Field("sample_27-FEB-17.sample_27-FEB-17.EXPIRY_DATE")
Value = DateAdd("m",EXPDate, Date)

OnProcessData (Line 2): Invalid procedure call or argument: 'DateAdd'

 

0
Avatar
Pete Thane
Comment actions Permalink

The problem is the ,Date part in DateAdd call.

However saying that I don't think you are going to get want you are looking for from this. 

Ignoring the last day of the month part for now, where does the date actually come from? Is this something like the users choose/enter a date on a Data Entry Form and then onto this you need to add X months where this value comes from your Oracle table?

0
Avatar
Zsolt Beregszaszi
Comment actions Permalink

Hi,

Yes, you are correct with a slightly correction this script should trigger to add X DAYS to be shown end of the month days and not X months. 

0
Avatar
Pete Thane
Comment actions Permalink

Okay it looks like you will need to use the DateSerial command for part of this but I am still unclear on where the dates are coming from:

"I am trying to generate an "EXPIRY_DATE" field using today's date and an Oracle database field."

- so for this you want to take the date of printing (ie Today's PC clock date) and it to a value read from an Oracle database?

- Does the Oracle table contain a value in days or months to add on as your last post seems to contradict your code from earlier

"The date parameter set by the user however depending on what the user set"

- Not sure what you mean by this, please can you explain. . 

than I want to be returned always the last day of the month."

- after all the above you then want the actual date shown to be the last day of the month calculated from all the above? 

 

 

0
Avatar
Zsolt Beregszaszi
Comment actions Permalink

- so for this you want to take the date of printing (ie Today's PC clock date) and it to a value read from an Oracle database?

+We have got a web based application which is for the user to select the product and the assigned shelf life days in the dropdown list. The date is coming from the Oracle table.

- Does the Oracle table contain a value in days or months to add on as your last post seems to contradict your code from earlier

+ This is contains only days. I was tring it to use with "d" but I got the "Invalid procedure call or argument: 'DateAdd'" error

- after all the above you then want the actual date shown to be the last day of the month calculated from all the above? 

+ Yes, it is correct. 

I have been trying to play around with the below:

Dim EXPDate As EndMonthDate
EXPDate = DateSerial(Field("sample_27-FEB-17.sample_27-FEB-17.EXPIRY_DATE",-1))

0
Avatar
Pete Thane
Comment actions Permalink

Okay, still not sure what you mean by the web application part, but I believe this should work:

 

  1. Add a Date text field onto the Template screen but off to the side of the label itself and so it will not print.
  2. Use the Change Data Source Name button to give this a name of EXPDate (as per the image below)
  3. Click the Transforms tab and the Offset option and offset the date by Days based on the Database field which should be your "sample_27-FEB-17.sample_27-FEB-17.EXPIRY_DATE" field)
  4. Next on your label add a new text field and make this a Visual Basic Script. You maybe able to use the Multi-line script option or if not Event Controlled Script>On Identical Copies I would suggest.
  5. Add the following code into the scripting box (if you used the Event Controlled Script option, you may want to add Value = Now() into the AutoSelected event option too)

Value = Format.NamedSubStrings("EXPDate").Value

Value1 = Year(Value)

Value2 = Month(Value)

Value = DateSerial(Value1,Value2,0)

 

  

0
Avatar
Pete Thane
Comment actions Permalink

Oops sorry that will give the last day of the preceding month, please amend the VB to include the DateAdd command shown below.

0
Avatar
Zsolt Beregszaszi
Comment actions Permalink

Hi Pete,

I have managed it to work with a slightly change. The Web application is a HTML website where the user can chose an item code which get feeds by Oracle database.
After your reply everything make sense to me so I did the followings.
1, I already had a text box as an object value which linked to the "sample_27-FEB-17.sample_27-FEB-17.EXPIRY_DATE" field.
2, As you suggested I created a text box and I used your VB script with a little change to do the Format.Substring for a Format.Objects

Value = Format.Objects("BBDATE").Value Value = DateAdd ("m",1,Value)
Value1 = Year(Value)
Value2 = Month(Value)
Value = DateSerial(Value1,Value2,0)


Everything is working as I expected. I much appreciate your help in this case.

Many many thanks

Regards,

Zsolt

Please sign in to leave a comment.