UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Count Number Of Incidences That Fall Between Two Date Ranges, Access 2016    
 
   
mjcrn
post Dec 9 2019, 09:27 AM
Post#1



Posts: 30
Joined: 17-May 16



Hello!
Forgive me if this answer is somewhere quick and I can't seem to find it.
I am looking to build an SQL for a query looking at: How many assaults took place between two dates, of those who had intakes between the same two dates.

I have figured out the form situation that feeds the date range into the query, but I can't figure out how to get the 2nd layer.
here is what i have so far...

SELECT Count(Assault_Table.AssaultDate) AS CountOfUNBassaults
FROM Assault_Table INNER JOIN Campus_Advocacy ON Assault_Table.SurvivorID = Campus_Advocacy.ClientID
WHERE (((Campus_Advocacy.SSchoolName) Like "UNB*") AND ((Assault_Table.AssaultDate)>=[forms]![AdministrativeForm]![StartDatebox] And (Assault_Table.AssaultDate)<=[forms]![AdministrativeForm]![EndDatebox])) OR ((([forms]![AdministrativeForm]![StartDatebox]) Is Null));

and Campus_Advocacy.IntakeDate is the second variable I would like to filter the information by.
please help!
pullhair.gif
Go to the top of the page
 
theDBguy
post Dec 9 2019, 11:07 AM
Post#2


UA Moderator
Posts: 78,094
Joined: 19-June 07
From: SunnySandyEggo


Hi. Since you have a combination of AND and OR logical operators, you'll have to make sure you organize them using parentheses to indicate precedence. For example, True AND True OR True is not the same as (True AND True) OR True, and also different from True AND (True OR True).

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
mjcrn
post Dec 9 2019, 12:13 PM
Post#3



Posts: 30
Joined: 17-May 16



Thanks for the quick response. Since I am fairly new to writing these types of formulas. Could you share a way I might be able to use the parentheses to my advantage? or direct me to a good resource to learn about that?
Thanks!
M
Go to the top of the page
 
theDBguy
post Dec 9 2019, 12:26 PM
Post#4


UA Moderator
Posts: 78,094
Joined: 19-June 07
From: SunnySandyEggo


Hi. Based on this statement:
QUOTE (mjcrn)
I am looking to build an SQL for a query looking at: How many assaults took place between two dates, of those who had intakes between the same two dates.

I don't understand why you had an OR in your criteria. Otherwise, it might be something like this:

...AssaultDate Between Date1 And Date2 And IntakeDate Between Date1 And Date2

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th May 2020 - 04:22 PM