strSQL doubt

[EL-g]

Hey guys,
I was wondering how could i replace this statement:

strSql = "SELECT Avg([Value]) AS [" & FileName & "_AVG] FROM [" & FileName & "] WHERE ((([" & FileName & "].Date) Between #" & mindate & "# And #" & maxdate & "#));"

By this one:

strsql="select avg[Value] as all_Average from (" & vbcrlf
for i= 0 to filename.getupperbound(0)
strsql&="select " & filename(i) & ".Value as Value from " & filename(i) & " where date between Between #11/6/2003# And #11/7/2003#" & vbcrlf
if i<>flename.getupperbound(0) then strsql &="union" & vbcrlf
next i
strsql &=")"

I still think im missing a few details on the second code.
the vbcrlf is a is a constant defined in vb.NET that represents the carriage return and the linefeed. I think i need to change this to access too........Im so lost here

Can anyone help ?

 

[EL-g]

so far my code looks like this

Function queryexists(tbl As String) As Boolean
On Error GoTo nofile
queryexists = CurrentDb.QueryDefs(tbl).Name = tbl
'this will be true, if table exists, or cause an error if it doesnt

exithere:
Exit Function

nofile:
queryexists = False
Resume exithere
End Function

******************************************************************
Sub Average_dynamic_loop()

Dim dbs As Database
Dim rs As Recordset
Dim qdf As QueryDef
Dim strSql As String
Dim mindate As String
Dim maxdate As String
Dim nFiles As Integer
Dim FileName As String
ReDim Files(1 To 10) As String
Const Path As String = "G:\xTemp\txt.NET\"

Set dbs = CurrentDb()

FileName = Dir(Path & "*.*")


Do While (Len(FileName) > 0)
nFiles = nFiles + 1
' If array is full...
If nFiles = UBound(Files) Then
' Make room for 10 more files
ReDim Preserve Files(1 To nFiles + 10)
End If
Files(nFiles) = FileName
'If no more files exist exit loop
If FileName = "" Then Exit Do
'Make FileName without .txt extension
FileName = Left(FileName, InStr(1, FileName, ".") - 1)
'Make The SQL query
'this is the new code

strSql = "select avg[value] as all_average from ("
For i = 0 To UBound(Files)
strSql = strSql & "select Value as Value from " & FileName(i) & " where date between Between #11/6/2003# And #11/7/2003#" & vbCrLf
If i <> UBound(Files) Then strSql = strSql & ")"
Else
strSql = strSql & "union" & vbCrLf
End If
Next i

'this is the old code

strSql = "SELECT Avg([Value]) AS [" & FileName & "_AVG] FROM [" & FileName & "] WHERE ((([" & FileName & "].Date) Between #" & mindate & "# And #" & maxdate & "#));"

Set rs = dbs.OpenRecordset(strSql, dbOpenSnapshot)

With dbs
If queryexists(FileName & "AVG") Then DoCmd.DeleteObject acQuery, FileName & "AVG" Else
Set qdf = .CreateQueryDef(FileName & "AVG", strSql)
DoCmd.OpenQuery FileName & "AVG"
'Close the query window
DoCmd.Close acQuery, FileName & "AVG"
End With
'Get next file
FileName = Dir
Loop
dbs.Close
qdf.Close
End Sub

 

[gemma-the-husky]

if you are tring to split a line of code over several lines you don't do it with vbcrlf (although vbcrlf IS the carriagereturn linefeed constant - if you enter it correctly it should capitalize the V)

to split the code you need an underscore

ie strsql = "first part " & _
"second part"

 

[EL-g]

Thanks for the Reply Gemma. Actually what im trying to do is that instead of creating a query for every table I have i wanted to creat one query with them all, because to build a dinamic query I must have one think in mind, The final query should be exactly the same as if I typed it. So i was trying to concatenate fixed text with string variables.
But im still confused with the variables so far.
The 1st code I posted is the one that creates a query for each table. The second one i think is somewhere close to the concatenate thing i had in mind.

I EDITED THE PREVIOUS POST AND ADDED THE OLD CODE AND THE NEW ONE SO YOU CAN SEE THE DIFFERENCE

 

[EL-g]

This is the code Im using now

Function queryexists(tbl As String) As Boolean
On Error GoTo nofile
queryexists = CurrentDb.QueryDefs(tbl).Name = tbl
'this will be true, if table exists, or cause an error if it doesnt

exithere:
Exit Function

nofile:
queryexists = False
Resume exithere
End Function
Sub Average_dynamic_loop()

Dim dbs As Database
Dim rs As Recordset
Dim qdf As QueryDef
Dim strSql As String
Dim mindate As String
Dim maxdate As String
Dim nFiles As Integer
Dim FileName As String
ReDim Files(1 To 10) As String
Const Path As String = "G:\xTemp\txt.NET\"

Set dbs = CurrentDb()

FileName = Dir(Path & "*.*")
mindate = "09/29/2006"
maxdate = "10/01/2006"

Do While (Len(FileName) > 0)
nFiles = nFiles + 1
' If array is full...
If nFiles = UBound(Files) Then
' Make room for 10 more files
ReDim Preserve Files(1 To nFiles + 10)
End If
Files(nFiles) = FileName
'If no more files exist exit loop
If FileName = "" Then Exit Do
'Make FileName without .txt extension
FileName = Left(FileName, InStr(1, FileName, ".") - 1)
'Make The SQL query
strSql = "SELECT Avg([Value]) as all_average FROM "
strSql = strSql & FileName & " where [" & FileName & "].Date Between #" & mindate & "# And #" & maxdate & "# "
If nFiles <> UBound(Files) Then Else strSql = strSql & "union"
'Get next file
FileName = Dir
Loop
a = MsgBox(strSql, 1, "A")
'Set rs = dbs.OpenRecordset(strSql, dbOpenSnapshot)

With dbs
'If queryexists(FileName & "AVG") Then DoCmd.DeleteObject acQuery, FileName & "AVG" Else
Set qdf = .CreateQueryDef("AVG", strSql)
DoCmd.OpenQuery "AVG"
'Close the query window
DoCmd.Close acQuery, "AVG"
End With

dbs.Close
qdf.Close
End Sub

Its returning only one value. I cannot figure out why. The value it returns is only from the last name on the array.

 

[EL-g]

ok.......i solved this one