Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

Query Expression To Find Records 30 Days Past Due

I am trying to run a query to print invoices that are 30, 60, and 90 days past due. What expression do I use to search for records that were purchased 30, 60, 90 days prior? I was able to find the expression to add time for the due date but was unable to find the expression to subtract time for my search. Thanks for any help!! Beck

View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Find Records Within 30 Days Of Each Other.
I have a spreadsheet that has Account Number, Trade Date (mm/dd/yyyy), and Trade Amount ($).
What I need is to group by Account Numbers, and Sum Trade Amounts for any trades falling within a 30 days of each other (any 30 days, not necessarily the same calendar month) and determine if it is more than a set amount ($10,000).

Any help would be greatly appreciated.

View Replies !   View Related
Results Of Past 7 Days
I have a form in which the user inputs crane inspection results (a series of check boxes). When the user clicks the save button, I would like to run a query (I guess a query is how to do this) that searches records for the past seven days (less weekends and holidays) and alerts the user to days that inspection results were not entered for that particular crane (cboCrane.Value) and that particular shift (cboShift.Value) via a message box.

How do I get it to only look at records for the past 7 days (less weekends and holidays)?

Any help will be greatly appreciated.

View Replies !   View Related
Counting Number Of Days Past And Present

I am stumped on how to get a query to do the following:

I need to count the number of days which have fallen between a date range and then work the charges incurred both actual and projected. The date range is >date()-7 and date()+7

Is in basic terms the last seven days and the next seven days

So this is an example:

A box arrives on the 01/01/2006 and has a delivery date of 17/01/2006

I run the report on the 08/01/2006. The number of days I am charged for is 7 to date and the projected charges are 9

So i have a [Query].[Actual] and a [Query].[Future]

How do i get the query to place the number of Actual Days in the Actual Field and also place future days in the future column.

Can anyone assist?

View Replies !   View Related
Limited Records Past Date
I have a query that returns a set of records which details stock items that are older than a date given in a form. However i need to limit the results to the number of items held in stock
i.e say i want to look at stock over 1 year old, i get a list of all the stockids, and the date added.
Say there is a stock level of 3 for a particular stockid the results should be limited to the first 3 records that are over 1 year old. Rather than the whole list of dates I get now.

Any help or direction with searching terms would be appreciated


View Replies !   View Related
Retrieve Records For The Past Year
I trying to create a query that just returns all of the records in my database for the past year. I've tried to somehow do it using the date() function but haven't had much luck yet.
Any help is most appreciated.

View Replies !   View Related
Viewing Past Records On A New Form.
Greetings All,

I'm not sure if this is possible but I've been asked to devise a way of viewing past record entries from an old form on a new form. Our database contains yearly records of student details; each year has a separate form. What my manager would like is that when a student ID number is typed into this yearís form, that old data from previous years is retrieved and placed in the database OR some sort of command button that searches past records for that particular student ID.

Hope that makes sense, I'm completely confused about the whole thing, I'm not sure if itís even possible...any kind of help will be gratefully received!

View Replies !   View Related
Working Days Expression In My Form
Hi I have a Text box that stores a date of when a problem or query is received on to a customer account, when resolved the date it is resolved is inputted in the next text box as a resolved date. My third Text box then needs to display Yes or No depending if it took less than 28 days to resolve.

Ive used a Text box and DateDiff expression to work out the ammount of days in the control source, Called query dauration then a macro to test if its less than 28 days to display the yes or no. But this is not working days Mon - Fri. Has any one any ideas?

=DateDiff("d",[Date Query Raised],[Date Query Resolved])


IIf([Query Duration]<="28","Yes","No")

View Replies !   View Related
Query Expression To Filter Records
I am trying to build an expression in a query that will filter specific numbers when the value is entered. I would like the value box to read something like "Please enter the file number" and when entered, see example of file # in brackets (001-200-001), it will show those records with that file number only. In addition, because I will be printing it from a report, the filtered file numbers should also show up in the form.


View Replies !   View Related
Query To Find Missing Records


