Start Date/ End Date query

  • Thread starter Thread starter singing4phun
  • Start date Start date
S

singing4phun

I have created a query which pulls info from tables in the same database. I
placed a start date/ end date (including "is null") criteria on the query.

When I run the report I receive data however, there are "gaps" in the data.
For example, if I request the date range of 02/01/08 thru 03/30/08, I get
back a blank datasheet. The records are in the table, but not coming back in
the query.

Also, if I leave the start date and end date as "null", I get data back data
but it is sparratic and always produces the same records (beginning with
01/07/08, then jumps to 04/26/08, then jumps to 6/25/08, then 10/12/08, to
11/17/08 and finally 12/1 thru 12/10/08 with duplicates).

There are are over 4K in the database, but the query only pulls back a
portion and that portion is sprinkled through the date range in date groups,
with duplicates.

How do I fix this?
 
It would help if you provided a sample of the SQL statement you are trying to
use.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
This is the statement I have in the "Date" field of my query which is pulling
from my table. "Between [Start Date] And [End Date]"

The next two fields in the query are: [Start Date] Is Null and then [End
Date] Is Null.

I'm not sure where to copy and paste the actual SQL statement from.
 
From the query grid, find the View menu option or button on the ribbon bar
(2007), select the SQL view to view the entire SQL for the Query.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



singing4phun said:
This is the statement I have in the "Date" field of my query which is pulling
from my table. "Between [Start Date] And [End Date]"

The next two fields in the query are: [Start Date] Is Null and then [End
Date] Is Null.

I'm not sure where to copy and paste the actual SQL statement from.

Dale Fye said:
It would help if you provided a sample of the SQL statement you are trying to
use.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
Click to expand...
Click to expand...
 
Is this it?

