Queries and Dates

[stevesana]

@ isladogs I've tried the datepicker method but unfortunately is still stalling with OBDC timeout

 

[zeroaccess]

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):

View attachment 79917

I forgot one thing: you probably want to set that field to "WHERE". Right click, totals, then change to Where.

 

[stevesana]

@ CJ_London
Have you tried a shorter date range to reduce the number of records to be returned?
The query will only return a year of records using the "Between [Forms]![Reports]![StartDate] And [Forms]![Reports]![EndDate]"

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?
This was my first thought perhaps some invalid value and unfortunately I have no access to the SQL server.

how did you change the format? using the format function in your query? the format property of a control won't do it.
Yes, I did it this way and you are correct it didn't work

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)
User enters the dates in the date fields on the reports form and clicks on the Trial Balance command button
I am running the query with code behind a command button on the form DoCmd.OpenQuery "Collate TX Sales Invoices", acNormal, acEdit which opens a report form DoCmd.OpenReport "EuroOBTrial Balance For Company", acViewPreview

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?
I'll try and give this a try and no I have not disabled any error management whilst trying to resolve this issue

Thanks

 

[CJ_London]

how did you change the format? using the format function in your query? the format property of a control won't do it.
Yes, I did it this way and you are correct it didn't work

you need to use the format function

format([Forms]![Reports]![StartDate],"yyyy-mm-dd")

am running the query with code behind a command button on the form DoCmd.OpenQuery "Collate TX Sales Invoices", acNormal, acEdit which opens a report form DoCmd.OpenReport "EuroOBTrial Balance For Company", acViewPreview

so to clarify you run the collate query to populate the Collate TX Temp table in sql server, then open the trial balance report which uses the Collate TX Temp table as it's recordsource?

I have not disabled any error management whilst trying to resolve this issue

would be a good idea to do so - might give a clue as to what is going wrong

 

[isladogs]

@ isladogs I've tried the datepicker method but unfortunately is still stalling with OBDC timeout

Using a date picker makes it easier to enter valid dates ...but it certainly wasn't suggested as a way of preventing the ODBC timeout which occurs due to slow processing of the number of records involved in your query

 

[bastanu]

As I said in your other (cross-posted) post (https://www.accessforums.net/showthread.php?t=80019), have you tried to update your ODBC driver?

Cheers,
Vlad

 

[stevesana]

Hi Vlad, yes I did but still to no avail. I decided to use make table queries to pull all the records from Sales Invoices and Sales Invoice Details table from the SQL server and run this query from the front-end as a temporary solution. Messy but a temporary fix

 

[bastanu]

At least replace the make table queries with pairs of delete/append as the make -table queries are well known to dramatically increase the front-end size.

 

[stevesana]

Hi Bastanu, yes I did this.. thank you

 

[isladogs]

Actually, contrary to popular belief, delete/append queries cause similar amounts of file bloat to using make table queries.
See https://www.access-programmers.co.uk/forums/threads/mythbusters-make-table-vs-append-query.302259/

However, I'd still recommend avoiding make table queries as repeated use can lead to instability.

 

[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

just requoting myself, using the forms construct the data is forced into access first, then queried in access or client memory rather than in the database.