Crosstab Between dates

MickJav said:
Well the data query opens with the limiting txtboxes on the main rota but the crosstab just opens with a white screen.


I may run the office repair but not sure if that will make it worse or just give up for the day lol
Click to expand...

I don't think there is anything wrong with your system, just your and my lack of expertise with crosstab queries.;)
 
See if you can fix the crosstab query. Not sure what was there initially :(
 

Attachments

Gasman said:
I don't think there is anything wrong with your system, just your and my lack of expertise with crosstab queries.;)
Click to expand...


I have the same problem with crosstabs this is my first in years
thanks for your help I'll try the db guys solution again with my rota screen be back soon
 
I seem to recall that you have to do something fancy to get dynamic column headings?

I can change the values on the form and open the crosstab and I get the same data from when it worked, with Sep before Aug.?
 
Added the peramators both the way thedbguy and Isladogs suggested


Code: 
PARAMETERS Forms!frmRota.form.txtFrom DateTime, Forms!frmRota.form.txtTo DateTime;
TRANSFORM First(QryJobCrosstabData.Times) AS FirstOfTimes
SELECT QryJobCrosstabData.Employee
FROM QryJobCrosstabData
GROUP BY QryJobCrosstabData.Employee
ORDER BY Format([AssignedDate],"Short Date")
PIVOT Format([AssignedDate],"Short Date");


I still get a blank subform but it opens when I open the crosstab from the db window but it still shows all records
 
Actually that was the DB Guy's suggestion.
AFAIA I have never used parameters with a crosstab query.
Some forum members state they are required in any crosstab query. I disagree.
 
Yes, but if you change the form values, you still get the same data?

MickJav said:
Added the peramators both the way thedbguy and Isladogs suggested


Code: 
PARAMETERS Forms!frmRota.form.txtFrom DateTime, Forms!frmRota.form.txtTo DateTime;
TRANSFORM First(QryJobCrosstabData.Times) AS FirstOfTimes
SELECT QryJobCrosstabData.Employee
FROM QryJobCrosstabData
GROUP BY QryJobCrosstabData.Employee
ORDER BY Format([AssignedDate],"Short Date")
PIVOT Format([AssignedDate],"Short Date");


I still get a blank subform but it opens when I open the crosstab from the db window but it still shows all records
Click to expand...
 
Well had no luck tried every way I can think of to add the limiting dates I've run out of idears
 
Gasman said:
Yes, but if you change the form values, you still get the same data?
Click to expand...


If it's opened without the form then it displays all data I had a look at your one could see where you made the change but as you said it mucks the date data up.


thanks
mick
 
I can get it to respect the form values now, but Aug still comes after Sep ?:banghead:
 
See if you can understand this one from HiTechCoach

I am sure I did something like this for the call logs in work.

I'll have a look tomorrow to see how I did it.
 

Attachments

Gasman said:
See if you can understand this one from HiTechCoach

I am sure I did something like this for the call logs in work.

I'll have a look tomorrow to see how I did it.
Click to expand...


That worked nicely might use tables with mine and create it manually.


tried useing the where like they did and thing still wouldn't open.


I might right a code solution as that I know would work just means another local table but was hoping to get this working.


thanks for all your help mick
 
OK, I found one of mine.

I used an actual form for the crosstab, not just a subform control.
Each of the controls is named vName and a digit, eg vName1, vName2

attachment.php

The form has no record source, that is created on Form open

Code: 
Private Sub Form_Load()
Dim n As Integer
Dim fld As Variant
  
For Each fld In Me.Recordset.Fields
    Do
        With Me.Controls(n)
            If .ControlType = acTextBox And Left(.Name, 5) = "vName" Then
                .ControlSource = fld.Name
                .Controls(0).Caption = fld.Name
                 n = n + 1
                 Exit Do
              End If
           End With
           n = n + 1
      Loop
 Next
End Sub

Public Sub Form_Open(Cancel As Integer)
Dim strCaller As String, strSql As String, strQuery As String

strCaller = PersonList()

strQuery = "qryWeekCallSummary_Crosstab"

'strSQL = "PARAMETERS pCallSystem Text ( 255 );"
strSql = strSql & " TRANSFORM Sum([SumOfCallSecs]/86400) AS Duration"
strSql = strSql & " SELECT qryWeekCallSummary.WeekEndDate"
strSql = strSql & " FROM qryWeekCallSummary "
If Len(gCallSystem) > 0 Then
    strSql = strSql & " WHERE qryWeekCallSummary.CallSystem = '" & gCallSystem & "'"
End If
strSql = strSql & " GROUP BY qryWeekCallSummary.WeekEndDate"
strSql = strSql & " ORDER BY qryWeekCallSummary.WeekEndDate DESC"
strSql = strSql & " PIVOT qryWeekCallSummary.Caller IN (" & strCaller & ")"

'CurrentDb.QueryDefs(strQuery).Parameters("[pCallSystem]") = gCallSystem
CurrentDb.QueryDefs(strQuery).SQL = strSql
Me.RecordSource = strSql
End Sub

PersonList was just getting active users. In your case it would be your dates.
gCallSystem was either BT or the other one which I cannot remember now.:banghead:

So in fact mine is the opposite to yours as I have limited users and lots of dates, but that is a reasonably simple method to have dynamic crosstab headings.

HTH
 

Attachments

  • crosstabform.PNG
    crosstabform.PNG
    49.1 KB · Views: 224
I just found this which might be of interest he might share the code he uses if you ask nicely lol.


I didn't think of a coded solution I'll give it a try but got to write a time validation tonight that will complete my rota entry system.


thanks for your help mick
 
MickJav said:
I just found this which might be of interest he might share the code he uses if you ask nicely lol.


I didn't think of a coded solution I'll give it a try but got to write a time validation tonight that will complete my rota entry system.


thanks for your help mick
Click to expand...

I think you have forgotten the link?
 
DONE IT you have to add both the Criteria and paramiters

Criteria = Between [Forms]![frmRota]![txtFrom] And [Forms]![frmRota]![txtTo]

paramiters
Forms!frmRota!txtFrom
Forms!frmRota!txtTo


You should note it don't like the brackets in the paramiters


Still having problems with the subform but I think It dosn't like an embeded query but it's working so nearly there lol


attachment.php
 

Attachments

  • 2019-09-02.png
    2019-09-02.png
    18.9 KB · Views: 227
Last edited:
You must log in or register to reply here.

Similar threads

J
Default Date in Textbox
Replies
5
Views
656
Pat Hartman
P
MajP
The Microsoft Engine Does not Recognize... as a Valid Field Name in CrossTab
Replies
5
Views
424
Pat Hartman
P
S
Link records between two tables based on range?
Replies
3
Views
428
Mark_
M
S
Crosstab/Parameters/Reports combo
Replies
15
Views
888
DHookom
DHookom
S
Solved Criteria Between two Dates Optional and not Mandatory
Replies
13
Views
1,078
Sarah.M
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom