473,231 Members | 1,815 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,231 software developers and data experts.

Querying table using duel date fields

I'm trying to filter a table that has 2 date fields, the first date will
always have a value but the second will only occasionally has a value. Each
date field also has a corresponding text field to record the record's
status.

Sample data:

*tblTest*
Model Parts CDate CStatus RDate RStatus
616 $359.79 03-Nov-03 C
616 $109.56 18-Nov-03 C
616 $0.00 20-Jan-04 D 16-Feb-04 C
620 $8.75 07-Jan-04 C 17-Feb-04 D
624 $41.23 24-Oct-03 C 17-Feb-04 D
624 $340.39 10-Feb-04 C 17-Feb-04 D
626 $371.64 28-Oct-03 C
624 $204.98 19-Feb-04 C

What I want to do is display all records between 15-Oct-03 and 18-Feb-04
that have a RStatus of "C" or a CStatus of "C" if RStatus is null. This will
show records 1, 2, 3 & 7. If I change the filter to between 15-Oct-03 and
15-Feb-04 it should show records 1, 2, 4, 5, 6 & 7. Changing the filter to
between 10-Feb-04 and 20-Feb-04 should show records 3 & 8.

My criteria is that is the date in the RDate field does not fall inside the
date range or the RDate field is null, the test should then apply to the
CDate and CStatus fields.

Can somebody guide me in how to create a query to do this. The dates used
for the filtering will come from an open form.

Stewart

Nov 12 '05 #1
4 2459
The Nz() function allows you to specify a value to use if one is null.
Assuming that RDate and RStatus with be Null at the same time (i.e. either
both Null, or neither Null), your SQL statement could be:

WHERE (Nz([RStatus], [CStatus]) = 'C') AND
(Nz([RDate], [CDate]) Between
Forms!MyForm!StartDate And Forms!MyForm!EndDate)

If you place literal dates into the SQL statement, they must be in the
mm/dd/yyyy format. More info on that in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stewart Allen" <sa****@ThisPartNotVailid.wave.co.nz> wrote in message
news:c1**********@news.wave.co.nz...
I'm trying to filter a table that has 2 date fields, the first date will
always have a value but the second will only occasionally has a value. Each date field also has a corresponding text field to record the record's
status.

Sample data:

*tblTest*
Model Parts CDate CStatus RDate RStatus
616 $359.79 03-Nov-03 C
616 $109.56 18-Nov-03 C
616 $0.00 20-Jan-04 D 16-Feb-04 C
620 $8.75 07-Jan-04 C 17-Feb-04 D
624 $41.23 24-Oct-03 C 17-Feb-04 D
624 $340.39 10-Feb-04 C 17-Feb-04 D
626 $371.64 28-Oct-03 C
624 $204.98 19-Feb-04 C

What I want to do is display all records between 15-Oct-03 and 18-Feb-04
that have a RStatus of "C" or a CStatus of "C" if RStatus is null. This will show records 1, 2, 3 & 7. If I change the filter to between 15-Oct-03 and
15-Feb-04 it should show records 1, 2, 4, 5, 6 & 7. Changing the filter to
between 10-Feb-04 and 20-Feb-04 should show records 3 & 8.

My criteria is that is the date in the RDate field does not fall inside the date range or the RDate field is null, the test should then apply to the
CDate and CStatus fields.

Can somebody guide me in how to create a query to do this. The dates used
for the filtering will come from an open form.

Stewart

Nov 12 '05 #2
Thanks Allen, it was a simpler solution than what I was using but it still
doesn't pull the required records. If the RDate falls outside the date
criteria (even if it's not null), the filter should use the CDate.

Rec CDate CStatus RDate RStatus
1 03-Dec-03 C
2 20-Jan-04 D 16-Feb-04 C
3 07-Jan-04 C 17-Feb-04 D

A date range of 1-Dec-03 to 16-Feb-04 with 'C' as the status should pull
records 1 & 3.
A date range of 1-Dec-03 to 17-Feb-04 with 'C' as the status should pull
records 1 & 2.
If the RDate Is Null then the RStatus Is Null just as you said but the
filter should test the date in the CDate and the CStatus if the RDate Is Not
Null and the RDate falls outside the filter range.

I know this is a tough one but any help would be appreciated.

Stewart
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40***********************@freenews.iinet.net .au>...
The Nz() function allows you to specify a value to use if one is null.
Assuming that RDate and RStatus with be Null at the same time (i.e. either
both Null, or neither Null), your SQL statement could be:

WHERE (Nz([RStatus], [CStatus]) = 'C') AND
(Nz([RDate], [CDate]) Between
Forms!MyForm!StartDate And Forms!MyForm!EndDate)

If you place literal dates into the SQL statement, they must be in the
mm/dd/yyyy format. More info on that in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stewart Allen" <sa****@ThisPartNotVailid.wave.co.nz> wrote in message
news:c1**********@news.wave.co.nz...
I'm trying to filter a table that has 2 date fields, the first date will
always have a value but the second will only occasionally has a value.

Each
date field also has a corresponding text field to record the record's
status.

Sample data:

*tblTest*
Model Parts CDate CStatus RDate RStatus
616 $359.79 03-Nov-03 C
616 $109.56 18-Nov-03 C
616 $0.00 20-Jan-04 D 16-Feb-04 C
620 $8.75 07-Jan-04 C 17-Feb-04 D
624 $41.23 24-Oct-03 C 17-Feb-04 D
624 $340.39 10-Feb-04 C 17-Feb-04 D
626 $371.64 28-Oct-03 C
624 $204.98 19-Feb-04 C

What I want to do is display all records between 15-Oct-03 and 18-Feb-04
that have a RStatus of "C" or a CStatus of "C" if RStatus is null. This

will
show records 1, 2, 3 & 7. If I change the filter to between 15-Oct-03 and
15-Feb-04 it should show records 1, 2, 4, 5, 6 & 7. Changing the filter to
between 10-Feb-04 and 20-Feb-04 should show records 3 & 8.

My criteria is that is the date in the RDate field does not fall inside

the
date range or the RDate field is null, the test should then apply to the
CDate and CStatus fields.

Can somebody guide me in how to create a query to do this. The dates used
for the filtering will come from an open form.

Stewart

Nov 12 '05 #3
To filter on the CDate field if RDate is null or ourside the range, your
WHERE clause would include:

WHERE IIf(([RDate] >= Forms!MyForm!StartDate) AND ([RDate] <=
Forms!MyForm!EndDate), [RDate], [CDate])
Between Forms!MyForm!StartDate And Forms!MyForm!EndDate

The Immediate Iff() chooses the RDate if the its first argument is True.
Otherwise (False or Null), it chooses the CDate field.
The result is then used for the Between operator.

I'll let you add the Status bit.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stewart Allen" <sa****@wave.co.nz> wrote in message
news:23*************************@posting.google.co m...
Thanks Allen, it was a simpler solution than what I was using but it still
doesn't pull the required records. If the RDate falls outside the date
criteria (even if it's not null), the filter should use the CDate.

Rec CDate CStatus RDate RStatus
1 03-Dec-03 C
2 20-Jan-04 D 16-Feb-04 C
3 07-Jan-04 C 17-Feb-04 D

A date range of 1-Dec-03 to 16-Feb-04 with 'C' as the status should pull
records 1 & 3.
A date range of 1-Dec-03 to 17-Feb-04 with 'C' as the status should pull
records 1 & 2.
If the RDate Is Null then the RStatus Is Null just as you said but the
filter should test the date in the CDate and the CStatus if the RDate Is Not Null and the RDate falls outside the filter range.

I know this is a tough one but any help would be appreciated.

Stewart
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<40***********************@freenews.iinet.net .au>...
The Nz() function allows you to specify a value to use if one is null.
Assuming that RDate and RStatus with be Null at the same time (i.e. either both Null, or neither Null), your SQL statement could be:

WHERE (Nz([RStatus], [CStatus]) = 'C') AND
(Nz([RDate], [CDate]) Between
Forms!MyForm!StartDate And Forms!MyForm!EndDate)

If you place literal dates into the SQL statement, they must be in the
mm/dd/yyyy format. More info on that in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

"Stewart Allen" <sa****@ThisPartNotVailid.wave.co.nz> wrote in message
news:c1**********@news.wave.co.nz...
I'm trying to filter a table that has 2 date fields, the first date will always have a value but the second will only occasionally has a value.

Each
date field also has a corresponding text field to record the record's
status.

Sample data:

*tblTest*
Model Parts CDate CStatus RDate RStatus
616 $359.79 03-Nov-03 C
616 $109.56 18-Nov-03 C
616 $0.00 20-Jan-04 D 16-Feb-04 C
620 $8.75 07-Jan-04 C 17-Feb-04 D
624 $41.23 24-Oct-03 C 17-Feb-04 D
624 $340.39 10-Feb-04 C 17-Feb-04 D
626 $371.64 28-Oct-03 C
624 $204.98 19-Feb-04 C

What I want to do is display all records between 15-Oct-03 and 18-Feb-04 that have a RStatus of "C" or a CStatus of "C" if RStatus is null. This
will
show records 1, 2, 3 & 7. If I change the filter to between 15-Oct-03
and 15-Feb-04 it should show records 1, 2, 4, 5, 6 & 7. Changing the filter to between 10-Feb-04 and 20-Feb-04 should show records 3 & 8.

My criteria is that is the date in the RDate field does not fall inside the
date range or the RDate field is null, the test should then apply to

the CDate and CStatus fields.

Can somebody guide me in how to create a query to do this. The dates used for the filtering will come from an open form.

Stewart

Nov 12 '05 #4
Thanks Allen. After a bit of playing around I got the Status part
working as well and the whole thing works corectly.

Stewart
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40***********************@freenews.iinet.net .au>...
To filter on the CDate field if RDate is null or ourside the range, your
WHERE clause would include:

WHERE IIf(([RDate] >= Forms!MyForm!StartDate) AND ([RDate] <=
Forms!MyForm!EndDate), [RDate], [CDate])
Between Forms!MyForm!StartDate And Forms!MyForm!EndDate

The Immediate Iff() chooses the RDate if the its first argument is True.
Otherwise (False or Null), it chooses the CDate field.
The result is then used for the Between operator.

I'll let you add the Status bit.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Gleep | last post by:
sorry i didn't explain it correctly before my table is like this example fields: ID name username outcome date1 date2 date3 (etc..) - date15 price1 price2 price3 (etc..) I know that...
7
by: Marc Pelletier | last post by:
Hello, I have a table with a Day field, defined as smalldatetime. I am filling it from a CSharp application with the following code: DataRow r = dtStaDays.NewRow(); r= station_ID; r =...
3
by: AndyBell | last post by:
Hi all! I have an Access 2000 database for the Habiat for Humanity where I work. This is the second database I have written and it gets a bit more complex each time... I have learned much and...
5
by: Sami | last post by:
Please bear with me, and if you answer this question, please do it step by step. I am new at Access, not at all sophisticated. I am using Office XP. This will need to be read in Access for...
5
by: Shane | last post by:
I wonder if someone has any ideas about the following. I am currently producing some reports for a manufacturing company who work with metal. A finished part can contain multiple sub-parts to...
0
by: roiavidan | last post by:
Hi, I'm having a bit of a problem with a small application I wrote in C#, which uses an Access database (mdb file) for storing financial data. After looking for a similiar topic and failing to...
11
by: Dixie | last post by:
How can I programatically, take some Date/Time fields present in a table in the current database and change their type to text? dixie
3
by: laurastevens | last post by:
I have the following tables with the following fields I need from each of these tables. Customer table: fields: Customer Name, Customer No. Sales Header table: fields: Customer No., Order Date,...
2
by: divyac | last post by:
I am doing a project on inventory control of grocery items using PHP and mysql 5.In my project i have 2 tables.. 1.master_in_outs->where all the inward and outward transactions of particular items...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
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: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
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...

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.