DateAdd Function. Retrieve Records That Were Made This Month.

Aug 7, 2006

Hi Guys,

I have an SQL statement that looks something like this:

SELECT * FROM tblBandwidth WHERE (DateCreated > (DateAdd('m',-1,Date())));

So... would that retrieve all the records which are a month old (ie 31 days old, for example), or would it return all of the records that were made in the current month... there is a big difference!

I basically want a way of returning all of the records that were made in the current month.

Thanks!

View Replies

Queries :: Retrieve Records From Specific Month

Mar 10, 2014

I have a query which I'd like to create which would be to retrieve records from a specific month of a specific year, in this format "01/14" "02/13" etc..

Obviously it would need a prompt box for the query for the user to input the month and year.

View 6 Replies View Related

DateAdd Function

Sep 24, 2004

I am using the following to populate text boxes that are functioning as labels in my report. I am getting an error: #NAME. Do you know what's wrong with this code??

=DateAdd("d",-1,Forms!frmDates!txtEnd)

This prompts me for txtEnd. When I put in the date (which is a parameter from my SQL cross tab query, the values in the report WORK, but the label doesn't. Any ideas?

View 1 Replies View Related

Complex DateAdd Function

Aug 4, 2006

Hi All,

i have a slight problem i have a access table which has the following fields:

Name
Photocard ID
Purchase Date
Photo
Cashsaver Zone
Valid From
Period of Validation
Expiry

the period of validation field has a lookup to another table with the folwing values: 28 Days, 3 Months, 6 Months and Annual.

the valid from date is entered manually.

basically (maybe not uite the right word!) i need the the expiry field to automatically insert the correct expiry date by looking in the valid from date and adding the correct amount of time onto it according to what is selected in the period of validation field.

example:
Valid from date is 01/01/2006
Period of validation is 6 Months
Expiry date should be 01/06/2006 (01/01/2006 + 6 Months)

i came up with the following formula although i know its not really correct and some of the words arent functions at all just to show what is should be based on:

WHERE Period of validation <= DataValue ("28 Days") then DateAdd("d",28, Period of validation)
WHERE Period of validation <= DataValue ("3 Months") then DateAdd("m",3, Period of validation)
WHERE Period of validation <= DataValue ("6 Months") then DateAdd("m",6, Period of validation)
WHERE Period of validation <= DataValue ("Annual") then DateAdd("m",12, Period of validation)

also i am unsure into what box to type this into?

i would greatly apperciate some help

Spindlemania

View 6 Replies View Related

Queries :: Calculate Profit Made By Type / Month And Year

Apr 22, 2013

I want to make a query to figure out profit made by month year and room type.

View 1 Replies View Related

Queries :: DateAdd Function Using Existing Field Values

Nov 26, 2013

I have a query in design view that consists of two calculated fields at the moment. One of the calculated fields is supposed to add one year onto a value from another date/time field called "Last_Date."

The expression I've written in the "Field:" box in Query design view is

Expr 2: DateAdd('yyyy',1,[Last_Date])

When I try to run the Query it doesn't work! Access just prompts me to enter a parameter value for Last_Date.

View 1 Replies View Related

Retrieve Name Of Current Sub Or Function

Feb 22, 2005

Is there a function, or an object property, which will return the name of the Sub routine or Function that is currently processing?

View 10 Replies View Related

Cannot Retrieve Unique Records

Mar 14, 2006

:confused:

I have been working on a normalized db for the past few months and it works wonderfully. But now I need to find a way to create a form so that the rest of the company can update their data.

My Table has the 5 following fields. Also listed is an example of the types of data I have there

StoreID_Catagory_Customer Year_Month_MixPercentage
IN00001_Japanese_____2005_____Jul_______.01______
IN00001_Japanese_____2005_____Aug______.02______
IN00001_Japanese_____2005_____Sep______.01______
IN00001_Chinese______2005_____Jul_______.35______
IN00001_Chinese______2005_____Aug______.25______
IN00001_Chinese______2005_____Sep______.26______
IN00001_Japanese_____2006_____Jul_______.01______
IN00001_Japanese_____2006_____Aug______.02______
IN00001_Japanese_____2006_____Sep______.01______
IN00001_Chinese______2006_____Jul_______.35______
IN00001_Chinese______2006_____Aug______.25______
IN00001_Chinese______2006_____Sep______.26______

As you can see I have multiple StoreID's for one store, IN00001. My db goes up to IN00419 so it ends up displaying 29,664 records. There are 4 different categories (Japanese, Chinese, Local, and Other). Until now everything has been taken from a datasheet and put into an Update Query but now that other people have to quickly access the system I need a slick interface.

What I need is a way to have a form that can update the table and add new entries that looks like this in the form:

IN00001____2005________Jul__Aug__Sep__Nov__Dec__EC T.
Japanese MixPercentage__.01___.02__.01___.03__.01
Chinese Mix Percentage___.35___.25__.26___.33__.30
Local Mix Percentage_____.60___.70__.69___.62__.65
Other Mix Percentage_____.04___.03__.04___.02__.04

IN00001____2006________Jul__Aug__Sep__Nov__Dec__EC T.
Japanese MixPercentage__.01___.02__.01___.03__.01
Chinese Mix Percentage___.35___.25__.26___.33__.30
Local Mix Percentage_____.60___.70__.69___.62__.65
Other Mix Percentage_____.04___.03__.04___.02__.04

I hope this gives you a clearer picture. I need to be able to update 96 entries at a time (12months, 4 customer types, 2 years) but do it in an efficient manner. The table only has 5 fields so I need to figure out how to display multiple entries on the same form. I also need a way to add new entries. In other words for me to add another StoreID such as IN00420 then I would have to enter in a value for every field for 96 records in the table. When in reality I should only need to update the mix percentages since the months and years remain constant. HELP!!

Any help would be much appreciated. Due to the variable nature of the db I cannot establish very many one-to-one relationships. So therein lies another problem.

View 3 Replies View Related

Cascading Combo Boxes - Interact And Record Selections Made In New Records Within Destination Table

Sep 12, 2012

I am trying to get a series of combo boxes to interact and record the selections made in new records within a destination table. Here is situation with respect to the tables involved:

1 table lists the names/acronyms of various research facilities and the branch to which they belong (acronym is PK)
1 table lists Financial Points of Contact for each facility (acronym is FK to relate to earlier table)
1 table lists Technical Points of Contact for each facility (acronym is FK to relate to Facility table)
1 table lists program participants and the branch they belong to (Branch in FK to relate to Facility table)

I am trying to create a form that allows me to set the participant and, from this selection, restricts the facility choices in the Facility combo box to those that fall within the branch to which the participant is assigned. I think the next step is pretty obvious too. Once the facility is selected I have combo boxes that would only display those Financial and Technical PoCs assigned to that facility.

I have watched the 4 Data Pig tutorials and I can easily make functional cascading combo boxes as long as they only refer to the look up tables. I generated perfectly functioning cascading combo boxes on a test form. How do I generate combo boxes that will then store the info selected in my destination table? Whenever I try to set a Control Source it disrupts the functioning of the queries.

View 4 Replies View Related

Query - Retrieve A Set Number Of Records

Nov 4, 2005

Hi all,

I want to retrieve a set number of records from a table, using a query. I'm not sure of how to do this.

I can't use the ID, because the table gets its data replaced contantly by imports (so the ID doesn;t start at 1...)

I want to be able to retrieve, say, the first 25 records on that table.

Any help is appreciated

View 2 Replies View Related

Retrieve Records For The Past Year

Mar 21, 2008

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 3 Replies View Related

Forms :: Retrieve Records Based On Combobox

Jun 25, 2013

I have two Tables, "staff data" (staffID, name) and "issues log" (IssueID, StaffID, 20+ fields about individual Issues). StaffIDs are linked together so each employee can have multiple issues..I have (so far) Form1 containing individual Issues that can be filled in, or retrieved one at a time / scrolled through in IssueID order

What I want is a second form, containing a combobox with all StaffIDs (+ names) listed; when a StaffID is selected, a table is loaded containing all Issues for that employee. When any of these is selected, Form1 is loaded

I have created the second form, including the combo that contains both StaffIDs and Names. I can't yet grasp how to show the set of Issues applicable to that employee when I change that combo...If I could have the combo on Form.

View 2 Replies View Related

Queries :: Tracking Database - Retrieve Top 100 Records

Jul 24, 2013

I am working on a project where I am creating a database that tracks open orders (old and new orders that still need to be billed but haven't been billed for one reason or another).

I have my "Master" table built and now I need to create 2 queries, one query retrieves the Top 20 orders based on value and that are older then 30 days and the second query is to retrieve the Top 100 orders based on the same criteria but the Top 100 query should not include the orders that appear in the Top 20 query.

My Top 20 query is below

SELECT TOP 20 MASTER.COMBO, MASTER.[Customer Name], MASTER.Status, MASTER.CCD, MASTER.Area, MASTER.[Order Type], MASTER.[Order Value], MASTER.AGE, MASTER.ANALYST, MASTER.COMMENTS
FROM MASTER
WHERE (((MASTER.AGE)>=30))
ORDER BY MASTER.[Order Value] DESC , MASTER.AGE;

I have tried multiple ways to do the Top 100 query and exclude what is returned in my Top 20 query but so far no success.

View 10 Replies View Related

Creating A Query That Will Retrieve Different Data From Different Records?

Mar 17, 2014

I am trying to create a Database that will type our orders. I have a table with our customer list that includes both billing and shipping information. The problem is that sometimes one customer will request a "drop shipment" to another customer. Is it possible to retrieve different data from two different customers? Billing info for customer "A" and shipping info for customer "B" without creating seperate tables?

View 1 Replies View Related

Using Multiple Combo Boxes To Retrieve (not Edit) Records From A Table

Mar 24, 2005

I'm creating a form that has combo boxes pertaining to each of the following fields from table "Documents": DocumentID (primary key), DocumentTitle, DocumentAuthor, and DocumentYear. No combo box is used with any priority over the others. The working form will allow a user to retrieve the full document record (data for all fields) by using any combo box they want, as well as any combination of combo boxes. This means that if a selection is made in one field's combo box, the drop-down lists in the other boxes need to update based on that preliminary selection. The filtered results for each field, based on any and all combo box selections, are always shown in a single datasheet on the form.

The kicker is that when a user starts filtering records by making selections from the drop down list in a combo box, but then decides to TYPE in another field's combo box, I want the combo box they typed in to do two things: 1) filter the records for that field based on what they typed, if any records meet that criteria (e.g. they typed "B" so only records beginning with B are shown), and 2) if no records match the typed criteria, the search starts all over (at the top of the cascade), filtering all library records based only on what they typed in the most recent combo box.

Does that make sense? Any tips welcomed.

I don't know how to cascade combo boxes in this way.

View 2 Replies View Related

Modules & VBA :: Creating A Function That Counts Records And Use That Function In A Query

Dec 11, 2013

So basically I need making a function that will count the number of records from another table/query based on a field from the current query.

View 2 Replies View Related

Queries :: Retrieve Records From One Table Where Dates Are Between Records In Another Table

Dec 30, 2013

I have a survey database that I've been using for the last year for monthly auditing of employees files. I need to be able to get monthly audit scores for each employee but grouped by their manager. The problem I'm having is employees have moved between managers throughout the year, so employees that are listed under Manager 2 now were actually working for Manager 1 when the audits occurred.

ie. Audits occurred Jan - April for Employee 1 while they were assigned to Manager 1. Employee 1 moved to Manager 2s team in May. So when running monthly reports for the year Employee 1 audits should fall under Manager 1 for Jan-April and Manager 2 for May-Dec.

I do have a history table set up like:
tblEmployeeHistory
ID (PK)
EmployeeID (FK to Employee table)
ManagerID (FK to Manager table)
MoveDate (date employee assigned to manager)

The Employee table is set up like:
Employee ID (PK)
EmployeeName
ManagerID (FK to Manager table)

The Manager table has the ManagerID and ManagerName.

That's the employee side of things; then I have the tables that store the audit results:

tblAudit
AuditID
FileNumber
AuditDate
EmployeeID (FK to employee table)

tblAuditResults
AuditID; QstnID (Composite PK, QstnID is FK to tblQuestions)
Answer

How can I use AuditDate and MoveDate to relate audits to the managers the employees were under when the audits occurred?

View 14 Replies View Related

Queries :: Include Date Criteria In User Defined Function That Calculate End Of Current Month

Jul 22, 2014

I have written a user defined function that calculates the end of the current month. This I named EndOfThisMonth. It works well as a function. Now I would like to use it as date criteria to include in a query. The function is included as such EndOfThisMonth().

The field on which this function is to enter as a criteria is another calculated date function called Due.

When I run this query I get an error message saying Undefined Function 'EndOfThisMonth' in expression.

View 3 Replies View Related

Search All Records For A Given Month

Jan 11, 2007

Hi All,

I was wondering how would you search all records by a given month. What criteria would you put in the date field?

View 12 Replies View Related

Delete Records For Last Month

Oct 16, 2007

Hi All.
I need to modify my database that give me ability to delete all record form table for last month. How to solve that problem?
Thanks.

View 9 Replies View Related

Queries :: Records From Now Until End Of Next Month

Sep 27, 2013

I am using the following code to get results from next month,

Code:

Month([ptdate])=Month(Now())+1

which works great to get next months results. I try to get what is left of this month by adding the code:

Code:
Between Now() And Month([ptdate])=Month(Now())+1

This actually gives me results from July and August, eventhough it is Sept.Basically I want the results from the remainder of this month until the end of next month.

View 2 Replies View Related

Show Records By Week Per Month

Feb 20, 2007

Good day!

I'm planning to create a form that would show total number of sales transaction per week: If today's month is February, i would like users to see records like this: (Week should start on a Monday and ends on Saturday)

GroupNo______Week1_____Week2_____Week3____Week4___ _Week5___MTD
GroupA________12_________2__________10________11__ _____15_____50
GroupB_________8_________5___________7_________1__ _____21_____42

I can't find a sample code for this particular date criteria. :-(

Thanks in advance!

Sheila

View 6 Replies View Related

Grouping Records By Month Query

Oct 27, 2005

This is probably an easy question for you all:

I want to run a query that does a GroupBy with DATE and Counts the number of records associated with that DATE.

I can do this, but I also want the dates to group together by Month so I can count the number of records per month for multiple months in the same query. How do I do this?

Thanks very much

View 1 Replies View Related

General :: Group Records By Month

Aug 3, 2012

I would just like to know if it is possible to group Months together: this is what i have did so far :

Code:
SELECT Count(Source.ID) AS CountOfID, Clusters.Cluster_Desc, Department.Dept_Desc, DatePart('m',Source.Day_Month_Year) AS [Month], Source.Original_Source, Source.Headline, Source.Issue, Source.Analysis, Source.Action
FROM Source INNER JOIN (Department INNER JOIN (Clusters INNER JOIN Cluster_Dept ON Clusters.Cluster_ID = Cluster_Dept.Cluster_ID) ON Department.Dept_ID = Cluster_Dept.Dept_ID) ON Source.ID = Cluster_Dept.ID
GROUP BY Clusters.Cluster_Desc, Department.Dept_Desc, Source.Original_Source, Source.Headline, Source.Issue, Source.Analysis, Source.Action, Source.Day_Month_Year;

View 1 Replies View Related

Reports :: Records In Current Month?

Mar 13, 2013

I have a table with those fields:

Name date(d,m,y)
John smith 1/2/2013
Mary loe 25/2/2013
Mary loe 1/3/2013
Jim tonel 3/3/2012
Jim tonel 5/3/2012

I want to create a report or query that will calculate how many times a name appears in current month(03/2013) and if not it should return 0.

For example the report or query should look like this:

Name count
John smith 0
Mary loe 1
Jim tonel 2

View 1 Replies View Related

Query That Won't Group Records By Month

Nov 14, 2012

my database was working fine until a new month was added in the attendance now each staff member has about 20 records when they should only have two. if i remove the second month it corrects its self but i can't do that permanently also i have office 2010 but the office has 2003 so i saved it to 2002-2003 file format and it was working fine up until today when it has started to give and error message Which Reads: Your Microsoft Office Access database or project contains a missing or broken reference to the file 'ACEDAO.DLL' version 12.0

View 6 Replies View Related