Solved Query to Find Items a Week Overdue (1 Viewer)

Bean Machine

Member
Local time
Today, 06:33
Joined
Feb 6, 2020
Messages
98
Hi!

I am trying to create a query that will return only items that are a week overdue. I have tried using the Date() function and the Between function but to no avail. Is it possible to do a range? Like Date() = DueDate +7,+6,+5,+4 etc.? Any help would be greatly appreciated! Thanks.
 

plog

Banishment Pending
Local time
Today, 05:33
Joined
May 11, 2011
Messages
11,611
To do a range you could use BETWEEN (https://support.office.com/en-us/article/between-and-operator-a435878d-63f7-4825-8c31-999432ae8223) or just simply use the greater than and less than operators (>, <). However, the way you posed your issue, this has nothing to do with a range--there's only one value that need to be compared, not two.

The way your criteria should work is with just one comparison--you would see if a date is greater than another date. Perhaps DateAdd (https://www.techonthenet.com/access/functions/date/dateadd.php) will help you get there. If not, please post details of yoru database (table/field names, example data, the code you have tried) so that we can help you more specifically.
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:33
Joined
Jan 23, 2006
Messages
15,361
Please show us your query SQL.
Something like this
Code:
Select * from YourTable
where Date>DueDate + 7
 

Bean Machine

Member
Local time
Today, 06:33
Joined
Feb 6, 2020
Messages
98
Okay, DueDate is defined as a date like so month/day/year. DueDate is typically defined as the end of a semester as it is a database tracking loans to students. The table that DueDate is pulling from is called Dates and Item-In/Out (bad name for it in hindsight). Here's what I have tried thus far to no avail: Between Date() And [DueDate]+7, I have also tried something like this: DateAdd("d",7,[DueDate])
 

plog

Banishment Pending
Local time
Today, 05:33
Joined
May 11, 2011
Messages
11,611
Please post the full SQL and tell us how you know its not working (error message? no results? unexpected results? etc.)
 

Bean Machine

Member
Local time
Today, 06:33
Joined
Feb 6, 2020
Messages
98
Here is a sample of what I'm working on, it has everything I've worked on up to now. The query I am trying to get to work is the Overdue Items query. I appreciate the help thus far!
 

Attachments

  • Loans Database Sample.zip
    448.4 KB · Views: 121

plog

Banishment Pending
Local time
Today, 05:33
Joined
May 11, 2011
Messages
11,611
This SQL will give you every record in [Dates and Item-In/Out] that are over a week due:

Code:
SELECT [Dates and Item-In/Out].[Due Date], [Dates and Item-In/Out].*
FROM [Dates and Item-In/Out]
WHERE ((([Dates and Item-In/Out].[Due Date])>Date()+7));
 

Bean Machine

Member
Local time
Today, 06:33
Joined
Feb 6, 2020
Messages
98
This SQL will give you every record in [Dates and Item-In/Out] that are over a week due:

Code:
SELECT [Dates and Item-In/Out].[Due Date], [Dates and Item-In/Out].*
FROM [Dates and Item-In/Out]
WHERE ((([Dates and Item-In/Out].[Due Date])>Date()+7));

Where should I go about putting that code? I apologize for my lack of knowledge I have just started working with access recently. I appreciate the response!
 

plog

Banishment Pending
Local time
Today, 05:33
Joined
May 11, 2011
Messages
11,611
Ribbon - > Create -> Query Design then close the box that appears.

Next in the upper left corner of the Ribbon click SQL and paste it in the window that appears and then click the upper left corner of the Ribbon which has changed from SQL to View. That will show the results. Click it a third time and you will go to the Query Designer you are used to.
 

namliam

The Mailman - AWF VIP
Local time
Today, 11:33
Joined
Aug 11, 2003
Messages
11,696
spaces and special characters in column names and tables

No naming convention like "tblItem" or "qryItem" etc...

I fear looking at the db :(

OK I looked against better judgement
- Loan Number as your primary key of the student information table :(
Instead call that number Student Number or (abuse) theStudent ID column
- You have an Item ID, which is a string and an item number
- You have two items "Drill Bits" this should be one item with one Item number in a seperate table tblItems
The have your current items-in/out register the StudentID and ItemID (or your current Item Numer) as well as in and out dates etc.

Probably more issues, my strong suggestion, go back to the drawing board, learn about normalization and related stuff before restarting.
It will cost you now, but help you loads down the road.
 
Last edited:

Bean Machine

Member
Local time
Today, 06:33
Joined
Feb 6, 2020
Messages
98
Ribbon - > Create -> Query Design then close the box that appears.

Next in the upper left corner of the Ribbon click SQL and paste it in the window that appears and then click the upper left corner of the Ribbon which has changed from SQL to View. That will show the results. Click it a third time and you will go to the Query Designer you are used to.

This works, but what I was trying to say (and I'm sorry I did not communicate it more effectively) is I want to be able to view items that are specifically one week overdue as staff will often chase down the students who do not return the items but after about a week give up as it is too time consuming with little pay off (most items left un-returned are only 5 to 10 dollars anyway). If there is a way to limit it to specifically a week let me know. Thanks!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:33
Joined
Oct 29, 2018
Messages
21,357
Hi. Pardon me for jumping in; but if you want "exactly" one week, try changing the greater than sign (>) into an equal sign (=) in your criteria (WHERE clause). Hope that helps...
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:33
Joined
Jan 23, 2006
Messages
15,361
I was going to suggest that too dgBuy, but I figured he'd have to run on weekends and holidays to be current.....
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:33
Joined
Oct 29, 2018
Messages
21,357
I was going to suggest that too dgBuy, but I figured he'd have to run on weekends and holidays to be current.....
Right. Hopefully, books(?) don't become overdue on weekends. :)
 

namliam

The Mailman - AWF VIP
Local time
Today, 11:33
Joined
Aug 11, 2003
Messages
11,696
Please consider my suggestion, stop and redesign your database now before you run into more trouble :(
 

Bean Machine

Member
Local time
Today, 06:33
Joined
Feb 6, 2020
Messages
98
Please consider my suggestion, stop and redesign your database now before you run into more trouble :(

I'll consider it, although what you've outlined other than loan number and the special characters (yeah I was dumb to stick special characters in there but I was just starting out with access) wouldn't make sense. Item ID is a completely seperate thing and can be letters and numbers, and some items don't have an ID, tying items altogether would be a good idea as well I suppose, but I want to do that when I start on the inventory database I'm going to make. This has just been a sort of starting point for me as I was going to make a completely fresh database to merge with inventory down the line anyway. Mostly just learning, thanks for the feedback though I will take it into consideration.
 

Bean Machine

Member
Local time
Today, 06:33
Joined
Feb 6, 2020
Messages
98
Right. Hopefully, books(?) don't become overdue on weekends. :)

I'll try to clarify a little better here, I want to see items one week overdue overall (for example: If an item goes overdue on monday I want to be able to see it on the query tuesday, wedensday, thursday and friday and then after that period I no longer want it to show up) not just the items due at exactly one week out. Is that possible? Thanks.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:33
Joined
Oct 29, 2018
Messages
21,357
I'll try to clarify a little better here, I want to see items one week overdue overall (for example: If an item goes overdue on monday I want to be able to see it on the query tuesday, wedensday, thursday and friday and then after that period I no longer want it to show up) not just the items due at exactly one week out. Is that possible? Thanks.
Okay, let's try to use some concrete examples to make sure we get what you're asking. If an item is overdue on 12/14/2020, when do you want to see it in your query? For example, on 12/14/20, 12/15/20, 12/16/20, 12/17/20, and 12/18/20?
 

Bean Machine

Member
Local time
Today, 06:33
Joined
Feb 6, 2020
Messages
98
Okay, let's try to use some concrete examples to make sure we get what you're asking. If an item is overdue on 12/14/2020, when do you want to see it in your query? For example, on 12/14/20, 12/15/20, 12/16/20, 12/17/20, and 12/18/20?

Hit the nail on the head, that's what I want.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:33
Joined
Oct 29, 2018
Messages
21,357
Hit the nail on the head, that's what I want.
Okay, thanks. Then you could try using BETWEEN. For example:
Code:
...WHERE Date() BETWEEN [DueDate] And [DueDate]+4
Hope that helps...
 

Users who are viewing this thread

Top Bottom