date range on a cross-tab report 2

[Chummly66]

Hey everyone,

I serched around and found a couple of threads but being so new at this part of access, I am completely confused in adding in a date range selection.

Here is what I have for my cross-tab, which is a fairly simple one.

TRANSFORM Count(Workorders.WorkOrderID) AS CountOfWorkOrderID
SELECT Workorders.cboManufacturer, Workorders.cboModels, Count(Workorders.WorkOrderID) AS [Total Of WorkOrderID]
FROM Workorders
GROUP BY Workorders.cboManufacturer, Workorders.cboModels
PIVOT Workorders.cboCategory;

I am trying to add a date field called "txtDateToRepair" so I can select which month I want the report to show the data from.

One of the threads I saw earlier went through and defined a date field, and had them add some code in, but every time I tried it, all I got were errors like "statement after end of sql statement" or something like that.

Any help would be extremely appreciated!!

Richard

 

[IT4EVR]

I'm assuming you already have a date field in one of the tables defined. I'm also assuming you have a text field on a form named txtDateToRepair:

PARAMETERS [Forms]![formname]![txtDateToRepair] DateTime;
TRANSFORM Count(Workorders.WorkOrderID) AS CountOfWorkOrderID
SELECT Workorders.cboManufacturer, Workorders.cboModels, Count(Workorders.WorkOrderID) AS [Total Of WorkOrderID]
FROM Workorders
WHERE [datefield] = [Forms]![formname]![txtDateToRepair]
GROUP BY Workorders.cboManufacturer, Workorders.cboModels
PIVOT Workorders.cboCategory;

 

[IT4EVR]

If you need a range between two dates, then you would have to add have two date fields on your form and two date fields (begindate, enddate) defined as parameters. Your where statement would be WHERE Datefield BETWEEN begindate AND enddate.

 

[Chummly66]

IT4Ever,

thanks a bunch for the help. I almost have it exactly where I need it. Your code and info helped out tremendously. One last question, if you dont mind.

I want the report to select all the records based on that txtDatetoRepair field. Basically, I want to enter in a start date of 7/1 and enter an end date of 7/31 and all records where the txtDateToRepair field has a July date, will appear. As you can see, I really am basing the selection on a date range for one field, not on two. Can this be done?

Richard

 

[PHV]

PARAMETERS [Forms]![formname]![whichMonth] Integer;
TRANSFORM Count(Workorders.WorkOrderID) AS CountOfWorkOrderID
SELECT Workorders.cboManufacturer, Workorders.cboModels, Count(Workorders.WorkOrderID) AS [Total Of WorkOrderID]
FROM Workorders
WHERE Month([datefield]) = [Forms]![formname]![whichMonth]
GROUP BY Workorders.cboManufacturer, Workorders.cboModels
PIVOT Workorders.cboCategory;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Chummly66]

PHV,

Superman to the rescue once again. thanks!!!! worked like a charm!

Richard