Solved Query to Find Items a Week Overdue

[Bean Machine]

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]

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]

Please show us your query SQL.
Something like this

Select * from YourTable
where Date>DueDate + 7

 

[Bean Machine]

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]

Please post the full SQL and tell us how you know its not working (error message? no results? unexpected results? etc.)

 

[Bean Machine]

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!

 

[plog]

This SQL will give you every record in [Dates and Item-In/Out] that are over a week due:

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]

This SQL will give you every record in [Dates and Item-In/Out] that are over a week due:

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]

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]

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.

 

[Bean Machine]

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]

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]

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 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]

Please consider my suggestion, stop and redesign your database now before you run into more trouble

 

[Bean Machine]

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]

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'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]

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]

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

Okay, thanks. Then you could try using BETWEEN. For example:

...WHERE Date() BETWEEN [DueDate] And [DueDate]+4

Hope that helps...