Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Query To Retrive All Orders Made Within The Last 7 Days


Hi, I wonder if any one could help me with what I think is a simple query.

I would just like to show all the orders thats have been made within the last 7 days.

So far I can retrieve all the orders made today, by using 'Date()' as my criteria, but cant figure out what criteria to use for the last 7 days. It would also be good, if i could figure out how to retrive all orders made within the last month, and as well select all orders made between two specific dates.

Regards


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Query From 2 Queries To Retrive Limited Records
Hi,
I am in need of help to sort out some records. I have tow existing queries I would like to combine and get one final set of records out of. They go like this:

Query 1. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 x x (check
Y ACD 2 x x boxes)

Query 2. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 8/8/05 9/8/05 10/8/05 (query
Y ACD 2 8/8/05 9/8/05 10/8/05 performs calculations)

What I am looking to retrive through the third query is this:

Query 3. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 8/8/05 9/8/05
Y ACD 2 8/8/05 10/8/05

Where the third query only shows the calculated dates when the check box is true. So for month 1 & 2 I get dates for Lot X and for Lot Y I get dates for month 1 & 3. I have tried to go through the expression builder, but to no avail. I either get all records, like query 2 or I get nothing reported. I am not sure how to limit the records based on the check boxes.

Thanks,
CB

View Replies !   View Related
Delete Old Orders Query
I want to write a query that would delete records from 3 linked tables

Customer has Orders that have Items

The Order table holds the date of the orders. I want to delete all customers that have not placed in order since a user entered date.

Not got off the ground on this one. Any help appreciated.

Badger

View Replies !   View Related
Query If Any Changes Have Been Made
I have created a button to save changes and to undo changes on a form. However if the user has not made any changes, i get a message saying that the Undo command is not available and the same with save changes.

Therefore if no changes had been made i would like to just ignore that part of the action and continue with the rest of the event procedure.

My code for undo is..

Public Sub UndoChanges_Click()

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Me.frmtabContact_Details.Enabled = False
Me.frmtabCustomer_AC.Enabled = False
Me.frmtabCustomer_Options.Enabled = False
Me.Edit_Contact.Visible = True
Me.Edit_Contact.SetFocus
Me.frmSave_Changes.Visible = False

End Sub

View Replies !   View Related
How To Retrive Data From DB
hi all, :D

i am new in this forumn i have one access db in which i have one table all Customer having info of no of customer i want to design one form in access which access the record from table customer and also i am able to edit and update the reocrds in form i want to navigate the records as fristrecords,next record,prevoius record, and last record how can i do this pls help me regarding this...

thanx,
Regards,
Nilesh thakur

View Replies !   View Related
Undo Changes Made By Query.
Hi, i am stumped.

I have two tables Tbl_Referrals and Tbl_CMSData. Both of these tables have an identifier in called ConflictID. I have created a query which selects the records from both tables where the conflict ID is the same.

I have a form running this query. Down one side of the form i have all the details from Tbl_CMSData and on the otherside of the Form i have the corresponding details from Tbl_Referrals. I need the user to be able to update field by field from Tbl_CMSData to TblReferrals. This is done by means of a simple update query for each field. This all works fine.

Problem - I need an undo button for each field. Me.Undo does not work becuase the changes have been made by a query and not on the form.

Is there a way of undoing what the query has updated? Possibly by setting the focus to a Tbl_Referrals and then undoing the changes?

Thanks in advance

View Replies !   View Related
Identify Any Changes Made To Query Criteria ?
Hello,

I was wondering if there is anyway to be able to identify or monitor changes made to a query.

Basically looking to identify if anyone goes in and manipulates the criteria field within the query ?

Using Access 2003.

Any thoughts or help are greatly appreciated.

Thanks

View Replies !   View Related
Question 2: Pre-made Query Access In VBA
I would really like to populate a combobox with a list of queries. This would allow the user to add a group of data into a form's subform all at once. Although I have no clue how to use the pre-made queries from VBA or even how to get access to them via a combobox.

Any ideas? If I didn't make myself clear, I can explain further. Thanks in advance!

View Replies !   View Related
How To Retrive Automatically Generated Results
Hey guys.This is a real basic question but I am a newbie at this.:confused:I have created a form in Access that is actually an INVOICE for my company and therefore I have fields like [qty1],[rate1] & [amount1] In the form in [amount1] I have used the following expression to get automatically generated values.=[qty1]*[rate1]This gives me the Amount. But problem is that the result that is automatically generated by this mathematical operation is not stored in my database under the field amount1 and the field is therefore blank. I wish to use this resulted value in a report and also want to use it in other forms e.g. my sales register.kindly help me out with this.:)

