Date parameters, ACcess

Dev Articles Community Forums Sponsor:

July 20th, 2008, 11:20 PM

genuine

Registered User

Join Date: Jul 2008

Posts: 7

Time spent in forums: 1 h 19 m 10 sec
Reputation Power: 0

Date parameters, ACcess

I have this access query I set up and I am using 3 dfferent date parameters in my query, Basically I need to pull al records on my " Open", 'prelim", and Final " fields (names of my fields. But tye query i seto up with date date parameters is doing an OR FUNTION and not and AND Function.

July 21st, 2008, 08:35 AM

dykebert

Contributing User

Join Date: Apr 2008

Posts: 427

Time spent in forums: 5 Days 2 m 9 sec
Reputation Power: 2

Don't know why it should do that. Care to post the query so people can offer suggestions?

__________________
P.S. I am looking for work. <grin>.

July 22nd, 2008, 11:25 AM

genuine

Registered User

Join Date: Jul 2008

Posts: 7

Time spent in forums: 1 h 19 m 10 sec
Reputation Power: 0

Quote:

Originally Posted by dykebert

Don't know why it should do that. Care to post the query so people can offer suggestions?

Not really sure how to post it here as I am new at this site.
Can you let me know how?

July 22nd, 2008, 12:12 PM

dykebert

Contributing User

Join Date: Apr 2008

Posts: 427

Time spent in forums: 5 Days 2 m 9 sec
Reputation Power: 2

I am going to assume this is a stored or named query. In other words it shows up when you open the query section in Access.

1. Open the query in design view.
2. On the Menu go View > SQL
3. Copy the SQL text.
4. Paste it here.

That should do it.

July 22nd, 2008, 12:15 PM

genuine

Registered User

Join Date: Jul 2008

Posts: 7

Time spent in forums: 1 h 19 m 10 sec
Reputation Power: 0

