I have a simple report which i open from a navigation form, i have a textbox in the report i wish to use as criteria, on start the report will be blank until i enter a name into the textbox and requery. However im not sure how to link this and how to stop access asking for this information before the report is opened?
View RepliesI am trying to use a textbox value as a query criteria using the code [Forms]![Home]![mtdDateBox], following the standard structure of referencing a form item. However, I am not returning any records, when it should be giving some. The data in the textbox follows the date format "DD.MM.YYYY" but is used as a string.
Is there something I am missing out? I am using MS Access 2010
I'm trying to build a query to use as a filter in the DoCmd.OpenForm function.
I have done this several times before, referencing values from forms e.g.
Point To
Field: LeadID
Table: Lead
Criteria: [Forms]![GridDisplay1]![LeadID]
This filter is used in the procedure:
Code : DoCmd.OpenForm "LeadDetails", acNormal, Point_To", , , acDialog, """"
And it works great, I click a button next to the record I want to select (records displayed on continuous form) and it opens the Lead Details form on that particular record.
However now I am trying to accomplish the same thing, except instead of a continuous form I need to draw from a continuous sub-form. The form "BrokerMgmt" contains a sub-form named "BrokerSearch." The RecordSource for the sub-form is set once the user enters data into a few textboxes on the main form and clicks the search button:
Code:
Private Sub brkSearch_Click()
Dim argCount As Integer
On Error GoTo Err_Handler
If IsNull(brkFirstName.Value) And IsNull(brkLastName.Value) And IsNull(brkCompany.Value) Then
MsgBox "You Need To Select Some Values", vbCritical, "Lead Tracking"
[Code] ....
The sub-form then displays basic info such as Name, Company, State, and the "BrokerID" which is the primary key of the "Broker" table. This "BrokerID" is displayed in "Textbox 20" so I set a button next to each result to on_click perform the procedure:
Code:
DoCmd.OpenForm "BrokerDetails", acNormal, "Point_To_Broker2", , , acDialog, """"
"Point_To_Broker2" is set up in the same style as "Point_To":
Field: BrokerID
Table: Broker
Criteria: [Forms]![BrokerSearch]![Text20]
However instead of the BrokerDetails form opening to the appropriate record I get a msg box asking for a parameter. Why is this? Even if I enter the correct BrokerID as the parameter it still gives me an error saying you can't go to the specified record.
Is there a way in access, through vba or any other means, which would allow me to either enable or disable criteria in a query based on the value of textbox??
Like i have a query that displays bookings customer has made between two dates, so i made a query and in the starting and ending date fields i get the value from form. In case if the use does not enter ending date, i would like to run the query with starting date only that displays booking made after the starting date.
Currently the starting date criteria is set to a textbox of form, and so is ending date. So if customer selects 1st April 2015 as starting date and 30th April 2015 as ending date the query should display the bookings between 1st and 30th of April. And if the user enters only 1st April in starting date it should display bookings starting from 1st April onward.
I have a query that uses the input from a form as criteria, which is then used in a report. The form input is a drop down based on another table. This is a sales pipeline report, and the list is a list of sales people. The report works perfect for all sales people except one. When I run it for the one, I get the following error:
"This expression is is typed incorrectly, or it is 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 DO NOT get the same error when running the query by itself - so assuming there is something in the report causing this. I do have some sum formulas in the report.
Again, no other salespersons selected cause this error -- so I am assuming there is something in the dataset for this person that is causing the error.
Is it possible to insert text from a textbox on a form (Data) when a combobox on (Data) meets a criteria?
Example: Test: IIf([Results]="Positive" text207)
So if the Results combobox is Positive then the text from Text207 is inserted.
I have an unbound form (named frmReportSearch) with unbound text & combo boxes providing the criteria for a query (named qSeqStreets). The form / query utilize 4 optional fields as search criteria plus date from / to. The results are returned via a report (named rptSeqStreets). The whole operation worked perfectly, however I realized I needed to change one of the criterion to a multivalued field. The change in the table (named Tasks) worked perfectly. I used three checkboxes (named chkA, chkB and chkC) to allow the user to select any combination of the 3 choices, including none (to be treated as no filter on [fldShifts]).
The three options in the field (named fldShifts) are "A" "B" and "C". I am able to manually run the query from design view by typing in the criteria "A" Or "B"... "A" Or "B" Or "C"... and any combination of the three options in the criteria box and running the query. I am using the following code under the OK button's OnClick. The Code below has other items related to all the options .... I didn't want to give partial code so you may understand better:
Code:
Private Sub btnOK_Click()
Dim strShift As String
Dim strA As String
Dim strB As String
Dim strC As String
[Code] .....
My problem is that the query criteria needs to be entered into the criteria box with quotes and separated by "Or" depending on if multiple checkboxes are selected.
I can get the results to show correctly in the textbox, however I imagine the query is adding an extra set of ""s to the string so rather than "A" Or "B" .. it is getting ""A" Or "B"". My query Sql and even design mode are pretty complex, so I wouldn't know how to use the sql in VBA without blowing some fuses.
I have a query, one of fields have a criteria. On other hand i have a form. User entry a value in textbox an click on a button then query run. criteria is value in textbox. What i must write in criteria?
View 1 Replies View RelatedIs it possible in access 2010 to create a a query that only shows a text box if a combo box criteria is met?
Example: On a form there is a combobox (Result) that can either be negative or positive. If the value is Negative then a query is already setup that populates a mailmerge with some text. If Results=Positive can a query be created that will show the textbox (Data). It only needs to show if the positive criteria is met.
I have a report that gets its data from a query. I need the query to run before the report based on criteria based from two combo boxes on a form.
View 3 Replies View RelatedI have a form. On the form I have a button to run a report.The query associated with the report selects all records within a unit (field name (Unit) is used as the selection criteria).Rather than type in the unit name when the report is run, I want to select the unit that is currently shown on the form.
View 4 Replies View RelatedI have a table called estimates and a field in that report is called RFA and this is a checkbox. How can i open a report i created (called EReport) for all rhe records in this table that have the RFA field checked (therefore true).
View 2 Replies View RelatedI have a client that I made a database for with Purchase Orders. He wants to call them purchase Orders instead of Invoices (whatever).I have two reports created, Sales by Customer and Sales by Account Manager. I have two fields on the reports menu for Start Date and End Date.
The queries for each of these reports looks at the Start Date and End Date just fine. Now, the client wants optional criteria of selecting all customers or a specific customer, or all Account Managers or a specific Account Manager.
the fields are CustomerID and AccountManagerID. What would the criteria code look like for each to give the user a choice as specified above?
I'm having a very simple issue (I think) trying to pass a textbox value as parameter in a sql query.I have a multiline textbox and I use it to find several values in the database, so in this textbox all the values are pasted each per line and I have a small code to convert it to single line comma separated values.This is an example:The user enter the data:
[Text0]
A1C556CC3C-TNNN
C010070H13
The code convert this data to a single comma separated string and runs the query: ex: "A1C556CC3C-TNNN","C010070H13"
Code:
test = """" & Replace([Forms]![Search]![Text0], Chr(13) & Chr(10), """,""") & """"
[Forms]![Search]![Text0].Value = test
DoCmd.OpenQuery "FindPartNo", acViewNormal, acReadOnly
In the SQL code I use the IN operator to find the exact value for each record:
FindPartNo sql query:
Code:
SELECT Classifications.BU, Classifications.WisperPlantID, Classifications.PartNumber, Classifications.PartDesc, Classifications.US_CL_Code, Classifications.MX_CL_Code, Classifications.TARIC_CL_Code, Classifications.COEProject, Classifications.Supplier, Classifications.BrokerRequest, Classifications.CreatedBy
FROM Classifications
WHERE Classifications.PartNumber In ([Forms]![Search]![Text0]);
The problem here is, the query doesn't return results but if I modifiy the query and I put:
Code:
WHERE Classifications.PartNumber In ("A1C556CC3C-TNNN","C010070H13");
... the query returns the correct results.I'm passing the value incorrect into the sql code?
Have a query which looks up an address using the text input into a textbox by the user.
What I'm now struggling with is getting the query result into the textbox.
Am still new to access and am hoping this is something fairly simple.
how do you take a date from a textbox on a form into an Append query and increase the date entered into the textbox by 1 day.This is what I have so far but not working??
MealDate: [Forms]![FrmSwitchBoard]![txtweekend]=DateAdd("d",1,Date())
I have a two-column list box where the user selects multiple Test Names and Test Measures. Through VBA, I loop through and create a string of the selected items and store into two seperate variables, one for each column. I concatenate with the "In" and some parenthesis to end up with the following:
In(ELA,MEAP,Star Math)
In(DRA, Math, PercentileRank)
I place each of the In statements into two seperate dummy text boxes on the form. Then I point the query criteria to these text boxes.
When I run the query, I get nothing. However, if I copy and paste the In statements above from the text boxes directly into the query criteria, I get the desired results.
I changed the code to create an "Or" statement (e.g. "ELA" OR "MEAP" OR "Star Math"), but still the same issue.
why the query will not read from the text boxes on the Form?
I have built a database with only my department in mind that tracks three types of documents; Which works fine. Now some of my fellow department heads desire to use what I have built.
This is not a problem as their data structure is the same. The difficulty lies in changing the 30 queries. In the queries I hard coded my department number in the receiver (department field) criteria.
Additionally, my HQ is requesting me to perform some analysis on the other department's data. So other than manually changing the department number each time; Can I use a form or report to modify these 30 queries? I attempted to use a combo box but it would not hold the value when I closed the form.
I saw this thread today, http://www.access-programmers.co.uk/forums/showthread.php?t=102036 , but am not sure it will do what I want.
Suggestions welcomed.
Gunner...:confused:
Hi,
Need advise on how to display on my report the criteria that i had specified in the parameter query even if the result is nil.
How can this be done??
Thanks!
I want to create a form that allows a user to enter criteria that will be passed to an existing make table query. suggestions on a user friendly book on Access 2010 programming, I'd be really interested. I'd like to be able to do more with Access 2010.
View 3 Replies View RelatedI can't get my reports' unbound field's to display the criteria parameter I enter when report is opened. I have done this before but not in Access 2010.
In my parameter, I do have a long string inside the brackets (e.g. =[Enter START Date "1/1/14" or Leave Ranges Blank to show ALL]). I am wondering if this or the quotes inside the brackets are causing it not to work.
I have a three-column query that tells me how many hours I have available per week for a given resource type (e.g. welders). I have a second three-column query that tells me how many hours of work I have planned per week for a given resource type.I'm hoping to produce a query (the source for a report) that will show resource types in rows and twelve months in 24 columns. the first column for each month will show how many hours I have available for all my resources, the second column for each month will show how many hours I have allocated.
How do I produce a query that will combine the other two queries, inserting zeroes where necessary considering that for any given week I might have allocated work to a resource that isn't available (because the inconsiderate buggers think they are entitled to holiday) or I might have a resource that has no work allocated (because I'm incompetent)?
Basically, I have a Combo Box [Forms]![Sales]![Site] which a user can use to select either an exact location or a market area for multiple locations.
I also have a query for sales with a SellingBranch field on.
I want the query to display sales for only specific selling branches as chosen using the above Combo box. However, I'm struggling to get it to display multiple selling locations for one selection. For example I want to tell it that if [Forms]![Sales]![Site] is "Bolton MA" then the query needs to show results for when the SellingBranch is either "Bolton" or "Chorley".
At present I can get it to work to show just Bolton but don't know how to adjust it to show Bolton and Chorley when "Bolton MA" is selected.
IIf([Forms]![DatesSalesLeague]![Site]="BOLTON MA","BOLTON","MANCHESTER")
I have a textbox on a form. I am using
Code: =DCount("*","suspend_time_lapse",[Days from Target to Planned End dates]>5)
as the text box source. I get name? or error no matter what i do.
I want to count all records in the [suspend_time_lapse] table where [Days from Target to Planned End dates] is greater than 5.
Hi,
I am in need of help to sort out some records. I have tow existing queries I would like to combine and get one final set of records out of. They go like this:
Query 1. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 x x (check boxes)
Y ACD 2 x x
Query 2. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 8/8/05 9/8/05 10/8/05 (query performs
Y ACD 2 8/8/05 9/8/05 10/8/05 calculations)
What I am looking to retrive through the third query is this:
Query 3. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 8/8/05 9/8/05
Y ACD 2 8/8/05 10/8/05
Where the third query only shows the calculated dates when the check box is true. I have tried to go through the expression builder, but to no avail. I either get all records, like query 2 or I get nothing reported. I am not sure how to limit the records based on the check boxes.
Thanks,
CB
Cannot seem to find an answer to this, but please point me in the correct way if you know of one!
Quite simple i think, but blank mind at moment!
How would i use the values in a table/query as the criteria for another query? I believed i could type in [qryOne]![classification] in the criteria box, but this does not seem to work.
Thanks in advance,
Emily