Queries and Dates

[stevesana]

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

 

[theDBguy]

Hi. An ODBC timeout error may have something to do with network latency. Just a guess...

Has anything changed in that arena within your organization?

 

[stevesana]

I thought this as well but I have 25 queries running behind this form and it's only this query that's giving me the problem. I'm guessing it has something to do with how the query is reading the dates. It works perfect if I call the criteria from the query as Between #01/01/1998# And #16/03/2020# but if I call the criteria Between [Forms]![Reports]![StartDate] And [Forms]![Reports]![EndDate] it just stalls, hangs and then OBDC timeout

 

[HiTechCoach]

It could be because you are not using the # delimiter around the dates

Also, it may not like form field references within the SQL statment

 

[stevesana]

For 15 years this query had no issues until now..
How would you put a # delimiter around this as the criteria " Between [Forms]![Reports]![StartDate] And [Forms]![Reports]![EndDate] "

 

[jdraw]

Have you used DD/MM/YYYY format for all your previous runs? Sometimes Access demand US date MM/DD/YYYY...... just curious.

 

[stevesana]

jdraw I have tried changing the format but still to no avail.. thanks

 

[jdraw]

More of a guess ---The form is open when the you execute the query, right?
I did a small test with a table and a form; made sure form was open; and proved to myself that the # delimiters for Dates were not need in my query. If my form wasn't open, I got a popup asking me for a value for a date.

 

[zeroaccess]

It could be because you are not using the # delimiter around the dates

Also, it may not like form field references within the SQL statment

It's not a problem, it's the same method I use.

I get the same functionality as jdraw.

OP, what are the properties of your date text boxes? This works for me (ignore the Visible No):

 

[stevesana]

zeroaccess did your method unfortunately this didn't work either. 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..

HiTechCoach said:
Also, it may not like form field references within the SQL statment
It appears the Access is either having a difficult time trying to pull this much records ( 148,983 ) using this criteria method " "Between [Forms]![Reports]![StartDate] And [Forms]![Reports]![EndDate]" vs this criteria method "Between #01/01/1998# And #16/03/2020#"from the SQL Server.
I guess Access has it's limitation and presume due to the data size perhaps the query ( Stored Procedure ) must be ran on the server side

 

[zeroaccess]

Are you using the date picker?

That number of records shouldn't be a problem.

 

[stevesana]

zeroaccess unfortunately no, I wrote this accounts program 15 years ago with Access 2000 and to be honest our company is still on Access 2003. So it's been 15 years since I last messed around with Access and I can see the date picker is a new feature in the format property

 

[zeroaccess]

Try the properties in my screen shot, including the date picker. That should ensure the proper format is being fed to the query.

Edit: Unless you're saying Access 2003 doesn't have the date picker? Then you might need some code to alter the input of the user.

 

[stevesana]

Correct Access 2003 doesn't have a date picker but I can try your suggestion and code for a date picker to use as an input

 

[zeroaccess]

I believe isladogs has a date picker that is actually a form - could be added on and popped up from that box.

 

[stevesana]

Thanks I have a date picker form in this accounts program. I'll give it a try

 

[namliam]

Between [Forms]![Reports]![StartDate] And [Forms]![Reports]![EndDate]))

is only available at access time, thus the query will be pushed to SQL without the where resulting in a rather big recordset.


Between #01/01/1998# And #16/03/2020#
are hard dates that can be pushed to SQL server and the entire query runs on it rather than in Access memory for a large part.

Try to force the second notation always when pushing queries to SQL Server ensuring the query is properly pushed to SQL server

 

[isladogs]

One more thing that could be causing issues is using the name Reports for your form. Suggest you change it to e.g. frmReports to avoid possible confusion.
As previously suggested, you would need to use both mm/dd/yyyy date format and # delimiters if the code in post 1 is a SQL statement, However neither are required if using a query as Access will handle the dates correctly.
If you do want to look at my date picker form, you can find it at http://www.mendipdatasystems.co.uk/better-date-picker/4594398118

 

[CJ_London]

a flurry of questions....

in sql server, usual date format is yyyy-mm-dd although I suspect the odbc driver will convert it - but it will convert from the US standard

for something to stop after 15 years implies something has changed

If only the one query is affected (and I presume you have other queries which use dates) the implication is it has to be to do with your data or the query. Have you tried a shorter date range to reduce the number of records to be returned? Or a range that is US compliant? (e.g. 16/03/2020 should be interpreted as 16th March, but a date of 12/03/2020 will be treated as 3rd December) Have you taken a look at the data in sql server to see if there are some invalid values there? or the fields/tables have been changed in some way?

I have tried changing the format but still to no avail.. thanks

how did you change the format? using the format function in your query? the format property of a control won't do it.

when I use this query behind the form it just stalls or ODBC timeout

what do you mean by 'behind the form'? are you running the query with code or are you creating the query in code and executing it? (suspect the former, but just to confirm)

assuming the former what if you run the query from the navigation pane (with your form open and the two fields completed), rather than manually populating the values. And with regards your code, have you disabled any error management whilst trying to resolve this issue?

with regards your query, you are grouping on [Sales Invoices].SalesInvoiceID, [Sales Invoice Details].VatRate but not actually selecting those values, it probably doesn't make a difference but worth just checking.

other thoughts are to use a passthrough query or stored procedure

 

[stevesana]

Between [Forms]![Reports]![StartDate] And [Forms]![Reports]![EndDate]))

is only available at access time, thus the query will be pushed to SQL without the where resulting in a rather big recordset.


Between #01/01/1998# And #16/03/2020#
are hard dates that can be pushed to SQL server and the entire query runs on it rather than in Access memory for a large part.

Try to force the second notation always when pushing queries to SQL Server ensuring the query is properly pushed to SQL server

This makes perfect sense with the Between #01/01/1998# And #16/03/2020# and and the entire query runs on it rather than in Access memory for a large part