Did you try as suggested in my earlier post?
Did you try as suggested in my earlier post?
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
In fact it can be done without a second table or caertesian query. Instead use a TempVar.
Change the CholesterolQry SQL to
I've removed the unnecessary groupingCode:SELECT BloodData.CurrDate, BloodData.CholesterolTotal, BloodData.HDLCholesterol, BloodData.[LDL Cholesteral], CInt([TempVars]![RefLine]) AS RefLine FROM BloodData;
Then use this code in the Form_Load event of the form containing the chart:
This sets the value for use by the chart. QED!Code:Private Sub Form_Load() TempVars!RefLine = 200 End Sub
Last edited by isladogs; 07-27-2023 at 12:01 AM.
I have solved my problem. I added fields in the table that indicates the value of each reference range and the the value is the same for every record. I added that field to the query for the chart. This gives me a straight line to reference the results of each chart. In Chart Settings I edited the Display Name to show the value the line represents. I've attached the updated DB
BloodTestsUpdate.zip
Isladogs, I saw your post after I had come up with another solution. Your option does provide what I was looking for. I'll try your option and see how it works. Thanks for the input
Your solution should work but both of my alternatives should achieve the same result with less effort. No need to add multiple values that are identical
isladogs, I tried your solution and it is much more efficient than mine. I will continue to use it. Thanks!
Excellent.
I'll write a short web article explaining this approach and reference this thread
Out of interest, which of the two methods did you use?
I used the TempVar solution
Yes, that is OK. There is nothing personal or proprietary in the DB.
Web article now published: Using Combo Charts in Access
Thanks for asking the question and allowing me to use your data
I have one more question regarding the reference line on the chart. Some of the values for the reference line are in tenths, such a 5.7 or 3.2. I set those values for the TempVars in the OnLoad section of the form. However when I run the query for the reference line the values always round either up or down to a whole number. Anyway to get the reference line to show as the exact value shown for the TempVars? I tried formatting the query field with Fixed or Standard and there was no change
The query I provided cast that as an integer: CInt([TempVars]![RefLine]) AS RefLine for reasons i didn't originally explain in the article
Remove that and replace with e.g. Round([TempVars]![RefLine], 1) AS RefLine where 1 = number of decimal places required.
NOTE: Due to an Access bug, you MUST cast or format the TempVar field in the query.
You can't just use [TempVars]![RefLine] in the query as that will produce gibberish:
I've just added a comment to that effect in the web article