Hi,
I want to let users type 20181231, so I want to get the following via letting a variable be equal to that date users type.
task.AddExtraction dbName, "", acilirListeVerisi1 & ">" & " ""20181231"" "
That is if I let var=dlg.txt1, then how should I modify (task.AddExtraction dbName, "", acilirListeVerisi1 & ">" & " ""20181231"" ") so that I can still get the desired code when a user type 20181231 manually?
I tried this (task.AddExtraction dbName, "", acilirListeVerisi1 & "> "" " & var & " "" " ), but it didn't work. Any help would be appreciated.
leylalkan
Offline
Last seen: 4 years 1 month ago
Joined: 11/27/2019 - 03:43
I tried this one: acilirListeVerisi1 & ">" & """" & var & """"
Luckily it worked!
You could have also done
acilirListeVerisi1 & ">" & chr(34) & var & chr(34)
The chr(34) is the ASCII number for the double quotes, what you did is correct but sometime it is easier to write it this way.
Wow, yes this is way easier. Thanks
Hi i am trying to make a variable for a date: but it´s not working. May you guide me what i am doing wrong:
Sub Main
Call deals
End Sub
Function deals
Dim da As String
da = "20210129"
MsgBox (da)
dbName = "dbo.DEALS.IMD"
Client.ImportODBCFile "" & Chr(34) & "dbo" & Chr(34) & "." & Chr(34) & "DEALS" & Chr(34) & "", dbName, FALSE, ";DSN=Conexión SQL Server ibs all;Trusted_Connection=Yes;APP=IDEA;WSID=CM-DKCLPV2;DATABASE=IBS_HST", "SELECT * FROM " & Chr(34) & "dbo" & Chr(34) & "." & Chr(34) & "DEALS" & Chr(34) & " WHERE DATA_DT='da' "
Client.OpenDatabase (dbName)
MsgBox "Exitoso"
End Function
Hi Gabriel,
I think your problem is that your da variable is actually within the quotes instead of outside. The way you have it set up the WHERE DATA_DT ='da' is actually seeing da instead of "20210129". You need to close the quotes then use the da as it is a variable, also there were several quotes that needed to be double. Here is an update that will hopefully work. In the final I made it into a variable so you can view it and then adjust it if necessary.
Chr(34) & "dbo" & Chr(34) & "." & Chr(34) & "DEALS" & Chr(34) & """, dbName, FALSE, "";DSN=Conexión SQL Server ibs all;Trusted_Connection=Yes;APP=IDEA;WSID=CM-DKCLPV2;DATABASE=IBS_HST"", ""Select * FROM " & Chr(34) & "dbo" & Chr(34) & "." & Chr(34) & "DEALS" & Chr(34) & " WHERE DATA_DT='" & da & "' "
Thanks a Lot for your help i have one more question if i want to use this variable as global and be reconize by my other funtions what i have to do?
My Best Regards
Hi Gabriel,
You have to declare it before the sub main function. So if you had a string variable that will hold the file name you could do somthing like this:
Dim sFilename as string 'this variable is a global variable
sub main
sFilename = "This is my file.IMD" 'this variable is available in the entire script.
end sub
Thanks , i have one last question i am using the same variable as global but is not working there is another rule that i have to apply?
Function dealsp
Set db = Client.OpenDatabase("dbo.DEALS.IMD")
Set task = db.Extraction
task.IncludeAllFields
dbName = "DEALSP.IMD"
task.AddExtraction dbName, "", "DATA_DT_FECHA == """"&da&""""And. DEASTS <>""C"""
task.CreateVirtualDatabase = False
task.PerformTask 1, db.Count
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
End Function
If you have defined the variable before the sub main line then the problem is probably a lack of spaces in your line. &da means something different from & da &, make sure you use spaces between variables. You also have the wrong number of quotes on each side of the da. So your line should probably be:
task.AddExtraction dbName, "", "DATA_DT_FECHA == """ & da & """And. DEASTS <>""C"""
One way to check is make the string part equal to a variable and then use msgbox to view the contents of the variable, this way you can easily check the proper formatting.