I basically have General Date field (e.g. 10/1/2014 6:34:11 PM) and I want to limit the results to only a specific month and only to show reuslts after 6PM. I tried everything and still stuck.
View RepliesI am trying to create a mailing list of patients. Let's say I am creating a mailing list for February. I need the mailing list to consist of people who have had surgery in February from the beginning of the database, and people who have had surgery three months ago, so anyone who had surgery in November. I have created a form that has a button which is connected to a query, the form has a unbound textbox where I can enter the month in (2 for February). Then the query uses the datepart function to search for this month in their date of surgery. But this only gives me people for surgeries with february, how would I get people who have had surgery three months ago in the same query.
View 2 Replies View RelatedI don't know what it would be called or even how to start doing it in access other than it requires a criteria here is what I'm trying to make happen with access
name date yes/no criteria would be set to date > 180 days then it would equal value of No < 180 days then equal value Yes
If i set a today's date value on the database and then criteria is based of the value in the date box with the above information how would this be done.
to show only those dates between a certain time interval as in:
between jan 1 2007 to jan 1st 2008?
Is there a certain type of format I need to type into the criteria field of the query?
Thanks in advance!
I have a table that has several fields including CallID (autonumber) and SKU (text)
SKU can be anything up to 9 characters, sometimes numeric sometimes alphanumeric. For example: 24300, AA23145, G58d444, 24999, 89332,...
Based on the Count of CallID I can easily get the top20 calls on each SKU. This is the query I use for that:
Code:
SELECT TOP 20 Count(Calls.CallID) AS CountOfCallID, Calls.SKU
FROM Calls
GROUP BY Calls.SKU
HAVING ((Not (Calls.SKU) Is Null))
ORDER BY Count(Calls.CallID) DESC;
The problem is that now I have been asked to create two different lists. One that has the top 20 SKU that range from 24520 and 24599 and another one that does the res tof the SKUs.
Obviously my problem is that the SKU field is text, not numbers so I can't just limit the results in the query by using "Between 24520 and 24500" in the query criteria.
How do you calculate or find Month To Date in a query as it relates to a hand keyed criteria.
For example I have a field called Operating Day and right now my criteria in my query is Between DateSerial(Year(Date()),Month(Date()),1) And Date(). Works perfectly. I'm using this data in a sub report.
But now my requirements have changed and its possible that I may need to report on something from January (or December and so forth). Well this criteria will show data from February. But the Month To Date data should show totals for the Month I'm reporting on.
Is this even possible?
I am trying to count the amount of records that were created and closed for last month but I am having problems inserting the correct criteria along with the DCOUNT syntax. DCount("*","obsvnofilterqry","(Date_Closed)=MONTH( Date())").Works fine but figuring out how to get the amount of Date_Closed for last month is proving tricky.
View 11 Replies View RelatedBelow is a simplified example of what I'm trying to achieve with a parameter query.
The source table for the query contains two fields:
Reading_Date (short date) and Use_value (integer)
The parameter query sums Use_value between two dates for various date ranges specified as 'or' criteria. SQL as follows:
SELECT Sum(Table1.Use_Value) AS SumOfUse_Value
FROM Table1
WHERE (((Table1.Reading_Date) Between #1/1/2013# And #1/5/2013#)) OR (((Table1.Reading_Date) Between #1/1/2014# And #1/5/2014#));
This produces a single sum total, but I'd like the query to give a total per criteria date range. In other words to group results by criteria. As date ranges may span year change, grouping by year is not possible.
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.
I have a drawing register database which notes the revisions for all drawings issued. One drawing can be revised several times so I have a details table which notes the revision letter, date issued and the notes for each revision. Using this table I created a max date query to show the latest issue date and revision for each drawing. This query is used for a transmittal form for issuing drawings. Both the form and report is working perfectly.
The TBLTransmittal consists of the following fields
ContractName
IssueDate
SubSup
DwgNo (multi-value checkbox)
Using the TBLDwgRegisterDtls or the query QRYDtldDwgReg (which includes the drawing titles) I would like to create a query that would look up the latest revision at the date of issue and show the revision letter.
I started a query combining TBLTransmittal and TBLDwgRegisterDtls and in the criteria for DateIssued of the TBLSDwgRegisterDtls (which is when the drawing was issued by the architect to the contractor) I put "Not > [IssueDate]". This is filtering the information to show revisions issued up to the date we (the contractor) issued the drawing to our sub-contractor but I need now to pull the max revision only from this information.
Before I go I will give you an example of what I want from the end result
Drawing BK2-02 rev. D was issued by the architect to the contractor on July 17th. This drawing was in-turn issued to our sub-contractor (Sub "A") this Saturday, August 9th. The same drawing was revised this morning and rev. E was issued to Sub "A" today, August 11th.
I want the end report to show that Sub A received Rev. D on 09-08-14 and Rev. E on 11-08-14.
I'm wondering if another max date query on this new query would work? I'll try and see what happens.
I am trying to limit what a user can see when accessing a database in access. I am only testing with this in access before taking it to SQL server. I have 3 databases and I combined the data into one set of tables and now what i am trying to do is a certain user can only view data from a particular database.
I have a table with the users id and password, as well as an ID number from each database that I am trying to tie to the users.
for example one user can view and add/edit data in databases 1 and 3 but not 2. while there might be a user that can view and add/edit on 1 and 2 but not 3.
I'm trying to derive the month in digits from a date/time stamp field. Any month between 10 and 12, I need the first 2 digits but anything <=9 is only 1 digit.
MONTH: IIf(Left([LOAD TO SKU LEVEL DETAIL]![PICK_PLAN_TS],2)="10" Or "11" Or "12",
(Left([LOAD TO SKU LEVEL DETAIL]![PICK_PLAN_TS],2),(Left([LOAD TO SKU LEVEL DETAIL]![PICK_PLAN_TS],1))))
IIF/left statement not returning false argument
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.
Is there any way this can be done?
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.
Is there any way this can be done?
Is it possible to have a running total either in a query or using the Running Sum function on a text box on a report that will reset after a specific value. Here is what I would like to have happen:
The RunningTotalCube field to reset when it has reach 2.3 or whatever number comes closest to that number.
Date Time Item Cube RunningTotalCube
4-2-15 12:05 15615 0.5 0.5
4-2-15 12:06 15918 0.8 1.3
4-2-15 12:10 98563 0.5 1.8
4-2-15 12:12 45268 0.4 2.2
4-2-15 12:15 25854 0.9 0.9 {reset}
4-2-15 12:17 75136 0.5 1.4
Is this possible either in the query or the report/in Access or in VBA?
Any way I can set a table up with an intrinsic limit, specifically that only ONE record at a time (this will vary) can possibly have value X selected from a lookup field containing assigned values X, Y and Z. Is this possible? If so, how do I do this?
View 2 Replies View RelatedI need a subgrouping for a report I am working on.
I have a DateIn field that contains date as mm/dd/yy. I have managed to get a count of all of a certain type of document that came in during a certain period of time that usally spans a quarterly period. So typically there are three months in the query. I would like a subgroup that breaks the numbers up in the 3 month period by each month.
So if a total of 150 docs moved in 3 months it shows 50 moved the first month, 50 the second, etc. I believe this is grouping by month. I have not been able to determine the criteria for getting the query to group by a single month as well as the three months period being grouped by document type. Here is what I have so far.
SELECT ctblDocuments.DocTypeID, tlkpDocTypeList.DocType, Count(ctblDocuments.DateIn) AS CountOfDateIn
FROM ctblDocuments INNER JOIN tlkpDocTypeList ON ctblDocuments.DocTypeID = tlkpDocTypeList.DocTypeID
WHERE (((ctblDocuments.DateIn) Between #7/1/2005# And #9/30/2005#))
GROUP BY ctblDocuments.DocTypeID, tlkpDocTypeList.DocType
ORDER BY tlkpDocTypeList.DocType;
i''m currently trying to count the amount of records that fall in each month in the date complete field. i am currently using a count query and it it bringing up the dates not the month.
View 1 Replies View Relatedhi, i have certain jobs and i want to select them based on the job date.
in my current query, i use the job date parameter
[Enter a Date:]
when the user types in the date, then the jobs for that date and their corresponding information is displayed.
however, i have created a form for this query displaying the results as a datasheet.
instead of the user having to enter the date for the query manually, is there any way to modify the query to only show results for the current day.
eg. today is the 24/02/06.. so i would like the results from the query to automatically show the jobs for that date. this way, only todays jobs will be shown in my form.
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.
I tried to word the title as accurately as possible.
What I'm trying to build is a Form where they can select data in different fields to limit (filter) the list. In my query criteria I want to read the form, if there's a value use THAT in the criteria (for that field) otherwise ignore it.
I've tried isnulls etc. If I put a value (like City) it properly returns all the records with that city, however if I put nothing the "ISNULL" returns a value that the criteria doesn't match i.e. I get nothing.
There must be a way to tell a query to NOT use any criteria if an object (form!field) is blank...?
I have a big list of data, with a row of data for every day for each client.
I need a selection criteria that will provide show the last day of the every month (historic data) for each client.
I've had a go but I'm not making much progress.
Is it possible to limit the type of data that can be entered in a query that has a LIKE criteria?
( Like [Enter Data] & "*")
limit to two digits or any number of digits, or limit to numbers only or letters only. .
I am trying to create a query with a parameter on it, but on a date box, but only searching for a specific month, without regards to the year. So what I want is for me to run the query, a pop up box asks "which month?", I then put in Jan for example, and the results will show all records with the month Jan regardless of the day or year.
Is this possible? If so - whats the code I would use?
Access 2007 query that has a specific date range between #6/1/12014# and #7/1/2014# returns the date I need. How do I now get it to pull just occurrences on Fri Sat and Sun of the month of June?
View 5 Replies View RelatedHow do you write a MS Access query criteria that looks for records like the current year only for a date field ? I tired Like *Year(Now()), it did not work.
View 8 Replies View Related