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 RelatedI 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 RelatedBasically, I have a database table that is maintained every week. It is about e-mail account licenses for Office 365.
Each column represents a week of license data for every mailbox account which is about 10 000 plus users with the date as the field headers( something like "License information as of 06122013").
Basically, I have created 52 queries based on the license type I require to be calculated & I have a form created to control it. However, right now it is all output only as I have yet to create any user parameter to specify on any of the queries about the date I want
As I am currently unsure how to specify all the queries to use one field date header which I want the user to specify via drop down list in order to calculate all the licenses on this specific date.
For example, if I want to see the license information for 06/11/2013, I would choose "License as of 06112013" & it would then run all the queries based on the header specified earlier & output that information on the form.
Right now the queries are all configured as the "Select" type.
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 have a series of dates over several months, and I want to write a parameter query which will list only those from the month of August. I know how to write a general parameter query, but I can't figure out how to write one that specific.
View 10 Replies View RelatedI 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 2 Replies View RelatedI am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?
Is there a better way to build this database? I thought about just have four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.
Hello everyone,
Ive designed a databse - no problem. Ive done all queries I need to - no problem. Ive designed forms for entering data and reports for showing data - no problem. THE ONE PROBLEM I have is with 1 field.
The problem is that the field is a memo box, used to add data on groups attended. For example: General, customer care, mystery shopping.
Can I run a query so that a different user can search the memo box for attendence to a certain group? eg, search this databse and field for customer care.
At the moment I can only seach on the first word in the field. I dont really want to have seperate fields as each employee can have attendence to different groups ie bo can have just general while bill has general, mystery shopping, customer care etc.
Any help would be great
I am currently working through my A2 (final year of high school in the UK) ICT coursework, in which I am building an Access database system for a small wedding car hire company.
My current problem is I would like to (not even sure if it's possible but I assume it is) have a form that searches a query for a date that the customer has entered.
If there is no date in the query that matches the one that the customer has entered, it means that their chosen date is available. If there is a match however it means that the date is unavailable.
I am using Microsoft Access 2010.
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?
I have many queries for separate types of income that when criteria is entered on the switchboard, a report is created for either a specific month or range of dates. This works fine. Now I need to create one report that shows all 7 types of income in one spot, but once again based on a specific month or range of dates. I have a query that produces the entire income for all dates, but can't get it narrowed down to the specific criteria.
View 8 Replies View RelatedIs there a way to look at only data from a specific month? It's June now so let's say I want to look up only data from June without having to hardcore the number 6 or letters "June" in my query. How would I go about doing this?
Another question would be how can I convert this to a number to multiply the numbers in my data with?
So I want to retrieve only data for the current month, June, and then multiply numbers associated with that data with the number of the month for June (that is, 6). How would I go about doing this?
I am trying to query any date before current month. My data is employee start dates, thus my end aim is to pick up all employees at end of last month.
View 1 Replies View RelatedI am trying to make a query that counts the orders on a day of an specific month selected from a form.
The problem is that i want to show a chart with de 31 days of the month, even if in those days there are no orders.
Any way of making a query that adds the days with no records with a count value of 0?
I am creating a line graph from a running total query to show our income from items shipped for each month. Currently I have the following Code in my query which works but it displays the month as a number in my graph and I would like it to show the Month name.
Code:
SELECT DatePart("yyyy",[ShippedDate]) AS AYear, DatePart("m",[ShippedDate]) AS AMonth, DatePart("d",[ShippedDate]) AS ADay, Format(DSum("SalesPrice","tblJobs","DatePart('d', [ShippedDate])<=" & [ADay] & " AND DatePart('m', [ShippedDate])<=" & [AMonth] & " AND DatePart('yyyy', [ShippedDate])<=" & [AYear] & ""),"Currency") AS RunTot
FROM tblJobs
WHERE (((tblJobs.ShippedDate) Is Not Null))
GROUP BY DatePart("yyyy",[ShippedDate]), DatePart("m",[ShippedDate]), DatePart("d",[ShippedDate])
ORDER BY DatePart("yyyy",[ShippedDate]), DatePart("m",[ShippedDate]), DatePart("d",[ShippedDate]);
I tried this solution, but I get an error in the RunTot field, I'm assuming because Access can't use the month name in dsum.
Code:
SELECT DatePart("yyyy",[ShippedDate]) AS AYear, MonthName(DatePart("m",[ShippedDate])) AS AMonth, DatePart("d",[ShippedDate]) AS ADay, Format(DSum("SalesPrice","tblJobs","DatePart('d', [ShippedDate])<=" & [ADay] & " AND MonthName(DatePart('m', [ShippedDate]))<=" & [AMonth] & " AND DatePart('yyyy', [ShippedDate])<=" & [AYear] & ""),"Currency") AS RunTot
FROM tblJobs
WHERE (((tblJobs.ShippedDate) Is Not Null))
GROUP BY DatePart("yyyy",[ShippedDate]), MonthName(DatePart("m",[ShippedDate])), DatePart("d",[ShippedDate])
ORDER BY DatePart("yyyy",[ShippedDate]), MonthName(DatePart("m",[ShippedDate])), DatePart("d",[ShippedDate]);
Do any of you know a way I can make this work?
This is my expression to format crosstab query by month
Expr1: Format([Recharge_Date];Month([Recharge_Date]))
but i can't make it with month and year....
My question is that I wish to have a query that looks up on a name in a form (No problem got this bit) and also the Date field from the same form so that from the date :-
example 24/03/2013 only cares about the Month and Year so would look up only March in 2013
The end result would be to supply me with a number (using count) of how many times this persons name has occurred within the calendar month and year of the date supplied in the form
I have a table that has a date column. I am assigning one group of students a test date of 4/29 and another group 4/30. I don't want access to change the column or ask for a parameter during the query, but no matter what I do, it keeps asking me for a parameter. I just want it to use the data that is already in the column.
I tried changing the column name in Excel to DOT from Date. That didn't work.I also tried changing the column to text, so that it wouldn't be a "date." That didn't work.
I am trying to create a button on an access form that will prompt the user to enter a search month and then export to excel all records in that database that match that month.
So, if the user prompt "what month" is October, then THE 30 records in the field [receivedate] that are in october (10/XX/XXXX) are exported to excel.
I want to create a simple query from a list of orders dating back over 12 months.
The fields I have is Ord_Date, Qty
I need to show the orders by month for the last 12 months.
The problem I am getting is that the orders for the month of April (as we are in April now) contain orders from 1st - 22nd April 2013 and orders from 23rd - 30th April 2012, therefore confusing the figures.
I would just like orders grouped by month with a total qty dating back 12 months, but without any old orders for the current month.
I have multiple queries that I need to have filered by the same parameter (Client). There is a list of clients that receive this report (the report has a pre-made template with multiple tabs, hence the multiple queries).
Currently the user of the database has to filter the exported excel file for each client re-save and email basied on the client. The above is done when the user pushes the specified button on a form (which just says run report a on it).
I am trying to figure out how the write a code so that a parameter (or table if that would work better) pulls the first record of the Client query to filter the other queries being run. Then to loop that process with the second record and so on through the Client query until all the clients have had the report run for them.
Attached are the current VBA being used and the current SQL for the first query that needs the parameter value added.
can i set a value in report as parameter in query
View 1 Replies View RelatedIt is possible to create a parameter query in Access that will search for records that match the parameter entered by the user AND contain all other records that have that value plus other text.
For instance you may want Access to return all records that begin with the letter Q.
To do this you need to enter the following expression into the criteria row:
Like [Find records beginning with:] & "*" Or Is Null
The user can enter a Q and the criteria concatenates the wildcard character * to this parameter value, to find all text strings that begin with a Q.
By also using Or Is Null in the criteria the user can enter a blank value into the parameter query to return all records, even if this field is blank.
But how do I search if i want all the records with John anywhere in the field???
I have a query where I want to use a combo box in the parameter. However, its not working.
SELECT Call_Details.Emp_ID, Call_Details.[Call Status]
FROM Call_Details
WHERE (((Call_Details.Emp_ID)=[Forms]![TTY_ID]![TTY_ID]));
I get this in the Parameter Value Dialoge
Forms!TTY_ID!TTY_ID
Should I be entering this in the criteria field at all? Do I have to build an event to make this work?