Table A has records 1,2,3,4,5,6,7,8,9,10

Table B has records 1,2,3,4,5,6

How do I create a query that returns values 7,8,9,10 for Table B when I compare Table A and Table B?

What sort of query do I need? I tried the "Unmatched query" but this did not get the result I wanted.

Thanks for reading.....


View Replies !   View Related
Query To Pull Past 3 Months
I have a query that I need to always pull data from the newest past 3 months, so if this query were ran today it would take from months 2,3,4 (Feb, Mar, Apr). Any clue on how to do this?

View Replies !   View Related
Simple Query - Find Junk Records
Well, I'm trying to enforce referential integrity on one of my relationships. But it seems that there are 5 records in the Subtable that don't have a corresponding record in Table, so it won't work. I tried copy/pasting from Subtable to Table, and it says 'unable to make changes because this would make duplicates in the primary key' to all values except 5. Which it asks me if I want them pasted. If I click 'yes' or 'no' nothing happens either way.

Well, anyway, I need code for a query that selects all records from Subtable.Field1 that don't exist in Table.Field1.

View Replies !   View Related
Find Records With A Line Break Using A Query
Hello all,

I want to create a query that looks for a manual line break in a memo field. Is there a code or some way that I can do this? (i.e in a Memo field the user has entered some data, hit Ctrl-enter to get to a new line, entered more data on the new line. I want to find the records where this has occured)

Also, on a similar note, is there a way I can deny my users the option of doing a manual break (Ctrl-Enter) in a Control in a Form? (to prevent me needing to run this query in the future)

Please advise.


View Replies !   View Related
Query The Occurrence Of A Particular Absent Reason In Past 12 Months?
Hi, i have a query which looks at a table named "attendance" the query includes the fields: "employee number", "absent code" (a 3 letter code), "absent reason" (list of different reasons) "shiftdate" (weekly shift dates in format: DD/MM/YYYY). If possible i would like to be able to promt the user to enter a employee number, then a absent reason (ideally from a list box) and show all occurences (with all the shiftdates) only of that particular absent reason within the last 12 months, and ideally calculates in another field the number of times that employee has been absent for that particular reason for the last 12 months.

Does this make sence?, i know how to promt the user to enter a employee number but not sure how to go about the rest, any help with what seems to be quite a challenging task to me would be excellent.

View Replies !   View Related
How To Query A Table To Find Records Showing Checked Boxes
I have a table listing xxx reasons for dispute - Users will check boxes. How do I build my query that would list all the entry numbers which have a text box?

Might sound stupid but what would be the criteria? Think it would be easier to get these results into a report and sum.

I'm baffled!19142:confused:

View Replies !   View Related
Creating Expression To Find A Percentage
I created the following expression on a report, under the details, in a text box:

=nz(pathjbo50,0)/nz(pathjbo50,0)+nz(pathcol50,0)+nz(pathpp50,0)+nz( pathlm50,0)

what I want is to find out what percentage pathjbo50 is of the sum of the other 4 fields.

but I keep getting an "Error" message. How do I get this percentage?

Where is the best place to find literature on writting expressions?

View Replies !   View Related
The Expression You Entered Has A Function Name That XXX Can't Find
I have a database functioning pretty well.
All I did was move the files from one computer to another via USB stick and now I get this alarm when I try to run this particular macro.

It is a macro to set a value to specific field.
The Macro uses a condition [Forms]![tblCustomerCall]![Alarm Number]="000".
If the condition is met then it is supposed to set a value to a field in the same form.
[Forms]![tblCustomerCall]![Alarm Descrption] is the item to set.
Left("TV Parity Alarm",50) is the expression.
So if 000 is entered in then the text TV Parity Alarm is filled into the Alarm description field.

It was working perfect until I moved the files to another computer.
I have Office SP2 installed and have updated office with everything available.

Please help.

View Replies !   View Related
Show Only Records From Last 7 Days?
This is fairly simple i think although i cant do it right i have a field in a query named; "shiftdates" which uses the format: DD/MM/YYYY, how can i set the criteria for this to only show records from the past 7 days?

