My dates are stored as numbers (long integers), because they need to be in the form YYYYMMDD, and I couldn't figure out how to enter them and keep them in that format as Date/Time.
Anyhow, I have a form with a textbox and a button. When the user enters in a date range, I need totals to show up for only dates that are in-between and/or equal to the date(s) typed in the textbox.
For example when the user types in 20050904-20051004, and pushes the button, a display of totals will be displayed for only those dates and those in-between. If the user leaves the textbox blank, and pushes the button, I want totals from all dates to be shown.
As you may know from my previous question, I am a relative newbie to Access. Suggestions about how to do this maybe even how to derive and present the totals, and especially specific code will be very helpful and VERY much appreciated.
I have a client that wants to enter a range of dates in a query of when they will call that person back. Then they want to be able to type in a range of dates and have a make table query show them all the people that fall in between these two dates....is this even possible??? Ex. Joe March 3 to March 8 Mary March 4 to March 9 John March 5 to March 10
So if they type into the query March 3 to March 6 all three people should show up because one of the dates specified lies within the parameters they are asking for.....man I am out of ideas
Below is the code I have for a query that shows me the number of WorkUnits via a date range that is put in via calendars. This works great. It gives me one total for the date range. What I would like to be able to do is see the WorkUnit totals by month via a date range. If I put in the date range: Jan 1-April 30 I would like to get four WorkUnit totals instead of one. I would like to see the WorkUnit totals for each month. Can anyone assist with this? I asked a similar question like this yesterday but he SQL for this code is different and I have not been able to figure out how to apply yesterday's answer to this one. I have tried everything I can think of to no avail.
SELECT 'Total Work Units' AS FaultCategory, Count([WorkUnit]) AS [WU Totals] FROM [Select Distinct [WorkUnit] FROM WorkUnitsFaultsMainTBL WHERE BuildID IN ("G004","E818","N005","F813","D024","C879") AND PossibleCause NOT IN ("Out of Stock") AND [TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] AND [Forms]![Queries_ReportsFRM]![EndDateTxt]]. AS vTbl;
I have 15 text boxes on a form. After the last text box is filled in, I can get the average okay, the sum okay, but would like to caculate the difference between the highest and the lowest numbers (Range). I've tried a ton of "If" statments, but did not seem to work. Any ideas would be helpful. I searched the forum for "Range" but could not locate an answer.
I am working on a small database for a school to track their live and silent auction. Currently I have a table set up as follows.
Silent Auction table : Item Number Short Item Description Long Item Description there are more fields that I am not interested in right now.
Mainly what I am after right now is to do some decent reporting for generating the catalog and posting the silent auction items on the web site for viewing before the night of the event.
I need to generate a report that contains the Item Number, short and long description. I need to group on Category which corresponds to the item numbers. Example:
Item Number 100 - 110 = Class Projects 110 -199 = Live Auction Items 200 - 299 = Sports and Recreation 300 - 349 = Entertainment and so on......
I realize that had they numbered them in the 100 range I could group in my report by 100's and everything would be pretty good however, I did not have the opportuinity to be involved in the numbering scheme.
Even if the numbers had been in the order of 100s I still would need a way to say that the 100 -110 range is Class Projects.
I think I need another table and link it but I am not sure how to set up the range of numbers for the category.
I need some help please. I am working on a performance management database. In this database we keep track of daily overtime and how that overtime is broken down into sub categories. For example, my staff worked 14 hours of overtime yesterday. 8 went for weather related overtime and 6 went to cover vacations. I have a report that shows all of the supervisor's overtime in a continuous list of running totals. But it's getting to big. I am trying to create a query for each supervisor that pulls the daily totals for the previous week and gives me that total sum. So far, my individual supervisor queries look like this. DATE, SUPERVISOR, DAILY TOTAL. They click on the report button and then they just count 7 days worth of numbers.
How do I create this?
TOTAL FOR SUPERVISOR X FOR THE WEEK OF 00/00/00 through 00/00/00 that pulls the daily totals out of my "overtime table"? and gives me a weekly total?
I need to create a very simple database that would just store records and produce a couple of reports. I have three tables: one with the roster , one has records of the inventory items people from the roster receive and another one contains types of inventory we have. Everything is very simple except for one part. Every time we make a record of an inventory item given to someone, it requires not only employee id and inventory type from the existing tables. It needs us to enter a serial number of an item. This serial number contains a letter and a number.
Looks something like this - M100. Many people receive a consecutive set of inventory items. For example, from M100 to M150. There is no way to have a separate table with all serial numbers because they constantly change. That is why we need to have two text boxes that would allow us to input a range of serial numbers or just one number. Then the program should separate numbers from letters, evaluate the range, create new records of numbers and then put new numbers and a letter back together into one field in the table where we have all inventory records.
I found the following code online that allows me to find numbers within a range, but it only works for numbers.
Dim varRange As Variant Dim lngLow As Long Dim lngHigh As Long Dim lngCounter As Long DoCmd.Hourglass True
[code]...
I then found a piece of code that is supposed to separate numbers from letters, but I can't find a way to make it work.
Public Function FindNum(strName As String) As String Dim strTemp As String Dim i As Integer For i = 1 To Len(strName) strTemp = Mid(strName, i, 1) If (Asc(strTemp) < 91 And Asc(strTemp) > 64) Or (Asc(strTemp) < 128 And Asc(strTemp) > 96) Then FindNum = Right$(strName, Len(strName) - i) End If Next i End Function
how to alter the code to make it work for my specific situation.
I have 3 fields on a form PU_POINTS, SU_POINTS & 2_MILE_RUN_POINTS. The user enters a number 1-100 in each of these fields. If the user enters a number in any of the 3 fields of anything less than 60 then I want it to check a fail box. If the user enters all numbers 60 or more then i want it to check a pass box.
As part of a larger issue, I am trying to figure out how to have an Access 2000 select query produce all dates in a date range into one field on the query. Assume at this point there are no tables in the query.
If 01/01/06 (mm/dd/yy) and 02/01/06 is used, then it'd list all the dates between those two, inclusive.
I am trying to create a Totals Query which returns a data set between two dates. So far I have managed to select the data I want (Please see attached screenshot). However, I only want to select records between a date range working on my field [DueDate]. If I add the due date field to the current query then it removes the grouping and all records are displayed.
I need to print out a report from a start to end date. But theres a catch i also need to print those items from previous months that are not yet resolved. I have a checkbox that tells me if the record has been resolved or not. Is there a way that I can put both the conditions into my query, one for the dates of transactions and the other being all outstanding items no matter what the date.
I have a form and table which I use to collect some reservation information. I then have a subform/table which I use to enter the actual dates of for that reservation. This subform/table has only three fields. The foreign key field that links it to the main form/table, the date(s) entered and the subform/table autonumber primary key.
What I would like to do is use an ActiveX type of calendar as the subform and be able to select a range of dates. If someone is making a reservation for 8 days, I want to be able to select that range of days on the calendar and have create/enter all 8 of those records in the subform/table.
I would much prefer this as opposed to simply collecting a single IN date and a single OUT date.
I want to create a form that ask the user for a start date and an end date. Then when the click OK it will append a record to a table for each date and each date in between based on the increment. For example the start date is 6/20/2014 and the end date is 6/27/2014 and the increment will be 1 day. I want to add records to my table for:
I am tring to add a number of years to a dob. I'm doing this by adding my date+years*365.26 I get a string of numbers. I then convert the number in the next column to actual date again. I'm getting the correct date. I've tried unsuccesfully to use the dateadd function.Now I want my criteria on that column to allow me to return only date in a given to from period of my choosing. I want to determine the date range each time I run the query.
An example would be:
DOB age date I need to follow up 5/15/1935 + 75 = 5/15/2005
Then I request dates between 4/1/05 and 5/30/05 and this date is returned.
I've tried the between_and functions but nothing is returned. I'm guessing it's because the column is still a calculation and not a true date.
I've tried: Adding a column to my query to convert the number string back to date format (criteria failed to return a result). Adding a second column that ='s my converted number to date column (criteria failed to return a result) And I've tried the make table query and then run another query from the new table. I can't seemto make this happen. I think it's not recognizing my data as a date because it isn't returning any data. Is ther an easier way to: Add a number of years to a date and have it return a date and not the number string? If not is there a better way to convert the string to a date? Is there a way to get my criteria to recognize the date and return date between my begin and end dates?
4/11/2007 1:01:28 AM 4/12/2007 2:02:52 AM 4/13/2007 4:21:30 AM 4/14/2007 2:22:21 AM 4/15/2007 3:34:53 AM 4/16/2007 4:02:37 AM 4/17/2007 5:03:15 AM 4/18/2007 7:17:34 AM
I need some helps I have a table with date + time, how can I query so that it retrieve a 7 days range from today and go back ti 7 days
i though this should work (it works if the the table has date only no time) Between Date() And DateAdd("d",-7,Date())
I have a query and I want to make a certain range of dates for the query to run. For example: from 10/6 to 10/7. What would be a parameter for this range of date? Should I put it on a query?
I am using Between Me.Firstdate and Me.Lastdate in a query. I thought that it would include both Firstdate and Lastdate in the range. But it appears that it does not. Is there a way to include Firstdate and Lastdate in the range of dates?
I have a table called StockTable with the following fields Location, Status, Serial, Make, Model, LastDate, DotNumber
I also have a table called FCDateRange with three fields
DateStart DateEnd and DotNumber
For example
3/7/13 - 3/13/13 - 1 3/14/13 - 3/20/13 - 2
Im trying to figure how to write a query that if the lastdate from the StockTable falls during the DateStart and DateEnd fields it will assign it the number in the DotNumber field
Unable to use Between query without pulling dates out of range.I
f I set the Criteria to Between [Enter the start date:] And [Enter the end date:] and input the dates 1/2/2014 & 1/15/2014 i'm pulling data for those dates but for year 2011, 2012, 2013 and 2014 as well.
if I enter in Between #1/2/2014# And #1/15/2014# it gives me just the dates I requested.I need to be able to have a user use the macro and just enter in the dates they need data for.
Also, I am using the criteria on a date ime field that I have set up an expression on - CreateDate: DateValue([TABLE]![date])the date in the table is stored with time and I just need to show the date.
I am setting up a database for a training centre, part of this includes registration The academic year I am working with runs over 36 weeks with 24 teaching weeks, 2 x 3 week holidays, 2 x 1 week reading weeks and the final 2 weeks for exams.
I aim to set the system up so that when a user opens the application, I get the username from windows, the day and time then open the correct register. That bit should be fine.
As I have 36 weeks I want to some how map the week numbers to the dates at the start of the term and also the holidays. There are 2 reasons for this, the first being so that when the user logs in the register only shows the current week instead of needing to search along all 24 or 36 entries, the other reason is so that when checking the whole register the holidays are not shown.
Does anybody have any suggestions or pointers as to how I can do this?
I am trying to write some VBA to convert a date into a week number.
My work colleagues do not not what system is used to define the week numbers. So I have gone through all their records for a few years and deciphered this pattern:
I need the code to define that there are 52 weeks in a year. The last sunday of Dec is the beginning of Week 1 for the following year. Except when it is a leap year where Week 53 exists. otherwise it only goes up to week 52.
Start day for the week is Sunday. End day is the Saturday.
Some examples to check against:
22nd December 2013 = Week 52 2013
29th December 2013 = Week 1 for 2014 ----
21st December 2014 = Week 52 for 2014
28th December 2014 = Week 1 for 2015 ----
20th December 2015 = Week 52 for 2015
27th December 2015 = Week 1 for 2016 ----
25th December 2016 = Week 53 for 2016
1st Jan 2017 = Week 1 for 2017 -----
Tried various methods already
iso 8601, wrong week start day
intWeek = DatePart("ww", datDate, vbSunday, vbFirstFourDays) Was right for 2013 but wrong for later dates.
My table has many records for each month. I am creating a report that will display only the records in one month of a year. I have been able to create a form that gives the user the choice of the Month and Year for which to create the report. The code I am using to create the combo box is:
Code: SELECT DISTINCT Month([QTDate]) AS MoNum, Format([QTDate],"mmmm yyyy") AS MoName FROM MainTBL ORDER BY Month([QTDate]);
What I need to do now is create the query for the report that displays all records for the chosen month and year. If I simply reference the combo box, all it shows is mmmm yyyy and the days are missing so the query doesn't work. What do I need to do to create the query so it displays all days within the month and date selected?
Hey all! This is my first post. Been searching through the net all day trying to find a solution to this problem. Basically i have a table that looks like this (regular text is what i have and bolded text is what I need:Name Date Qty MOBrad 12/12/2007 23323 4423John 12/11/2007 3445 4432 John 12/11/2007 344 4432 John 12/11/2007 45 4432 John 12/11/2007 44 4432 John 12/11/2007 3445 4432 Grand Total: (Qty)And then I'd like to be able to carry this over and display a grand total at the bottom of every page of a report that I would need to generate. Our company produces forms and we sometimes have 60 - 70 people working on a single job. We want to see their hours individually but we would also like to see a grand sum of all their hours. If someone could help with this or needs more info let me know. Thanks for all your help!
I'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:]) Site - (criteria = Like "*" & [Enter Site:] & "*") Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
Ok so in excel I have some numbers that are stored as text. The reason being that they are zip codes and some begin with 0 and excel doesn't want numbers to start with 0....so when I import these into an access field that has an input mask for zip codes...will it convert these correctly since the field is a text with input mask?
