MS query - Date Problem

[symze]

Hi,

I am a complete beginner with VBA so any help would be appreciated. I have setup a query using MS Query to pull some periodic call data from an ACD database. In the query I have specified a date and time from which to collect the records. As this has been recorded as macro the date parameters are changeable manually by going into the code and running the macro again.

My questions is how do I programme an inputbox that allows me to enter any date and update the query to pull data for the date required.

As I said I am a complete beginner.

Thanks

 

[SkipVought]

Hi,

In the code for the query, you have a string assigned to the CommandText property of the QueryTable. I have simplified my macro to look like this...

    With ActiveSheet.QueryTables(1)
        .Connection = sConnect
        .CommandText = sQuery
        .Refresh BackgroundQuery:=False
    End With

where sConnect has my connect string and sQuery has my query SQL code. This is what you will need to modify. Here's an example...

    dteInp = InputBox("Enter Date")
    If dteInp = "" Then Exit Sub
    sQuery = "SELECT * FROM MyTable " & _ 
      "WHERE MyDate='" &  dteInp & "'; "
    With ActiveSheet.QueryTables(1)
        .Connection = sConnect
        .CommandText = sQuery
        .Refresh BackgroundQuery:=False
    End With

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[symze]

hi thanks for your reply,

However, I am still a bit confused I tried the above and must be doing something wrong, here is the code for the query, it is the date parameters that I would like to be able to be changed as per my input box:

ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=ICCM_PREVIEW_DSN;SRVR=ICCM_PREVIEW;DB=blue;UID=Paul;", Destination _
:=Range("A3&quot)
.Sql = Array( _
"SELECT iApplicationStat.Timestamp, iApplicationStat.Application, iApplicationStat.CallsAnswered, iApplicationStat.CallsAbandoned" & Chr(13) & "" & Chr(10) & "FROM blue.dbo.iApplicationStat iApplicationStat" & Chr(13) & "" & Chr(10) & "WHERE (iApplicationSt" _
, _
&quot;at.Application='UBFE_English_App') AND (iApplicationStat.Timestamp>={ts '2003-10-01 06:00:00'} And iApplicationStat.Timestamp<={ts '2003-10-01 18:00:00'}) OR (iApplicationStat.Application='ICFE_Englis&quot; _
, _
&quot;h_App') AND (iApplicationStat.Timestamp>={ts '2003-10-01 06:00:00'} And iApplicationStat.Timestamp<={ts '2003-10-01 18:00:00'})&quot; & Chr(13) & &quot;&quot; & Chr(10) & &quot;ORDER BY iApplicationStat.Timestamp&quot; _
)
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=True

End With
End Sub
Thanks

 

[SkipVought]

Sub test()
    'assuming that you have 4 textboxes with dates...
    
    sConnect = "ODBC;DSN=ICCM_PREVIEW_DSN;SRVR=ICCM_PREVIEW;DB=blue;UID=Paul;"
    sQuery = "SELECT iApplicationStat.Timestamp, iApplicationStat.Application, " & _
        "iApplicationStat.CallsAnswered, iApplicationStat.CallsAbandoned " & _
        "FROM blue.dbo.iApplicationStat iApplicationStat " & _
        "WHERE (iApplicationStat.Application='UBFE_English_App') AND " & _
        "(iApplicationStat.Timestamp>={ts '" & Textbox1.Text & "'} And " & _
        &quot;iApplicationStat.Timestamp<={ts '&quot; & Textbox2.Text & &quot;'}) OR &quot; & _
        &quot;(iApplicationStat.Application='ICFE_English_App') AND &quot; & _
        &quot;(iApplicationStat.Timestamp>={ts '&quot; & Textbox3.Text & &quot;'} And &quot; & _
        &quot;iApplicationStat.Timestamp<={ts '&quot; & Textbox4.Text & &quot;'}) &quot; & _
        &quot;ORDER BY iApplicationStat.Timestamp&quot;
        
    With ActiveSheet.QueryTables(1)
        .Connection = sConnect
        .CommandText = sQuery
        .Refresh BackgroundQuery:=False
    End With
End Sub

Does this help?


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[symze]

Hi Skip,

Thanks for your help so far on this. I have simplified my query and changed it to reflect your changes, however when I run my Macro I get an error:

Compile error
Expected function or variable.

In Debug it highlights the Textbox1_Change part of the code.
Am I to assume then, that I need to define a function that returns the date in the text box field. If so how!!
Or am I totally lost?

Thanks

 

[SkipVought]

Where is the Textbox1_Change part of the code?

Nothing in the code you posted that had Textbox1_Change???

Whatever is in Textbox1 is returned with Textbox1.Text.

???

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[symze]

Skip,

My apologies I just took the Change() bit after the textbox 1 name as part of it. OK I am now getting

Compile error:

Variable not defined and it is highlighting Textbox1

Cheers

P.S. my head is now ready to explode.

 

[SkipVought]

Do you have an object named Textbox1?

Where are you ie what workbook object is active for this code?

For instance if yor code is in a module, you need to quallify the control object with it's parent object, either a userform or a worksheet...

WorkSheets("Sheet1").Textbox1.......

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[symze]

Skip,

I have used the following code and am getting some success. I can get the return value from my input box OK, however in my query code I get a run time error saying OBJECT REQUIRED.

This is my Input box and return function:

Public Function IptBox()
Dim InputBox As String
InputBox = (&quot;&quot
End Function

Public Function Rtn()
Dim StrA As String
StrA = InputBox(&quot;&quot
Debug.Print StrA
End Function

Query code:


Public Sub Macro2()
'
' Macro2 Macro
' Macro recorded 15/10/2003 by pasy1807
'

Dim StrA As String

With ActiveSheet.QueryTables.Add(Connection:= _
&quot;ODBC;DSN=ICCM_PREVIEW_DSN;SRVR=ICCM_PREVIEW;DB=blue;UID=Paul;&quot;, Destination _
:=Range(&quot;B12&quot)
.Sql = Array( _
&quot;SELECT dApplicationStat.Timestamp,dApplicationStat.Application, dApplicationStat.CallsAnswered&quot; & Chr(13) & &quot;&quot; & Chr(10) & &quot;FROM blue.dbo.dApplicationStat dApplicationStat&quot; & Chr(13) & &quot;&quot; & Chr(10) & &quot;WHERE (dApplicationStat.Timestamp={ts '&quot; & Rtn.StrA & &quot;'})&quot; _
, _
&quot;AND (dApplicationStat.Application='HPCE_English_App')&quot; & Chr(13) & &quot;&quot; & Chr(10) & &quot;ORDER BY dApplicationStat.Timestamp&quot; _
)
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.SavePassword = False
.SaveData = True
End With
End Sub

Cheers
Paul

 

[SkipVought]

Hi,

Loose the second function. Try this...

Public Function IptBox()
IptBox = InputBox("Enter")
End Function

s = "SELECT 
dApplicationStat.Timestamp,dApplicationStat.Application, " & _
    "dApplicationStat.CallsAnswered " & _
    "FROM blue.dbo.dApplicationStat dApplicationStat " & _
    "WHERE (dApplicationStat.Timestamp={ts '" & IptBox & "'}) AND " & _
    "(dApplicationStat.Application='HPCE_English_App') " & _
    "ORDER BY dApplicationStat.Timestamp "
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com