Good Morning, Everyone!

In my continuing saga of dealing with a wonky scatter chart embedded on an Access 2010 form, I need to know the easiest way to extract XValues, Values from points inthe single series on my chart.

Can someone kindly help me?

Many Thanks!

MinnesotaPete

Hi MinnesotaPete

Is this on a form or report? - your post states "embedded on an Access 2010 form", while the post is in the reports forum.

Where does the data come from which the graph is generated from?, can you access this data directly from it's source?

If not, you could use the Mouse Down event of the graph control, this will return the X&Y position of the click related to the controls edges (in TWIPS), if you can then relate the X and Y co-ordinates to the values on the appropriate X&Y axes then this would give you some idea of the values, though they may be subject to some error. Dependent on the XY values, it may be possible to 'snap' the XY returns to specific values within a range of the values returned by the Mouse down event.

hth

MinnesotaPete,



This is too abstract, and doesn't provide any actual information we need. To "extract" something means to remove something from something else. You need to let us know what you're actually trying to do, in plain english, and in more detail.

For example, are you trying to remove certain rows of data from the query that drives your chart? If so, what sort of data are you working with, and what rule would you use to define the rows that need to be removed?

Or are you trying to create a trend line on your chart, and then determine a Y value on that trend line based on a given X value?

Dennis

Here's what I'm attempting to do.

0. One at a time read a table containing X and Y values (all integers)

1. Then, read through the points on a graph, the graph containing one and only one series, and for each point determine programmatically via VBA what the X and Y values are.

2. When I identify the X and Y values I am looking for, highlight that point on the graph by changing the points colour.

3. This all takes place on a single Access 2010 form.

That's it!

As always I do appreciate your assistance. I've learned a lot from this Forum.

PS: I didn't see an Access Graphing forum. That's why I put this question to this forum.

MinnesotaPete,

You can easily go through the chart's recordset, reading through the values - no need to get the chart involved until you color in your final decision. Speaking of which...



HOW do you identify the X and Y you're looking for?

Dennis

Hi Dennis!

The two values are in a table that gets updated daily. I only need to read through the XValues & Values (I think that's right) once and match them against a point on a chart that are comprised of the same values. What I need is a code sample that I could use to read through the chart's points, parsing / pulling / extracting / retrieving the X and Y values from the chart.

As they say in Finland . . . Kiitos!

MinnesotaPete

MinnesotaPete,

Forget about the chart for now. A chart is just a way of DISPLAYING data. What you're describing is data mining, which doesn't require a chart.

Here's what you can do... If you have a table of data for your chart like this:



And you have another table with a single pair of coordinates:



You can use a DLookup() function to find which record in the larger table matches the record in the single-record table:

X = DLookup("[LineID]", "tblChartData", "[CoorX]=" & FindX & " And [CoorY]=" & FindY)

"LineID" represents the Primary Key field for the first table, a field that has a unique value for each record. People generally use an Autonumber field for this sort of thing. The above DLookup() function will return a value of 14, indicating that the record where LineID = 14 is the one that contains the coordinates that match the ones in your table with a single value.

Now you have figured out which record in the chart data is the one that needs to be colored, and you didn't need the chart to do so. You also didn't need to read through each record one at a time - the DLookup() function just needs to know how to filter down it's results. In this case, the CoorX value in the chart table had to match the FindX value in the other table AND the Coor& value in the chart table had to match the FindY value in the other table. Once the DLookup function finds a record that matches those rules, it returns the value for the field you ask for. For more info, check out the Help file on DLookup in Access's Visual Basic Editor.

Hope this helps,

Dennis

That works perfectly. Now I just need to know how to take the two values and make a point on my graph out of them. What is the best way to do that? Is there a function that would allow me to pass in two values (X and Y) and then plot the resulting point?

Thanks!

MP

MP,



If this question has anything to do with your previous thread, I'd say use a chart like in the demo I uploaded to that thread. Create a query that combines your two tables so that you have three columns - one with the X values, one with all of the Y values, and a third column with a single Y value in the appropriate row. Once you have that query, use it to drive your chart. No need for a DLookup function, even.

This is a good example of why you should provide all of the relevant info when asking your question. It would've been much simpler had you indicated that you have two tables, one with a single record that you want to find a match for in another table, and you want to chart both the main data and the matched data.

Hope this helps,

Dennis

MP,

Here's the SQL for a query that would combine the two tables I've described above:

SELECT tblTestData.PlotX, tblTestData.PlotY,
IIf([tblFindData].[FindX]=[tblTestData].[PlotX],IIf([tblFindData].[FindY]=[tblTe
stData].[PlotY],[tblTestData].[PlotY],Null),Null) AS CFDY
FROM tblTestData, tblFindData;

So, if the PlotX value is the same as the FindX value AND the PlotY value is the same as the FindY value, then the 3rd column will contain a copy of the PlotY value. Otherwise, it will be Null.

Using this query for the form/chart I posted to the previous thread should work just fine. You just need to add the extra table with a single record in it so the query can find that data.

Hope this helps,

Dennis