quoting quotes

[tealeaf]

hi say i want to pass a string to another function ie
Call func(astr)
but astr looks like "..." & Chr(13) & "..."
so when it is passed Chr(13) is no longer part of the string.
basically i want the whole thing to act as one string called astr

[tealeaf]

maybe the code would clarify what i so incoherently am trying to ask. see COstr is a command for a query in SQL so i need the Chr(13) and the Chr(10)...

Sub theQuery()
COstr = "SELECT" & Chr(13) & "" & Chr(10) & "FROM"
Call QueryFour("D1", COstr)
End Sub

[dk]

On 2002-05-23 11:39, tealeaf wrote:
maybe the code would clarify what i so incoherently am trying to ask. see COstr is a command for a query in SQL so i need the Chr(13) and the Chr(10)...

Sub theQuery()
COstr = "SELECT" & Chr(13) & "" & Chr(10) & "FROM"
Call QueryFour("D1", COstr)
End Sub

Hi,

Sorry but I don't understand what you're trying to do. Why do you need to include the carriage return character (13) in an SQL query? There's no need for that if what you're trying to do is get the SELECT and the FROM on different lines.

Can you please clarify a little further?

Regards,
Dan

[tealeaf]

Sorry but I don't understand what you're trying to do.

that makes 2 of us
what i'm trying to do is pass the string COstr to another function. In this function, it will be used in an SQL query. kinda looks like
.Command = COstr

the reason i'm doing this, is i will be making several queries and if i just had one function called say MyQuery(...) then I could just pass it the different strings which all resemble my COstr example in terms of format.
thanks

[dk]

Sorry, but I'm still confused - mainly by this:-

so i need the Chr(13) and the Chr(10)...

Why are you using Chr(13) and Chr(10) in the first place? Is the string being displayed somewhere else first and are you using the CR and LF characters for neatness?

I think it's best if you post your code, what you would like to happen and indicate where in the code you are having the difficulty.

Dan

[tealeaf]

my code:
this is a query that works:

' date
date1 = Format([b1] & " 07:30", "yyyy-mm-dd hh:mm:ss")
date2 = Format([b1] + 1 & " 07:30", "yyyy-mm-dd hh:mm:ss")
datemid = Format([b1] & " 19:30", "yyyy-mm-dd hh:mm")
[b2] = date1
[c2] = date2



' QUERY FOR Perte de gaz CO
COstr = "SELECT GC_CO_Generation_H.Date, GC_CO_Generation_H.No_Fr, GC_CO_Generation_H.Vent, GC_CO_Generation_H.Supply" & Chr(13) & "" & Chr(10) & "FROM Opp.dbo.GC_CO_Generation_H GC_CO_Generation_H" & Chr(13) & "" & Chr(10) & "WHERE (GC_CO_Generation_H.Date>{ts '" & date1 & "'}) AND (GC_CO_Generation_H.Date<={ts '" & date2 & "'}) AND (GC_CO_Generation_H.No_Fr<>9)"
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=OPP;Description=OPP;UID=dumaisa;PWD=opp;APP=Microsoft® Query;WSID=WQITTRACONFDDP;DATABASE=OPP;Address=172.30.2.70,1433;UseP" _
), Array("rocForPrepare=0;QuotedId=No")), Destination:=Range("AA3"))
.CommandText = COstr
.Name = "Lancer la requête à partir de OPP"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

now this is the query i'm trying to get to work:

Sub theQuery()
COstr = "SELECT GC_CO_Generation_H.Date, GC_CO_Generation_H.No_Fr, GC_CO_Generation_H.Vent, GC_CO_Generation_H.Supply" & Chr(13) & "" & Chr(10) & "FROM Opp.dbo.GC_CO_Generation_H GC_CO_Generation_H" & Chr(13) & "" & Chr(10) & "WHERE (GC_CO_Generation_H.Date>{ts '" & date1 & "'}) AND (GC_CO_Generation_H.Date<={ts '" & date2 & "'}) AND (GC_CO_Generation_H.No_Fr<>9)"
Call QueryFour("D1", COstr)
End Sub

Sub QueryFour(mydstn As String, mystr As String)
'
' QueryFour Macro
' Macro enregistrée le 2002-05-23 par STAGIAIRE_DPF
'

'
Range("C:Z").Select
Selection.Delete
date1 = Format([b1] & " 07:30", "yyyy-mm-dd hh:mm:ss")
date2 = Format([b1] + 1 & " 07:30", "yyyy-mm-dd hh:mm:ss")
datemid = Format([b1] & " 19:30", "yyyy-mm-dd hh:mm")
[b2] = date1
[c2] = date2

' QUERY FOR Perte de gaz CO

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=OPP;Description=OPP;UID=dumaisa;PWD=opp;APP=Microsoft® Query;WSID=WQITTRACONFDDP;DATABASE=OPP;Address=172.30.2.70,1433;UseP" _
), Array("rocForPrepare=0;QuotedId=No")), Destination:=Range(mydstn))
.CommandText = mystr
.Name = "Lancer la requête à partir de OPP"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
end sub

thanks for your help
teal

[tealeaf]

format of my previous post was confusing...sorry think this might help

this is a query that works:

