Need Querry To Show Records That Are 3 Months Old
Aug 27, 2005
I have a querry designed to show loans that have a start date and an end date. I want my querry to show loans that have a start date, no end date, and that are 3 months old. Here is what I have so far but I can't figure out how to increment the 3 months on the start date:
SELECT Detail.CustomerID, Detail.TransactionID, Loan.LoanAmount, Loan.StartDate, Loan.EndDate, Loan.LoanLender
FROM Detail INNER JOIN Loan ON Detail.CustomerID = Loan.TransactionID
WHERE (((Loan.EndDate) Is Null))
WHERE ((( Loan.StartDate)) Is
Please help finish this line.
raydan
View Replies
ADVERTISEMENT
Mar 3, 2008
Is there an expression to show records in a query that are older than 6 months?
At the moment I have to go into the database every month and change the criteria date ie:
<#01/09/2007#
It would be much easier if it could do this automatically with an expression.
Can anyone tell me if ones exists?
Thanks
View 9 Replies
View Related
Jun 27, 2005
Hi,
Just wondering how can I make a form that shows only 5 months old records. I know it has to be through a query but don't know how to proceed.
Thanks
Rajput
View 2 Replies
View Related
Jul 13, 2007
Can someone help?
I need the code for a filter or query which will show the past x (12, 24 etc) months worth of data. Each record has a date tag so i need a filter which will just show records for say the past 3 months.
Any ideas?
View 4 Replies
View Related
Apr 22, 2013
I want to create a simple query from a list of orders dating back over 12 months.
The fields I have is Ord_Date, Qty
I need to show the orders by month for the last 12 months.
The problem I am getting is that the orders for the month of April (as we are in April now) contain orders from 1st - 22nd April 2013 and orders from 23rd - 30th April 2012, therefore confusing the figures.
I would just like orders grouped by month with a total qty dating back 12 months, but without any old orders for the current month.
View 6 Replies
View Related
Mar 22, 2013
I currently have a form where users can enter an "End Date", click a button, and it queries the data from 12 months prior to "End Date" entered.
Is there a way to force the query to show all 12 months, even if there are no records for a particular month? For example: if the user enters February 2013 in the date field, I would like the query to return:
March 2012
April 2012
May 2012
June 2012
July 2012
August 2012
September 2012
October 2012
November 2012
December 2012
January 2013
February 2013
...so even if June 2012 has no records, it is included in the query with a value of zero.
This is what I have so far:
WHERE (((Qry_Tbl_Assets.Dte) Between DateAdd("m",-12,[Forms]![Main_screen]![End_Date]) And [Forms]![Main_screen]![End_Date]))
View 2 Replies
View Related
Mar 12, 2008
Can't think how I would do this. If I create a query with a certain date then it will need to be changed every time the query is run. How would I write a query that automatically looks for a date 3 months or more old.
Thanks for any help. :D
View 2 Replies
View Related
Aug 6, 2014
We have a field (tbl_Enrolment.CourseCompletedOn) which holds the date a colleague completed a course. I want to bring through all courses completed within 12 months of todays date ( Date() ) from our database. I checked google and it suggested YEAR but that would only bring through dates with the same year, not within a 12 month period.
View 4 Replies
View Related
Nov 16, 2004
I have built a qry that initially shows the correct information. For example.
tblContent has 289 records with a Type = Class.
I built a Query to select from tblContent Type = Class and I get 289 records. I add additional criteria of Progress <>"Not Scheduled", I then get 206 records. I then add additional criteria Last Name <>"Demo" And <>"Care" And <>"Support". This brings up 200 records, but the query appears to duplicate each record 3 times. I do not have 3 of the same types of records.
The SQL Statement is below
SELECT tblProfile.LoginName, tblProfile.FirstName, tblProfile.LastName, tblProfile.Organization, tblProfile.CostCenter, tblContent.Title, tblContent.Type, tblContent.Code, tblContent.[Date Assigned], tblContent.[Date Started], tblContent.[Last Accessed], tblContent.Progress, tblContent.[Date Completed]
FROM tblProfile INNER JOIN tblContent ON tblProfile.LoginName = tblContent.LoginName
WHERE (((tblProfile.LastName)<>"Demo" And (tblProfile.LastName)<>"Care" And (tblProfile.LastName)<>"Support") AND ((tblContent.Type)="Class") AND ((tblContent.Progress)<>"Not Scheduled"));
The qry is named qryPhysical Class. I have provided the link to view the database. Can you help me?
http://briefcase.yahoo.com/turnerbkgabrobins
Thank you in advance for your assistance.
View 1 Replies
View Related
Sep 16, 2007
Guys,
Could you please help me to make a query with the below conditions from the attached test.mdb file?
1. Enq Received Date 10 days before the search date AND/OR
2. Technical Sub Date 4 weeks in advance of the search date AND/OR
3. Potential Date 4 weeks in advance of the search date
The querry will display all the records which satisfies the above conditions.
Thanks in Advance.
Prajith
View 11 Replies
View Related
Oct 17, 2006
I have a table, [temp_indicateur] which has fields [id,libelle_court,description,calcule,resitute,orig ine,seuil_min,seuil_max]
[number,text,text,boolean,boolean,boolean,number,nu mber]
My querry is this: insert into temp_indicateur (id,libelle_court,description,origine) values (" & rs!id & ",'" & rs!libelle_court & "','" & rs!description & "'," & rs!origine & ")"
But it gives me an error: Too little parameters
The table has no primary keys and no foreign keys. rs!id is the value of an earlier querry which I am running. Independently, I get the values of rs correctly but I can't get them to execute in this querry.
View 2 Replies
View Related
Apr 26, 2006
Hi,
I have a table with an autonumber incremental ID field using the format 0000, 0001, 0002 all the way to 1530.
I'm trying to create a query that returns the last 5 records in decending order, so...
1530
1529
1528
1527
1526
any ideas?
Thanks.
View 2 Replies
View Related
Jul 19, 2005
My current table
ClassName PropertA
ClassA 1
CassA 2
ClassB 2
ClassA 3
ClassA 2
ClassB 2
THis is my sample table with two columns. Property A can have 3 values 1,2 and 3. I want TO SELECT 4 columns such as classname, CountOf1, CountOf2, CountOf3. I just want ClassA in one row and at the same time get the number of 1's, 2's and 3's for that classA in seperate columns in the same row.
I appreciate your time. Please Let me know how to
write this querry.
View 1 Replies
View Related
Nov 28, 2005
Hello,
This may sound too common a question, but I really need help on it. Prior querries in the same do not seem to solve it.
I have two tables, table1 and table2 with the following fields
table1 ( ACID, name, address, Salary, MStatus,Age)
table2 ( ACID,salary, Age)
ACID is the common field in the two tables ( primary key in table1)
Required
I need to update table1 with the colums in table2 for all the similar ACID in in table1.
Basically, after teh update, table1 will have all the information it had before the update, but a few fields will have changed coz of teh new information coming from table2.
Please help with the code. I'm using access, and the table structures are all the same.
Thanks in advance
View 1 Replies
View Related
Feb 21, 2006
I have these three tables A, B, C and D. IN table A, SSN is the primary key. Tables B, C, and D have other columns, but the also have SSN as one of the columns. Also SSN is NOT required in other tables.
What I need to do is to be able to run an update querry that will update the value of the SSN in table A, and in any other table that will have that SSN.
I use Access 2000
View 1 Replies
View Related
Oct 25, 2006
I Have A Querry That Adds The Amount Of A Certain Light Bulbs I Have Order Since 2003. I Then Subtract The Amount Of That Light Bulb I Have On Hand Now...it Gives Me The Amount I Have Used Since 2003. Now I Want The Querry To Take That Number And Divide It By The Numberber Of Months Its Been When The Querry Was Open.
Example...i Open The Querry 10-23-06
It Tells Me The 60 Watt Bulb
Has Been Ordered 900 Times Since 12-2003
I Have 300 In Stock On 10-23-06
I Have Used 600 Since 12-2003
The Querry Needs To Give Me This Answer
You Use 17 60 Watt Bulbs A Month
View 6 Replies
View Related
Nov 20, 2007
hello all,
i would very much like anyones help with this,, i'm hoping its not too much to ask,, the story is a sfollows,,, i have two tables both conatining the same fields,, table 1 has approx 1k entries, where table 2 has approx 400 entries,, the data in table 2 is the newer data. what i would like to do is run a querry that would enable me to 'post' the data from table2 into table1,, for example table 1 has activity jc111 at 10% where table2 has jc111 at 20%,, if i could simply run this querry that would generate a new table or just up-date table1 my life would be very much better,, i really appreciate anyones help
- please remember that i'm quite new to this
thank you very very much in advance
best regards
View 3 Replies
View Related
Mar 14, 2008
Hi,
I am just starting with access and dbs and already have a problem
i am working on a db for managings goods being retured
my db has particular tables:
GOOD
GUpc (PK)- unique product code
GDescription
PERSON
PReference (PK)
PName
PSurname
PTel
PMail
COMPANY
CReference (PK)
CName
CAddress
CTown
CPostcode
CCountry
RGA
RGAReference (PK)
CReference (FK)
PReference(FK)
GUpc(FK)
RGADate
RGASatus
RGACostOfRepair
I managed to get a query working so that i can search the PERSON table searched by surname
PARAMETERS [PSurname] Text ( 255 );
SELECT PERSON.PReference, PERSON.PName, PERSON.PSurname, PERSON.PTel, PERSON.PMail
FROM PERSON
WHERE (((PERSON.PSurname)=[PSurname]));
but i cant get it to work i wanted to search it by PReference
PARAMETERS [PReference] Text ( 255 );
SELECT PERSON.[PReference], PERSON.[PName], PERSON.[PSurname], PERSON.[PTel], PERSON.[PMail]
FROM PERSON INNER JOIN RGA ON PERSON.PReference=RGA.PReference
WHERE (((PERSON.PReference)=[PReference]));
I get a msg that The expression is typed incorectly or is to complex to be evaluated. For example a numeric expression my contain too many complecated elements. Try symplyfing the expression by assigning parts if the expression as variables.
AND THIS IS NO HELP FOR ME... francly
The same situation is with all the other tables- its possible (query- search) for everything apart for the primary keys.
I prepared a querry gathering data from all those tables. I managed to get it work with parameter like the one below with surname
PARAMETERS [PSurname] Text ( 255 );
SELECT COMPANY.*, RGA.*, PERSON.PName, PERSON.PSurname, COMPANY.CName, GOOD.GDescription
FROM PERSON INNER JOIN (GOOD INNER JOIN (COMPANY INNER JOIN RGA ON COMPANY.CReference = RGA.CReference) ON GOOD.GUpc = RGA.GUpc) ON PERSON.PReference = RGA.PReference
WHERE (((PERSON.PSurname)=[PSurname]));
but the same situation again, i cant get it to work if i want to get it for RGAReference, CReference, PReference,GUpc
I don't know what i am doing wrong?
have I designed it all wrong or is it just a small thing...
Thank you for all your help
My problems started when I tried to run a querry that will return the GDescription (this tables store data about 3000 products)
View 2 Replies
View Related
May 6, 2005
I have created a search form made up of a number of different boxes. In my querry for the search I am using the following statement:
Like "*" & [Forms]![Search]![ExerciseName] & "*"
I a have a number of theses all saying similar things. At present I have to put something in each of the search boxes. Is there a way that I can leave a box blank and still search by other criteria. If I leave a box blank at present it returns no results.
View 11 Replies
View Related
Sep 20, 2004
Hello All
This might be an easy problem but I am not quite sure how to go about solving it. Presently I have a querry that is linked to a form so that when a person selects a role from the drop down list and you click search it uses the elment selected in the drop down list to show the coreesponding users. My probelme now is that I want to be able to select two different roles and use this querry to give me the users corresponding to these 2 roles. I would really appreciate your help what I have tired to do is create a new combo box and named it role 1 (the first one was named role) and then in the query in the criteria i tired to put the following
[Forms]![Users To Role Search Engine]![Role, Role1]but that give me nothing I am sure there is a flaw in my logic but I am not sure how to solve this. Thank you for your help
View 2 Replies
View Related
Oct 13, 2006
Hi,
I have this querry but when I execute on a button click it dosen't give me any error....but no data is saved in the table, why????
strSQL = " UPDATE DISTINCTROW projet set id='" & txt_id.Value & "',code_NOP='" & txt_code_NOP.Value & "',client='" & txt_client.Value & "',libelle_projet='" & txt_libelle_projet.Value & "',id_clarity='" & txt_id_clarity & "',technologie_projet='" & txt_technologie_projet.Value & "',em='" & txt_em.Value & "',date_debut=#" & txt_date_debut.Value & "#,date_fin=#" & txt_date_fin.Value & "#,site_mandataire='" & txt_site_mandataire.Value & "',charge_totale=" & txt_charge_totale & ",ca_total_vendu=" & txt_ca_total_vendu.Value & " WHERE id='" & Form_projet.id.Value & "';"
View 7 Replies
View Related
Dec 1, 2006
I have a string which I want to use as a querry, but its giving me an error saying "object required" even before executing the querry..
strSQL = "INSERT INTO projet (id,code_NOP,client,libelle_projet,id_clarity,tech nologie_projet,em,date_debut,date_fin,site_mandata ire,charge_totale,ca_total_vendu,no_sous_fon,no_ec rans_s,no_ecrans_m,no_ecrans_c,no_traite_s,no_trai te_m,no_traite_c,no_impression_s,no_impression_m,n o_impression_c,prov_risque,prov_garantie)" & _
" values('" & txt_id.Value & "','" & txt_code_NOP.Value & "','" & txt_client.Value & "','" & txt_libelle_projet.Value & "','" & txt_id_clarity.Value & "','" & txt_technologie_projet.Value & "','" & txt_em.Value & "',#" & Format(txt_date_debut.Value, "mm/dd/yyyy") & "#,#" & Format(txt_date_fin.Value, "mm/dd/yyyy") & "#,'" & txt_site_mandataire.Value & "'," & txt_charge_totale.Value & "," & txt_ca_total_vendu.Value & "," & txt_sous_fon.Value & "," & txt_ecrans_s.Value & "," & txt_ecrans_m.Value & "," & txt_ecrans_c.Value & "," & txt_traite_s.Value & "," & txt_traite_m.Value & "," & txt_traite_c.Value & "," & txt_impression_s.Value & "," & txt_impression_m.Value & "," & txt_impression_c.Value & "," & txt_prov_risques.Value & "," & txt_prov_grantie.Value & " );"
Can anyone solve it?
View 1 Replies
View Related
Dec 6, 2006
Hi,
I am running a querry, but it is not selecting any data...
Now I have checked all the stuff..everything is fine except the date part.
strSQL = "select periodicite.libelle as pl,indicateur.libelle_court as il,valeur.valeur,valeur.date_valeur,valeur.id_proj et" & _
" FROM valeur,indicateur,periode,periodicite" & _
" WHERE valeur.id_indicateur=indicateur.id " & _
" AND periodicite.id= " & cmb_periodicite.Value & "" & _
" AND periodicite.id=periode.id_periodicite" & _
" AND periode.id=valeur.id_periode" & _
" AND valeur.date_valeur BETWEEN #" & txt_date_debut.Value & "# AND #" & txt_date_fin.Value & "# "
I am printing the querry too, so that I can check whether it is giving the correct values from the txt_date_debut and txt_date_fin. I found out that everything is fine. I just can't figure out the problem !!! Can anyone help please????
View 2 Replies
View Related
Nov 8, 2007
Hi all
I have a subform which loads the items from a table the form is non editable just for viewing the items, the problem is how can i
A. limit the subform to only show the first say 10 items in the subform
B, to have a command button on the form where the the subform is on (command button not in the form) and when the user clicks it it goes to the next set of records say from 11 to 21 and displays that in the form.
i tried doing record cound but somehow record count is not there when selecting, also would the code go in the subfrom on load or on the form load where the subform is based?
any help on this would be appreciated.
thanks.
View 7 Replies
View Related
Sep 14, 2005
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 4 Replies
View Related
Jul 26, 2006
Hi everyone
My problem goes as followed:
As you can see in the picture there are 175 records currently showed. What I want to achieve is that this number is displayed in a Text Box.
Thanks in advance.
View 4 Replies
View Related
