Crosstab Between dates (1 Viewer)

Dreamweaver

Well-known member
Local time
Today, 20:45
Joined
Nov 28, 2005
Messages
2,466
I have a query that I built to feed data to a crosstab query As Below


Hope somebody can point out where I'm going wrong with the between data And date I created these with the expression builder


Code:
SELECT QryEmployeesCombos.Employee, tblEmployeeAndJobs.AssignedDate, tblJobs.JobName, Format([tblEmployeeAndJobs.TimeStart],"Short Time") & " | " & Format([tblEmployeeAndJobs.TimeEnd],"Short Time") AS Times
FROM tblJobs INNER JOIN (tblEmployeeAndJobs INNER JOIN QryEmployeesCombos ON tblEmployeeAndJobs.EmployeeID = QryEmployeesCombos.EmployeeID) ON tblJobs.JobID = tblEmployeeAndJobs.JobID
WHERE (((tblEmployeeAndJobs.AssignedDate) Between [Forms]![FrmJobsCrosstab]![txtFrom] And [Forms]![FrmJobsCrosstab]![txtTo]));
The crosstab works fine without the limits I.E. between txtFrom And txtTo


When I add the limits I get the message below




The crosstab is a datasheet subform as below but once I'm happy with it I intend using it on a report but wanted to play with it as I haven't done a lot with crosstabs before adding to a report


 

Attachments

  • 2019-09-01.png
    2019-09-01.png
    8.8 KB · Views: 450
  • 2019-09-01 (1).png
    2019-09-01 (1).png
    38.7 KB · Views: 491

Gasman

Enthusiastic Amateur
Local time
Today, 20:45
Joined
Sep 21, 2011
Messages
14,047
And that is the form FrmJobsCrosstab ?
 

Dreamweaver

Well-known member
Local time
Today, 20:45
Joined
Nov 28, 2005
Messages
2,466
This is the SQL for the crosstab just in case it'll help


Code:
TRANSFORM First(QryJobCrosstabData.Times) AS FirstOfTimes
SELECT QryJobCrosstabData.Employee
FROM QryJobCrosstabData
GROUP BY QryJobCrosstabData.Employee
PIVOT Format([AssignedDate],"Short Date");


EDIT: When I add the Between dates into QryJobCrosstabData like Between #01/09/2019# And #07/09/2019# It works
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 20:45
Joined
Sep 21, 2011
Messages
14,047
No, I am just asking as you said you built the query parameters from the Builder, so the control and form names should be correctly spelled, and that form is open when you search?, so at a loss as to why access cannot find the controls.

As a workaround you could use Globals or TempVars?, but I'd love to know what the problem was if you get to the bottom of it.?
 

Dreamweaver

Well-known member
Local time
Today, 20:45
Joined
Nov 28, 2005
Messages
2,466
No, I am just asking as you said you built the query parameters from the Builder, so the control and form names should be correctly spelled, and that form is open when you search?, so at a loss as to why access cannot find the controls.

As a workaround you could use Globals or TempVars?, but I'd love to know what the problem was if you get to the bottom of it.?


I have a feeling it's related to windows update as had a problem yeasterday with another between query I found a work around there will try using your idear but not done that before with a query


thanks mick
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:45
Joined
Sep 21, 2011
Messages
14,047
I'm on 1903.
Do you want to upload the minimum to test it.?
 

Dreamweaver

Well-known member
Local time
Today, 20:45
Joined
Nov 28, 2005
Messages
2,466
I'm on 1903.
Do you want to upload the minimum to test it.?


Just About to go shopping but can pull that data and queries out Have got rid of the datasheet subform and now using the Query As the subforms recordsource like Query.QryJobCrosstab I no longer get the message but I don't get any data using the between txtfrom and txtto but it works when i use between #01/09/2019# and #07/09/2019# in the data query:banghead:
 

isladogs

