SQL Query - Name and Date search in one SQL query

[DanSMT]

All,

I'm trying to pull the last items entered from the latest date added from my Database

Not sure how to tackle two statements at once.

There can be 10 or more items with the same ID number on the same date, which I would like to receive all of them. There can also be only 1 occurrence in some cases.

Current code is below. This pulls are entries based on a part number, but I would like to pull only the items that were entered on the last recorded date.

SQL = "SELECT * FROM RIDatabase WHERE [Part Number] = '" & var & "'"

 

[james_lankford]

select 
  *
from 
  your_table
where 
(
  (
    int( your_datetime_field )  =  ( select max( int( your_datetime_field ) ) from your_table )
  )
)

 

[DanSMT]

I've decided to pull data in "AS IS" followed by a filter to remove old dates.

Code below will pull that information.

SQL = "SELECT * FROM RIDatabase WHERE [Part Number] = '" & var & "'"

Still appears to be no effective method for pull with only specific (max) dates.

 

[james_lankford]

> Still appears to be no effective method for pull with only specific (max) dates.

that's exactly what my code does

 

[DanSMT]

> Still appears to be no effective method for pull with only specific (max) dates.

that's exactly what my code does

James, I believe there is a difference between working through excel VBA vs. working within MS Access. The code isn't directly translating between the two. I've tried all the suggestions with no success.

 

[DanSMT]

After some research I've developed a bandade for this.

I'm pulling the information from the ADODB database with the following code.

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim dbPath As String
Dim SQL As String
Dim var
'add error handling
On Error GoTo errHandler:
'Disable screen update.
Application.ScreenUpdating = False
Worksheets("data").Activate
ActiveSheet.AutoFilterMode = False
'clear worksheet
Worksheets("Data").range("A2:ZZ10000").ClearContents
'get the path to the database
dbPath = "C:\Users\dans\Documents\Dest2\Database.accdb"

Set cnn = New ADODB.Connection

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";Jet OLEDB:RIDatabase"

For a = 1 To 24
var = Me("Part" & a)
'exit sub if textbox is empty
If var = "" Then
GoTo done2:
End If

SQL = "SELECT * FROM RIDatabase WHERE [Part Number] = '" & var & "'"

'Create the ADODB recordset object.
Set rs = New ADODB.Recordset 'assign memory to the recordset

rs.Open SQL, cnn

'Check if the recordset is empty.
If rs.EOF And rs.BOF Then
'Close the recordset and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing
'Enable the screen.
Application.ScreenUpdating = True
'In case of an empty recordset display an error.
MsgBox "There are no records in the recordset!", vbCritical, "No Records"

Exit Sub
End If

'Write the recordset values in the sheet.
Worksheets("Data").range("A2").CopyFromRecordset rs

Once the data is transfered into the spreadsheet, im using excels filtering feature to only pull the latest dates entered.

Worksheets("data").Activate

'Dim cell As Range
Dim rng As range
Dim shtSrc As Worksheet, shtDest As Worksheet
Dim ite
Dim myDate As Date

myDate = Application.Max(Columns(1))
ActiveSheet.AutoFilterMode = False
Columns(1).AutoFilter Field:=1, Criteria1:=Format(myDate, [A2].NumberFormat)

Hope this helps anyone searching in the future.