User inputted value within formulas
Forums
I am trying to wrap up some inputted value within a formula using the following bit.
Hi there,
I have two bits:
1) This works fine
Function Starters
Set db = Client.OpenDatabase("PAYROLL_DATABASE_COMPLETE.IMD")
Set task = db.Summarization
task.AddFieldToSummarize "PERSON_REFERENCE"
task.AddFieldToSummarize "SHORT_DESCRIPTION"
task.AddFieldToTotal "VALUE"
task.Criteria = "DATE_STARTED > @Ctod(""&dYear&"",""ddmmyyyy"")"
dbName = "Starters.IMD"
task.OutputDBName = dbName
task.CreatePercentField = FALSE
task.StatisticsToInclude = SM_SUM
task.PerformTask
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
End Function
2) This does not work
Sub Main
Dim dYear As String
Dim dYearend As String
dYear = InputBox("Insert date to query from (i.e. 01012019")
dYearend = InputBox("Insert end period to query (i.e. 31122019")
Set db = Client.OpenDatabase("2019-Database.IMD")
Set task = db.Extraction
task.IncludeAllFields
dbName = "StartersLatePay.IMD"
task.AddExtraction dbName, "", "@BetweenDate(DATE_STARTED, @Ctod(""&dYear&"",""ddmmyyyy""), @Ctod(""&dYearend&"",""ddmmyyyy"")) "
task.CreateVirtualDatabase = False
task.PerformTask 1, db.Count
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
End Sub
The issue is that it give me an empty extraction but It work if I substitute the variable with a fixed parameter.
I tried to find the solution searching for it but did not find anything useful.
Where is the error?
Many thanks for your help.
Hi Vitopiepoli
There are a two things that are wrong with your equation, first of variables like the dYear and dYearend need to be outside of your equation, in your case they are still within your equation, a good way to check is to make your equation equal to a variable like eqn and then use a msgbox to view the contents. So if you tried this you can see what the problem is:
eqn = "@BetweenDate(DATE_STARTED, @Ctod(""&dYear&"",""ddmmyyyy""), @Ctod(""&dYearend&"",""ddmmyyyy"")) "
msgbox eqn
You would see that your equation is:
@BetweenDate(DATE_STARTED, @Ctod("&dYear&","ddmmyyyy"), @Ctod("&dYearend&","ddmmyyyy"))
So you can see this is why you are getting no results. The first thing you need to do is make sure the variables are outside of the string, you do that by adding on a double quote to close and then reopen the string and you need spaces around the &, if you don't do this IDEA will interpret this as you defining the variable or just giving you an error. Your new equation should look like:
"@BetweenDate(DATE_STARTED, @Ctod(""" & dYear & """,""ddmmyyyy""), @Ctod(""" & dYearend & """,""ddmmyyyy"")) "
Hopefully this will fix your problem.