Fixing Date formatting

E.Heizyk

Forums

I am building quite the long script but I will outline the current issue I am having and paste in the relevant code sections:
I am appending a file with the age of individuals as of the year end.  The year end date is input by the user into a dialog box, once ok is selected, I want a number of functions to run based on various inputs into the dialog box.
I am getting an error saying -Paremeter not date.  I have a number of fields that are dictated by this date, so I am hoping one fix will remove errors in all of the functions. 
Here are sections of my code:
Dim sYEDate As String
Dim dYEDate As Date
'supply mask for text boxes
dlg.uYearEnd = "YYYY/MM/DD"
Case "uYearEnd"
sYEDate = dlg.uYearEnd
'validate that it is a valid date
If Not IsDate(sYEDate) Then
MsgBox "Year End Date is in the incorrect date.  Please enter again using YYYY/MM/DD"
bError = true
End If
'convert date as a string to a date expression
dYEDate = CDate(sYEDate)
Case "OKButton1"
'Append census with the age field
Call AppendFieldForAge
 
 
'Append database to calculate the age at year end
Function AppendFieldForAge
Set db = Client.CurrentDatabase()
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "AGE_AT_YE"
field.Description = "Calculate age as of Plan Year End"
field.Type = WI_VIRT_NUM
field.Equation = "@age(""&dYEDate&"",DOB)/365"
field.Decimals = 0
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Function

Brian Element Sat, 12/15/2018 - 14:07

Hi E.Heizyk,

When using dates within the equation editor they need to be strings and not not date variables.  Using the date variable would be for performing calculations in the script.  Also the date format needs to be in the following format for the equation editor to use is YYYYMMDD, so any dates you obtain from a dialog need to be formatted as YYYYMMDD to use.  Here is an example with an input box using the Sample-Bank Transactions.IMD file.


Dim sCurrentDate As String

Sub Main
	sCurrentDate = InputBox("Select current date", "Select current date", "YYYYMMDD")
	Call AppendFieldForAge()
End Sub

'Append database to calculate the age at year end
Function AppendFieldForAge
	Set db = Client.OpenDatabase("Sample-Bank Transactions.IMD")
	Set task = db.TableManagement
	Set field = db.TableDef.NewField
	field.Name = "AGE1"
	field.Description = ""
	field.Type = WI_VIRT_NUM
	field.Equation = "@age(""" & sCurrentDate & """ , DATE ) / 365"
	field.Decimals = 2
	task.AppendField field
	task.PerformTask
	Set task = Nothing
	Set db = Nothing
	Set field = Nothing
End Function

E.Heizyk Mon, 12/17/2018 - 19:33

Brian, Thanks for the help, I got rid of my date object and made sure the date is being entered in the correct format, however I am still getting the error.  I have attached my script and a test file I have been using.  Any further help as to why I am still getting the date error is appreciated! Thanks!

Brian Element Mon, 12/17/2018 - 19:45

I just had look at your script and see a few problems.  When ever you have a variable you are missing one set of double quotes and you need to have spaces.  So something like this:

(""&sYEDate&"",  should actually be:

(""" & sYEDate & """, if you don't have spaces between the & it is possible it means something else.  Also the "" quotest together equals one " in the equation but you need an additional quote to close the string, insert the variable and the another " to open the string again.

When I fixed things up I am getting an error that the variable dYEDate has not be defined and I couldn't see where you are getting it in the script.  Have a look at these and maybe this will fix some of the problems.

Thanks

Brian