Double Quote Issue

leylalkan

Forums

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 Fri, 12/27/2019 - 09:02

I tried this one: acilirListeVerisi1  & ">" & """" & var & """"
Luckily it worked!
 

Brian Element Tue, 12/31/2019 - 10:45

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.

gabriel Thu, 03/18/2021 - 18:41

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

Brian Element Fri, 03/19/2021 - 06:39

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 & "' "


Function deals
Dim da As String
da = "20210129"
MsgBox (da)
 
dbName = "dbo.DEALS.IMD"
import = 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 & "' "
MsgBox import
Client.ImportODBCFile import
Client.OpenDatabase (dbName)
MsgBox "Exitoso"
End Function

gabriel Fri, 03/19/2021 - 15:44

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

Brian Element Fri, 03/19/2021 - 16:50

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

gabriel Fri, 03/19/2021 - 17:21

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
 

Brian Element Sat, 03/20/2021 - 06:44

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.