Using field list combobox to set field names in query

[GS500]

I’m developing a claim tracking database that tracks dates of events that occur in the course of processing a claim; such as, Loss Date, Report Date, Estimate Date, Payment Date, etc. There are 16 different “Events” in all.

I currently have the following tables set up:

tblClaim
ClaimID
ClaimNumber
fkEmpID

tblEmployee
EmpID
EmpName

tblEvents
EventID
EventName

tblClaimEvents
ClaimEventID
fkClaimID
fkEventID
EventDate

What I need to do is create a form where management can choose two or more events, and calculate the average number of days between two of any of the events, for an employee, or all employees.

I have created a crosstab query to change the values in the EventName field in tblEvents to field names, and the EventDate as values for the related EventNames. I created another query based on this query to do the DateDiff.

I created combo boxes on my form with the Row Source Type set to Field List, for a list of fields in my crosstab query. I’ve tried to use the following DateDiff function to get the days between the two fields selected in my combo boxes:

DateDiff("d",[Forms]![frmReportBuilder]![cboEvent1],[Forms]![frmReportBuilder]![cboEvent2])

But I get an error about unrecognized field name or expression for my combo boxes. So I added my combo boxes in the query parameter window, with a data type as both text and value, but with both I get an error “This expression is typed incorrectly or is too complex to be evaluated.” I also specified the column headings in the crosstab but I still am getting the “too complex” error.
I’m pretty sure it’s trying to do a Datediff on the literal values in the comboboxes and not recognizing that I’m trying to specify field names.
Is it possible to assign field names in DateDiff this way? Or is there a completely different way I should go about this?

 

[Guus2005]

before you create a crosstab query you have to make sure that all fields are present. especially calculated fields. once a crosstab query is created you can only filter on the resultset.

so make sure that you datediff field is part of the query which will be converted into a crosstab query.

split the problems into smaller parts and create queries to solve them. later you can combine the queries to produce the resultset. when you have the resultset, create the crosstab query based on this resultset.
this way each query is easier to debug and the resulting crosstab is not complex anymore.

make sure that your datediff function produces the right result. feeding the function a NULL value messes up the result and returns an error message.

HTH