cross tab query question

[xmattxman]

I am trying to create a cross tab query that shows row of employee names and columns of dates and in the columns it will show the amount of hours worked that day. I have it working properly but my main issue is I need the user to enter a date range for the query. Not sure how to put that into the cross tab query. When I add in a criteria I get an error message saying it is not in the list. I added an attachment of the original query and the cross tab query I created off of that.

[ranman256]

put the criteria in the base query, Q1 (qryEmpAttendance)
then make a crosstab from Q1

NOTE: crosstabs require you to enter PARAMETERS.
so in the crosstab query, click the parameter button
then enter the data range fields
forms!myForm!txtStart , date/time
forms!myForm!txtEnd , date/time

[xmattxman]

When I put the criteria Between [Forms]![frmEmployeeATLX]![dfsBegDate] And [Forms]![frmEmployeeATLX]![dfsEndDate]in the first query then make the cross tab I get the error Microsoft access database engine does not recognize [Forms]![frmEmployeeATLX]![dfsBegDate] as a valid field name or expression?

I just saw the last half of your post I will try that out.

[xmattxman]

That worked perfectly! Thanks for the help! I had one other question. Right now the columns are showing the amount of hours they worked that day. Is there a way I can make it so if there is a number in that field it shows present and if it is blank it show an X? The field is ATX_TIME_AMOUNT

[ranman256]

The Xtab sums values. X is not a number.

[isladogs]

Use an IIF statement in another query based on your crosstab query

IIf([atx_time_amount]>0,"present","X")

Ranman has pointed out that you can't add text to a number field ...in the sum field of your crosstab

[xmattxman]

That worked great but I need the X to be put where a field is blank? We don't have any negatives on the report only >0 and blanks for when they are absent.

[isladogs]

Do 2 steps:
1. Create your crosstab query
2. Create a new query based on that using the IIf statement I posted before as an additional field

Use the 2nd query for your report