SQL query

[Guest]

thanks for any help,

I have a small SQL query(shown below) that will retrieve some date from .mdb
according to the date selected. It will get the data greater then the data
selected or the date less or equel to selected date. I need to select between
two dates and get the data from that. Like I select 11/15/2005 00:00:00 to
11/31/2005 00:00:00 and only the data from those two dates are returned.

Thanks again for any help. darron


SQL Query-------------------------------------


SELECT TrendAnalogJoin.DateTime, TrendAnalogJoin.TagName,
TrendAnalogJoin.Value
FROM TrendAnalogJoin TrendAnalogJoin
WHERE (TrendAnalogJoin.DateTime>'2005/10/28 23:59:00')
ORDER BY TrendAnalogJoin.DateTime

 

[Guest]

Hi,

You can use a QueryTable in excel:
- menu Data > Get (or Import) External Data > New Database QUery. The
'Choose Data Source' pops up.
- choose 'MS Access Database' from the Databases tab and make sure the
checkbox 'Use the Query Wizard...' is checked. Click OK.
- The Select Database dialog pospup. Choose your mdb file. click OK.
- follow the wizard, you'll be able to filter on any field so you can filter
on your date field.

 

[Guest]

Thanks for you help,

I went through your insructions and I'm at Query Wizard Data Filter window.
It allows me to pick the datetime field to filter with options to pick
greater than or less than; then a pull down selection lets me select the
date. Below that I have a "and" button and a "or" button to select the next
argument. From this I'm having trouble getting it to pick two dates and
return the date between those dates.

again thanks for your help.

 

[dysgraphia]

Darron,
In the Query criteria use BETWEEN Date1 AND Date2 where Date1 and Date2
cover the range of dates you want.
HTH, cheers, Peter.

 

[Guest]

To make it simpler, you could add parameters to your query. This requires
you to work in the MS Query:
- right - click the returned data and choose Edit Query from the popup menu.
- at the last step of the Wizard ("Query Wizard - Finsh"), choose 'View Data
or Edit Query in Microsoft Query' , click ok (or finish). MS Query opens.
- In MS Query:
- make sure the Criteria pan is visible (menu View > Criteria).
- there, as Criteria Field, enter the date field name , say MyDate, and
as Value enter: Between [param1] And [param2]
This automatically crates 2 paramaters
- In menu View > Parameters, select each of them, click Edit and change
their datatype to Datetime.
- run the query to test (chcek the sql too)
- return the data to Excel: menu File > Return Data to MS Excel
- Back to Excel. Now we want to specify the parameters.
- right-click the query data and choose Parameters from the popup menu
(notice that the Paramaters submenu is enabled now)
- Choose from the 3 options proposed to you: prompt for value at each
refresh/run of the query, use a constant value, or get the value from a
specific cell (with or without auto-refresh when the value changes).

You could set 2 cells to contain the date parameters and have the query
auto-refresh when it is modified. Using Data Validation, you could also have
these 2 cells have a dropdown allowing the user to select some dates from a
set of values.

I hope this helps,

 

[Guest]

'Darron

'If you would like to try an ADO procedure copy and paste
'the following to the code sheet of "sheet1"
'place a cmd button on Sheet1

'Remember:

'To use ADO objects in an application add a reference
'to the ADO component. From the VBA window select
'>Tools/References< check the box
' "Microsoft ActiveX Data Objects 2.x Library"

'This procedeure calls a parameterized query from Access

Private Sub CommandButton1_Click()
Dim CMD As New ADODB.Command
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset

db_Name = ("C:\Program Files\Microsoft Visual Studio\VB98\NWind.mdb")

cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & db_Name & ";" & ", , , adConnectAsync;"
cnn.Open
CMD.ActiveConnection = cnn
CMD.CommandText = "[Sales By Year]"
CMD.CommandType = adCmdUnknown

Dim Param As ADODB.Parameter

Set Param = New ADODB.Parameter
Param.Name = "Date1"
Param.Type = adDate
Param.Value = InputBox("Starting", App_Name, Date)
' Param.Value = Worksheets("Sheet1").Range("J18").Value
' InputBox("Beginning Date", App_Name, #1/1/1995#)
Param.Direction = adParamInput
CMD.Parameters.Append Param

Set Param = New ADODB.Parameter
Param.Name = "Date2"
Param.Type = adDate
Param.Value = InputBox("Ending", App_Name, Date)
Param.Direction = adParamInput
CMD.Parameters.Append Param
rs.CursorLocation = adUseClient
Set rs = CMD.Execute

rs.MoveFirst
Dim rg As Range
Set rg = ThisWorkbook.Worksheets(1).Range("a1")
Worksheets("Sheet1").Range("a1").CopyFromRecordset rs
rg.CurrentRegion.Columns.AutoFit

'Close the connection
cnn.Close
Set cnn = Nothing

'Destroy the Recordset
Set rs = Nothing

Exit Sub

End Sub

'Good Luck
'TK