' date
date1 = Format([b1] & " 07:30", "yyyy-mm-dd hh:mm:ss")
date2 = Format([b1] + 1 & " 07:30", "yyyy-mm-dd hh:mm:ss")
datemid = Format([b1] & " 19:30", "yyyy-mm-dd hh:mm")
[b2] = date1
[c2] = date2

' QUERY FOR Perte de gaz CO
COstr = "SELECT GC_CO_Generation_H.Date, GC_CO_Generation_H.No_Fr, GC_CO_Generation_H.Vent, GC_CO_Generation_H.Supply" & Chr(13) & "" & Chr(10) & "FROM Opp.dbo.GC_CO_Generation_H GC_CO_Generation_H" & Chr(13) & "" & Chr(10) & "WHERE (GC_CO_Generation_H.Date>{ts '" & date1 & "'}) AND (GC_CO_Generation_H.Date<={ts '" & date2 & "'}) AND (GC_CO_Generation_H.No_Fr<>9)"
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=OPP;Description=OPP;UID=dumaisa;PWD=opp;APP=Microsoft® Query;WSID=WQITTRACONFDDP;DATABASE=OPP;Address=172.30.2.70,1433;UseP" _
), Array("rocForPrepare=0;QuotedId=No")), Destination:=Range("AA3"))
.CommandText = COstr
.Name = "Lancer la requête à partir de OPP"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

now this is the query i'm trying to get to work:

Sub theQuery()
COstr = "SELECT GC_CO_Generation_H.Date, GC_CO_Generation_H.No_Fr, GC_CO_Generation_H.Vent, GC_CO_Generation_H.Supply" & Chr(13) & "" & Chr(10) & "FROM Opp.dbo.GC_CO_Generation_H GC_CO_Generation_H" & Chr(13) & "" & Chr(10) & "WHERE (GC_CO_Generation_H.Date>{ts '" & date1 & "'}) AND (GC_CO_Generation_H.Date<={ts '" & date2 & "'}) AND (GC_CO_Generation_H.No_Fr<>9)"
Call QueryFour("D1", COstr)
End Sub

Sub QueryFour(mydstn As String, mystr As String)
date1 = Format([b1] & " 07:30", "yyyy-mm-dd hh:mm:ss")
date2 = Format([b1] + 1 & " 07:30", "yyyy-mm-dd hh:mm:ss")
datemid = Format([b1] & " 19:30", "yyyy-mm-dd hh:mm")
[b2] = date1
[c2] = date2

' QUERY FOR Perte de gaz CO

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=OPP;Description=OPP;UID=dumaisa;PWD=opp;APP=Microsoft® Query;WSID=WQITTRACONFDDP;DATABASE=OPP;Address=172.30.2.70,1433;UseP" _
), Array("rocForPrepare=0;QuotedId=No")), Destination:=Range(mydstn))
.CommandText = mystr
.Name = "Lancer la requête à partir de OPP"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
end sub

[tealeaf]

give it a try Ian? heehee

[Ian Mac]

On 2002-05-23 12:12, dk wrote:
Sorry, but I'm still confused - mainly by this:-

Why are you using Chr(13) and Chr(10) in the first place? Is the string being displayed somewhere else first and are you using the CR and LF characters for neatness?

I think it's best if you post your code, what you would like to happen and indicate where in the code you are having the difficulty.

Dan

Why are you using these?

Also, you haven't explained why it doesn't work, that is to say, is it query that doesn't work or the code?

If it's the code where does it breakdown?

If the Query, why not, just no data? not what you asked for?

These will be helpful in answering your problem.

Regards

[tealeaf]

Well the exact SQL request looks like:
SELECT GC_CO_Generation_H.Date, GC_CO_Generation_H.No_Fr, GC_CO_Generation_H.Vent, GC_CO_Generation_H.Supply
FROM Opp.dbo.GC_CO_Generation_H GC_CO_Generation_H
WHERE (GC_CO_Generation_H.Date>{ts '2002-05-09 00:00:00'}) AND (GC_CO_Generation_H.Date<{ts '2002-05-10 00:00:00'}) AND (GC_CO_Generation_H.No_Fr<>9)


i modified the dates to be variables.
the code in VBA of the same retrieval looks like:

Application.WindowState = xlMinimized
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=OPP;Description=OPP;UID=dumaisa;PWD=opp;APP=Microsoft® Query;WSID=WQITTRACONFDDP;DATABASE=OPP;Address=172.30.2.70,1433;UseP" _
), Array("rocForPrepare=0;QuotedId=No")), Destination:=Range("A4"))
.CommandText = Array( _
"SELECT GC_CO_Generation_H.Date, GC_CO_Generation_H.No_Fr, GC_CO_Generation_H.Supply, GC_CO_Generation_H.Vent" & Chr(13) & "" & Chr(10) & "FROM Opp.dbo.GC_CO_Generation_H GC_CO_Generation_H" & Chr(13) & "" & Chr(10) & "WHERE (GC_CO_Generation_H.Date>{ts '20" _
, _
"02-05-09 07:30:00'}) AND (GC_CO_Generation_H.Date<={ts '2002-05-10 07:30:00'}) AND (GC_CO_Generation_H.No_Fr<>9)" _
)
.Name = "Lancer la requête à partir de OPP"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub


I made the .Command = COstr
and COstr = "..."
The problem with my trying to pass the string, is i guess it's not coming out in the right format so it's not doing the retrieval properly and gives me an error in the last line of the VBA code.
better?