so i have an interesting question and im hoping that someone can help on this one. i need to pull date from a specific table, no problem, that's written and working fine, next i need to be able to join the data from another table by a primary key, again no problem. third, i need to be able to select the date (using WHERE) for a specific date range. (i.e. i enter the date range of 01/7/2007 to 15/7/2007) and the query comes back only showing the data from that specific time, not the data from before or after. this is where my problem lies, all the entered data is being shown after entering my date range. i am going to include my SQL statement, just so you can actually see what im really talking about.
SELECT srealest.Name0, srealest.Dist1, SREpayments.Face2Pd, SREpayments.Penalty2Pd, SREpayments.[2paid], SREpayments.Face3Pd, SREpayments.Penalty3Pd, SREpayments.[3paid], SREpayments.Face4Pd, SREpayments.Penalty4Pd, SREpayments.[4paid], srealest.Map, srealest.Parcel, srealest.LeaseHold, srealest.TaxRebate1, srealest.TaxFace1, srealest.TaxPenalty1, srealest.TaxYear, srealest.BillNo, srealest.PdRebate1, srealest.PdFace1, srealest.PdPenalty1, srealest.DatePd
FROM SREpayments INNER JOIN srealest ON SREpayments.BillNo=srealest.BillNo
WHERE (((SREpayments.[2paid]) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date]) Or ((SREpayments.[3paid]) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date]) Or ((SREpayments.[4paid]) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date])) Or (((srealest.DatePd) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date]))
ORDER BY srealest.Name0;
any thoughts or ideas on how to accomplish this would be greatly appreciated!
I have multiple buildings that I own. Each building earns a monetary amount each day. Some days they earn $0, some days they earn $1,000. This is all kept track in a data base in which someone manually enters the information each day.The three fields are:
Building Date Profit
I have narrowed a query down to one building, and I am interested in two things. How do I write a query that shows a 30 day peak (the most profitable 30 days). In other words. I want to see the following Building: GNB Tower Date: 02/14/13 - 03/15/13 Profit: $25,162
The next thing I am interested in is a 7 day peak from within these 30 days. This shouldn't be too difficult if I can figure out how to query these 30 days, I can query 7 days out of those 30 days. So this bigger mind stumper is the first one.
out of a date range of months and months (Let's say 10/12/13 - 6-14-13).Once I figure this part out, I am sure I can figure out how to compile all buildings into one large report with their 7 day peak and 30 day peak.
Hi all, I need to extract in a query a recent range of records, these records should be all before a certain date and all the following future records example: if today date is 12/12/2007 I need to extract all records that are dated from the 1st of november and all the records that come after the 12/12/2007. In the criteria I have set "Date()" what else should I be adding to get the range mentioned above? Thanks
I'm having issues with reports. What I want to do is only print specific dates in a report. I have read and tried everything to accomplish this but it just isn't working. Any suggestions????????????
I want to make a delete query, which delete record between certain dates, I want access to ask user to specify range of date before executing the query.
I am trying to calculate the total hobbs time (Ending Hobbs - Starting Hobbs = Total Hobbs) based on a user inputed date range. The query that I created (see attachment) doesn't seem to give me what I'm wanting.
Hi I could do with some advice on how to start the following and if best to use both excel and access to achieve it.
I want to enter a range of data when I receive a bill such as date, supplier, bill amount, description, quoted price, customer job and retail price. Once I enter this I want to be able to do the following,
1.Pick a customer job and date range and get a report for all the details of bills received in relation to that customer in that time so that I can send the customer a list of items they bought in that time period and what each item cost. 2.I want to be able to compare cost prices from different suppliers and print a list of all items which shows who the preferred supplier is for each item based on the cheapest. 3.The quoted price from suppliers should automatically appear when the description and supplier has been entered. 4.There needs to be some sort of warning if the quoted price doesn’t match the bill price 5.I need to be able to get a report which gives the details for any bill which was entered that was not bought from the preferred supplier.
Some advice on pointing me in the right direction to get started is what I need and would be greatly appreciated
I have a weekly list of transactions that come in the format DD/MM/YYYY HH:MM:SS, I need to tag these individual transactions with a week number. The problem is, I can't use the Datepart function etc. as the day is classed as running from 8am to 8am rather than midnight. I have a list of all of the weekly date ranges for a few years (with the time) so I was able to solve this problem in excel by using the Index and Match functions. However, I'm trying to automate this process as much as possible so I'd rather perform this function in Access.
I am trying to connect to excel from Access database. Once I make the connection I want to delete data from a range on a specific sheet. There are 4 columns on the spreadsheet but i wouldn't know how many rows. So for example, A150... but I may not know what the last row is. How would I be able to delete data from a range of columns to the last row?
In the Owner Name field, I am looking for a way to pull out all estates. They are abbreviated with "EST" or "Estate". The problem is throughout the database, if you do a simple filter for "contains est" it will include names such as "Forester" or "LHB Investments".
How do I query, or filter this database such that it only returns the estates abbreviated with "EST" or "Estate"?
I am using Access 2007. This is the problem; I have a warehouse divided into four departments North, East, South, and West. Each department is assigned its own storage location range North is N1-N1000, East is E1-E1000, South is S1-S1000, and West is W1-W1000. On the access input form of my database are two fields, one combo box field for Department and one combo box field for location stored.
When the user is updating the data base for parts received or shipped, the user must enter the department and location stored to correctly debit or credit that department. The location stored entered must be assigned to the department. (Example: dept. is north and storage location is N65, the user enters D65 instead, error message is displayed, "you have entered the wrong location").
What I need is instructions how to write the code to check the fields to verify location stored matches department, and display a warning message. How do I do this? I have a table with department names and storage location ranges.
I have a spreadsheet that my company uses that has about 20 clients presented in a single sheet in Excel so I was wondering if it was possible to display only a selected range (i.e. only one client) in a specific form in Access to cleanse this up a bit.
I have a table with more than a million records. I want to add a new column with date equals to e.g.3/14/2012 for all the records. I am using update query, but it is super slow (taking more than a hour)..is there any other way to do this?
how to read a specific line in a CSV file (using VBA), to see if the phrase "There are no records available." is present.
If it is present, then I'm going to do a debug.print stating that there are no records to load - and then the script will move on to the next file. If the phrase isn't present, then I'm going to upload the file to Access, parse the information, and then upload it to a CRM. (I already have the latter portion of the code up and running....I just need to account for the first part, where I can determine if the file has data or not).
The structure of the file never changes. The first row is composed of eight column headers (Post Date, Card Number, Card Type, Auth Date, Batch Date, Reference Number, Reason, Amount) and (if) the phrase "There are no records available." is present, it will show up on the second row, in the first column (under Post Date).
I have recently been doing a lot of work on this area. Im able to export to where i want to and run macros through the VBA code inside of Access to edit the spreadsheets. This is ok if your making a new excel workbook/worksheet.
But what im stuck on is exporting to a so called template in excel. I can export to it at the moment but creating a new worksheet, in which i have to then cut and paste the data into the correct worksheets through code and then delete the worksheet that i had been working from (which is annoying because you have to confirm the deletion of this worksheet, which is why i couldnt really do the process this way).
What i want to know is there a specific way of telling the data you are exporting from a table/query/querydef to go into a certain worksheet and into a certain cell. For example; a list of names, i want all the Surnames to go into a worksheet called "Claim_Breakdown" and start from cell "A15" downwards until they have all been exported into the worksheet.
Anyone have any ideas on how i could achieve this? Thanks.
I want to hyperlink from a query direct to the relevant record in a specific form. I have a hyperlink field in the form which shows up in the query. When clicked in the query, this hyperlinks to the form but I cannot make it select the correct record in the form.How do I get it to select the correct record?
I am trying to figure out how to make a button that sends an email to a specific email address, containing the information from 1 row.
we have rows where we put the the address, the quantity and the time interval we can collect the packages in. these information have to be send to a trucking company. I want access to send an email to the trucking company's email address, and not a whole report of all the rows, but be able to choose to send row 1,2,3 etc.
Hello, I have just spent ages doing searches and reading everything I can on locking. But, I have yet to find an answer as to how I can lock a specific field in a specific record.
e.g. Staff enter customer details, then at the end of the day the admin (me) checks it over and presses a big old button that stops them from locking certain fields in the current record only - they must still have access to the unlocked fields of the current record, and it must not lock any other records.
I'm guessing there's some VB code in the form of fieldname.lock = true, but then it locks the field throughout the whole table!
I am new to access i have a problem which is i have made a form which contains a subform and a read only subreport, what i want is the ability to select a record in read only subreport as in the picture attached and make a button that when i press on it, it should copy the values of the itemsID field, Packing field, ContainerNo field and origin field from the selected record and then paste them in the subform below.
Also i want to add more then one item, so the when i press on another record it should paste the values below the first record.
Ok let me explainI have a list of 50 people who may have accessed events A,B,C at different times but what I want to do is to put a date in my form, run the quaery which will list all people who have Attended A,B or CWhen I do my normal query, I put the fields in, but if I put a date filter in Event A, it will not show up any person who may have attended event B but not Event A.I thought about running an append query but this would mean running the data several times with possible duplication of client names.Ideally I would like to have my report to showDate: 31/07/2006Name A B CK.Brown Y YJ Blogg Y YK Smith YK Ellum Y Ythen I can just use a count at the bottom :)The methods I have tired will filter event date A, but will not show J BloggIf I create another append for Event B, and append that data to a table, then I will have duplicaion of K Brown.. Any Help Appreciated.Last thing - when I run the append queriy and get my duplications, the fields where I had a Y/N option - if it was YES then I get a -1 instead of 1 ??? Why? and how do I make sure that when appending that the Y answer is a 1 not a -1
I need a query that calculates a date that is the first of the month following a specific date. Example: The first of the month following the 65th birthday.
Hello all...New to Access & still crawling under the tables....So any help is most appreciated.
I have a table w/ a field named 'Week Ending'....The 'Week Ending' column is based on each Friday's date, and may contain anywhere from 5 - 30 entries per Week End date.
Now for the simple question I'm obviously missing the answer too...I have a query that I run weekly based on the entire record set for the 'current' week ending.
My last set is obviously 1/26/2007. Is there anyway to make the most recent week ending date the default criteria for the record set; instead of changing the query design each time? It may seem trivial to some, but I just want to cut any step possible for any additional users..
I need a query that calculates a date that is the first of the month following a specific date. Example: The first of the month following the 65th birthday.
I was wondering how I could select a date for a particular field in my main form. What I'm trying to do is this: When I make a particular selection in one of my fields, I need to be able to specify a date that will be displayed on the main form, and also print on the report. I need to be able to select a specific date, and not a default date.
Hope this is clear, trying to keep it simple - which can be pretty complex.
