Calculate Column in Listbox based on User Date (1 Viewer)

marabak

Registered User.
Local time
Today, 15:18
Joined
Nov 4, 2015
Messages
14
Hi people.

I'm new to this forum, but not truly new to Access.

I currently have a system whereas I have a single table containing some information regarding on-site radioactivity. On a form, I have a listbox displaying some information from that table or, if the user checks an appropriate box, only a portion of the information from the table. This is controlled with some VBA (see below).

I'd like an extra column to be added to the listbox displaying a decay-corrected value of the radioactive materials based upon the date the user puts in a textbox on the same form (defaults as Date() but the user can put in other dates). I can easily refresh the data in the listbox if the date is changed using VBA to requery.

My original thought was to try and create a calculated field in the table, but it really didn't like my use of "[Activity] * Exp(-(log(2)*(date()- [RefDate] ))/8.04)", claiming I was trying to use things that weren't in the table. I have managed to partway solve the problem by adding another field called TodaysDate with a default value of Date() but this is fixed unfortunately (and I'm not sure if it will change on a day to day basis, or just be fixed as that date forever). I can't find a way to direct the calculated field to a textbox on a form to grab the date from there when the query is run.

Fundamentally, I need to dynamically calculate a value based upon a user input for whatever is returned by my query and the user supplied date, and use this to populate an additional column within the listbox. Anyone have any ideas on the best way to proceed with this?



My VBA

Private Sub boxShowOnSite_Click()
'base population of listbox showing capsules on whether user wants to see ALL or just UNUSED

'change some visual aspects of the listbox
If Me.boxShowOnSite.Value = False Then
Forms("Inventory").CapsuleList.ColumnCount = 4
Forms("Inventory").CapsuleList.ColumnWidths = "5cm;4cm;4cm;4cm"
Else
Forms("Inventory").CapsuleList.ColumnCount = 5
Forms("Inventory").CapsuleList.ColumnWidths = "5cm;4cm;4cm;4cm;3cm"
End If

'rerun the query to refresh
Forms("Inventory").CapsuleList.RowSource = loadQuery()
Forms("Inventory").CapsuleList.Requery

End Sub

Function loadQuery() As String
'function to create the listbox SQL string dependent on whether the user wants to see ALL or just UNUSED capsules

'set sql statement
loadQuery = "SELECT OnSite.SerialNo as [Serial Number], OnSite.Activity as [Activity (MBq)], OnSite.RefDate as [Reference Date], OnSite.ExpDate as [Expiry Date], OnSite.Used FROM OnSite"

'finish SQL statement based on value of checkbox
If Forms("Inventory").boxShowOnSite.Value = True Then
loadQuery = loadQuery & " WHERE (OnSite.Used)=False;"
Else
loadQuery = loadQuery & ";"
End If

End Function
 

spikepl

Eledittingent Beliped
Local time
Today, 16:18
Joined
Nov 3, 2010
Messages
6,144
Calculating values in tables based on stuff outside of the tables makes no sense, since the values are no longer fixed. Do that in a query, base your listbox on that query. In a query you can calculate many things ...

Try that. If that fails then show the SQL that "fails", and say in what way it does not produce what is desired
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:18
Joined
May 7, 2009
Messages
19,169
change the function:

Function loadQuery() As String
'function to create the listbox SQL string dependent on whether the user wants to see ALL or just UNUSED capsules

'set sql statement
loadQuery = "SELECT OnSite.SerialNo as [Serial Number], OnSite.Activity as [Activity (MBq)], OnSite.RefDate as [Reference Date], OnSite.ExpDate as [Expiry Date], OnSite.Used [Activity] * Exp(-(log(2)*(#" & Format(date, "mm/dd/yyyy") & "# - [RefDate] ))/8.04) As [Decay-Corrected Value] FROM OnSite"

'finish SQL statement based on value of checkbox
If Forms("Inventory").boxShowOnSite.Value = True Then
loadQuery = loadQuery & " WHERE (OnSite.Used)=False;"
Else
loadQuery = loadQuery & ";"
End If
 

marabak

Registered User.
Local time
Today, 15:18
Joined
Nov 4, 2015
Messages
14
Thank you Spike. I had just figured it out as well, and came back here to post my result. I didn't realise that the SQL in the query could be calculated as well at the time, I thought it was just selecting what was in the table.

Thanks for the reply

My new code is:

Function loadQuery() As String
'function to create the listbox SQL string dependent on whether the user wants to see ALL or just UNUSED capsules

Dim userDate As Date

userDate = Forms("Inventory").dateCalculate

'set sql statement
loadQuery = "SELECT OnSite.SerialNo as [Serial Number], OnSite.Activity as [Activity (MBq)]," _
& "OnSite.RefDate as [Reference Date], OnSite.ExpDate as [Expiry Date]," _
& "Round([Activity] * Exp(-(log(2)*(date()- [OnSite.RefDate] ))/8.04),0) as [Activity on " _
& userDate & "], OnSite.Used FROM OnSite"

'finish SQL statement based on value of checkbox
If Forms("Inventory").boxShowOnSite.Value = True Then
loadQuery = loadQuery & " WHERE (OnSite.Used)=False;"
Else
loadQuery = loadQuery & ";"
End If

End Function
 

Users who are viewing this thread

Top Bottom