User inputted value within formulas

VITOPIEPOLI

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.

Brian Element Fri, 11/20/2020 - 06:58
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.

VITOPIEPOLI Fri, 11/20/2020 - 07:10

Hi Brian,
I got it now!
Thank you for your help, it works fine.

I will keep your suggestion to test the formulas as well.

Best regards,

Vito

majo2000 Thu, 11/28/2024 - 05:44

Hi, my error is similar. How can i fix it. The formula is task.AddExtraction dbName, "", "NUMBER=="& NR
NR is a variable. Could you help me?

Brian Element Thu, 11/28/2024 - 12:56

Hi maho2000,

Can you post the entire code for the task as I don't see any problem with this line.  Also is it giving you and error or just not outputting what you are expecting.  If you are getting an error, the error could actually be part of the task and be on a different line.

Thanks

Brian