View Replies !   View Related
Second Combo Made Visible Based On Associations Table And Query
I have a form that is used for recording test scores. The first selection that is made is a type of test. After the test is selected, some tests have a second sub-type of test that needs to be recorded. My quetion lies in how to hide or grey out this second combo box until a test is selected that requires it.

I don't want to "hard-code" it in the sense that I create an If Else statement that requires looking at values from the 1st test type combo box. I want to make sure that the DB is scalable and when additional tests are added, additional sub tests can be added if necessary.

If this makes sense and anyone has any ideas, I would appreciate it.

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

Tim

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
Else
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)

rs.MoveLast
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
Else
Sunday.Value = 0 'If less than 7 days then it is a regular day
End If

Else
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.

<=DateAdd("w",-12,Date())

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.

<=DateAdd("w",-12,Date())

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
Jabez

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.

Example:
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.

Problem:
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:

7/6/2006
7/1/06-7/31/06
7/1-7/5

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
Loop
WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function
'xxxxxxxxxxxxxxxxxxxxxxxx

Can someone please direct me to the right path.

Your help is highly appreciated.

qwerty70

View Replies !   View Related
Query The Number Of Business Days
Hello all,

I have 2 tables:
- Employee
- PackageTracker (Contains PackageNumber, date received, date sent, status)

I would like to query:
- which employee has the package
- How long have we had the package (calculate only business days)
- What is the status

I would like the things mentioned above to be ran in one query.

I am stuck with the calculations. I found a macro code to calculate the business days but I am stuck.

Please advise.

Thank you,


John

View Replies !   View Related
Working Days Query Problem
Date Recieved: Date letter in

SLA: combo box: 7 days
15 days
20 days

SLA Date: Date recieved plus 7, 15 or 20 working days.

Any ideas on how I would do this?

View Replies !   View Related
Number Of Days Between Dates In Query
I want the number of days between a specific date (roll out date) and 12/31/2005 so that
I can use the number to project revenue for this year.

This is what I'm using but it doesn't give me the right number. I don't know how to transform
something to a datetime variable so maybe that's the reason. Can you help me out?

Expr4: Day(12/31/2005-[Scheduled Roll Out Date]-0.49999)

View Replies !   View Related
Combo Box "unable To Retrive Data"
I have a frm (frmWMS) which has 4 fields,
Location, Room, Number and Quick dial

Its based on qryWMS which has one criteria in the Location field, therefore the form shows all the results for bath or bristol or wherever the user selects.

I have gone to add a combo box that lets the user select a record based on which room they choose in the combo box, but when i use the wizard it says unable to retrieve data......

I can see no reason and its driving me nuts, ive searched for everythin i could think off!

View Replies !   View Related
Query Calculating No Of Days Between Start And End Date
i am trying to run a query from a form which will bring up the no of days difference between the start and end date also on the same form.
The query doesn't bring back any results can someone please guide in what i am doing wrong.
Here is the query
SELECT DateDiff('d',[start date],[end date]) AS [no of days]
FROM [booked property]
WHERE ((([booked property]![start date])=[forms]![booking]![booked property]![start date]) AND (([booked property]![end date])=[forms]![booking]![booked property]![end date]));

Thanks

View Replies !   View Related
Days=NextDate-CurrentDate But How In Access Query?
I have two tables
ACCOUNTS
with fields
(AccountNumber, Name, AccountTypeID, Address, Phone, Date A/C Opened)

And
TRANSACTIONS
(TransactionID, TransactionDate, Particular, ChequeNo,AccountNumber,AccountTypeID, Rate, WithdrawalAmount, DepositAmount)

I created a query based on these two tables
CUSTOMER
(AccountNumber, TransactionDate, Days)

In this query Days: (NextTransactionDate-TransactionDate)
How I access NextTransactionDate of a specific Account in the record?

View Replies !   View Related
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 !   View Related
Orders Calendar
i am trying to find a code that will present me some orders calendar of rooms.
i a trying to build some calendar that will show me the order date and the end date of the roon, rent by te customer, somehow that i will see that all the dates between these two date are occupied, like an outlook calendar.

View Replies !   View Related
Multiple Orders
I am new to access and have a form set for processing orders. How do I process multiple orders i.e a customer orders an onion and a pepper. Currently I have to create 2 orders and would like 1 order with 2 items. I am sure this is very simple. Hope someone can help

View Replies !   View Related
Orders Database
I know this is probably very simple but I do not know how to do it.
I have:
Table 1 - customers
Table 2 - Orders

At the moment, when a customer orders 2 items, I have to create 2 seperate order numbers.
I would like to have both items under one order number. Can anyone help with an easy solution.

Thank you

View Replies !   View Related
Multiple Orders
I have asked this before but could not work out the answer i got(sorry). I need things in very simple terms. I hope someone can assist me.