i think i need something like: date()-7 (this doesnt work though), anyone help me out?, :o

View Replies !   View Related
Selecting Records Greater Than Seven Days Old
I tried the Crystal Reports forum, but no responses. Can anyone see what I'm doing wrong with the below statement in Crystal Reports 8.5. I'm
trying to select reecords that exceed 7 days based on a Crystal Reports parameter. The parameter has the format of 2/27/2006 (Discrete value). The 'LastRouteDate' has a format of 'datetime'. Thanks ! ! !

{?Select_Date} - DateValue({Applications.LastRouteDate}) > 7

View Replies !   View Related
Counting Number Of Days That Contain Records
I've been searching the Queries Forum for help on this issue. I have a database that records loading records for a Taconite facility. Some days contain many records and on other days there are no records because of no loading activity. I create reports that group these loading records by each day, but I really need a quick and dirty report that just shows me the number of days in any month in which we had loading activity (records present). I have not figured out a way to create a query which counts the number of days in a month which contain records. I'm hope I'm explaining this well enough for someone to help me understand what to do.

Thanks ... Erik

View Replies !   View Related
Trouble Implementing The Regular Days And Sunday Working Days?
Hello everyone,

I have problem with my code here. I am using this code to determine if the LogDate with a weekday of Sunday can be declare as Sunday Work or Regular Work. The 1st week of my entry can determine if the LogDate is Sunday then compute if it reaches a 7 days working, if he is absent even 1 (one) day in his previous working days within the week. His Sunday LogDate is considered as Regular Days instead of Sunday Work. Can anyone tell me what is wrong in this code?

The problem here is the computation of 7 days after 1 week. There should be a 7 days computation every week per month in able to declare Sunday Work or Regular Work. I tried this code but it did not work on 2nd week, 3rd week and 4th week of the month.

How come on 2nd week, Sunday (LogDate) is not considered as Sunday Work even he completed the 7 days (working days) per week? I need help on this ASAP Thanks


Below is the code i'm using for defining Regular days and Sunday Working Days:

Private Sub LogDate_AfterUpdate()
Dim rst As DAO.Recordset, rs As DAO.Recordset
Dim db As DAO.Database
Dim dteToday As Date, dtePrior As Date
Dim i As Integer

dteToday = LogDate
dtePrior = DateAdd("d", -6, dteToday)

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)

rst.FindFirst "[HolidayDate] = #" & dteToday & "#"
'See if the date entered is a holiday
If rst.NoMatch Then
Holiday.Value = 0
Holiday.Value = 1
End If
If Weekday(dteToday) = vbSunday Then 'If dated entered is a Sunday determine if
Dim strSQL As String 'it will be a regular day or a Sunday

strSQL = "SELECT [LogDate] "
strSQL = strSQL & "FROM tblLog "
strSQL = strSQL & "WHERE [LogDate] >= #" & dtePrior & "#"
strSQL = strSQL & " AND [LogDate] <= #" & dteToday & "#"

'Create a recordset of records from date entered and 7 days prior
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

i = rs.RecordCount 'Count the number of days between the two dates

If i = 7 Then 'If there are seven days then it is a Sunday
Sunday.Value = 1
Sunday.Value = 0 'If less than 7 days then it is a regular day
End If

Sunday.Value = 0
End If

DayofWeek.Value = Format(dteToday, "dddd")

End Sub

View Replies !   View Related
Date Add Problem (calendar Days Vs Working Days)
I have a query and I need the records to display 3 working days before the 15 working day deadline.

I used the following in the criteria box below the received date field and it doesn't pull the correct number of workdays, it's pulling calendar days instead.


Can anyone help, thanks for your time!!!

View Replies !   View Related
Date Add Problem (calendar Days Vs Working Days)
I have a query and I need the records to display 3 working days before the 15 working day deadline.

I used the following in the criteria box below the received date field and it doesn't pull the correct number of workdays, it's pulling calendar days instead.


