Scotto123
asked on
Form with comboboxes to allow selection of date fields for reporting
I have these date fields in my database.
ConsultDate
InitialSim
VSimSchedule
FusionComplete
NormalContourComplete
MDContourComplete
MDFieldsDrawn
DosimetryComplete
MDApprov
PhysicsApprov
I want to create a form that will allow the user to select data using comparison of date1 to date2 and put that data into a report. So, the user may want to calculate difference between ConsultDate and MDApprov or maybe VSimSchedule and DosimetryComplete.
Is there a way to have the fields be selectable in comboboxes and calculate the difference?
ConsultDate
InitialSim
VSimSchedule
FusionComplete
NormalContourComplete
MDContourComplete
MDFieldsDrawn
DosimetryComplete
MDApprov
PhysicsApprov
I want to create a form that will allow the user to select data using comparison of date1 to date2 and put that data into a report. So, the user may want to calculate difference between ConsultDate and MDApprov or maybe VSimSchedule and DosimetryComplete.
Is there a way to have the fields be selectable in comboboxes and calculate the difference?
Have 2 combo boxes: cbo1, cb2
Control rowsource: Qyery/Table : SELECT datfield from tble;
select date1 from cbo1 and date2 from cbo2
in report: textBoxDate: = Forms:theOpenForm!cbo2 - Forms:theOpenForm!cbo1
You may test which is supposed to be greater cno1 or cbo2
You may filter cbo2 to include dates greater than the selected date in cbo1. by adding a where close.
Control rowsource: Qyery/Table : SELECT datfield from tble;
select date1 from cbo1 and date2 from cbo2
in report: textBoxDate: = Forms:theOpenForm!cbo2 - Forms:theOpenForm!cbo1
You may test which is supposed to be greater cno1 or cbo2
You may filter cbo2 to include dates greater than the selected date in cbo1. by adding a where close.
ASKER
Maybe I wasn't specific enough. Or I just don't get it.
I want to have only 2 comboboxes on the form. I want to be able to select which field the query will calculate. So in cbx1 I want a list of the posible fields and cbx2 I want a list of the posible fields.
If I select ConsultDate in cbx1 and MDApprov in cbx2 I want to calculate the difference.
Is this what you have above?
I want to have only 2 comboboxes on the form. I want to be able to select which field the query will calculate. So in cbx1 I want a list of the posible fields and cbx2 I want a list of the posible fields.
If I select ConsultDate in cbx1 and MDApprov in cbx2 I want to calculate the difference.
Is this what you have above?
ASKER
I set the combobox to
set the rowsource of your combo box with
select ConsultDate,InitialSim,VSi
From YourTable
So when I open the drop down list I get a date from my first field. I want to have a list of fields to select from.
set the rowsource of your combo box with
select ConsultDate,InitialSim,VSi
From YourTable
So when I open the drop down list I get a date from my first field. I want to have a list of fields to select from.
using two combo boxes
* difference between ConsultDate and MDApprov
DateDiff("d",me.cbx1,me.cb
* difference between ConsultDate and MDApprov
DateDiff("d",me.cbx1,me.cb
ASKER
I want the user to be able to select which date fields they want to calculate.
ASKER
Here is how I am seeing it.
Date-Selection-Form.jpg
Date-Selection-Form.jpg
to have the names of the fields shown in the combo box
set
Row source type Field lists
Row Source NameOf the table
set
Row source type Field lists
Row Source NameOf the table
ASKER
Got it. Now how do I calculate the difference?
To get the difference in days, just use:
date2-date1
If you set the control source in the same form to cbo2 - cbo1 it displays the difference in days.
To use it in a report, add the qualifying path to the form: Forms!formName!cbo2 - Forms!formName!cbo1
For days, or other formats you may use DateDiff ("interval", date1, date2)
Interval includes: day, month, ... seconds. For days use d, check help on DateDiff for a list of available intervals.
date2-date1
If you set the control source in the same form to cbo2 - cbo1 it displays the difference in days.
To use it in a report, add the qualifying path to the form: Forms!formName!cbo2 - Forms!formName!cbo1
For days, or other formats you may use DateDiff ("interval", date1, date2)
Interval includes: day, month, ... seconds. For days use d, check help on DateDiff for a list of available intervals.
ASKER
The values in the Comboboxes are not dates - they are the date field names.
I tried this but it doesn't work:
Dim SQL As String
Dim SelFields As Variant
SelFields = [" + Me.Combo1 + "]
SQL = "Select " & SelFields & " from [BoardData]"
I tried this but it doesn't work:
Dim SQL As String
Dim SelFields As Variant
SelFields = [" + Me.Combo1 + "]
SQL = "Select " & SelFields & " from [BoardData]"
I see! It can be done.
Can you upload a sample database, and state one example of expected result after selecting from combo boxes?
Can you upload a sample database, and state one example of expected result after selecting from combo boxes?
ASKER
I want to calculate the difference between InitialSim and MDContourComplete and populate this in a query. The result for the one record I have is 26 days.
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK, Cap. I like it!
As I stated in my question, I need to pull this data in a report (or query for report record source). Can I pull the same info for all records in a query to I can generate a report.
As I stated in my question, I need to pull this data in a report (or query for report record source). Can I pull the same info for all records in a query to I can generate a report.
ASKER
Or how about the form pushes the data to a temp table? I'm looking at the code - it's awesome. I just cant figure how to get it in a query.
ASKER
Added new question to continue the solution:
https://www.experts-exchange.com/questions/27824768/Form-comboboxes-Field-List-used-in-for-reporting-Question-2.html
https://www.experts-exchange.com/questions/27824768/Form-comboboxes-Field-List-used-in-for-reporting-Question-2.html
Yes..
set the rowsource of your combo box with
select ConsultDate,InitialSim,VSi
From YourTable
difference is calculated by the DateDiff() function, something like this
* difference between ConsultDate and MDApprov
DateDiff("d",me.combo.colu