SELECT [Field Tagger UT EID].[UT EID], [UT Field Rep Tagging].[Tag ID #],
[UT Field Rep Tagging].[Serial #], [UT Field Rep Tagging].Cost, [UT Field Rep
Tagging].[Model #], [UT Field Rep Tagging].Description, [UT Field Rep
Tagging].[Item Class Code], [UT Building Detail].Site, [UT Building
Detail].[Builging #], [UT Field Rep Tagging].Floor, [UT Field Rep
Tagging].[Room #], [UT Field Rep Tagging].[PO #], [UT Field Rep
Tagging].[Item #], [UT Building Detail].[Mac Addres], [Unit Codes].Unit, [UT
Field Rep Tagging].Notes, [UT Building Detail].[Add To], [UT Field Rep
Tagging].[Date Tagged]
FROM [UT Building Detail], ([Unit Codes] INNER JOIN [UT Field Rep Tagging]
ON [Unit Codes].[Unit Description] = [UT Field Rep Tagging].[Unit Department
Name]) INNER JOIN [Field Tagger UT EID] ON [UT Field Rep Tagging].[Field Rep
Name] = [Field Tagger UT EID].[Field Tagger Name]
WHERE ((([UT Field Rep Tagging].[Date Tagged]) Between [Start Date] And [End
Date])) OR ((([Start Date]) Is Null) AND (([End Date]) Is Null));


Dale Fye said:
From the query grid, find the View menu option or button on the ribbon bar
(2007), select the SQL view to view the entire SQL for the Query.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



singing4phun said:
This is the statement I have in the "Date" field of my query which is pulling
from my table. "Between [Start Date] And [End Date]"

The next two fields in the query are: [Start Date] Is Null and then [End
Date] Is Null.

I'm not sure where to copy and paste the actual SQL statement from.

Dale Fye said:
It would help if you provided a sample of the SQL statement you are trying to
use.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

I have created a query which pulls info from tables in the same database. I
placed a start date/ end date (including "is null") criteria on the query.

When I run the report I receive data however, there are "gaps" in the data.
For example, if I request the date range of 02/01/08 thru 03/30/08, I get
back a blank datasheet. The records are in the table, but not coming back in
the query.

Also, if I leave the start date and end date as "null", I get data back data
but it is sparratic and always produces the same records (beginning with
01/07/08, then jumps to 04/26/08, then jumps to 6/25/08, then 10/12/08, to
11/17/08 and finally 12/1 thru 12/10/08 with duplicates).

There are are over 4K in the database, but the query only pulls back a
portion and that portion is sprinkled through the date range in date groups,
with duplicates.

How do I fix this?
Click to expand...
Click to expand...
Click to expand...
 
Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
This is the statement I have in the "Date" field of my query which is pulling
from my table. "Between [Start Date] And [End Date]"

The next two fields in the query are: [Start Date] Is Null and then [End
Date] Is Null.

I'm not sure where to copy and paste the actual SQL statement from.

Dale Fye said:
It would help if you provided a sample of the SQL statement you are trying to
use.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
Click to expand...
Click to expand...
 
Are [Start Date] and [End Date] parameters that you are passing to the query
when it gets run? If so, this is what we call a parameter query.

If so, they probably need to be declared as dates.
1. Open the query in design view
2. Right click in the top portion of the query design window, but not on one
of the tables, then select the Parameters option from the popup menu
3. In the parameters dialog box, enter [Start Date] on one row, and [End
Date] on another, making sure to select Date/Time as their data type. Then
click OK
4. Save the query
Now, run the query, are you getting the results you expect?

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



singing4phun said:
Is this it?

SELECT [Field Tagger UT EID].[UT EID], [UT Field Rep Tagging].[Tag ID #],
[UT Field Rep Tagging].[Serial #], [UT Field Rep Tagging].Cost, [UT Field Rep
Tagging].[Model #], [UT Field Rep Tagging].Description, [UT Field Rep
Tagging].[Item Class Code], [UT Building Detail].Site, [UT Building
Detail].[Builging #], [UT Field Rep Tagging].Floor, [UT Field Rep
Tagging].[Room #], [UT Field Rep Tagging].[PO #], [UT Field Rep
Tagging].[Item #], [UT Building Detail].[Mac Addres], [Unit Codes].Unit, [UT
Field Rep Tagging].Notes, [UT Building Detail].[Add To], [UT Field Rep
Tagging].[Date Tagged]
FROM [UT Building Detail], ([Unit Codes] INNER JOIN [UT Field Rep Tagging]
ON [Unit Codes].[Unit Description] = [UT Field Rep Tagging].[Unit Department
Name]) INNER JOIN [Field Tagger UT EID] ON [UT Field Rep Tagging].[Field Rep
Name] = [Field Tagger UT EID].[Field Tagger Name]
WHERE ((([UT Field Rep Tagging].[Date Tagged]) Between [Start Date] And [End
Date])) OR ((([Start Date]) Is Null) AND (([End Date]) Is Null));


Dale Fye said:
From the query grid, find the View menu option or button on the ribbon bar
(2007), select the SQL view to view the entire SQL for the Query.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



singing4phun said:
This is the statement I have in the "Date" field of my query which is pulling
from my table. "Between [Start Date] And [End Date]"

The next two fields in the query are: [Start Date] Is Null and then [End
Date] Is Null.

I'm not sure where to copy and paste the actual SQL statement from.

:

It would help if you provided a sample of the SQL statement you are trying to
use.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

I have created a query which pulls info from tables in the same database. I
placed a start date/ end date (including "is null") criteria on the query.

When I run the report I receive data however, there are "gaps" in the data.
For example, if I request the date range of 02/01/08 thru 03/30/08, I get
back a blank datasheet. The records are in the table, but not coming back in
the query.

Also, if I leave the start date and end date as "null", I get data back data
but it is sparratic and always produces the same records (beginning with
01/07/08, then jumps to 04/26/08, then jumps to 6/25/08, then 10/12/08, to
11/17/08 and finally 12/1 thru 12/10/08 with duplicates).

There are are over 4K in the database, but the query only pulls back a
portion and that portion is sprinkled through the date range in date groups,
with duplicates.

How do I fix this?
Click to expand...
Click to expand...
Click to expand...
 
That apparently did the trick. Thanks a million!

Dale Fye said:
Are [Start Date] and [End Date] parameters that you are passing to the query
when it gets run? If so, this is what we call a parameter query.

If so, they probably need to be declared as dates.
1. Open the query in design view
2. Right click in the top portion of the query design window, but not on one
of the tables, then select the Parameters option from the popup menu
3. In the parameters dialog box, enter [Start Date] on one row, and [End
Date] on another, making sure to select Date/Time as their data type. Then
click OK
4. Save the query
Now, run the query, are you getting the results you expect?

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



singing4phun said:
Is this it?

SELECT [Field Tagger UT EID].[UT EID], [UT Field Rep Tagging].[Tag ID #],
[UT Field Rep Tagging].[Serial #], [UT Field Rep Tagging].Cost, [UT Field Rep
Tagging].[Model #], [UT Field Rep Tagging].Description, [UT Field Rep
Tagging].[Item Class Code], [UT Building Detail].Site, [UT Building
Detail].[Builging #], [UT Field Rep Tagging].Floor, [UT Field Rep
Tagging].[Room #], [UT Field Rep Tagging].[PO #], [UT Field Rep
Tagging].[Item #], [UT Building Detail].[Mac Addres], [Unit Codes].Unit, [UT
Field Rep Tagging].Notes, [UT Building Detail].[Add To], [UT Field Rep
Tagging].[Date Tagged]
FROM [UT Building Detail], ([Unit Codes] INNER JOIN [UT Field Rep Tagging]
ON [Unit Codes].[Unit Description] = [UT Field Rep Tagging].[Unit Department
Name]) INNER JOIN [Field Tagger UT EID] ON [UT Field Rep Tagging].[Field Rep
Name] = [Field Tagger UT EID].[Field Tagger Name]
WHERE ((([UT Field Rep Tagging].[Date Tagged]) Between [Start Date] And [End
Date])) OR ((([Start Date]) Is Null) AND (([End Date]) Is Null));


Dale Fye said:
From the query grid, find the View menu option or button on the ribbon bar
(2007), select the SQL view to view the entire SQL for the Query.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

This is the statement I have in the "Date" field of my query which is pulling
from my table. "Between [Start Date] And [End Date]"

The next two fields in the query are: [Start Date] Is Null and then [End
Date] Is Null.

I'm not sure where to copy and paste the actual SQL statement from.

:

It would help if you provided a sample of the SQL statement you are trying to
use.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

I have created a query which pulls info from tables in the same database. I
placed a start date/ end date (including "is null") criteria on the query.

When I run the report I receive data however, there are "gaps" in the data.
For example, if I request the date range of 02/01/08 thru 03/30/08, I get
back a blank datasheet. The records are in the table, but not coming back in
the query.

Also, if I leave the start date and end date as "null", I get data back data
but it is sparratic and always produces the same records (beginning with
01/07/08, then jumps to 04/26/08, then jumps to 6/25/08, then 10/12/08, to
11/17/08 and finally 12/1 thru 12/10/08 with duplicates).

There are are over 4K in the database, but the query only pulls back a
portion and that portion is sprinkled through the date range in date groups,
with duplicates.

How do I fix this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top