Cross-tab Criteria not evaluating

[Juan Schwartz]

I am running a query that prompts for a date and runs a week from that
day. Right now, no matter what is entered, it returns all of the
records in the table. Here is the query.

PARAMETERS [Enter Week Start Date(mm/dd/yy): ] DateTime;
TRANSFORM NZ(Count([Itemized Records].Subject),0) AS Calls
SELECT [Itemized Records].Subject
FROM [Itemized Records]
WHERE (((Format([Date],"mm/dd/yy")) Between [Enter Week Start
Date(mm/dd/yy): ] And (DateAdd("d",6,[Enter Week Start Date(mm/dd/yy):
]))) AND (([Itemized Records].IntrType)="Phone"))
GROUP BY [Itemized Records].Subject
PIVOT Format([Date],"yyyy-mm-dd");

 

[Duane Hookom]

Your query parameters states the parameter is a datetime value yet your
WHERE clause converts the date field to a string. Try:
PARAMETERS [Enter Week Start Date(mm/dd/yy): ] DateTime;
TRANSFORM NZ(Count([Itemized Records].Subject),0) AS Calls
SELECT [Itemized Records].Subject
FROM [Itemized Records]
WHERE [Date] Between [Enter Week Start Date(mm/dd/yy): ]
And (DateAdd("d",6,[Enter Week Start Date(mm/dd/yy):]
AND [Itemized Records].IntrType="Phone"
GROUP BY [Itemized Records].Subject
PIVOT Format([Date],"yyyy-mm-dd");

Better yet, get your criteria from controls on a form rather than parameter
prompts.

 

[Juan Schwartz]

I would like to do that actually.

I am trying to use dates as column headings which will of course not be
pre-defined...

Can you point me to a site with some info on building queries in VBA?

I am experienced in PHP/MySQL and a little VB so it shouldn't be too
hard to pick up, but I can never find a solid site when I look around.

I would like to do a query based upon the criteria... ie, if they
choose a dept from the drop down, it filters it in the query...

That would be something like...

If Me.txtBox1.ControlSource != ""
Then sql .= "bleh"
End If

Am I going in the right direction with this?

 

[Duane Hookom]

Did you try the SQL that I suggested?

Can you live with a specific number of columns? Are the results from this
query going to a report?

 

[Juan Schwartz]

I can live with a pre-determined number as I am pulling between the day
they select and +6 to that. I created a form with a calendar and set
the criteria in my Crosstab as

"Between [Forms]![Weekly]![Startdate] AND [Forms]![Weekly]![Stopdate]"

The StartDate field is a hidden textbox that is populated by the
OnClick code of the Calendar... The Stopdate is calculated from there.
Both are Formatted to "mm/dd/yy" as well.

When I leave the column heading property filled ie, "1,2,3,4,5,6,7",
the query will run. However, when I remove that I get the error "The
Microsoft Jet database engine does not recognize
'Forms!Weekly!StartDate' as a valid field name or expression". Do I
need to add this to the parameters property somehow?

 

[Juan Schwartz]

I did some searching around and found a post where you addressed that
question. I have added it to the parameters and it seems to be working
fine now with the exception of the report. I'm still very interested in
how that would work. The set number is 7 for the columns.

 

[Duane Hookom]

Here is a standard FAQ I have posted at Tek-Tips. You should be able to
change the solution to days rather than months.

===================
Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.
===================

 

[Juan Schwartz]

I was able to play around with the above. I tried adding a dept and
intr_type to my form so they can filter it more. I added it the same
way as the dates, but it doesn't seem to work. These are list boxes and
the query always returns nada with no column headings... only row
headings.

PARAMETERS [Forms]![Weekly]![StartDate] DateTime,
[Forms]![Weekly]![StopDate] DateTime, [Forms]![Weekly]![Dept] Short,
[Forms]![Weekly]![IntrType] Text ( 255 );
TRANSFORM NZ(Count([Itemized Records].Subject),0) AS Calls
SELECT [Itemized Records].Subject, [Itemized Records].IntrType
FROM [Itemized Records]
WHERE (((Format([Date],"mm/dd/yy")) Between
[Forms]![Weekly]![StartDate] And [Forms]![Weekly]![StopDate]) AND
(([Itemized Records].IntrType)=[Forms]![Weekly]![IntrType]) AND
(([Itemized Records].Dept)=[Forms]![Weekly]![Dept]))
GROUP BY [Itemized Records].Subject, [Itemized Records].IntrType
PIVOT Format([Date],"yyyy/mm/dd");

 

[Duane Hookom]

I am not sure how you consider your SQL as "play around with the above". You
haven't used any date difference in the PIVOT and haven't set the Column
Headings property.

I think that I already mentioned the issue with specifying your parameters
as dates and then converting your date field to a string.

Are your list boxes single or multiple select?

 

[Juan Schwartz]

I actually was able to get everything running through VBA. I want to
thank you for your help so far. I've also been using the search in the
newsgroup and a few exerpts from books.

I really enjoy the flexability of the "other side" of access(the
non-wizard side).

I currently have a string that is going to be like: "2,4,5,6" or "2,4"
or "2". I am using it as WHERE field IN(string). Right now it is the
numeric ID of the names of fields from another table. This is part of a
list box. Column 1 = ID, Column 2 = Name. I'd like to echo the names
out on the report... However, the list box returns a list of numbers
seperated by columns.

In php you can use explode() to seperate things out that are seperated
by a common char(in this case a comma). Is there a similar function in
access? I would imagine that within a For Each loop would give me the
desired result.

Thanks for your help in getting me familiar with Access so far. I know
1000% more now then I did before posting here(which is still about 1%
of what I need to know), but progress none-the-less.

Jimmy

 

[Duane Hookom]

There are several ways to use a multi-select list box in filtering a
query/report. Your method is not one of them.

You would need code to write the SQL or a where clause that you can use in
the OpenReport or OpenForm method or update the SQL of a saved query.

 

[Juan Schwartz]

The filtering is already working for the query.

I am doing this in a form just to test my ability to create the query
based on user selections...

------------------------------------------------------------------------------------------------------------

Dim db As DAO.Database
Dim qdf As DAO.QueryDef


If IsNull(StartDate) Then
error = "Please choose a report week"
MsgBox error
Else

For Each varItem In Me.Dept.ItemsSelected
strDept = strDept & "," & Me.Dept.ItemData(varItem)
Next varItem

If Len(strDept) = 0 Then
strDept = ">0"
Else
strDept = Right(strDept, Len(strDept) - 1)
strDeptDisplay = strDept
strDept = "IN(" & strDept & ")"
End If


For Each varItem In Me.IntrType.ItemsSelected
strIntrType = strIntrType & ",'" & Me.IntrType.ItemData(varItem) _
& "'"
Next varItem

If Len(strIntrType) = 0 Then
strIntrType = "Like '*'"
Else
strIntrType = Right(strIntrType, Len(strIntrType) - 1)
strIntrType = "IN(" & strIntrType & ")"
End If



strSQL = "PARAMETERS [forms]![Weekly]![StartDate] DateTime, " & _
"[forms]![Weekly]![StopDate] DateTime; " & _
"TRANSFORM NZ(Count([Itemized Records].Subject),0) AS
Calls " & _
"SELECT [Itemized Records].Subject FROM [Itemized Records]
" & _
"WHERE (((Format([Date],""mm/dd/yy"")) Between
Forms!Weekly!StartDate And Forms!Weekly!StopDate)) AND " & _
"[Itemized Records].IntrType " & strIntrType & " AND " & _
"[Itemized Records].Dept " & strDept & " " & _
"GROUP BY [Itemized Records].Subject " & _
"PIVOT Format([Date],""yyyy/mm/dd"");"
Debug.Print strSQL

