Dateadd Query to Retrieve Specified and Before and After Records

[Walt62]

Attempting to construct a Dateadd query to retrieve all occurrences of any specified 3 digit lottery number and dates of occurrence and also the 3 digit lottery number that came 1 and 2 days before and 1 and 2 days after for each occurrence.


The database consist of date and lottery number fields.
If the Dateadd function is not the ideal method I am open to other solutions.

[CJ_London]

dateadd is the function to use

dateadd("d",2,Date()) will add 2 dates from today and dateadd("d",-2,Date()) will deduct 2 days

[Walt62]

Thank you for your quick response and for assuring me the Dateadd function is the way to go in tackling this task however I want to retrieve/list the specified 3 digit lottery number and date every time it occurs/came in the 10 years of records in the database and the lottery numbers that preceded it each time and followed it for 2 days.
Example: if the specified number that I am looking up is '123', I want find this number every time it came in the database and what numbers preceded it and followed it every time for 2 days.
I am very appreciative of your to help.

[CJ_London]

not clear what you are asking but sounds like you need a Cartesian query something like:

SELECT B.* 
FROM table1 as A, table1 as B
WHERE A.lotterynum=123 and B.lotterydate between dateadd("d",-2,A.lotterydate) AND dateadd("d",2,A.lotterydate)

if this is not correct, please supply your table and field names with an example of the data and the resulting output you require

[Walt62]

Date.Winning number
05152015321
05142015952
05132015.123
05122015.770
05112015.752
....
12202013421
12192013.821
12182013.123
12172013.077
12162013.092
...
08302010475
08292010.128
08282010.123
08272010.707
08262010.560
...
I want to be able to search the table for all occurrences ofany lottery number between ‘000’, ‘001’, … thru ‘999’. I want the output toreturn all occurrences of the 3 digit number and dates of occurrence AND the 3digit numbers that occurred 1 and 2 days before and 1 and 2 days after thesearched number.
In this example above I selected the number ‘123’ to searchand I want the query to return the generated list.
The list display the each occurrence of ‘123’ and date ofoccurrence along with the 3 digit numbers that preceded it the previous 2 daysand the 3 digit numbers that occurrence 1 and 2 days after the ‘123’ number.

[CJ_London]

so what is wrong with the suggestion I made?

Is your question now about converting 05132015.??? into a date?

[Walt62]

I don't fully understand your earlier select code. I think you are telling me that this code will give me the output that I am seeking and that I provided in my previous post. If possible would you further explain your select code to this novice so I may implement and test and finalize my query request. Thanks

[CJ_London]

I don't fully understand your earlier select code

If you don't understand it, ask a question, don't just ignore it and repeat your question.

you did not provide any details of your table or field names so I've assumed your table was called table1 and your fields were called lotterydate and lotterynum respectively

You have still not provided a table name but have indicated that lotterydate is called Date and lotterynum is called Winning Number

So just replace those in my suggestion with your real names. Note that Date is a reserved name (it is the name of a function which returns todays date) so you should change this and having spaces in names can also cause issues. In both cases, using square brackets will often 'solve' the problem, but not always.

From what you have supplied, your date is not actually a date but a string which you will need to convert back to a date - suggest you use the following

datevalue(left(lotterydate,2) & "/" & mid(lotterydate,3,2) & "/" & right(lotterydate,4))

As to how the query works, we take two 'views' or proper term 'Aliases' of table1 - A and B - which is done here

FROM table1 as A, table1 as B

we cannot have a join between these two aliases because there is none so without any filtering you will get rows for the number of records squared - ie. if you table has 1,000 records, the query will return 1,000,000 rows

Alias A we take as being filtered to only include those records where the lottery number =123 - done here

WHERE A.lotterynum=123

so lets say that out of your 1,000 records in table1, only 20 have the number 123 - the rows returned will be reduced to 20 x 1000=20,000

Alias B we take to be the table which returns the records we want - and are filtered to only include those which are within 2 days of the Alias A date - here

and B.lotterydate between dateadd("d",-2,A.lotterydate) AND dateadd("d",2,A.lotterydate)

which will return the records either side of the 123 date plus the 123 date as well - so 5 records

So on the basis 5 records will be return for each filtered row in AliasA we will have 20 x 5 rows=100 records from the original million. Note you may not get 5 records - if for example the 123 is the very first number, they there won't be any records to return prior to this - so only 3 records would be returned.