Display days following a specific number
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Display days following a specific number
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.
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.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Display days following a specific number
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
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
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Display days following a specific number
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 wrote: ↑16 Apr 2024, 06:42Create 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
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Display days following a specific number
Please explain very clearly what exactly you want.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Display days following a specific number
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.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Display days following a specific number
What you presented worked, except it was an equality.
-
- NewLounger
- Posts: 15
- Joined: 16 Aug 2022, 05:39
Re: Display days following a specific number
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
-
- 2StarLounger
- Posts: 117
- Joined: 22 Feb 2022, 09:04
Re: Display days following a specific number
How are you meant to work out which comparison operator you want to use?
Using Access 2007/2019.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Display days following a specific number
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.EileenStifone wrote: ↑16 Apr 2024, 23:19Attach 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
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
-
- 2StarLounger
- Posts: 117
- Joined: 22 Feb 2022, 09:04
Re: Display days following a specific number
So when would it be > or >= ?
Using Access 2007/2019.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Display days following a specific number
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 approachHansV wrote: ↑16 Apr 2024, 06:42Create 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
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
You do not have the required permissions to view the files attached to this post.
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Display days following a specific number
Actually there is one small issue, namely weekends. Is there a way to modify tradeday+2 to weekdays? I triedbknight wrote: ↑17 Apr 2024, 20:14I was experimenting with these queries. The first gives an error with anything except a number inequalities don't work, so I tried a similar approachHansV wrote: ↑16 Apr 2024, 06:42Create 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+2Code: 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.Outputs a list that can be used .Code: Select all
SELECT tblMcClellan.* FROM tblMcClellan, quFilterOSC WHERE tblMcClellan.TradeDay Between quFilterOSC.TradeDay And quFilterOSC.TradeDay+2
Code: Select all
SELECT tblMcClellan.*, tblMcClellan.TradeDay
FROM tblMcClellan, quFilterOSC
WHERE (((tblMcClellan.TradeDay) Between [quFilterOSC].[TradeDay] And Weekday([quFilterOSC].[TradeDay]+2)));
-
- 2StarLounger
- Posts: 117
- Joined: 22 Feb 2022, 09:04
Re: Display days following a specific number
Not nit picking. I am trying to understand your logic.Now instead od nit picking how about a solution?
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.
Using Access 2007/2019.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.