I have set up a report which prompts the user for a start and end date. However, if they want to run it for a full month they enter eg. 01/07/07 and 1/07/07 - this will not include entries ending on 31/07/07.
Is there a way I can change the criteria prompt so that it read both dates entered as inclusive in the query. I am currently using
Between [enter earliest date:] and [enter latest date:]
I have a table of data going back to 2007 that needs to be looked at on a monthly/quarterly/annual basis. I am able to filter the data when running a normal query by using
Between [Start Date] and [End Date]
in the Criteria section of the Date field. I now need to apply this same idea to a crosstab query. My current set up is:
[Gender]- Group By / Row Heading
[Plan Type]- Group By / Column Heading
[Pmt Amt]- Sum / Value
[Date]- Where / Criteria = Between [Start Date] and [End Date]
I am getting an error message that says:"The Microsoft Office Access database engine does not recognize '[Start Date]' as a valid field name or expression."Am I setting something up incorrectly or is it impossible to use input prompts in a crosstab query like this?
Access 2000
Hi All.
I am running a select query on a date field.
I populate the table "date" field from a Microsoft Date and Time Picker Control 6.0(SP6).
I havent been able to figure out how to ONLY populate the date portion of the control so my field value containes Date & Time.
When I run the query, I have to select the day after my request to include the records I need. I think this is because of the time in the field
Example, If I want all records thru 6/17/2006 I must select "thru" 6/18/2006. This mucks things up if I have records dated 6/18/2006
Both of these statements give the same bad results:
Between [forms]![freports]![from] And [forms]![freports]![thru]
>=[forms]![freports]![from] And <=[forms]![freports]![thru]
Any ideas?
TIA
Detrie
I currently have a form where users can enter an "End Date", click a button, and it queries the data from 12 months prior to "End Date" entered.
Is there a way to force the query to show all 12 months, even if there are no records for a particular month? For example: if the user enters February 2013 in the date field, I would like the query to return:
March 2012
April 2012
May 2012
June 2012
July 2012
August 2012
September 2012
October 2012
November 2012
December 2012
January 2013
February 2013
...so even if June 2012 has no records, it is included in the query with a value of zero.
This is what I have so far:
WHERE (((Qry_Tbl_Assets.Dte) Between DateAdd("m",-12,[Forms]![Main_screen]![End_Date]) And [Forms]![Main_screen]![End_Date]))
Right now I have this in the query criteria row:
>=[Enter Start Date:] And <=[Enter End Date:]
This makes two prompts pop up one at a time. I was wondering if there was a way to combine both start and end date boxes in one prompt so I can see what dates I decide to type side by side. Is this 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.
Hi there,
I have to format a date following dd-mmm-yy in an expression also containing text.
Expr1: "Text" & " " Date()
Although separately formatting the date is easily done. I need to keep both int he same expression.
I have a query that pulls scores for this month only for each class member. Problem is, I only need a count of these scores (per person) and because the date is in the query, it doesn't group the scores together and count them as one. i.e. it sees score 1 and score 2 as separate because they have different dates so they won't count together.
The only purpose of date in this query is to filter out only this month's dates. Is there some advanced query expression something or other that will tell it to leave date out of the count and only use it to filter?
Trying to get a formula or function for calculating profit for each month between selected dates. Partial month calculations are required.
Source Data (record) examples:
Contract1: Startdate = 6/30/12; EndDate = 9/22/12; DailyProfit = $500; MonthlyProfit = $9500
Contract2: Startdate = 7/7/15; EndDate = 12/17/12; DailyProfit = $600; MonthlyProfit = $11400
Contract3: StartDate = 8/1/12; EndDate = 6/30/13; DailyProfit = $350; MonthlyProfit = $6650
User Query #1:
Notes:
ReportStartDate will always be entered as the first day of the month;
ReportEndDate will always be entered as the last day of the month
The report will always show 6 consecutive months
User selects
ReportStartDate = 7/1/12;
ReportEndDate = 12/31/12
Notable conditions:
-The calculation for Contract 1 will have a partial month profit calculated for September 2012. The other months can show the MonthlyProfit value, with October and December being $0.
-The calculation for Contract 2 will have a partial month profit calculated for July 2012 and December 2012. The other months can show the MonthlyProfit value.
-The calculation for Contract 3 will have no partial months since the StartDate is on a month beginning, and the ReportEndDate is prior to the EndDate. July 2012 will show $0.
Request: I either need a super complicated IIF statement for 6 fixed columns in a query (one column for each month) or a smart function, neither of which I can do.
Using access 2010; i have a form that includes a date field. Is there a way to force the user to only choose a month end date? When the user clicks the date from the popup, they may use 9/1/2013 when the mgr. want them to use only 8/31/2013. I am thinking validation field to put a msg but want to be able to force it not the option.
View 2 Replies View RelatedI have a need to prompt a user for a date, When they enter that date the qry should do as follows.
if rec_date <= (DATE PROMPTED FOR) and rel_date is > (DATE PROMPTED FOR) ,
then
return Qty and a few other fields....
No the DATE PROMPTED FOR is not a field in the table.
Do I need it to be?
I have a textbox on my form that is for "week of", always a Monday. There is vba to populate the appropriate Monday when the user selects something other than Monday via the date picker. This code is in the on change event. However, this is not friendly to a user entering a date in this field. I did try after update event, but that requires user to leave the "week of" field. This is not acceptable in this instance, there are other form fields that change as a result of this "week of" value.I also tried evaluating the length of the text or else exiting the code. However, I was expecting the date being returned from the date picker to always be 10 positions, but for March 3rd, it would return 3/3/2015...which is only 8 positions.
View 2 Replies View RelatedI have looked but have been unable to find the answer in past posts and this must be a common problem. I wish to include all records up to and including a date passed in the prompt "Criteria: <=[enter end date]". I changes the date format in the back end to short and I am in the US. However I get only dates less then the the prompted date.
Thanks for any help
Jim
I have a field that is a Date Time field (mm/dd/yyyy hh:mm:ss) and I would like to prompt the user for a begining date range and ending date range. I know how to prompt the user, but I would like for them to only key in the Date portion and not the time. Can anyone offer any suggestions on how to handle this? Thanks
View 1 Replies View RelatedI'm using MS Access 2002 and I'm trying to create a prompt for a date and then enter that date into the body of the letter.
Also can the date format be short and converted into a long format on the letter.
Example:
Prompt - Enter Date
Date entered = 12/31/2013
Sample Letter:
Your subscription ends on December 31, 2013 if you would like to renew etc.....
Hello buddies :D, do you have any idea how to make this work?
To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.
This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only.
([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null)
How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif
I have been trying a week to do this query!!
I am trying to get the MaxServicedate for each Product (PIDFK) for every Location (LID), but then I also need the MaxServicedate regardless of Product use, for each Location that is smaller than the previosly obtained MaxServicedate.
Currently I have the following.
SELECT T.LID,
T.PIDFK,
T.MaxOfServicedate,
Max(tblTakings.Servicedate) AS NextMax,
T.Installdate
FROM
(SELECT tblLocations.LID,
tblTakingsProductMM.PIDFK,
Max(tblTakings.Servicedate) AS MaxOfServicedate,
tblLocations.Installdate
FROM (tblLocations INNER JOIN tblTakings ON tblLocations.LID = tblTakings.FKLID)
INNER JOIN tblTakingsProductMM ON tblTakings.TID = tblTakingsProductMM.TIDFK
GROUP BY tblLocations.LID, tblTakingsProductMM.PIDFK, tblLocations.Installdate) AS T
LEFT JOIN tblTakings ON T.LID = tblTakings.FKLID
WHERE (((tblTakings.Servicedate)<[T].[MaxOfServicedate]))
GROUP BY T.LID,
T.PIDFK,
T.MaxOfServicedate,
T.Installdate
ORDER BY T.LID,
T.PIDFK
The sub query returns 90 records with the MaxServicedate each Product was used in each Location.
The main query only returns 88 records, as the missing 2 records dont have a Servicedate previous to the MaxServicedate. In this situation, I want it to give the Installdate instead.
I really have tried allsorts, using nz(Max(Servicedate),Installdate) but cannot get it to forward the 2 missing records from the subquery.
Many thanks in advance.
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code:
Between [StartDate:] And [EndDate:]
And
Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
Neither of which work ....
I'm trying to get my "IncidentDate" field to autopopulate two other date fields to a few days from the "IncidentDate". The other two date fields are "ContainDueDate" and "RootDueDate". I'm trying to accomplish this on my "Test CAP Form"
I tried using the following in the BeforeUpdate of "ContainDueDate" and received a complier error: expected =
Code : DateAdd(d,2,[IncidentDate])
so I removed the parenthesis and nothing happened
Code : DateAdd d,2,[IncidentDate]
I even tried redoing it in the AfterUpdate of "IncidentDate" and nothing happened either
Code : DateAdd d,2,[ContainDueDate]
I'm not sure if I'm even using the right function to get what I want.
I have two tables, a tblUsers and a tblDailyReport.
The tblUsers only stores a User ID. The tblDailyReport stores both the Date and the User ID. I have a relationship from tblUsers to the User ID on tblDailyReport. tblUsers has its primary key set to User ID. While tblDailyReport currently has no primary key.
When I use it this way I can have a User ID use more than one date. The issue here is that I can also have the User ID use the same date numerous times and have it stored in the tblDailyReport, creating multiple records for the same User ID and date.
If I make the Date in tblDailyReport a primary key, I can only use one User ID per date.
I need to ultimately have the same date but for different User IDs. However, I can't have duplicate records of the same date and User ID. What can I do to fix this?
I keep thinking this should be pretty simple, but more than 2 hours searching through posts has revealed no answers, probably because the answer is too simple.
OK, I have an append query. I've created a prompt to enter the number of the record the user wishes to append, as it is usually one single record. However, I've recently run into a problem that now there is the possibility of needing to specify more than one record at the same time to be appended.
How do I change the criteria syntax to allow the user to enter two, three, or a dozen (if need be) different record numbers? They are not listed sequentially, so I can't use a "between" statement. I've tried separating the numbers by commas, quotes, and so forth, but can't get it to recognise the records.
Can anyone help please? Thanks in advance. :o
Im seeking for a solution to prompt all user with windows alert for Notification
Case 1:One staff applied leave and clicked "Submit" , after clicked I want the system will prompt his/her Supervisor for his/her leave approval or like a notification that the Supervisor need to go somewhere to approve (Selected Supervisor)(Maybe the Supervisor need to 7x24 Opening the access database).
Can someone tell me how to get year to date totals, month to date totals, week to dates in a query? I need to get all three for three different fields.
I was not able to get the totals with the formulas given. I received the totals for each day instead. Are there any other suggestions? I am trying to different formulas, but they are not working either. I did try doing different queries with the formulas to see if that would work.
I have a form in which I collect approvals from two different departments. To approve an item the user selects their name from a combobox, and then tick an option box to indicate approval. The combobox is from table 'Users' and has a query as a source with the following fields, 'UserNum, First, Last, Password'. The 'approval' fields are on table 'Approvals' and are yes/no fields.
What I'd like to have happen is that the user chooses their name from the combobox and then ticks the option box for approval; when ticked I would like a messagebox to appear asking for the users password based on the name chosen in the combobox.
Is this even possible?
I want to run an activity query (i.e. objective is to check which data lines have changed) within a user defined date range. Please advise what is the most efficient way to achieve this.
Is it possible to run such a report without having a date column i.e. does Access have a hidden line modification date that can be used in a search?
Look forward to your feedback - thanks!
Hello all, im new here so hi to everyone.
Im stuck! I am pretty much a novice when it comes to access and have been given the task or creating some reports to stop us doing it manually.
Within a query, I have a date column which we would currently enter the following into the criteria:
Between #01/01/2008# And #31/01/2008#
for the month of January.
What i want to be able to do (and i know is possible as ive done it in the past but cant remember how) is have a pop up when the query is ran to enter the Start date and End date. I remember it being something to do with square brackets but cant get it to work.
Help please! lol