Using The Results of a Select Query To Provide Value For Variable

[RunTime91]

Howdy...

Not sure I'm in the right place for this one guys & gals - So if I am lost please kindly redirect - Thanks

I'm developing a SQL select statement in Excel to provide a value for a variable used in a second SQL select statement.

My approach to this is to get both SQL statements to work and then combine them...They both work but trying to combine them has led me here

Below is my first SQL statement - Which, when test separately works perfectly as it places the TOP (1) or (2) or (3) in my excel sheet

StrSQL1 = "SELECT TOP (1) [BatchID]" & _
                "FROM [MyServer].[Schema].[TblData]" & _
                "WHERE BatchID >= '20190114_050000 AND BatchID<= '20190114_060000'"

What I'm trying to design here is a way to pull just the first data load in the 5 AM hour

where, depending on the day and volume can be 3-4 runs at various times within the hour.



The below also works perfectly as it places the entire RecordSet in my excel sheet as well...

StrSQL2 = "SELECT  [ReportDate], [SalesType], [TotalSales]" & _
                "FROM [MyServer].[Schema].[TblData]" & _
                "WHERE BatchID = '20190114_052356'"

As you can see the immediate above is hard coded

What I'm trying to get to work is something like the below

StrSQL2 = "SELECT  [ReportDate], [SalesType], [TotalSales]" & _
                "FROM [MyServer].[Schema].[TblData]" & _
                "WHERE BatchID = 'StrSQL1' "

I've tried several variations of assigning the RecordSet value of StrSQL1 to a variable (Dim'ing Strings, Collections, Etc) then replacing the hard coded BatchID with the Variable

And, well, I'm here so you know how well that's working...

Thanks As always for any help

RT91

[ranman256]

you could use a form with 4 text boxes..
txtDate
txtStart hour
txtEnd hour
txtTop# (manual)

a timer could run the query 2 or 3 times an hour
every top of hour, it resets the boxes
txtDate = date
time = 6am

txtStart

= 5 am
txtEnd = 6 am

select * from table where

WHERE BatchID >= '" & txtDate & "_" & txtStart & "' AND BatchID<= '" &

txtDate & "_" & txtEnd & "'"

[RunTime91]

Hey Ran...

Genius Solution and something I can use in another project I'm working on - Would have never thought of that...

But for this project, I'm kinda looking for something a little more tailored - I'm sure there is a way to do this using the SELECT as the means to grab the value of the WHERE Clause variable

I was just playing with it and got it to work - But on my second attempt -it failed, so I know I'm close or perhaps light years away.

[davegri]

This is aircode, but should give the right idea.
You need to create a recordset to extract the BatchID:

dim sBatch as string
dim sSQL as string
dim StrSQL2 as string
dim rst as dao.recordset
dim db as dao.database
set db=currentdb
sSQL="SELECT TOP (1) [BatchID] " & _                
"FROM [MyServer].[Schema].[TblData] " & _
                "WHERE BatchID >= '20190114_050000 AND BatchID<= '20190114_060000'"
set rst =db.createrecordset (sSQL)
rst movelast
rst movefirst
if rst.eof = true and rst.bof = true then
    msgbox "Select is empty"
    exit sub
endif
sBatch = rst!BatchID
StrSQL2 = "SELECT  [ReportDate], [SalesType], [TotalSales] " & _
                "FROM [MyServer].[Schema].[TblData] " & _
                "WHERE BatchID = '" & sBatch & "'"

[RunTime91]

Hey Dave - Thanks pitchin' I think this is pretty close - I'm not able to get it to work

but I think this may be due to my using an ADODB Recordset.

It wont let me use the CreateRecordset Method - and any workarounds I've come up with have failed

[davegri]

Should be db DOT Createrecordset as below... Also edited in post #4

set rst =db.createrecordset (sSQL)

[RunTime91]

Success!!!

Ranman & Dave - Thank You!!

The following seems to work perfectly - However, if anyone sees any potential future flaw - please feel free to say something - Thanks

Otherwise here is the code

StrSQL1 = "SELECT TOP (1)[BatchID]" & _
          "FROM [RAOCStats].[tri].[tblCRTimeliness]" & _
          "WHERE BatchID >= '20190115_050000' AND BatchID <= '20190115_060000'"

Con.ConnectionString = StrCon
Con.Open
Rs.Open StrSQL1, Con

Set Rs = Con.Execute(StrSQL1)

If Rs.EOF = False Then
  Boo = Rs!BatchID
End If

[RunTime91]

Success!!!

Ranman & Dave - Thank You!!

The following seems to work perfectly - However, if anyone sees any potential future flaw - please feel free to say something - Thanks

Otherwise here is the code

 StrSQL1 = "SELECT TOP (1)[BatchID]" & _
           "FROM [RAOCStats].[tri].[tblCRTimeliness]" & _
           "WHERE BatchID >= '20190115_050000' AND BatchID <= '20190115_060000'"

 Con.ConnectionString = StrCon
 Con.Open

 Rs.Open StrSQL1, Con

 Set Rs = Con.Execute(StrSQL1)

 If Rs.EOF = False Then
   Boo = Rs!BatchID
 End If

StrSQL = "SELECT [ReportDate] ,[WorkType] ,[TotalCases]" & _
         "FROM [RAOCStats].[tri].[tblCRTimeliness]" & _
         "WHERE BatchID =  '" & Boo & "' "
Rs2.Open StrSQL, Con

Set Rs2 = Con.Execute(StrSQL)

Ws.Range("A1").CopyFromRecordset Rs2

*Resubmitted first attempt crashed

[RunTime91]

Success!!!

Ranman & Dave - Thank You!!

The following seems to work perfectly - However, if anyone sees any potential future flaw - please feel free to say something - Thanks

Otherwise here is the code

 StrSQL1 = "SELECT TOP (1)[BatchID]" & _
           "FROM [RAOCStats].[tri].[tblCRTimeliness]" & _
           "WHERE BatchID >= '20190115_050000' AND BatchID <= '20190115_060000'"

 Con.ConnectionString = StrCon
 Con.Open

 Rs.Open StrSQL1, Con

 Set Rs = Con.Execute(StrSQL1)

 If Rs.EOF = False Then
   Boo = Rs!BatchID
 End If

StrSQL = "SELECT [ReportDate] ,[WorkType] ,[TotalCases]" & _
         "FROM [RAOCStats].[tri].[tblCRTimeliness]" & _
         "WHERE BatchID =  '" & Boo & "' "
Rs2.Open StrSQL, Con

Set Rs2 = Con.Execute(StrSQL)

Ws.Range("A1").CopyFromRecordset Rs2

*Resubmitted first attempt crashed