I'm having some trouble developing a query based on a specific date criteria. I have a date field, called "SurgeryDate" with 4 fields next to it, "3Month", "6Month", "9Month", and "12Month", which are auto calculated with a control source of =DateAdd("y",+90,[SurgeryDate]), based on the date entered in "SurgeryDate". What I would like to do is create a query that, when run, will check each of these 4 fields against TODAY's date, and find any that match today's date, or are 2 days after today. My goal is to build a form off this query that will display the records that match this criteria so that they can be updated. I'm not really sure how to approach this issue. I think the problem I may be running into may be related to the fact that these fields are calculated from a different date. Any suggestions? Thanks.
I have set up a report which prompts the user for a start and end date. However, if they want to run it for a full month they enter eg. 01/07/07 and 1/07/07 - this will not include entries ending on 31/07/07.
Is there a way I can change the criteria prompt so that it read both dates entered as inclusive in the query. I am currently using Between [enter earliest date:] and [enter latest date:]
Hello buddies :D, do you have any idea how to make this work?
To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.
This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only. ([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null) How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif
I have this exercise I am doing for access on a sample database. I have already made a table form etc...there is a field with date in the form.. "1/6/2004" for example. The exercise wants me to set, for example, criteria between two dates. How do I do that? I used >#1/6/2004# AND <#30/6/2004#
for finding etc betweeen 1/6/2004 and 30/6/2004 but it doesnt work...
What is the correct syntax of this kind of expression?
hi guys, I am newbie :) just started learning how to use access a month ago. It may be a simple question to some of you guys, but I am having trouble figuring how to implement this.
So I created a query with a field "AuditDate" I want to audit record every 90 days after "AuditDate". I want like a warning to audit my record after 90days. Maybe Click on something on the form to show a list of record. Do anyone have a simple formula i can use to implement the "auditdate + 90 days"?
I also have a query on other database with a field "TermDate". I want to set a criteria to list all my records 90 days or older from TermDate. Please let me know if theres some simple formula
Hi all, I have a field in my database which shows the last date of review for clients. I have a query from this which shows any reviews which are overdue - they have to be completed every 6 months, so I use
<Date()-180 Or Is Null
The thing is, now we need a query to show clients whose review WILL be overdue in the following month from a date that we enter. e.g, if we enter 1/06/07, we want to see everything that will become overdue in the month of June.
Can anybody help me with the criteria I need to enter in a query to get this result? Thanks for any help :) elsiegee
Can anyone see a problem with this criteria in my query? SELECT Orders.[Date requested], Orders.[Item ordered], Orders.[Quantity ordered] FROM Orders WHERE (((Orders.[Date requested])>Date()-10)) ORDER BY Orders.[Date requested] DESC; I get and "Undefined function 'Date' in expression." message. I don't get it! :)
I need to create a query that only shows items that have a completion date of less than 7 days from the last completion date. i have inspections on vehicle that are due every 7 days but i have noway to tell without going through every one and would like to find out if there is a way to set up a query that takes the difference for the last two entries on on each license plate and if the diffrence is greater than seven then it will show me its last two dates of completion and nothing more.
Hey. Instead of going back every month and changing the date to the first. Is there a function I can put in the Criteria that will automatically filter for stuff only since the begining of the month. Right now I am using this:>=#5/1/2005#. I just want to to automatically change when a new month occurs.
I am wanting to use a query to find dates within a range, like a quarter. Within the criteria I put "[Enter Date:]". I ran the query and tried the "Between..And.." operator and even "<=9/1/05 and >=12/31/05". All I get is an error message saying incorrect syntax or structure. Any questions on how I could set this up so I could perform this search, that would be great.
Does anyone know how to write the criteria which includes both the date and time. for example, I want it to be the system's date and my specified date. e.g. (date() 14:00:00)
My query selects all those who donated to a fund.The max on the date restricts all but their last donation, working fine.But now I want to list all those who have not donated anything in the last x days (user defined). I want to use Date()- dateField>30 but now it does not let me peform the query. Any way I can do this in just one query? Thanks!
SELECT main.dgTitle, main.dgInit, main.dgSurname, main.dgNo, main.dgStreet, main.dgShul, Max(main.dgDate) AS maxdate FROM main WHERE (((Date()-[main].[dgdate])>30)) GROUP BY main.dgTitle, main.dgInit, main.dgSurname, main.dgNo, main.dgStreet, main.dgShul;
Wonder if anyone can help with the syntax in this query? It always returns no results and I can't for the life of me understand why.
SELECT tblExpectedShipments.ShipmentDate FROM tblExpectedShipments WHERE (((tblExpectedShipments.ShipmentDate)=IIf([Forms]![frmSelectShipperReport]![chkOverdue]=0,(tblExpectedShipments.ShipmentDate)>Date(),(tblExpectedShipments.ShipmentDate)<Date())));
Any pointers would be greatly appreciated. Thanks.
I have a form with two controls: DateFrom & DateToI want to filter based on those criteria but when the criteria is ommitted (NULL) I want to display all.In case of other controls I have an SQL as follows:... WHERE (tblClassesOffered.Course = Forms!frmClassReport.class Or Forms!frmClassReport.class Is Null)But with the dates controls the best I could come up with is:...Between IIf(IsNull([Forms]![frmClassReport].[DateFrom]),#1/1/1900#,[Forms]![frmClassReport].[DateFrom]) And IIf(IsNull([Forms]![frmClassReport].[DateTo]),#1/1/3000#,[Forms]![frmClassReport].[DateTo]) Or Is NullHowever that solution is kind of ugly becasue instead of wild cards (I couldn't make them work) I'm using those made up dates and I also wish not to use the IIF if possible (like the first statement).
I have a query which i would want to show records between January 2004 and September 2004, assuming the [Current Month] is October 2005.
The idea is to have a listing of sold policies between the first month of previous year and the month (of previous year) prior to the current month/year.
Here's the criteria i used: Between DateSerial(Year([Enter: Current Month])-1,2,0) And DateSerial(Year([Enter: Current Month])-1,Month([Enter: Current Month]),0)
The criteria only shows records between July 2004 and September 2004.
I'm trying to build a query for a customer that will automatically run without making them enter a date and time. Between [Enter Start Date] And [Enter End Date] Is there a way to make a query auto run a date and time with out entering a date and time?
I have a query with a Total Row and I want it to do its thing only on a specified date range. I have no problem getting date ranges to work in the Criteria section for Select Queries but am now trying it on this calculated query and it is not working. Is it possible to filter using Criteria when you are also using the Total Row? See the attached picture for the simple date criteria I am using that is having no effect at all on the results.
I have a table that has a field named interaction date. I am trying to write a query that shows all records that have an interaction date of up to and including todays date. Is there a criteria that i can enter that would allow for the query to pull todays date, so that with every day, the criteria would actually update itself?? Or do i have to hard code in todays date every morning when i run the query??
I am working on an older database that was built on the 2002/2003 version of Access. When we converted to 2007/2010 Access we lost a small function of the database and cannot find a way to override it. We have a report that prints all names with a summary of hours by the quarter. The database retains all data. After conversion the report will not print all names, only those that were in the database prior to conversion and those with hours in the current quarter. Is there a way to write a multiple set of criteria that will allow the names and the dates to function separately?
Hi, I have a membership database I'm working on and the problem I'm tearing what's left of my hair out over is this:
If the date of joining the club is <= 15th of that month then the renewal date is 1st of that same month in the following year.
However, if the date of joining the club is >15th of that month then the renewal date is 1st of the following month in the following year.
I hope that makes sense/ Example:
JoinDate = 03/08/06 then RenewalDate = 01/08/07
BUT IF
JoinDate = 16/08/06 then RenewalDate = 01/09/07
Any ideas on how I would go about doing this would be greatly appreciated.
I realise that I could simply use the DatAdd() function to add an entire year to the JoinDate, but as you see the criteria I have to work to is unfortunately not that simple.:(
Im looking for something that filters information for 6 months prior to any current, non-specific date. Like it will automatically get the data dated from 6 months ago from june, july, or august, without anyone having to go in an change the date criteria.
Below is a non working example of what I am trying to come up with. If I could get something like this, it would save me a ton of time going back and updating all the date criteria for my databases.
I am using a Form to input the criteria required for a query to run. All fields seem to work fine, except for the date field...(i hate dates!!)...i am using a Where clause and in the criteria field i have the following:
[Forms]![Bookings_Diary].[DateFrom]
However, when I run the form and input all the criteria required including the DateFrom textbox and click on the Load Query button, the system will still ask me to input the date field...
To make things clear, the DateFrom text box is an unbound text box having the format property set to 'Short Date'. In the query, the date field relates to a table Date/Time field..
What I want to do is from several records find the Max() date and update that one record with my stuff. However, I get an error message cannot use aggregate in update query or something to do that effect. Is there a way to get around this?