Can anyone help, thanks for your time!!!

View Replies !   View Related
Calculate Days In Advance Or Days In Arrears
I rent out a property , on the rental statement I have a field [Datepaid], [RentpaidFrom] and [rentpaidTo]. I would to have a field that can calculate the rent that is paid to show if the rent is in arrears or in Advance. I guess the code would be something like (datediff ???? etc) but i have no idea what it should be. the calculation should appear on the query as i.e.2 days in arrears or 12 days in advance etc

your help would be much appreciated

merry christmas to you all

View Replies !   View Related
Query For Last 7 Days - Mon To Mon
trying to think my way around this one. i am in search of a query that will allow users to see what they have ordered within the last week (7days max) which starts on monday of each week eg if they run the query on thurs, they will see mon, tues, weds & thurs and not just 7 days from thurs [Date()-7]

i'm assuminging its gonna be something like if day=monday then query will = date() else if day=tuesday then query will = date()-1 and so on.....

View Replies !   View Related
Run A Query Every 10 Days Etc
Hi i need to make a query run every 10 or so days as it is a query which checks if any printers in the database are running out of warranty

Can anyone help on this

I basically am trying to make this query run where if there is any data a report will preview stating this printer warranty is expiring etc

Many thanks in advance

View Replies !   View Related
Count Days In Query
I got an assignment from my supervisor and I am not getting it! :(

I have a query (see result in attachment) with the following fields:
tID, emailID, emailtype and EmailDate.

A customer (tID 1297542) sends an e-mail (emailtype 1) to the company on 2-8-2005.
The company replies on that e-mail (emailtype 3) on 5-8-2005.

I would like to create a query that shows per tID the total of days between the e-mail from the customer and the e-mail from the company (between emailtype 1 and 3).

View Replies !   View Related
How To Get Working Days In The Query?
Hi, i have a problem
I need to create a query that shows me only days which r:
1. working days
2. which r not in the table on which the query is based
Thanks in advance

View Replies !   View Related
Number Of Days Query Help
I imported an large Excel file with a date column consisting of 3 different date formats and need help on a query to extract the number of days. Examples of the date on that column:


Appreciate any advise.

View Replies !   View Related
Help With Query-limit On Days
I am working on my DB for work and am almost finished with my DB. I have a query that gives me totals for days. This is the code that I have been using.

TotalTMD: IIf([SumOfTMD Total]>=180,IIf(IsNull([SumOfLOA Total]),180,"180"),[SumOfTMD Total])

TotalLOA: IIf([SumOfLOA Total]>=180,IIf(IsNull([SumOfTMD Total]),180,"180"),[SumOfLOA Total])

The "TotalTMD" and "TotalLOA" days are limited to 180 days for each table. However what I need to do now is limit the combination of both fields to 180 days. Is this possible?

Since "TotalLOA" is more serious, I would like to use what ever days are in this field and then include what ever days are left over to total 180 days from "TotalTMD" days.

Maybe I could use this as an example:

Name TMDDays LOADays Total days
John 190 20 210
Jim 80 90 170
Tony 250 190 440

Should display:
Name TMDDays LOADays Total days
John 160 20 180
Jim 80 90 170
Tony 0 180 180

Any help would be greatly appreciated.

View Replies !   View Related
Calculate Working Days Between 2 Days
Dear All,

I'm new to VBA coding. A code below is copied from a friend of mine and I can't make it work. How to call up this function in my form. In my form I have 3 text boxes (StartDate, EndDate and NumOfWorkDays). My form is based on a table.

Please anyone who would help me on this, kindly give me the step by step procedure as I am really novice. Thanks in advance.

'*********** Code Start **************
Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
On Error GoTo Err_WorkingDays

Dim intCount As Integer

StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case WeekDay(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
WorkingDays = intCount

Exit Function

Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function

Can someone please direct me to the right path.

Your help is highly appreciated.


View Replies !   View Related

Copyright © 2005-08, All rights reserved