Display days following a specific number

[bknight]

I have a Db that has a bunch of numbers in it. What I would like to do is display the next couple of days numbers when a specific value occurs.
The date that has the number occurring and all the next two days values.
Attached is tonight's values plus previous daily values from the Db meeting the criteria.
So how would I design a query to display all the next two days values? Obviously there are no next two values from today.
The key value is the field OSC.

[HansV]

Create a query with SQL like this:

SELECT TradeDay FROM TableName WHERE OSC=specific_value

Let's say you name it qryDate.
Next, create a query like this:

SELECT TableName.*
FROM TableName, qryDate
WHERE TableName.TradeDay Between qryDate.TradeDay And qryDate.TradeDay+2

[bknight]

Create a query with SQL like this:

SELECT TradeDay FROM TableName WHERE OSC=specific_value

Let's say you name it qryDate.
Next, create a query like this:

SELECT TableName.*
FROM TableName, qryDate
WHERE TableName.TradeDay Between qryDate.TradeDay And qryDate.TradeDay+2

OSC=specific_value really needs to be either a l<,> or those followed by =. That creates an error where the expression is too complex error. That would report all the numbers not just one. For example the spreadsheet was created with a value of <-380.

[HansV]

Please explain very clearly what exactly you want.

[bknight]

All the days meeting or exceeding the criteria and the two days following each of those days. The criteria is an inequality but could be an equal proceeding the iniquality.

[HansV]

I hope someone else can help you.

[bknight]

What you presented worked, except it was an equality.

[EileenStifone]

Attach an excel file where for 2024:

• on one sheet of paper are all the data collected

• on another sheet all the data filtered with the OSC value

[Gasman]

How are you meant to work out which comparison operator you want to use?

[bknight]

Attach an excel file where for 2024:

• on one sheet of paper are all the data collected

• on another sheet all the data filtered with the OSC value

On one spreadsheet all the data that met the criteria of <-280 is diplayed on the file I submitted. All the data would be greater than allowed sorry.

[bknight]

How are you meant to work out which comparison operator you want to use?

Depends on the situation, currently it is < but, <= would work also wouldn't it?

[Gasman]

So when would it be > or >= ?

[bknight]

So when would it be > or >= ?

There is no way to answer that question other than I already have answered. Now instead od nit picking how about a solution?

[bknight]

Create a query with SQL like this:

SELECT TradeDay FROM TableName WHERE OSC=specific_value

Let's say you name it qryDate.
Next, create a query like this:

SELECT TableName.*
FROM TableName, qryDate
WHERE TableName.TradeDay Between qryDate.TradeDay And qryDate.TradeDay+2

I was experimenting with these queries. The first gives an error with anything except a number inequalities don't work, so I tried a similar approach

Code: Select all

SELECT tblMcClellan.DateIndex, tblMcClellan.TradeDay, tblMcClellan.OSC, tblMcClellan.Sum
FROM tblMcClellan
WHERE (((tblMcClellan.OSC)<=-290))
ORDER BY tblMcClellan.TradeDay;

Hard coded to a desired criteria.

Code: Select all

SELECT tblMcClellan.*
FROM tblMcClellan, quFilterOSC
WHERE tblMcClellan.TradeDay Between quFilterOSC.TradeDay And quFilterOSC.TradeDay+2

Outputs a list that can be used .

[bknight]

Create a query with SQL like this:

SELECT TradeDay FROM TableName WHERE OSC=specific_value

Let's say you name it qryDate.
Next, create a query like this:

SELECT TableName.*
FROM TableName, qryDate
WHERE TableName.TradeDay Between qryDate.TradeDay And qryDate.TradeDay+2

I was experimenting with these queries. The first gives an error with anything except a number inequalities don't work, so I tried a similar approach

Code: Select all

SELECT tblMcClellan.DateIndex, tblMcClellan.TradeDay, tblMcClellan.OSC, tblMcClellan.Sum
FROM tblMcClellan
WHERE (((tblMcClellan.OSC)<=-290))
ORDER BY tblMcClellan.TradeDay;

Hard coded to a desired criteria.

Code: Select all

SELECT tblMcClellan.*
FROM tblMcClellan, quFilterOSC
WHERE tblMcClellan.TradeDay Between quFilterOSC.TradeDay And quFilterOSC.TradeDay+2

Outputs a list that can be used .

Actually there is one small issue, namely weekends. Is there a way to modify tradeday+2 to weekdays? I tried

Code: Select all

SELECT tblMcClellan.*, tblMcClellan.TradeDay
FROM tblMcClellan, quFilterOSC
WHERE (((tblMcClellan.TradeDay) Between [quFilterOSC].[TradeDay] And Weekday([quFilterOSC].[TradeDay]+2)));

The output balooned to approximately 300 K records

[Gasman]

Now instead od nit picking how about a solution?

Not nit picking. I am trying to understand your logic.

That is essential in trying to find a solution. I believe I have one, but I will let someone else help instead.

From your extra tests, it would have worked as well, at least for the query part.

Ah well.