Set db = CurrentDb
Set qdf = db.QueryDefs("Weekly Phone")
qdf.SQL = strSQL
Set qdf = Nothing
Set db = Nothing
DoCmd.OpenQuery ("Weekly Phone")

-----------------------------------------------------------------------

The variable strDeptDisplay is going to be a list of numbers seperated
by commas or a single number. It will be used in the Where clause as
strDept would be something like " IN(2,4,5,6) ". I created
strDeptDisplay to show the list without the IN included. strDeptDisplay
would be "2,4,5,6" in this case. I want to break but the
string(presumably into an array).

 

[Juan Schwartz]

I also noticed that in my x-tab query that the PIVOT of the date is
skipped if there are no values for that day. Is there a way to force 0s
in that instance?

 

[Duane Hookom]

I would go back to my recommended method when creating crosstabs with date
intervals as column headings.

Otherwise, as long as you a messing around with the SQL property of the
querydef, create a list of all possible dates and include them in an IN
clause following the PIVOT.

PIVOT Format([Date],""yyyy/mm/dd"") IN ('2005/12/01','2005/12/02',.....);"

 

[Juan Schwartz]

Very good idea. I didn't grasp that previously. I will do that tonight.

 

[Juan Schwartz]

I was able to get this to work. However, when trying to get these to be
the column headings in a report... I failed miserably.

I can actually get the headings to change, but I can't make the control
sources right. How does one go about this?

 

[Duane Hookom]

What did you try? Did you use the DateAdd() functions in text boxes as my
solution suggested?

 

[Juan Schwartz]

How would I get the original date over to the report from the form to
use the DataAdd() function?

 

[Duane Hookom]

From my previous posting:
=====================
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
=====================
I expect you will need to change the form and control name as well as the
date interval.

 

[Juan Schwartz]

Thanks Duane... This is all working fine now.