Queries and Dates

[stevesa]

Hi,

Microsoft Access 2003 Front-End with SQL Server Backend


I have the following query that is run from a reports form. On the form is 2 fields for the StartDate and EndDate which opens a report.
This is was working for years and now has suddenly been stalling and causes an ODBC timeout.

I can run the query if i just put the following in the criteria Between #01/01/1998# And #16/03/2020# but when I use this query behind the form it just stalls or ODBC timeout

If put the following in the criteria on the query "Between [Forms]![Reports]![StartDate] And [Forms]![Reports]![EndDate] the query also stalls.

INSERT INTO [Collate TX Temp] ( CompanyID, Name, SumOfTX, TransTypeID, SumOfEuroTX )
SELECT Companies.CompanyID, Companies.Name, Sum((([Amount]*[VatRate]/100)+[Amount])) AS Gross, "13 Sales Invoice" AS [TX Type], Sum((([EuroAmount]*[VatRate]/100)+[EuroAmount])) AS EuroGross
FROM Jobs INNER JOIN ((Customers INNER JOIN (Companies INNER JOIN [Sales Invoices] ON Companies.CompanyID = [Sales Invoices].CompanyID) ON Customers.CustomerID = [Sales Invoices].CustomerID) INNER JOIN [Sales Invoice Details] ON [Sales Invoices].SalesInvoiceID = [Sales Invoice Details].SalesInvoiceID) ON Jobs.JobID = [Sales Invoice Details].JobID
WHERE ((([Sales Invoices].SalesInvoiceDate) Between [Forms]![Reports]![StartDate] And [Forms]![Reports]![EndDate]))
GROUP BY Companies.CompanyID, Companies.Name, [Sales Invoices].SalesInvoiceID, [Sales Invoice Details].VatRate
ORDER BY [Sales Invoices].SalesInvoiceID;

Any help would be greatly appreciated

[ranman256]

if the date field also has time it could stop working. remove the time in the query.
Some ODBC will convert the date to string and again the dates wont work.
convert the date/time field to date only:

DteFld: format([dateField],"mm/dd/yyyy")

then you must use zero fill full months & days format: 03/01/2020
then the dates work.

[ssanfu]

It looks like you are using non-US date format for the dates:
Between #01/01/1998# And #16/03/2020# (Between 01 January, 1998# And 16 March ,2020)

On the form, try entering the dates in the controls in US format:

 Between #01/01/1998# And #03/16/2020#  

(Between January 01,1998 And March 16,2020)
then open the report.


See the article by Allan Browne International Dates in Access

I have used the function in Paragraph #3 to convert the date format.

On the form, have 2 more unbound text controls. In the after update event of the two date controls, [Forms]![Reports]![StartDate] And [Forms]![Reports]![EndDate], have code to call the Function SQLDate and put the converted date into the new DateStart and DateEnd control. Reference the two new text date controls in the query.


If I wasn't clear, let me know.....

[stevesa]

if the date field also has time it could stop working. remove the time in the query.
Some ODBC will convert the date to string and again the dates wont work.
convert the date/time field to date only:

DteFld: format([dateField],"mm/dd/yyyy")

then you must use zero fill full months & days format: 03/01/2020
then the dates work.

ranman256 date field has no time but what I did was copy the 2 tables, Sales Invoices and Sales Invoices Details tables and made them local to my Frontend instead of a linking to the SQL Server. I ran the query again with the criteria "Between [Forms]![Reports]![StartDate] And [Forms]![Reports]![EndDate]" and this works fine no issue..

[Gicu]

What ODBC driver do you use to connect to your SQL back-end? If using the generic "SQL Driver" that comes with Windows try to replace it with ODBC Driver 17 for SQL Server and see if it works.
https://docs.microsoft.com/en-us/sql...l-server-ver15
Cheers,

[Bullschmidt]

And for dates with SQL Server you don't use the # before and after each date (such as in Microsoft Access) but rather treat as regular text such as:

 SELECT * FROM Orders WHERE OrderDate='2008-11-11'
 

as shown in this article:

https://www.w3schools.com/sql/sql_dates.asp