Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-01-2019, 04:25 AM   #1
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 942
Thanks: 64
Thanked 48 Times in 41 Posts
MickJav will become famous soon enough
Crosstab Between dates

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


Attached Images
File Type: png 2019-09-01.png (8.8 KB, 94 views)
File Type: png 2019-09-01 (1).png (38.7 KB, 97 views)

__________________
All open code examples and free projects are only available from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

MickJav is online now   Reply With Quote
Old 09-01-2019, 04:39 AM   #2
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,095
Thanks: 421
Thanked 753 Times in 731 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Crosstab Between dates

And that is the form FrmJobsCrosstab ?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 09-01-2019, 04:45 AM   #3
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 942
Thanks: 64
Thanked 48 Times in 41 Posts
MickJav will become famous soon enough
Re: Crosstab Between dates

Quote:
Originally Posted by Gasman View Post
And that is the form FrmJobsCrosstab ?

Yes Just using it so I can get it wright

__________________
All open code examples and free projects are only available from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

MickJav is online now   Reply With Quote
Old 09-01-2019, 04:49 AM   #4
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 942
Thanks: 64
Thanked 48 Times in 41 Posts
MickJav will become famous soon enough
Re: Crosstab Between dates

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
__________________
All open code examples and free projects are only available from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Last edited by MickJav; 09-01-2019 at 04:55 AM.
MickJav is online now   Reply With Quote
Old 09-01-2019, 04:51 AM   #5
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,095
Thanks: 421
Thanked 753 Times in 731 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Crosstab Between dates

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.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 09-01-2019, 04:58 AM   #6
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 942
Thanks: 64
Thanked 48 Times in 41 Posts
MickJav will become famous soon enough
Re: Crosstab Between dates

Quote:
Originally Posted by Gasman View Post
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
__________________
All open code examples and free projects are only available from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

MickJav is online now   Reply With Quote
Old 09-01-2019, 05:01 AM   #7
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,095
Thanks: 421
Thanked 753 Times in 731 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Crosstab Between dates

I'm on 1903.
Do you want to upload the minimum to test it.?

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 09-01-2019, 05:14 AM   #8
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 942
Thanks: 64
Thanked 48 Times in 41 Posts
MickJav will become famous soon enough
Re: Crosstab Between dates

Quote:
Originally Posted by Gasman View Post
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
__________________
All open code examples and free projects are only available from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

MickJav is online now   Reply With Quote
Old 09-01-2019, 05:25 AM   #9
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,388
Thanks: 112
Thanked 2,849 Times in 2,597 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Crosstab Between dates

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?
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 09-01-2019, 05:27 AM   #10
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,095
Thanks: 421
Thanked 753 Times in 731 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Crosstab Between dates

Probably something to do witth the format?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 09-01-2019, 06:08 AM   #11
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,687
Thanks: 50
Thanked 1,078 Times in 1,059 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Crosstab Between dates

Quote:
Originally Posted by MickJav View Post
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
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...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 09-01-2019, 06:24 AM   #12
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 942
Thanks: 64
Thanked 48 Times in 41 Posts
MickJav will become famous soon enough
Re: Crosstab Between dates

Quote:
Originally Posted by isladogs View Post
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
__________________
All open code examples and free projects are only available from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

MickJav is online now   Reply With Quote
Old 09-01-2019, 06:26 AM   #13
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 942
Thanks: 64
Thanked 48 Times in 41 Posts
MickJav will become famous soon enough
Re: Crosstab Between dates

Quote:
Originally Posted by theDBguy View Post
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
__________________
All open code examples and free projects are only available from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

MickJav is online now   Reply With Quote
Old 09-01-2019, 06:44 AM   #14
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 942
Thanks: 64
Thanked 48 Times in 41 Posts
MickJav will become famous soon enough
Re: Crosstab Between dates

Quote:
Originally Posted by isladogs View Post
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.
__________________
All open code examples and free projects are only available from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

MickJav is online now   Reply With Quote
Old 09-01-2019, 07:00 AM   #15
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 942
Thanks: 64
Thanked 48 Times in 41 Posts
MickJav will become famous soon enough
Re: Crosstab Between dates

Taken everything out thats needed and added to uploaded db


thanks all for your help mick
Attached Files
File Type: zip Database1.zip (44.9 KB, 2 views)

__________________
All open code examples and free projects are only available from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

MickJav is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Crosstab query using dates guinness Queries 7 06-26-2014 12:44 AM
Countiif between dates in crosstab query Cassim Queries 1 11-04-2013 10:15 AM
Crosstab Qry does recognize another Qry's Between Dates penwood Queries 2 06-13-2006 05:57 PM
Crosstab query dates sdawson Queries 8 03-11-2006 09:37 AM
Crosstab Query - Dates? Radach Queries 5 10-10-2005 10:50 AM




All times are GMT -8. The time now is 07:08 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World