473,288 Members | 1,794 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

Passing a date range into a crosstab query

I'm trying to allow a user to type in a beginning data and an ending date to be used in a crosstab query - Below is the query:

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(imagingdb.ID) AS CountOfID
  2. TRANSFORM Count(imagingdb.ID) AS CountOfID
  3. SELECT imagingdb.Scheduled, imagingdb.ATT, imagingdb.ACQ
  4. FROM imagingdb
  5. WHERE (((imagingdb.Scheduled) Is Not Null) AND ((imagingdb.ATT) Is Not Null) AND ((imagingdb.ACQ) Is Not Null) AND ((imagingdb.Project) Is Not Null))
  6. GROUP BY imagingdb.Scheduled, imagingdb.ATT, imagingdb.ACQ
  7. PIVOT imagingdb.Project;
It works if I type the date range Between 4/1/2007 And 4/30/2007 but I want to give the user a form and let them type in the range. I tryed the Between [Enter Beginning Date] And [Enter Ending Date] and I tryed a form for the date but to no avail.

Any help would be appreciated
May 19 '07 #1
1 5212
JConsulting
603 Expert 512MB
I'm trying to allow a user to type in a beginning data and an ending date to be used in a crosstab query - Below is the query:

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(imagingdb.ID) AS CountOfID
  2. TRANSFORM Count(imagingdb.ID) AS CountOfID
  3. SELECT imagingdb.Scheduled, imagingdb.ATT, imagingdb.ACQ
  4. FROM imagingdb
  5. WHERE (((imagingdb.Scheduled) Is Not Null) AND ((imagingdb.ATT) Is Not Null) AND ((imagingdb.ACQ) Is Not Null) AND ((imagingdb.Project) Is Not Null))
  6. GROUP BY imagingdb.Scheduled, imagingdb.ATT, imagingdb.ACQ
  7. PIVOT imagingdb.Project;
It works if I type the date range Between 4/1/2007 And 4/30/2007 but I want to give the user a form and let them type in the range. I tryed the Between [Enter Beginning Date] And [Enter Ending Date] and I tryed a form for the date but to no avail.

Any help would be appreciated

n your crosstab query (query design grid) enter yr parameters in criteria for example: Between [Enter Beginning Date] And [Enter Ending Date]

Then select Parameters option from Query Menu and enter same for example: [Enter Beginning Date] And [Enter Ending Date] in data field u can type date and it will work.

If u are using a form to supply parameters then that would be same for example : [Forms]![Form1]![startdate] ^ [Forms]![Form1]![enddate]
J
May 19 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Claus Haslauer | last post by:
Hi, I want to create a crosstab query that looks like this Date | Elevation 1 | Elevation 2 | ... ______________________________________________________________________ Date 1 | xx.y | xx.y...
2
by: david.isaacks | last post by:
I have a table with an as a field with the date/time in it. Formated like "3/20/2006 7:25:00 AM" I want to be able to create a query for just the dates in Feb, March, April, etc.. I do want to...
6
by: delsay | last post by:
I am attempting to create a query that creates a Count for each month and year combo starting in 1992 (Jan 1992, Feb 1992). The field Purchase Date contains dates that are in the Date/Time format. ...
2
by: sixdeuce62 | last post by:
Hello, I am trying to create a query that will prompt me to enter the parameter value if beginning date and ending date. I have created everything I need in the query, but I have to manually go...
1
by: kappa | last post by:
I have a problem in ALL my queries when I run a specific date range. Access always crashes when I view in Datasheet view. This doesn't occur if it's a parameter query and I am prompted to enter the...
5
by: HowHow | last post by:
First time using crosstab query because tired of creating queries and put them together in one query (such a stupid thing to do :D). Not sure how it works still. I have link table called...
2
by: Jim Devenish | last post by:
I wish to create a crosstab query as the record source for a report. It needs to count data between selected dates which are entered by the user in a popup window. The following Select query...
4
by: rekhasc | last post by:
hi friends.. i am using sql server 2005 reporting services.. in database the table contains startdate row and enddate row... i want the query which display all the contents between these...
6
by: lisacrowe | last post by:
I have a simple database recording complaints. A crosstab query is based on a query which returns resolved complaints only. The crosstab has the field Complaint Type as a row heading and Outcome as a...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.