So I found this video (youtube.com/watch?v=FTjyuSTcTII&spfreload=10) and followed it to do a query for all upcoming birthdays. The video shows how to do it for the next two months and I tried to modify it just for the next 7 days. When I tried to run it, I got "Date type mismatch in criteria expression" error. I tried putting it back in the criteria she had, but still got the same thing.
At 6:53 you can find the code she used for the first table (basically asking when their next birthday is) and at 9:57 is the code for the criteria. Below is my modification.
The title tells it all really. I need a way to take a list of records with the field D.o.B in short format e.g. 12/10/1990 and be able to find out who's birthday it will be in the next week. I cant find a way to take the date of birth and search it to find, say the week starting 11/10 and get the dates the next week (so up to 18/10) because of the date of birth not being the current date. sorry I'm not good at explaining things :o . I'm trying to say getting 12/10/2005 as the date of birth would be no problem as it would fall into the week choosen but 12/10/1990 is not the right year so doesnt appear but I want it to. so I need it to only search for the 12/10 part not the year so I can get the birthdays but I dont know how to achieve this. I'm not sure its possible but I'll probably be told its quite easy. Typically I want the query to ask the start date of the week that the user enters, and it will find all birthdays from that date to the date 7 days on. although asking for the start date and then what date do I want to stop searching for birthdays (i.e. 'enter first date: 12/10' and 'enter second date: 30/12' then find the birthdays inbetween the dates) would probably be more practical and flexible for future use. I'm not paticularly good with ms access and although looking and searching I cant see a way to achieve this but I cant believe its not possible so any help would be appreciated very much. thanks in advanced if you help me out at all
I need to add some code to our access 2002 database to test incoming files for bytes, zero byte files in particular. Our system will then pick out the zero byte files for emailing to our customers. Can someone provide code for testing for bytes or provide me some direction on this matter? Thank you!
I have a query that extracts the month and day of a bowlers birthday for a selected week and year (Done from a form). The query below works fine until the end of the year when the ScheduleDate crossover to a new year. At that point the query wants to include all of the bowlers birthdays even though I'm only trying to get the month/Day Between 12/31/07 and 01/06/08.
What am i doing wrong?
Hope I'm clear enough!
SELECT tblBowlers.FirstName, tblBowlers.LastName, tblBowlers.BirthDay, Format(DateDiff("yyyy",[BirthDay],[ScheduleDate]),"#") AS BowlerAge, tblSchedules.SeasonID, tblSeasons.Quit FROM tblSchedules, tblTeams INNER JOIN (tblBowlers INNER JOIN tblSeasons ON tblBowlers.BowlerID = tblSeasons.BowlerID) ON tblTeams.TeamID = tblSeasons.TeamID GROUP BY tblBowlers.FirstName, tblBowlers.LastName, tblBowlers.BirthDay, tblSchedules.SeasonID, tblSeasons.Quit, tblSchedules.ScheduleDate, tblSchedules.WeekNo HAVING (((tblBowlers.FirstName)<>"DUMMY") AND ((tblBowlers.LastName)<>"DUMMY") AND ((tblSchedules.SeasonID)=[Forms]![frmYearSelect].[SeasonYearID]) AND ((tblSeasons.Quit)=False) AND ((tblSchedules.WeekNo)=[Forms]![frmWeekSelectExcel].[txtNextWeek]) AND ((Format([BirthDay],"mm/dd")) Between Format([ScheduleDate]-2,"mm/dd") And Format([ScheduleDate]+4,"mm/dd")));
I need to create a guest database for a small hotel. And I would like to create a pop-up alert that appears when I open MS Access to remind me birthdays of the guests.
I've created a report based on a query that shows me the jobs that have been added to tblJobs between two selected dates.
The report works fine and displays all the information I need, however other than being sorted by day of the week i.e. Monday, Tuesday, Wednesday etc. The records just run on one after another.
I want to create a page break, so that a full week is shown on a single page before then forcing a new page when it moves in to the next week.
I have a table with products information (week, inventory, orders coming from supplier). I want to find out what week is the first order coming in. I have tried using dlookup to find week but, not working.
Curretnly I have a query setup with Area, EventDate, & Complete. I want to be able to count each occurance of complete in current week. So far I have the following criteria under EventDate.
DatePart("ww",[EventDate])=DatePart("ww",Date()) And Year([EventDate])=Year(Date()).
So, this does in fact get me the records of current week, the issue I'm having is when an area has occurences daily. It will not bulk that area into a weekly count. I just need to be able to see how many occurances happen in the week total not daily. So my results could be.
I am using access 2013 and have an issue creating a query with employee hours for a two week period.
For starters I have a table with the following:
Employee Number, Date, Time in, Time out, Description, Rate Code, Week end, Pay Period. (there are a couple others but they are not needed for the query)
My table is set up this way so that each employee can clock in and out 1-4 times a day based on what they are doing. For example I may work from 6-8 in the office then 8-10 in the field. The office has one rate code and the field has a different one so i cant just be clocked in from 6-10.
At the end of the pay period I create a report and send it to an accouting firm who does the payroll. Right now I have to manually enter in each employees time. I was hoping that by using this table I could create a query that will give me the calculations I need in order to create the report.
In my query (to Start) I need to have the following with it set up per pay period.
Employee Number Rate Code Regular Hours Overtime Hours
How to set it up so that it calculates the hours based per rate code with the given pay period.
I analyse things by the date they are ordered by the office.
The date gets put into a spreadsheet and i import this into access for the querying.
I need to summarise it by weeknumber and year.
I use the following queries:
To get the week: Week Number: DatePart("ww",[DateOrdered],0,2) To get the year: Year: Year([DateOrdered]) To put it together: Weekyear: [Year] & "_" & [Week Number]
The problem is when the year changes.
The dates for week 1 for 2014 are between 30/12/2013 and 05/01/2014 and therefore straddles 2013 and 2014.
Therefore when the year and week number is put together the following results are given for that particular week:
I built the below query to count the number of ID's for each week. The problem is that if one month ends mid-week it is counting the next month's IDs in the first month. I need to count the number of IDs within each week by month. I have the first qryGroupbyWeek that assigns a week number to each date then the below query to aggregate.
Code: SELECT qryGroupbyWeek.Week, qryGroupbyWeek.[Approved Date], qryGroupbyWeek.ID FROM qryGroupbyWeek WHERE (((qryGroupbyWeek.[Approved Date]) Between [start] And [end])) ORDER BY qryGroupbyWeek.ID;
I've got three fields - date_time, # of issues, issue reasons
I want to summarize these by work week.
So,
WW....... # of issues ...............................issue reasons 1 ..........<sum of all issues for the week>..list of all reasons entered 2 ..........<sum of all issues for the week>..list of all reasons entered 3 ..........<sum of all issues for the week>..list of all reasons entered 4 ..........<sum of all issues for the week>..list of all reasons entered 5 ..........<sum of all issues for the week>..list of all reasons entered
I know how to get the WW part - I do the datepart("ww",[Date_Time] for the expression. But how to write the query to do the other 2 parts, I'm lost.
I am trying to create a query that will group my data by each week so I can sum up some numbers and display them on a per-week basis... Prefer the week to start on Sunday but really not picky about it.
Using the following in the query design window.
Week of Year: Format([Date],"ww")
This is returning: 1 10 11 12 2 3 4 and so on.. See the problem?
I tried sorting but that doesn't work.
I also tried the following:
Format([Date],"mmmm,ww")
But this is returning December,51 December,52 February,8 February,9 January,1 January,2 January,3
Yes, there are some weeks without data but that's not the problem. The issue is that its putting February before January.. Why is this? Again, I tried sorting options and several other techniques but no success.
I have a table of end of week sales with ProductID, Volume_Sold, Year and WeekNo. I am about to create a historical table of RRP.
What is the best way to set this out so that I can query the two tables to that when I run a query over the two tables I get the correct price depending on the year and week number I am working with.
My new table "tblRRP" Could contain Year int, Week int, CountryCode nvarchar (2), ProductId nvarchar (15), RRP float;
The table is only appended to when the price changes. So some products may have a price increase 2 or 3 times a year others once every 18 months. And if the price changes any calculations need to allow for the 2 or 3 different RRPs the Product may have had during the queried period.
So that when I do year on year revenue calculations it works properly.
I have a query based form i use to gather data to generate a report. I have 2 forms Form A is where users open all reports from. On form A i have 2 unbound fields "Report Start Date" and "Report End Date" my queries us these from Form A as the criteria for the queries. I have like 30 reports using this method and it works great. I am now building 2 new reports, a monthly and a weekly report. These reports required and data entry point so i built "Form B"
I created my tables for these reports and went through and created the records. So when a manager needs to enter data for any given report they would us "Report Start Date" to identify what record they want to work with, so if they want to enter data for the December report the would select 12/01/2014 and then open "Form B". This works great and the report looks as good as any Access report can.
Now they are asking to add to "Form B" a comparison to the previous month or week depending on the report. for example:
This month we did 250 units Last month we did 300 units so we did 15% less "Yes my math is not exact"
They don't need to see last months data but i need a way to query the previous record to compare the data
This is the criteria code i use in the query that "Form B" is based on.
Code: [Forms]![Form A]![Report_Start_Date]
I am pulling up the 12/01/2014 in "Form B" but need the 11/01/2014 record floating behind to compare data.
How can i use the same setup but pull a 2nd record? I am thinking i can use a 2nd query but with different criteria.
I have a weekly list of transactions that come in the format DD/MM/YYYY HH:MM:SS, I need to tag these individual transactions with a week number. The problem is, I can't use the Datepart function etc. as the day is classed as running from 8am to 8am rather than midnight. I have a list of all of the weekly date ranges for a few years (with the time) so I was able to solve this problem in excel by using the Index and Match functions. However, I'm trying to automate this process as much as possible so I'd rather perform this function in Access.
I need a report that show Total ordered for week by Fish for a single customer. I have customer table and OrderDetals table with order date. Shipping date Monday to Friday. When preview the report the it shows the current week Total order quantity for each fish for single customer. How do I get this.
