Using Dates in Query Criteria

[brett621]

Hello,



I have searched and found numerous pages and threads on using dates in queries but nothing quite like what I am looking for so if anyone can help with my situation I would appreciate it.

I am using a large amount of transaction data that is pulled on an as needed basis from a linked table and based on certain criteria stored for 500 days in a table in the database. We basically want the table holding the information to be able to be updated with the click of a button from the user and not require hard coding the dates to query. For example the table in the database (TransDetail) has all transactions from 1/1/12 - 5/31/13. What I want to do is be able to use the max date of the TransDetail table, have the user click a button and have the transaction detail appended to TransDetail for all transactions from 5/31/13 - current (pulls detail from the linked table and then I will delete from the TransDetail table from Date()-500). Everything seems to work fine if the dates are hard coded but I can't seem to find the correct coding to pull transactions from the max date of TransDetail to current.

Thanks in advance.

[rpeare]

between [Enter the End Date] and dateadd("d", -500, [Enter the End Date])

[brett621]

If I am thinking correctly that is going to prompt for an input of the date to use for end date. I may not have been as clear as I thought I was in the explanation. We are trying to get the append to automatically append all records from the linked table to the TransDetail table using the most recent date from the TransDetail table and adding all records since then. For example if it were run today it would add all records after 5/31(the most recent date in TransDetail) through today. I then have a delete query to remove anything older than the 500 days.

We are trying to eliminate as much user input as possible so that is why we would like the query to pull from the table to get it's dates to use.

[Dal Jeanis]

1) Use The DMax function to find the highest date in your current database, then use that field in your append query.

Dim MyDate As Date
MyDate = Dmax("TransDate", "transDetail")

http://www.techonthenet.com/access/f...omain/dmax.php


2) The one thing I would caution you is to check your data carefully, and make sure you are timing your imports correclty. If you were to import today's data during the middle of the day, then when you next imported, you wouldn't get the end of today's transactions unless your transaction date is a timestamp and your MyDate field was also a timestamp field.

[brett621]

We never need the current day, but occasionally need the prior day so would it be possible to use that and simply add "-1" somewhere, so the next time it would always be pulling complete days of information?

[rpeare]

just modify dal's statement.

between dmax("[TransDate]", "TransDetail") and dateadd("d", -500, dmax("[TransDate]", "TransDetail")

You just have to substitute in your date field name and table name into the statement

[brett621]

Sorry if I am overthinking this but that would pull between the max date of my table and the 500 prior days, correct? I am looking to pull from the max date to the current date and then I have a separate delete query to simply eliminate the older items.

[Dal Jeanis]

So, assuming that the External Transaction date is just a date rather than a timestamp,

Your append will be all those records from your [ExtTransDetail] table WHERE [ExtTransDate] BETWEEN (dmax("[TransDate]", "[TransDetail]") +1) AND (Date()-1)

Your delete will be all those records in [TransDetail] with [TransDate] < (Date() - 500)

Test the code in a test version of your database until you get what you want. Then port the code (only) to your real database.


If you wanted to be extra sure, you could calculate the last date in your table, DELETE everything with that date or more from your table, then append everything from that date to yesterday's date. But that's just if you're paranoid like me.

[rpeare]

If you want to pull everything AFTER your most recent trans date the criteria would be

> dmax("[TransDate]", "TransDetail")

if you want to delete anything older than 500 days from the most recent transaction date the criteria would be

< dateadd("d", -500, dmax("[TransDate]", "TransDetail")

[Dal Jeanis]

rpeare is correct that it is generally better practice to use the dateadd function rather than +1/-1. Access can get finnicky.

If you got what you needed from the above discussion, then please mark the thread "solved". Top of page, under "thread tools".

If we're confusing you, then ask a specific question and we'll try to clear it up for you.

[brett621]

So, assuming that the External Transaction date is just a date rather than a timestamp,

The external table has the date listed as a Date/Time format rather than just a Date. I am not sure if that would affect the method used.

I am attempting to test this but the external table has millions of transactions in it and I am testing by running on a very small date range, it is still taking a while. One specific question I have is regarding the length of time it takes to run. When I input the specific dates (I used Between #6/1/2013# and #6/3/2013#) for the criteria, it runs in a matter of minutes, but when I use the Between (dmax("[TransDate]", "TransDetail")) And #6/3/2013# (I only did this range to test as usually I would want to pull everything using > dmax("[TransDate]", "TransDetail") it takes significantly longer to run and errors with a size error about the database only being able to be 2GB or the temporary memory can not handle this. Is a longer runtime to be expected? Is the running time affected by other criteria being used in addition to the date to get the records from the external table? I also do not understand why the data would return any larger amount of results than the coded dates would.

[rpeare]

domain functions are very memory intensive. Try this:

create a query that has your transdate ONLY, make it a summation query (look for the sigma button that looks like a capital E)

in the TOTALS line put MAX.

See how quickly that runs.

If it's fast you can change the query to look at this smaller, faster, query than using the domain function (this is exactly why I don't use domain functions unless I have no other choice).

[Dal Jeanis]

Brett -

1) The significant question regarding the external transaction date isn't the "format" of the field, it's what actual data is getting stored there. Just look at five or six of the transactions and see if they have a date, or a date and time. If you display them in date/time format and it says something like #6/22/2013 12:00:00 AM# then you have a date only, and can act accordingly. If they have an actual time along with the date, then again, you can act accordingly.

The critical goal is to craft your code so that it doesn't make a bad assumption about the data.

2) If you're running out of space, it sounds like you're using that calculation during the import. Instead of that, use VBA to determine the desired date before you try to load any external data, then feed the import the resulting date you got back. You might also want to delete the expired/oldest data before the import, rather than loading more data on top of it, for a little efficiency boost.

3) One other option - if you have an autokey field for your transaction table, and if it's adding them sequentially rather than randomly, then there may be a way to use the PK index to cut the time drastically on finding the highest date in your table. However, do not attempt that kind of thing unless the other method is unacceptably slow, because it enters an unnecessary complication and vulnerability into the design.

Another alternative might be to create a one-record "meta" table that remembers your highest transaction date, and update it during the import process. Again, don't do it unless the alternative is unacceptable. It's better practice to use the actual data as a reference, rather than metadata.