Hello All
I have a number of Select queries that select 1st quarter up to 4th quarter of the year for accounting purposes. These are quite simple queries such as:
between "10" and "24" entered in the Criteria window. 10 and 24 being week numbers.
However if I wish to randomly select the week numbers after the query is loaded what is the correct code?
Regards
Terence
London
Hello All
I have a number of Select queries that select 1st quarter up to 4th quarter of the year for accounting purposes. These are quite simple queries such as:
between "10" and "24" entered in the Criteria window. 10 and 24 being week numbers.
However if I wish to randomly select the week numbers after the query is loaded what is the correct code?
Regards
Terence
London
Hi,
I am trying, and getting nowhere...so would appreciate your help... to create a query where records with dates from 8 weeks, or two months ahead appear in the query.
That is I have contracts ending in two months in my table and I want to run a query on who I should call now to renew contracts.
I have a contract end date in table.
I have tried
>=DateAdd("m",2,Now())<=DateAdd("m",2,Now())
only to get all sorts?
>=DateAdd("d",60,Now())<=DateAdd("d",68,Now())
only to get all sorts of things appearing?
Anyone that could help would be great.
I have contact info in a TBLCONTACTS and linked to TBLMOBILES via COMPANY_ID
Thanks heaps!
C
Hi
Would be great if I could get some help on this:
I have big table that gets updated almost every day with new data. There is a date column. I have a Form where I can enter queries. I need to add a panel where I can spcify if the query should involve the data should involve the date from last week only, from the last 2 weeks, from the last month or if the query should involve the overall data.:confused:
It somhow need to be check what the date is today and then caclulate back.
Any help on this would be much appreciated.:D
Thanks
Daniel
I have below statement that select row from my table at random.
Code:
sql = "SELECT TOP 1 * FROM Question1to5table ORDER BY Rnd(-10000000*TimeValue(Now())*[id])"
How do make the row selection unique.
:::: maybe mark a selected row on the dataset, if possible how do i do that ::::
Hi All,
Any ideas on how I do the following.
I have a table with 1500 records in it.
I want to select at random 4 of these records and append them into another table along with some other data that the query will not pick up, in this case Pilot and Month.
I guess I am having to go the VBA route but dont have a clue on how to do it.
Cheers in anticipation.
Andy.
INSERT INTO Assignments ( flightcode, aircraft, depart, destin, pilotcode, [Month] )
SELECT Schedule.Flightcode, Schedule.Aircraft, Schedule.Departure, Schedule.Destination, [pilot] AS Expr1, [MONTH] AS Expr2
FROM Schedule
WHERE (((Schedule.Departure) Like "man*"));
I'm having trouble with a simple query. They have an access database with a table like this (simplified for this example):
--------------------------------
Name Result Date
--------------------------------
Bob 5 1/1/2014
Bob 6 2/2/2014*
Bob 7 3/3/2015
Bob 8 5/4/2015*
Len 5 1/1/2014
Len 6 2/2/2014*
Len 7 3/3/2015
Len 5 7/4/2015*
---------------------------------
how I would write a query that gives me the latest result that each person earned in each year (I put a * symbol against the rows above that I am talking about)?
Bob 6 2/2/2014
Bob 8 5/4/2015
Len 6 2/2/2014
Len 5 7/4/2015
(The database is normalised and this table has an ID field).
I have a huge table with transaction dates. I need to slice and dice
this data (sum, %'s, etc), but group by FY. Our fiscal year is from
7/1 thru 6/1.
For example:
1/8/2004 = FY 2004,
8/12/2004 = FY 2005,
2/3/2006 = FY 2006
THEN . . . . I need to also isolate certain periods, for example July-
March for YTD (year-to-date) analysis and compare YTD of 2006 with that
of 2005.
What do you suggest? Many thanks.
Mehran
I'm trying to add a couple of fields to the Contact database in Access 2010.
In the Contacts table, I created a field called "Sobriety Date" that has dates formatted like 12/27/1995
I am trying to add a calculated field called "Years Sober" which should be the current year minus the year in the 'Sobriety Date' field (1995 in the example above).
I have been trying to tweak this:
SUM(DatePart("yyyy",[Date]) - DatePart("yyyy",[Sobriety Date]))
but it's not working. Keeps giving me "The expression that you entered is not valid for web-compatible calculated columns"
I trying to figure out how to make this query work. I have a simple database that is being used to show employee employment information - name, hire date, salary, bonuses, etc. Everything is just about done but they want me to show what percentage of the prior year the employee was there. In other words if an employee was hired 4/20/2004 they want me to show the percentage of 2004 they were employed with the company. I've tried just about everything I can think of but nothing seems to give me the right answer. I am also showing the percentage for the current year (2005) and that works ok. Just can't figure out how the calculate it for a prior year.
This is being done in a query and we're using Access 2000.
Any help would be greatly appreciated.
Thanks,
I have fields [DayOfYear] and [Year] can I somehow produce the dd/mm/yyyy from this. I know how to do it in Excel but the Asscess function Date() is a little different.
I.e. if [DayOfYear] =152, [Year] = 2014 then [Date] = 2/6/2014
I'm going to try using the year, day of year, hour & minute (24 hour clock) as a report number. It's set up in a field on a table. Right now I have....
Default Value =Format(Now(),"yyyhhnn") 'which works but not exactly how I would like
yy = Last two digits of the year
y = Number of the day of the year (1 to 366) 'can this show three digits all the time?
hh = Hour in two digits (00 to 23)
nn = Minute in two digits (00 to 59)
For instance, right now for Jan. 10th, 2012, 1306 hours the result would be 12101304 which, for all intents and purposes works, but I would prefer the "day of the year" to always be represented by three digits and not just when it hits day 100 of the year.
I would prefer to see 120101304
Hello, I am new to Access and I am having a problem with something. I have a query which returns, among other things, dates. I need to organize (group) the dates into weeks. Does Access have a function which could do this? Or some other way of grouping the dates? Or How would I got about writing a function in Access?
This is done in a Pivot Table right now, and I just use a calculated field which is the date / 7. This gives some god awful numbers (like 5944), and not anything like what I want. I would want the function to return something like,
"Feb. 5, 2005 - Feb. 12, 2005". Any suggestions?
So I have a project where I have to get employees to use infopath to fill out details in a form and then submit it, the form gets saved in an access database. I have all that setup and it works perfectly. I uploaded the forms on a sharepoint site and when I open them up from my computer they submit properly to the databse. However when I open up the forms from another computer they don't submit to the database. I know why it does that (because the databases are not on every computer). I can't however figure out a way to solve this problem so ANY help would be amazing. I was thinking to have the access databse on sharepoint as well so the users can download it but won't that just make it that every single computer will have a different saved database? This problem is driving me crazy.
Thanks in advance for all the help.
I'm using the following in a query qwhich allows me to enter the week number as the criteria:
DatePart('ww',[Visitdate],2,2)
This works fine , no problems. What I would like to know, is it possible to enter muliptle week numbers in the criteria, say 14 16 21 to give me output for those weeks, I have tried different separators to no effect ie. : and ; It may be that it simply is not possible but it would be extremely useful if ti was.
Hi everyone!
I have two columns. In one of them I have daily dates and in the other I have some numbers. What I'm trying to do is to sum the numbers weekly by entering this in the field of a query:
Week: Format([Date];"yyyy ww")
The problem is that when I go to datasheet view, it is sorted wrong.
It looks like this:
2007 1
2007 10
2007 11
2007 12
.
.
.
2007 19
2007 2
2007 21
But I want it like this;
2007 1
2007 2
2007 3
2007 4
.
.
.
2007 9
2007 10
2007 11
and so on...
The other column has the rihght values for each week, but I need it in the right order...
What am I doing wrong? Or how can I fix it?
Thanks in advance
Jake
Hello all!, I am new to the form
I have one problem I can't seem to get to the bottom of. I have a database that counts the attendance of my basketball team. Everything works great but I have one problem. Our basketball program is 12 weeks for each student. Now students can join any time but all of them are required to stay 12 weeks to complete the training. How do I get Access to tell me how many weeks a student has left in the program? As i was saying it is easy for me to add together the days of the week that the student attended, but I can't seem to figure out how to get the amount of weeks a student been attending. One more thing, I know this is asking alot but is there a way to have an extra column that allow me to add additional weeks to certain individuals (for the students that like to skip a week or to)?
Any help would be appreciated
I have a query based on payment date which I have extracted the Year part as a seperate Field StartYear, but I want to now add EndYear which just adds 1 year to the StartDate. e.g. EndYear = StartYear +1. Anyone kow please I know i's proably simple but I keep getting syntax errors.
View 3 Replies View RelatedI have a database that I am using to report metrics based on seasonally available data. What I am running into is the problem that the seasonally entered data doesn't start on the same date every year.
An example:
I have data that was entered into my database last year starting July 26, 2004. This is considered part of week 1 of my season and I want to compare that data to records that started being entered on August 15, 2005. Which is week 1 of the 2005 season.
Records include the following information: id, date, other irrelevant details
Does anyone have any ideas on how I might be able to accomplish this? Thanks in advance for your help!
Hi there,
Was there a recent update or something that may have affected a Union Query in MS Access 2003 (operating system XP Pro, all current updates installed)?
In our database we have a union query that display available sessions and time period of availability, this is done by identifying the session with **available** at the start of each line for some reason it works on some computers and not others.
Does anyone know anything that may effect this code in operating 100% of the time?
thanks in advance.. Colin
The SQL code used is a follows:-
SELECT '** available **','', '',format(A.session_end, "dd/mm/yyyy") as START_DATE,format(A.session_end,"hh:nn") as START_TIME, format(B.session_start, "HH:MM") AS END_TIME, int(DateDiff("n",a.session_end,b.session_start) / 60) AS Expr1, (DateDiff("n",a.session_end,b.session_start) - expr1 * 60) AS Expr2,a.session_start
FROM session_last AS A, session_last AS B
WHERE (((B.session_start) Between (([Forms]![Bookings]![StartDateInput])) And (([Forms]![Bookings]![EndDateInput])) And (B.session_start)=(SELECT MIN(C.session_start)
FROM session_last C
WHERE C.simulator_code = A.simulator_code
AND C.session_start BETWEEN (([Forms]![Bookings]![StartDateInput])) AND (([Forms]![Bookings]![EndDateInput]))
AND C.session_start > A.session_start
)) AND (((A.simulator_code)=[Forms]![Bookings]![simulatorCodeCombo])) AND ((A.session_start) Between (([Forms]![Bookings]![StartDateInput])) And (([Forms]![Bookings]![EndDateInput])))
AND ((B.simulator_code)=[A].[simulator_code]) AND (([B].[session_start]-[A].[session_end])<>0))
UNION SELECT '',C.customer_code, C.session_type, format(C.session_start, "dd/mm/yyyy"), format(C.session_start,"HH:MM"), format(C.session_end,"HH:MM"), int(DateDiff("n", C.session_start, C.session_end) / 60) AS Expr3, (DateDiff("n", C.session_start, C.session_end) - expr3 * 60) AS Expr4,c.session_start
FROM session_last AS C
WHERE (C.session_start) Between (([Forms]![Bookings]![StartDateInput])) And (([Forms]![Bookings]![EndDateInput]))
AND (((C.simulator_code)=[Forms]![Bookings]![simulatorCodeCombo]))
ORDER BY 9, 4, 5, 1;
END of SQL code..
My main query has a list of events with the corresponding dates that they occured.
I want to create a second query that shows Monday’s date of the current week and the Monday date of the preceding 51 weeks.
How do I get my second query to show the dates for 52 consecutive Mondays starting with the current week (Monday date) and going back 51 weeks.
Then I want to create a third query by combining the first two such that the with the dates and events main query are displayed and grouped by the Monday dates for the year query.
2nd QryMain Query
MondaysEvent #Date
8/6/2007789328/6/2007
8/13/2007
8/20/2007
8/27/2007
9/3/2007
9/10/2007
9/17/2007
9/24/2007
10/1/2007
10/8/20078089910/8/2007
10/15/2007
10/22/2007
10/29/20078179111/1/2007
11/5/20078199111/8/2007
11/12/2007
11/19/2007
11/26/2007
12/3/20078269712/5/2007
12/3/20078274812/6/2007
12/3/20078278612/7/2007
12/10/2007
12/17/20078311112/18/2007
12/24/2007
12/31/2007835531/4/2008
12/31/2007835611/4/2008
Hi,
I have created a basic query, in MS 1997
Would someone be kind enough to give me the code for the following,
I need to display only the last 13 weeks worth of dates in this query.
The table is called "Tbl_History_RotAwaitShip"
The field is called "Time/Date" and is formatted as "13/05/2005"
Thankyou
I am trying to create a report which will show the last twelve weeks of orders by client. In the table I have an Order Date and of course the bill to clients. I tried to create a Crosstab query for the wizard and can get month by month but need the last twelve weeks, week by week. Is there a way to do this?
View 1 Replies View RelatedI am trying to convert a week which is entered in a text box on a form to the first day of the month in which it falls.
My text box (txtdw) is in the format yyww and I want it to be converted to 01/mm/yyyy.
example: 1401 would convert to 01/01/2014. 1406 would convert to 01/02/2014.
I am pretty close, I can get to the correct month but I can't work out how to change to the first day of the month.
Code:
DateAdd("D", Right(Me.txtDW, 2) * 7, DateSerial("20" & Left(Me.txtDW, 2), 1, 1))
I think i am missing the obvious but cannot seem to get this one....
I am trying to get my form to return a value of the amount of working weeks in a month i.e. a 4 week month (like june - 4 mondays) or a 5 week month (like may - 5 mondays).
i can calculate the number of full weeks using the control source..
=Int(DateDiff("d", Date1, Date2) / 7)
but this does not work for the 5 week months?
Any help would be greatly appreciated.
I've got a database used daily by 4 users. It's split into a frontend (10MB) with all the forms/queries/reports/vba and a backend that's just tables (170MB), and the users access the database from a network drive. All additions through the forms are logged to a text file, and at the end of the day, a report is run that prints the day's work to a PDF. The database / workflow has been stable for the last few years, with only minor edits to the code, and no programmatic changes in the last 3 months.
Today, after running a compact-and-repair, I realized that the database contained no entries added within the last 2 weeks. I checked my log files, and sure enough I see that all of my records were at some point added to the database. This is supported by the fact that I have PDFs for every day in the last 2 weeks that show exactly what was done (roughly 30 new records/day).
My first guess was that compact and repair had corrupted the database, and knocked out a chunk of records. Fortunately, I've got daily backups, so I started restoring to yesterday's database. At this point I found that the records were missing from there, and from every backup from the last 2 weeks. Now, it's possible that my backup solution (logMeIn backup) is hosed, but the the log files are getting properly restored by the backup, which leads me to believe the backup is working. So, somehow these records were never saved in the database, yet they magically appeared in my end-of-day reports?
I thought maybe I was getting stuck in some state where the database went read-only and the edits were getting stored in memory but never written to disk, but that doesn't make sense as we occasionally restart the database during the day for other reasons, and the end-of-day reports are always complete, which knocks that out. Having restored to a prior version of the DB, I attempted to make changes / add new records and they appear to be sticking, but I find my faith in Access rather shaken, all the more so because I haven't a clue what went wrong before.