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
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