Need to determine date range 2

[MemphisVBA]

Hello -
I have an app that imports a spreadsheet in to a table - I need help with a query that will tell me the date range of the records. Is there an easy way to get the minimum and maximum date values?

Thanks

 

[mp9]

SELECT Min([FieldNameHere]) AS MinOfField, Max([FieldNameHere]) AS MaxOfField
FROM TableNameHere;

http://www22.brinkster.com/accessory/

 

[MemphisVBA]

Thanks! That worked perfectly

 

[MemphisVBA]

How can I use these min/max values to query a different table?

I modified your query example to save the dates in a table (which would have only 1 record) - I thought I could use a Dlookup to get the dates, but I get a type mismatch error:

SELECT tblInvoice.InvoiceNo, tblInvoice.InvDate
FROM tblInvoice, tblDateRange
WHERE (((tblInvoice.InvDate) Between DLookUp([tblDateRange]![StartDate],"tblDateRange",[tblDateRange]![DateID]=1) And DLookUp([tblDateRange]![EndDate],"tblDateRange",[tblDateRange]![DateID]=1)));

Is there a better (correct) way

 

[PHV]

SELECT tblInvoice.InvoiceNo, tblInvoice.InvDate
FROM tblInvoice, tblDateRange
WHERE tblInvoice.InvDate Between tblDateRange.StartDate And tblDateRange.EndDate AND tblDateRange.DateID = 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MemphisVBA]

Thanks PVH - but I am still getting an Error:
"Data type mismatch in criteria expression" - I'll keep plugging away it it, but any suggestions would be greatly appreciated.

 

[MemphisVBA]

I got it to work by removing the "=1" part

SELECT tblInvoice.InvoiceNo, tblInvoice.InvDate
FROM tblInvoice, tblDateRange
WHERE tblInvoice.InvDate Between tblDateRange.StartDate And tblDateRange.EndDate

Thanks Again

 

[PHV]

AND tblDateRange.DateID = [!]'[/!]1[!]'[/!]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MemphisVBA]

Question:

I am trying to query a table (tblInvoice) based on the Min and Max dates of records in another table (ImportTable)

I need to temporarily save the dates for later use, so I have them in a table (tblDateRange)

With help (see previous posts above) I was able to come up with this to save the start and end dates (this works fine)

    MySQL = "INSERT INTO tblDateRange ( StartDate, EndDate ) " & _
            "SELECT Min(ImportTable.InvDate) AS StartDate, Max(ImportTable.InvDate) AS EndDate " & _
            "FROM ImportTable;"
    DoCmd.RunSQL MySQL

Below is my problem:

I have the code below that uses the dates saved in the table above, but it doesn't return any records - what am I doing wrong here?

SELECT tblInvoice.InvoiceNo, tblInvoice.InvDate, tblInvoice.PartNo
FROM tblInvoice, tblDateRange
WHERE (((tblInvoice.InvDate) Between DLookUp([tblDateRange]![StartDate],"tblDateRange",[tblDateRange]![DateID]='1') And DLookUp([tblDateRange]![EndDate],"tblDateRange",[tblDateRange]![DateID]='1')));

Thanks

 

[MemphisVBA]

I was able to get it working by making this change to the SQL:

Between DLookUp("[tblDateRange]![StartDate]","[tblDateRange]","[tblDateRange]![DateID]='1'") And DLookUp("[tblDateRange]![EndDate]","[tblDateRange]","[tblDateRange]![DateID]='1'")