Argument from dialog box not read correctly within extraction function

Pawelski_33

Forums

I am having  an issue with the argument passed by the user through the dialog box to be used in later stage of the code execution. Below is the call to the bespoke extraction function that i created:
Call extraction_selectedfields_and_export(TargetDb,"Sales journals","@Isini(""Revenue""," & UserChoice(3) & ")",Arr_FieldToInc(),FieldToInc)
The problem occurs with the criteria line for AddEtraction part which includes function Isini:
1. "@Isini("& UserChoiceRecords(0) &"," & UserChoice(3) & ")"
Where:
UserChoiceRecords is the value from FS_CATEGORY field selected by the user within dialog box (should be Revenue)
 
UserChoice is the field for FS_CATEGORY selected by the user within the dialog box
Unfortunately the bit "& UserChoiceRecords(0) &" is not recognised by the IDEA despite the fact that the msgbox which I placed immeditely before the function call shows the correct value of "Revenue". It must be something to do with the quotation marks around userchoice record but i pretty much tried every combination (including double quotation marks).
The only way it works is when I hard code 'Revenue' as a string to be looked for (as per below):
"@Isini(""Revenue""," & UserChoice(3) & ")"  
 

Brian Element Thu, 08/16/2018 - 07:17

I just tried out a scenario and I got it working: 


Option Explicit

Sub Main
	Dim TargetDb As String
	Dim Arr_FieldToInc(1) As String
	Dim FieldToInc As String
	Dim UserChoice(3) As String
	Dim UserChoiceRecords(3) As String
	TargetDb = "Database"
	Arr_FieldToInc(0) = "This"
	Arr_FieldToInc(1) = "That"
	FieldToInc = "The other thing"
	UserChoiceRecords(0) = "Revenue"
	UserChoice(3)  = "Helpme"
	Call extraction_selectedfields_and_export(TargetDb,"Sales journals", "@Isini("& UserChoiceRecords(0) &"," & UserChoice(3) & ")" ,Arr_FieldToInc(),FieldToInc)
	
End Sub

'eqn = "@Isini(""Revenue""," & UserChoice(3) & ")"


Function extraction_selectedfields_and_export(TargetDB As String, sName As String, eqn As String, FirstArray() As String, sField As String)
	MsgBox eqn
End Function

What I would probably try is instead of putting the equation in the Call I would create a variable like eqn as a string and make it equal to your equation such as:

eqn = "@Isini("& UserChoiceRecords(0) & "," & UserChoice(3) & ")"

Call extraction_selectedfields_and_export(TargetDb,"Sales journals", eqn, Arr_FieldToInc(), FieldToInc)

Then you can test with a message box if the eqn is proper.

Good luck.

Brian

Pawelski_33 Mon, 08/20/2018 - 12:10

Thank you Brian.
I sat together with my friend and he actually found a solution to the above and it is surprisingly  easy.
Essenitally argument passed from the dialog box which in my case is shown in the below expression:
 "& UserChoiceRecords(0) & " 
is recognised by the IDEA as a string 'Revenue'.
However as I am passing it again as an argument within equation that expression needs to be passed with double quotes therefore the final solution is:
"""& UserChoiceRecords(0) & """
and it works perfectly.
 
So final answer to my call funtion is:
Call extraction_selectedfields_and_export(TargetDb,"Sales journals","@Isini(""" & UserChoiceRecords(0) & """," & UserChoice(3) & ")",Arr_FieldToInc(),FieldToInc) 
Hope it helps others who came accross similar issue.
Thanks