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 Replies


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

Find A Record And Query Recent Changes To Records - Version: 2003 (11.0)

I have two questions, and appreciate your input.....

1. How do I go about searching for a record in a form by [project #] field and return the record in that form?

2. Is there a way I can see what changes have been made to any record between two dates?

Thanks Guys

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

Code Error: Can't Find '|' Referred To In Expression - Version: 2003 (11.0)

I'm getting an error message although it compiles fine....that it can't find '|' referred to in my expression.

I think it's at this point in the code....

If ([tblAssignInspectionNumber.UserId] = fOSUserName() And [tblAssignInspectionNumber.MainID] = Me.MainID) Then

any ideas?

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

Records For Last 30 Days - Version: 2003 (11.0)

This should be a pretty easy one.

I have a field called [date time]. I want the form to show me all of the records for the last 30 days.


View Replies View Related

Number Of Days Missed Records - Version: 2000 (9.0)

Hi my access database has a form on that form i can selecta student. When abutton is rpressed a query opens which shows dates and the number of classes a student has missed that day. I want it to show the number of full days a student is absent. As i could figure this out i have so it shows the number of classes absent and the number of clases toal for the day. My problem is because i'm using a where clause to extract only absent, i don't know how to count all the classes for that day including one with present and excused. I have attached my database for anyone to look at and hopefully help

The password is biocert.

Chris Lynch

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

Copyrights 2005-15 www.BigResource.com, All rights reserved