I have customer table and an order table. Customer table obviously holds all customer info. my problem is orders.

I would like a table/form that allows me to input muliple orders. i.e:

Customer: Smith
Item: Apple
qty: 2
Item: Pear
qty: 1
Item: Orange
qty: 3

I would like all items under 1 order. Currently I have to process them all individually. Can someone help please

Thank you

View Replies !   View Related
Duplicate Orders
It seems I continue to run into this problem.

A company sells products in packages.

Example: Letís say you build PCs

All of your PCs are sold as a complete product with 10 or so components

Your choices are to reenter every item each time you make a new quote, or automate the process.


This is the current method

Step1:
Create multiple tables identical with the orders detail table, and create all of the records needed to satisfy the order packages. Each table is one order package.

Step 2:
Create an order as you normally would.

Step 3:
Change the linking number in the package table (to the appropriate order number)

Step 4:
Paste the contents if the table into the Order Detail Table.

This works well but is labor intensive, and is not error proof.

So finely the question. Is there a way to automate this process with the selection being made form either a drop down list or a query .

My gut tells me it a macro thing but I donít use macros for much and my VBA skills are limited to adapting code that I pick up here and there.


Any guidance would be greatly appreciated.

Sincerely,
Joe

View Replies !   View Related
Available Stock For Orders
Hope someone can help with this.
I have a form which lists all my outstanding orders.
Via a Dcount I get a figure of whats in stock for each order (product description & length) & this figure is listed against each parrticular order.(stock Qty)
But what I also want it to do is look at the Qty feild for each order, sum them & subtract it from the total qty in stock & list the outcome against each order. (if there is enough stock to cover order for each line)
Is this possible.
I have attached a sample of DB to give a better understanding of where I am at.

Any help would be great.

View Replies !   View Related
Picking Orders For Invoices
Hi everyone,

I'm sort of new to Access, but over the past few months I've been able to develop an inventory database for my work which turned out quite nicely and does everything I need it to, except for one thing.

I want to also create invoices for my customers, and after looking at examples I understand that most people link the "order" with the invoice, so that there is one invoice per order. However, I need the ability to combine many orders into one invoice. That's where I'm stuck.

I don't know how to arrange my relationships and forms so that I can "pick" the orders I want on my invoice, and not at the time of the Order, but at some point after. Basically my orders will be established, but their allocation to invoices won't, until I come along and put them into their rightful bill.

Does anyone have any thoughts on this? I'm pretty stumped.

Thanks a lot for your help.

View Replies !   View Related
Sort Orders In Queries
i've now converted a large database to run with sql as well as linked jet tables. The main issue was checking every recordset to ensure it opened properly. I also found that sql returns a few different errors code from Jet, that needed trapping differently = all OK.

Now, in some cases I had opened the backend Jet table directly in order to use seek on a particular index, so this needed changing, as this is not available with SQL

I may well be wrong but i couldn't see an easy way of traversing a recordset table in SQL/ODBC according to a particular segmented key

Instead I was able to modify my process to extract the data I wanted with a query, and I then used a recordset on the query, which is fine.

HOWEVER, to emulate the index I was using, the query is sorted on about 6 or 7 different segments, the first of which is CustomerCode. Now when I opened the query to view it, I changed the sort order by clicking on a date column (as it happens) to sort it. When I then closed and reopened the query it still opened sorted on the date column, which is not right, as the customners are now all out of sequence. When the whole dataset was re-extracted it did return to the original and correct order

THEREFORE Is there a way of forcing a query to reset itself to its original ordering sequence. I think it is not likely that any user would ever open a query directly anyway, but it would be nice to ensure the query is always processed in the intended order. Perhaps it isn't a problem at all - maybe when a query recordset is opened in code, the sort order is reset anyway, but I would appreciate some views on this.

Thanks in anticipation

View Replies !   View Related
Maintaining History Of Orders
I am creating a database for a company that sells a product with a variety of options.

They have all their previous orders in a works spreadsheet file. Each customer has their own file with every order for the past 15 years. There are probably about 1.5 million records.

The company wants all those 1.5 million records accessible in their access database.

I've brought in about 20 records for the history and they can run a query to see a customer's past orders by their account number.

My question: Should I put all the history into one gigantic table or would it be best to try and create separate tables for the history? (Maybe history by state.)

The history table then will take the new orders each year and add to that table.

(Eventually, once the database gets done I'm thinking we'll have to step up to SQL but not sure.)

Thanks for any advice.
Melanie

View Replies !   View Related
Alphabetical Sort Orders.
Hello.

How can I preserve the user-defined sort order in a column in a table?

Every time I exit the table, and go back, my items have been alphabetically sorted. This results in my Combo Box displaying the list in alphabetical order, rather than my custom order.

Thanks ahead.

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved