NUmber predictions

[TheSpecialist]

Hello I have a database where I have dates for the past ten years and numbers along with the day the number came on. So three fields (Date, Draw, number)

If a number came on 10/12/2014 and it was 555. I want to be able to see all the numbers that came two days before and two days after this date. What do I need to put in my criteria for my query to see this information



Thank you in advance

[orange]

Date is a reserved word in Access.


General criteria would be along this pattern

..... Where  SearchDate is Between YourDate -2 and YourDate +2

[knarfreppep]

I'm using dd/mm/yyyy format.

When you say 'two days before and two days after', do you mean 08/12/2014 through 12/12/2014 or do you mean the last two days before 10/12/2014 for which there is data and the next two after 10/12/2014 for which there is data?

If the former (i.e. dates are contiguous) this should do it -
SELECT bytNumber FROM tblMyTable WHERE Not dtmDate = CDate("10/12/14") And dtmDate Between CDate("10/12/14") - 2 And CDate("10/12/14") + 2

Otherwise, it's a wee bit more complicated -
SELECT bytNumber
FROM tblMyTable
WHERE dtmDate IN (SELECT DISTINCT TOP 2 dtmDate FROM tblMyTable WHERE dtmDate<CDate("10/12/2014") ORDER BY dtmDate DESC)
UNION ALL SELECT bytNumber
FROM tblMyTable
WHERE dtmDate IN (SELECT DISTINCT TOP 2 dtmDate FROM tblMyTable WHERE dtmDate>CDate("10/12/2014") ORDER BY dtmDate)

I hope this helps.

[TheSpecialist]

Yes, I am using the 10/12/2012 so I want to see for instance 10/10/2012 to 10/14/2012 I want to see the numbers that came before 10/12/2014 and 2 days after 10/12/2012. How can I put that in my criteria so it will pull all the numbers on these days before 10/12/2012 and (2 days) before that date and 2 days after that date.

[knarfreppep]

Oh, I see ... you're in the USA looking for dates in October ... so use these -

SELECT bytNumber FROM tblMyTable WHERE Not dtmDate = CDate("12/10/14") And dtmDate Between CDate("12/10/14") - 2 And CDate("12/10/14") + 2

OR

SELECT bytNumber
FROM tblMyTable
WHERE dtmDate IN (SELECT DISTINCT TOP 2 dtmDate FROM tblMyTable WHERE dtmDate < CDate("12/10/2014") ORDER BY dtmDate DESC)
UNION ALL SELECT bytNumber
FROM tblMyTable
WHERE dtmDate IN (SELECT DISTINCT TOP 2 dtmDate FROM tblMyTable WHERE dtmDate > CDate("12/10/2014") ORDER BY dtmDate)

[TheSpecialist]

Is it anyway I can put it in the criteria section of the query design view

[knarfreppep]

If I understand your question ... start a new query, select SQL View, paste my suggested query to replace the highlighted 'SELECT;', select Design View.

This will only work with the simple (first) suggestion.

You'll need this table (but with your October dates) -

[TheSpecialist]

this is how it looks and I wanted to be able to see the dates 2 days before this number came and two days after this number came. How will I put it in my criteria section of the query

[knarfreppep]

You should be able to work out your problem from here.

[TheSpecialist]

thank you so much

[TheSpecialist]

Is it away that I can use any number I want in my database and apply the two days before and two days after regardless. For instance, if I want to use 123 and I want to be able to see all the times this number occurred and then I want to see all the numbers that came two days before the number and two days after that number. Is it a query for that as well

[orange]

Yes.
You would have query A that finds all Dates where your searchNumber occurs.
Then, query B would take each Date in queryA and do the search for 2 days before and after each Date.

For clarity and simplicity, you might want to have queryA store those Dates in a Table say TableWithDates.
Then use the Dates in TableWithDates in your earlier query.

Can you tell us what you plan to use this information for?

[CJ_London]

looks like exactly the same question as here

https://www.accessforums.net/queries...tml#post283343

[orange]

@Ajax

I agree seems eerily similar. Grammar seems a little different ??Could it be a class project???

[CJ_London]

Probably - first thread for both OP's.

Looks like my boy is slightly ahead of the game - at least he referred to the dateadd function

Will continue to watch their posts with interest....