MVP / VIP
Local time
Today, 20:45
Joined
Jan 14, 2017
Messages
18,186
1. You need Forms!FormName.Form.FormControlName.FormName. You omitted the .Form
2. If you are doing this as SQL in your VBA code, you need to format the UK dates as mm/dd/yyyy
2. Are you aware that the dates in your screenshot aren't in chronological order?
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:45
Joined
Sep 21, 2011
Messages
14,047
Probably something to do witth the format?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:45
Joined
Oct 29, 2018
Messages
21,358
Just About to go shopping but can pull that data and queries out Have got rid of the datasheet subform and now using the Query As the subforms recordsource like Query.QryJobCrosstab I no longer get the message but I don't get any data using the between txtfrom and txtto but it works when i use between #01/09/2019# and #07/09/2019# in the data query:banghead:
Hi. Just as a test, try adding the following at the beginning of your Crosstab Query SQL:
Code:
PARAMETERS [Forms]![FrmJobsCrosstab]![txtFrom] DateTime, [Forms]![FrmJobsCrosstab]![txtTo] DateTime;
Hope it helps...
 

Dreamweaver

Well-known member
Local time
Today, 20:45
Joined
Nov 28, 2005
Messages
2,466
1. You need Forms!FormName.Form.FormControlName.FormName. You omitted the .Form
2. If you are doing this as SQL in your VBA code, you need to format the UK dates as mm/dd/yyyy
2. Are you aware that the dates in your screenshot aren't in chronological order?


Yes I only intend for their to be one weeks entries so havent set an order as yet will try adding form to the expression Thanks
 

Dreamweaver

Well-known member
Local time
Today, 20:45
Joined
Nov 28, 2005
Messages
2,466
Hi. Just as a test, try adding the following at the beginning of your Crosstab Query SQL:
Code:
PARAMETERS [Forms]![FrmJobsCrosstab]![txtFrom] DateTime, [Forms]![FrmJobsCrosstab]![txtTo] DateTime;
Hope it helps...


Hi Just tried that no luck I have also noticed when I make changes to queries It no longer asks me to save the changes it just does it but on forms and others it asks me to save
 

Dreamweaver

Well-known member
Local time
Today, 20:45
Joined
Nov 28, 2005
Messages
2,466
1. You need Forms!FormName.Form.FormControlName.FormName. You omitted the .Form
2. If you are doing this as SQL in your VBA code, you need to format the UK dates as mm/dd/yyyy
2. Are you aware that the dates in your screenshot aren't in chronological order?


Sorted the ordering there are dates missing as these haven't had time assigned but would have for the complete weekly rota



Just tried adding form but no luck Going to see if I can pull what I need out and post it.


I have also noticed if the data query has the between limits when I click to open the crosstab Nothing happens so I copied the sql and deleted the old one and recreated it.
 

Dreamweaver

Well-known member
Local time
Today, 20:45
Joined
Nov 28, 2005
Messages
2,466
Taken everything out thats needed and added to uploaded db


thanks all for your help mick
 

Attachments

  • Database1.zip
    44.9 KB · Views: 123

Gasman

Enthusiastic Amateur
Local time
Today, 20:45
Joined
Sep 21, 2011
Messages
14,047
Works for me? if I do things manually.

I think the problem is you have the crosstab as a source for the subform, which you did not include.
However I believe the subform is created before the mainform controls, so at that time, access dows not know about the main from date controls.?

You need to structure your logic differently.
I'm not sure how at the moment though.:banghead:
 

Dreamweaver

Well-known member
Local time
Today, 20:45
Joined
Nov 28, 2005
Messages
2,466
Works for me? if I do things manually.

I think the problem is you have the crosstab as a source for the subform, which you did not include.
However I believe the subform is created before the mainform controls, so at that time, access dows not know about the main from date controls.?

You need to structure your logic differently.
I'm not sure how at the moment though.:banghead:


Lol I did have the Crosstab on a datasheet subform but when I changed dated the fields created got broken so had to change it for what it is now but when I had the subform I kept getting that message.


just had a thought I'll add the dates to the main rota screen as that will be open or I can make sure it is get back to you thanks mick
 

Dreamweaver

Well-known member
Local time
Today, 20:45
Joined
Nov 28, 2005
Messages
2,466
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
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:45
Joined
Sep 21, 2011
Messages
14,047
OK,
theDBguy had it. You need to put those controls as parameters to the crosstab query.
Even with the source query producing data and open with data, the crosstab still complained about the controls. once I put them as parameters, it does not complain.
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:45
Joined
Sep 21, 2011
Messages
14,047
I've mucked up the column headings now for the crosstab, it is complaining that before you assign to a subform you must set the headings.:banghead:
 

Users who are viewing this thread

Top Bottom