SELECT table_NED_AUDIT_Current_Year.ID_RSSD, table_NED_AUDIT_Current_Year.Event_Number, table_NED_AUDIT_Current_Year.Inst_Name, table_NED_AUDIT_Current_Year.Start_Date, table_NED_AUDIT_Current_Year.Finc_Date, table_NED_AUDIT_Current_Year.Exam_Type, table_NED_AUDIT_Current_Year.Discrepancy, table_NED_AUDIT_Current_Year.Reviewed_by, table_NED_AUDIT_Current_Year.Date_Reviewed, table_NED_AUDIT_Current_Year.City, table_NED_AUDIT_Current_Year.State, table_NED_AUDIT_Current_Year.Scope, table_NED_AUDIT_Current_Year.Open_Prepared_By, table_NED_AUDIT_Current_Year.Prelim_Prepared_By, table_NED_AUDIT_Current_Year.Final_Prepared_By, table_NED_AUDIT_Current_Year.[# of Discrepancies_pre], table_NED_AUDIT_Current_Year.Discrepancy_pre, table_NED_AUDIT_Current_Year.[# of Discrepancies_fin], table_NED_AUDIT_Current_Year.Discrepancy_fin, table_NED_AUDIT_Current_Year.[# of Discrepancies], table_NED_AUDIT_Current_Year.[Open Audit Date], table_NED_AUDIT_Current_Year.[Prelim Audit Date], table_NED_AUDIT_Current_Year.[Final Audit Date], table_NED_AUDIT_Current_Year.[Open Reviewed by], table_NED_AUDIT_Current_Year.[Prelim Reviewed by], table_NED_AUDIT_Current_Year.[Final Reviewed by]
FROM table_NED_AUDIT_Current_Year
WHERE (((table_NED_AUDIT_Current_Year.[Open Audit Date]) Between [Enter Start Date] And [Enter End Date]) AND ((table_NED_AUDIT_Current_Year.[Prelim Audit Date]) Between [Enter Start Date] And [Enter End Date]) AND ((table_NED_AUDIT_Current_Year.[Final Audit Date]) Between [Enter Start Date] And [Enter End Date]))
ORDER BY table_NED_AUDIT_Current_Year.Inst_Name, table_NED_AUDIT_Current_Year.Start_Date;

Quote:

Originally Posted by dykebert

July 22nd, 2008, 12:18 PM

genuine

Registered User

Join Date: Jul 2008

Posts: 7

Time spent in forums: 1 h 19 m 10 sec
Reputation Power: 0

I know that the results are wrong because I went into tables and manually counted the records.

Quote:

Originally Posted by genuine

July 22nd, 2008, 12:55 PM

dykebert

Contributing User

Join Date: Apr 2008

Posts: 427

Time spent in forums: 5 Days 2 m 9 sec
Reputation Power: 2

Well there's nothing obviously wrong with the query. And I'm assuming that the query is returning more records than you think it should.

There are a couple of things you can do to debug.

1. simply copy the query to a new query and see if you get the same number of records returned.

2. create a new query that just returns the ID and date fields and check the number of returned records and that the dates are within the Start and End Dates. something like this: *

Code:

SELECT table_NED_AUDIT_Current_Year.ID_RSSD,
	 table_NED_AUDIT_Current_Year.[Open Audit Date],
	 table_NED_AUDIT_Current_Year.[Prelim Audit Date],
	 table_NED_AUDIT_Current_Year.[Final Audit Date]
FROM table_NED_AUDIT_Current_Year
WHERE (((table_NED_AUDIT_Current_Year.[Open Audit Date]) Between [Enter Start Date] And [Enter End Date]) 
	AND ((table_NED_AUDIT_Current_Year.[Prelim Audit Date]) Between [Enter Start Date] And [Enter End Date]) 
	AND ((table_NED_AUDIT_Current_Year.[Final Audit Date]) Between [Enter Start Date] And [Enter End Date]))
ORDER BY table_NED_AUDIT_Current_Year.[Open Audit Date], 
	table_NED_AUDIT_Current_Year.[Prelim Audit Date], 
	table_NED_AUDIT_Current_Year.[Final Audit Date];

3. Create seperate queries for each Audit Date and then Join them to see if you get the correct results. Something like this:

qryAuditOpenDate

Code:

SELECT table_NED_AUDIT_Current_Year.ID_RSSD,
	 table_NED_AUDIT_Current_Year.Event_Number,
	 table_NED_AUDIT_Current_Year.Inst_Name,
	 table_NED_AUDIT_Current_Year.Start_Date,
	 table_NED_AUDIT_Current_Year.Finc_Date,
	 table_NED_AUDIT_Current_Year.Exam_Type,
	 table_NED_AUDIT_Current_Year.Discrepancy,
	 table_NED_AUDIT_Current_Year.Reviewed_by,
	 table_NED_AUDIT_Current_Year.Date_Reviewed,
	 table_NED_AUDIT_Current_Year.City,
	 table_NED_AUDIT_Current_Year.State,
	 table_NED_AUDIT_Current_Year.Scope,
	 table_NED_AUDIT_Current_Year.Open_Prepared_By,
	 table_NED_AUDIT_Current_Year.Prelim_Prepared_By,
	 table_NED_AUDIT_Current_Year.Final_Prepared_By,
	 table_NED_AUDIT_Current_Year.[# of Discrepancies_pre],
	 table_NED_AUDIT_Current_Year.Discrepancy_pre,
	 table_NED_AUDIT_Current_Year.[# of Discrepancies_fin],
	 table_NED_AUDIT_Current_Year.Discrepancy_fin,
	 table_NED_AUDIT_Current_Year.[# of Discrepancies],
	 table_NED_AUDIT_Current_Year.[Open Audit Date],
	 table_NED_AUDIT_Current_Year.[Prelim Audit Date],
	 table_NED_AUDIT_Current_Year.[Final Audit Date],
	 table_NED_AUDIT_Current_Year.[Open Reviewed by],
	 table_NED_AUDIT_Current_Year.[Prelim Reviewed by],
	 table_NED_AUDIT_Current_Year.[Final Reviewed by]
FROM table_NED_AUDIT_Current_Year
WHERE ((table_NED_AUDIT_Current_Year.[Open Audit Date]) Between [Enter Start Date] And [Enter End Date]) ;

qryAuditPreviewDate

Code:

SELECT table_NED_AUDIT_Current_Year.ID_RSSD,
FROM table_NED_AUDIT_Current_Year
WHERE ((table_NED_AUDIT_Current_Year.[Prelim Audit Date]) Between [Enter Start Date] And [Enter End Date]) ;

qryAuditFinalDate

Code:

SELECT table_NED_AUDIT_Current_Year.ID_RSSD
FROM table_NED_AUDIT_Current_Year
WHERE ((table_NED_AUDIT_Current_Year.[Final Audit Date]) Between [Enter Start Date] And [Enter End Date]));

qryAuditDates

Code:

SELECT qryAuditOpenDate.ID_RSSD,
	 qryAuditOpenDate.Event_Number,
	 qryAuditOpenDate.Inst_Name,
	 qryAuditOpenDate.Start_Date,
	 qryAuditOpenDate.Finc_Date,
	 qryAuditOpenDate.Exam_Type,
	 qryAuditOpenDate.Discrepancy,
	 qryAuditOpenDate.Reviewed_by,
	 qryAuditOpenDate.Date_Reviewed,
	 qryAuditOpenDate.City,
	 qryAuditOpenDate.State,
	 qryAuditOpenDate.Scope,
	 qryAuditOpenDate.Open_Prepared_By,
	 qryAuditOpenDate.Prelim_Prepared_By,
	 qryAuditOpenDate.Final_Prepared_By,
	 qryAuditOpenDate.[# of Discrepancies_pre],
	 qryAuditOpenDate.Discrepancy_pre,
	 qryAuditOpenDate.[# of Discrepancies_fin],
	 qryAuditOpenDate.Discrepancy_fin,
	 qryAuditOpenDate.[# of Discrepancies],
	 qryAuditOpenDate.[Open Audit Date],
	 qryAuditOpenDate.[Prelim Audit Date],
	 qryAuditOpenDate.[Final Audit Date],
	 qryAuditOpenDate.[Open Reviewed by],
	 qryAuditOpenDate.[Prelim Reviewed by],
	 qryAuditOpenDate.[Final Reviewed by]
FROM qryAuditOpenDate INNER JOIN qryAuditPreviewDate ON qryAuditOpenDate.ID_RSSD = qryAuditPreviewDate.ID_RSSD
	 INNER JOIN qryAuditFinalDate ON qryAuditOpenDate.ID_RSSD = qryAuditFinalDate.ID_RSSD
ORDER BY qryAuditOpenDate.Inst_Name, qryAuditOpenDate.Start_Date;

These should be close to correct.

July 22nd, 2008, 12:59 PM

dykebert

Contributing User

Join Date: Apr 2008

Posts: 427

Time spent in forums: 5 Days 2 m 9 sec
Reputation Power: 2

OOPs forgot

*
To create a query from SQL

1. click on Create a query in design view
2. Click Close on the pop-up that allows you to select tables.
3. on the menu View > SQL
4. copy and paste to the query window.

July 22nd, 2008, 01:30 PM

genuine

Registered User

Join Date: Jul 2008

Posts: 7

Time spent in forums: 1 h 19 m 10 sec
Reputation Power: 0

Hi:

Thanks but do I do 3 separate quiries and then join them?

Quote:

Originally Posted by dykebert

#10

July 22nd, 2008, 01:44 PM

genuine

Registered User

Join Date: Jul 2008

Posts: 7

Time spent in forums: 1 h 19 m 10 sec
Reputation Power: 0

AS you can probably tell, I am new at this access. Can I post my database and if you could help me put the quiries, I would appreciate it.

Quote:

Originally Posted by genuine

Hi:

Thanks but do I do 3 separate quiries and then join them?

#11

July 22nd, 2008, 02:36 PM

genuine

Registered User

Join Date: Jul 2008

Posts: 7

Time spent in forums: 1 h 19 m 10 sec
Reputation Power: 0

I just did Option # 2 and I am getting the same total as I was getting with my original query

Quote:

Originally Posted by genuine

AS you can probably tell, I am new at this access. Can I post my database and if you could help me put the quiries, I would appreciate it.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: