I have a report that has an underlying query that asks for Start Date and End Date.
Is there anyway that I can get what the user inputs into the box to be put into the Page header of the report?
ie: user enters into the parameters
[Start Date] 01/01/2006
[End Date] 04/04/2006
Then when the report displays it says
Report for the period 01/01/2006 to 04/04/2006
Thank you for your time
I have a report which asks for the beginning date and ending date which they want. The report is generated using this information. I want to put the information entered in the parameter request into the heading of the report. How do I recover this information to put it in.
Jerry Hughes
I have a query that has a date/time field (mm/dd/yyyy hh:mm:ss), I have created another field using the following syntax " Sdate: Format([AgentCallDetailInCalls.StartTime],"Short Date") ", I prompt the user for a date using this sdate field I created. The prompt syntax is [Please Enter Start Date]. Currently you have to enter 2/22/2008 (with slashes), I would like to enter 2222008 (without the slashes) to extract the data. The mask I have setup, does nothing. If I put in the date without the slashes, I get nothing, if I put in the slashes, it runs perfectly. Does anyone know a way around this? Thanks in advance for your most knowledgeable response. :confused:
View 1 Replies View RelatedI have to print a label quickly every time that the product hit the warehouse. The label has been created as a report linked to the query that will provide the info to the report. In order to make this report printing as quick as possible the idea is to scan the sample id from the product and once the label is printed scan the next sample and an on.
I'm not an expert on VBA but I have created the following scrip but the reports doesn't pop up.
Here is the code:
Dim SampleID As String
SampleID = InputBox("Enter Sample ID")
If SampleID > 0 Then
DoCmd.OpenReport "rptGRM_QuickPrintLabelDymo", acViewPreview, , "[Sample]=" & SampleID
Else
DoCmd.Close
End If
End Sub
Hey folks,
sorry if this is an oldy but.........!!!!!!!!!
I need to insert the parameter dates for a query into a report. I have tried all the methods I can find but none seem to work. I think its to do with the way my queries are structured.
I have a basic query (q1)! Based on this is a grouping query (q2)! Based on this grouping query is my report. I have set up a form prompting for the parameter dates. This all works ok and my report displays the correct data (between the dates entered). The problem is that I cannot get the report to show the parameter dates.
Help. Its driving me nuts.
Billsack
On my main form, I have a subform to input multiple dates, using date picker.
I also have a text box on the main form to input single dates, again using Date Picker.
On entering both the single date text box and the subform, I don't want the (default) date to be visible. I only want the date to be visible once I have selected a date from the Date Picker.
I have figured out how to do this with the single date text box. I have simply defaulted the forecolor to be white so that you can't see the font against the white backcolor. Then on the Change Event, I set the forecolor to be black. Works like a charm.
However... This same approach will not work with the subform. I've tried playing around with a bunch of different events, but so far nothing allows me to re-create what I am able to do with the single date text box on the main form.
I am not a programmer and I don't know VBA/VB.
I am using this as a workaround to avoid VBA functions (since I don't know them).
I can't seem to find a simple? solution to this.
I have a query that does a radial search in decimal degrees. I have created an unbound form as a dialogue box that converts degrees-minutes-seconds to decimal degrees and displays the results in a calculated text box. What I want to do is have the query take the results in the calculated text box as its parameters (along with a third parameter- distance) without prompting the user. How do I get the query to take its results from the fields on the form? I have tried Like [Forms]![frmName]![SearchValue], but I can't seem to make it work.
I'm using an Excel spreadsheet that is importing external data from an access database in which I've got a field where the name of a person checking materials out is entered. it is currently set up, and I cannot change it, as a free form field. So folks enter information in a variety of ways.
For example, Larry Martin might be entered as "Larry Martin" or "Martin, Larry" or "larry.martin@somewhere.com". I'm trying to run a query that would look in that field for any entry with the string I enter, such as "Martin."
I've tried setting the criteria like this:
Like "*" & [Which Last Name] & "*"
However, when I try and run the query I get a message telling me the system is expecting two parameters.
Does anyone have any idea what I'm doing wrong? I've been banging my head against this for awhile now and am thoroughly stumped! All assistance, as usual, is greatly appreciated!
I have a table which holds information on audits that have been carried out on staff member's actions. The 'Supervisor' field is populated via a combo box which is linked to a separate table (tblSupervisors).
I am now trying to build a query to allow me to extract all audits that have been carried out on a specific supervisor - rather than the criteria to be [Enter Supervisor Name] and allowing text entry, I thought it would be better to have form that pops up with a combo box that is used to select the supervisor (from tblSupervisors);
So far:
- Form "Supervisor_Select" is created, and has a combo box that looks up from tblSupervisors
- Macros as specified in the instructions are created (Open Dialog, Close Dialog, OK and Cancel)
- Query is done, all bar the criteria expression on the desired field.
- Module is created as described in the instructions, and is called "Supervisor_Select"
I have tried putting the following in the criteria;
[Forms]![Supervisor_Select]![cboSupervisor], however I think I am missing the bit where the query opens the "Supervisor_Select" form?? Will this only work from a button where the on click event runs the 'Open Dialog' macro and then runs the query?
Is there any way to avoid the error code when using the date parameters in Report header when there is no data for the specified range.
The following are the parameters being used to get the date range. They work fine as long as there are records in the date range entered.
=Reports![Toys and Adapted Equipment Centre Signed Out By Date Range]![Enter Start Date]
=[Reports]![Toys and Adapted Equipment Centre Signed Out By Date Range]![Enter Final Date]
I have this select query.
SELECT DISTINCTROW L160.Date, Avg(L160.Zinc) AS [Avg Of Zinc], Min(L160.Zinc) AS [Min Of Zinc], Max(L160.Zinc) AS [Max Of Zinc], Count(L160.Zinc) AS [Count of Zinc]
FROM L160
GROUP BY L160.Date
HAVING (((L160.Date)=[Forms]![L-160quarterfrm].[Date]));
How do I build a form that would ask the user to input a range of dates for the criteria?
Thanks! :o)
How do i can apply date parameter to a report which takes starting and ending dates from a form...
View 1 Replies View RelatedI have a list of coded transactions in column A, Amounts in B and Dates in C.
I want to show subtotal for all amounts with the same codes in D
I can do this simply without the date parameters, but I need to give timeframes.
When I add the [From] and [Until] questions it then shows the amounts next to each date even though the date column is hidden.
I'm aiming for the subtotal of amounts for same codes between given dates:
Example
ABB 100 1/6/11
ABB 100 6/6/11
ABB 150 1/8/11
ABC 50 1/7/11
ABR 50 2/8/11
ABJ 50 1/5/11
If I enter 1/6/11 and 30/6/11 I would expect ABB to show 200 total as 150 is after that date and so not counted.
Good Evening Everybody,
I am currently helping some people out at work with their database. My knowledge of Access is very limited, and whilst my it is improving I have nonetheless come up against a ‘hurdle’ which I am struggling to jump. The database in question is not complex, quite the opposite in fact. Basically 1 table ‘Main Data’ , 1 data entry form and a few reports. The table includes 8 fields in the following formats:
ID: Auto Number
Area: Text
Equipment:Text
EquipSrlNo:Text
EquipLocalNo:Text
MOPNo:Number
DateDone:Date/Time
MOPPeriodicity:Text
I was recently asked to produce a report for them that would search between two dates using a parameter query and to then return the result. The problem is that when I generate it using the following SQL it returns records in the period that I asked for, but then collects ‘additional records’ which fall outside the date-span that I originally requested, i.e. search Between 03/04/08 And 30/04/08 produces data that relates to data in May, June, July 08 etc. I thought that what I was doing was correct, and it has worked for me in the past- but on this occasion I am absolutely confused???
SQL
SELECT [Main Data].Area, [Main Data].Equipment, [Main Data].EquipLocalNo, [Main Data].MOPNo, [Main Data].DateDone, [Main Data].EquipSrlNo, IIf([MOPPeriodicity]="1",DateAdd("m",1,[DateDone]),IIf([MOPPeriodicity]="3",DateAdd("m",3,[DateDone]),IIf([MOPPeriodicity]="6",DateAdd("m",6,[DateDone]),IIf([MOPPeriodicity]="WEEKLY",DateAdd("ww",1,[DateDone]))))) AS TestDueNext, [Main Data].MOPPeriodicity
FROM [Main Data]
WHERE ((([Main Data].Area)=[Enter Area Type]) AND ((IIf([MOPPeriodicity]="1",DateAdd("m",1,[DateDone]),IIf([MOPPeriodicity]="3",DateAdd("m",3,[DateDone]),IIf([MOPPeriodicity]="6",DateAdd("m",6,[DateDone]),IIf([MOPPeriodicity]="WEEKLY",DateAdd("ww",1,[DateDone])))))) Between [Enter Date Start] And [Enter DateStop]));
Unfortunately, I do not have a copy of the database in question and so I am unable to post it for the purpose of explanation.
Can some kind person point me in the right direction as I am slowly losing the ‘will to live’ Ha? Ha? You have always been so helpful in the past and hence the reason I have called upon my ‘forum friends’ to help me out?
Look forward to your response(s)
Best Regards
CarolW
I have a parameter that I need to get a table contents between dates. In the query:
Birthdate: XXTable: criteria as follows: Between [Enter Start Date] and [Enter End Date:]
When run it gives me the Error - ! This expression is type incorrectly, or its too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables
I have tried almost everything. The formatting of the Birthdate is x/x/xxxx or shortdate. Will this affect the input thus affecting the outcome of the query.
I have a query with the following fields
*Date From
*Date Till
I have got the following formulas for each
Date From - >=[ENTER Start Date]
Date Till - <=[Enter End]
The results work fine to show which vehicles are booked between these dates, the only thing is I want to be able to show the vehicles that are not booked for the given dates in the parameter query is this possible?
I am trying to create a parameter query to return dates that have 2 years remaining.
For example I have dates for when mortgages expire, and I want to recognise the dates that have two years remaining using a parameter query but I can't figure out if I use DateAdd or DateDiff.
I created a pop-up that appears when i open my report so that I can automatically sort through the report by date. However, the report will only allow me to choose one date and i need to be able to choose multiple dates. This pop-up is connected to a list box which has the multi select turned on. How can choose multiple dates through my pop-up?
View 1 Replies View Relatedcan i set a value in report as parameter in query
View 1 Replies View RelatedWe have employee dates of birth in our database. For a birthday gift exchange program, I want to generate a query that returns only the day and the month of the employee birthday (to placate those employees who wish not to disclose their exact age). I'm struggling with making a custom date input mask that will do this.
View 3 Replies View RelatedI have an EndTerm field (this is a calculated field); I want to set up a parameter query that enables me to type in a date which will retrieve any records BEFORE that date. I've used < [Enter date] and results were not right. i.e., I entered 1/1/2007 in the parameter and the results came back with records dated 1/1/yyyy instead of anything before 2007.
View 7 Replies View RelatedI created a combo box that runs a parameter query based on the name entered in the box.
Is there some way to return the results as a report instead of in datasheet view?
I'm wondering if inserting some sort of code into the event procedure of the "OK" button (which launches the query) would work.
Would greatly appreciate help---
I have a query that I use to populate a report.
The query has a parameter box that opens and asks the user to enter a date.
I would like to display this date in the Report heading.
Is there any way that I can grab the value entered into the parameter query and display it on my report heading?
I would like to display the value of my parameter query into the title of the report.How could I do this on Access 2007?So far I have made another field in the query and called it ParaDate: [JobDate]
Then in the report title I wrote:
=Limousines booked for&" "&[JobDate]
But it's not working.
I have a couple of questions which are sort of related. They both revolve around Parameters with a Query/Report.
Here is a bit of background. The source Table is formatted in a way similar to this:
Code--Account#--Expense Description--Jan--Feb...
When running the Query, and associated Report, the user is prompted to input an Account# via a Parameter that I set up. The dialog box pops up with the word "Account#" and a box for the user to enter by what account that they want to restrict the Report. [Question 1] - Is there a way to make this dialog box contain a drop-down box with a list of predetermined accounts instead of the aforementioned entering of an account number? If not via this dialog box, is there another way?
Also, multiple accounts roll up into a single P&L line item. For example, let's say that accounts 1234 and 5678 both make up "Equipment". [Question 2] - Is there a way to run the Report in such a way to return all accounts which roll into a line item? I want to have the ability to run by either an individual account or the P&L line in total.
I have this pretty difficult thing that I would love to get done.
In table the user can set two dates, the begin date for a period and the end date for that same period. The time between the two dates is the time employee has been working.
Now, I'd need the query to show the first of ALL dates on one person and the LAST date. So, if the person has been working first between 1.1.2008 and 31.1.2008 and then between 1.2.2008 and 29.2.2008, the report should show that the person has been working 1.1.2008 - 29.2.2008.
I've tried all I could think of, but can't figure out how this is done.
Also, I've been wondering if it's possible to make the query show if the employee has been away, as in, if the person hadn't been working for couple weeks, let's say 1.3.2008 and 14.3.2008, but then worked between 15.3.2008 and 31.3.2008, the report would show that the person has been working
1.1.2008 - 29.2.2008
15.3.2008 - 31.3.2008.
Heh, that's whole lot of things to do. I hope that was enough information for someone to be able to help me!