General :: Select Top 100 In Query Variable Text Box
Nov 6, 2012
i have got a query that returns the top 100 results. i would like to link this to my report form where i have a text box that you can enter a number and the query returns the first of that ammount rather than going into the query everytime to adjust the results.the sql of the query is as follows
Im trying to pass a list box variable in a select query. I understand you cannot pass a variable directly but have to pass it through a function. I may be wrong in this, but whatever I do I cannot get it to work. Here's my code:-
Public Sub GetEquipment() List387.RowSourceType = "Table/Query" List387.RowSource = "SELECT findequipstr() FROM Equipment" End Sub
Public Function findequipstr() As String If IsNull(List371.Value) Then GoTo function_end findequipstr = List371.Value function_end: End Function
If I MsgBox(findequipstr()) within my Getequipment function, the variable is messaged,
I am using Access 2013. I have the ability to pull a selection from a listbox. I can create a Select Sql string using that variable
sql As String, strCompany As String, strWhere As String strCompany = strCompany & Me.lstResource.Column(0, varItem) strWhere = "[Company name]=" & "'" & strCompany & "'" sql = "select * FROM tblResources WHERE " & strWhere
From here I have trouble. I see lots of examples to run an active query but not much on a select query. I have tried a number of things with no success. How to use this select statement to actually run against an existing access table? I am not putting it into a form or report at this time, just running the query to check results.
I have a form that has a textbox in it. I enter text into the text box by scanning a bar code. I then use openreport.cmd to pass the variable in the text box to the report I generate. The report then has a button that when clicked references the variable that I passed to it.
Essentially the problem I am having is that when I set the textbox back to blank, it also erases the contents of the new variable created. I want to either delete the value from the textbox upon scanning the bar code each time, or highlight the value in the textbox for it to be deleted upon the first key stroke. I had read about setting focus to the textbox, but I have been unable to get that to work. Here is my current code for what I am doing.
I have a column which contains "text digit text" as "AAA 222 BBB". The numbers of letters or digits can vary.
I need to SELECT the column which contains digits in a specific interval. For Example I have "DFS 673 JKK" "A 3454 LJLJ" "SD 854 JKLJD"
I need to SELECT the column which contains 600 < Digit < 700 the result of the query in this case would be "DFS 673 JKK" because 673 is between 600 and 700.
(a) The On Open event of my report contains a VBA Sub that assigns a value to a variable named vShow. (Tracking the sub in VBA shows that vShow is correctly being assigned the desired value.)
(b) I then use vShow to try to control a calculation that occurs in one of the text boxes of the detail section in the report
(c) Basically, the control source of the textbox contains (in part) the statement (vShow>[fieldA]), which is embedded in a longer function.
(d) However, when I type this in at Control Source box of the Data tab of the the Property Sheet, Access always substitutes "[vShow]" for "vShow".
(e) So what I get is ([vShow]>[fieldA]), which treats vShow as the parameter of a parameter query.
ANY WAY TO OVERCOME THIS AND HAVE vShow recognized as the variable I defined at On Open? Perhaps there needs to be a variable declaration there, that I don't understand.
The basic form (vShow>[fieldA]) does not seem to be the problem, because I can enter, e.g. (500>[fieldA]) and everything works OK.
I have a database structure with a series of queries which are all inter-linked.
I want my database to be user friendly, what is the cleanest way to be able to enter a single variable eg as a criteria for a query - say the date, rather than having to change it in a series of queries manually, can I call it from somewhere?
Is it possible to use a variable in the first line of the following code?
Code: SELECT DISTINCT [June-11].[Full Name], [June-11].[Level 8 Name] FROM [June-11] WHERE ((([June-11].[Level 6 Name])="SMITH, BOB") AND ((Exists (SELECT * FROM [July-11] WHERE [June-11].[Full Name] = [July-11].[Full Name]))=False));
I have a combo box that lists months and I want to be able to choose which month goes in the SELECT DISTINCT line of code. So, where it says [June-11], I would like it to reference my combo box so I can select the month.
how to do to return a text for each row (as field value) when a table field contains "1" as value ?
for example i have a table named "products" with a field/column called "promotion". Sometime a product is promotional, so in this case, the "promo" column holds "1" as value.
during a select on products table, how can i do to return "in promotion" (e.g.) if the column "promo" holds "1" for a product ?
With the following VBA code, I'm trying to select the text value from a textbox and fill that in a query to select the code from a person. (a password only login).
However, I'm getting a run time error 3078 at the '' Set rs = db.OpenRecordset("strSQL")'' line.
Code: Private Sub Tekst6_AfterUpdate() Dim strSQL As String Dim strBarcode As String Set db = CurrentDb() 'strBarcode = Me.Tekst6 'MsgBox strBarcode
is it possible to use a sum select query in the control source field of a text box. I have a query that works fine in a combo text box but I keep getting a 'syntax error with subquery parenthesis' when I try and build on in a normal text box.
I am building the query using query design view and the query works and then I'm copying the SQL code behind the query into the text box field and access reformats it s a bit so not sure it's that's the reason.
R_P_Data_P = Table approvalNosys = Field in the table status = Field in the table score = Field in the table cmrOverview = Form [approvalNoSys] = field in form 1 is the criteria
Code: SELECT R_P_Data_P.approvalNosys, R_P_Data_P.status, Sum(R_P_Data_P.score) AS SumOfscore FROM R_P_Data_P GROUP BY R_P_Data_P.approvalNosys, R_P_Data_P.status HAVING (((R_P_Data_P.approvalNosys)=[forms]![cmrOverview]![approvalNoSys]) AND ((R_P_Data_P.status)="1"));
I have a query that is returning inaccurate "Date Completed" results. Here is the SQL:
PARAMETERS [Enter Start Date] Text ( 255 ), [Enter End Date] Text ( 255 ); SELECT DISTINCT [Qry BDM FA English Email Test Part 1].ADI_DISTRIBUTOR_ADVISOR, ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_DATE_COMPLETE D, [Qry BDM FA English Email Test Part 1].AGA_EMAIL, ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_EMP_ID, DateAdd("d",180,Date()) AS [Next Update],
I have records using a select query that I am sending to a make table. I would like to have those records excluded from being used again for 180 days, at which point they can be used again. Essentially, I have an ID and an email address which gets stored in the Make Table. I would need to ensure that if we send an email out in Week 1, we do not send an email again for another 180 days if there is activity from that same ID. On day 181, the ID/email address can then be resent.
I need code for save dialog file ,and select the file from textbox and save it to the selected location.i have only this code and i dont know what else i can do with this because it just opens the save file dialog !
I tried to display a variable and constant onto a text box by putting =myvariable onto the data source of the text box. However, Access seems keep threating my variable as a data field and giving me error message. What to do with this?
I am working on a database for my boss that stores the results of monthly file audits and generates reports. Each month about fifty files are checked against a check list and the results are to be entered into the database. Reports are generated quarterly. I have a date field that holds the date of the review. I would like the default value of this field to be what ever was entered in the previous record. That way this field can be tabbed through until the next month. When the data in the field is changed that will become the new default for the following records. I don't have any formal VB or VBA training. I just learn what I need for each project and save it for later use.
Hello, I've got a text box on my form header with a calculation in the control source that adds values from other text boxes on my form. My problem is that since the calculation is in the control source of my text box I can not save the sum of the calculation to a field on the forms bound table. Is there a way to move this calculation out of the control source and still have the calculation populate this text box with the sum of the calculation, so I can choice a field in which to save this calculations sum.
Calculation in the text box control source= [Tot40yrcomp]+[Totfelt1536]+[Totfelt3036]+ there will be many more other text boxes added.
It was suggested to me to have a Variable hold the calculation, I’ve been trying to write a variable but nothing seems to be working, I’ve been trying to place this variable I made into the after update and before update of the text box Nothing works I get all 0.00 in the form header text box, Here’s the code for the variable I made, can some one see if it looks correct.
Dim calcVariable calcVariable = [Tot25yrcomp] + [Me.feltstot] mat25yr = calcVariable
I was wondering if I need to say Me.mat25yr = calcVariable
Hello, I've got a text box on my form header with a calculation in the control source that adds values from other text boxes on my form. My problem is that since the calculation is in the control source of my text box I can not save the sum of the calculation to a field on the forms bound table. Is there a way to move this calculation out of the control source and still have the calculation populate this text box with the sum of the calculation, so I can choice a field in which to save this calculations sum.
Calculation in the text box control source= [Tot40yrcomp]+[Totfelt1536]+[Totfelt3036]+ there will be many more other text boxes added.
It was suggested to me to have a Variable hold the calculation, I’ve been trying to write a variable but nothing seems to be working, I’ve been trying to place this variable I made into the after update and before update of the text box Nothing works I get all 0.00 in the form header text box, Here’s the code for the variable I made, can some one see if it looks correct.
Dim calcVariable calcVariable = [Tot25yrcomp] + [Me.feltstot] mat25yr = calcVariable
I was wondering if I need to say Me.mat25yr = calcVariable
I have requested this before but the example given did not fit my situation and I had a heck of a time trying to adapt the example given to me so I will step by step describe what I need. thanks...
Can someone show me how to do the following:
1) Take a combo box that looks up a table that has 2 variables (Var1 and Var2).
2) Depending on the value chosen in the combobox (Var1) then a text field will automatically have the value (Var2) appear.
3) This information from combo box and text field will then be stored in a separate table that is linked to the form.
Is there a way to extend this example further by adding another combo box that takes the result from the first combo box to filter in values for the second combo box which will then automatically populate a text box which would then be stored in a separate table.
Someone kind of showed me before but they used unbound variables and this confused me as the combobox was unbound but how is that information stored into my table?
Pardon me for my redundancy but this is frustrating me to no end and I unfortunately have a deadline of today!!!! :(
I think it is the row source thing that confuses me. I take row source to mean where the data is stored. But where do you identify where to obtain the data to then store in your "inputed" table?
This is the first time that I have done any major work with forms. After I thought I had finished a problem came up. There are several forms that are use to input information into a table. When the form is opened it grabs an automated number for tracking. The problem is, that if the form is opened and then closed it creates a line of data with all null values.
What I would like to do is have the form open, the user fills out the information, and upon pressing the "save" button, the data will save to the table. I think I need to have each text box write to a variable and then save on the click event. Or I could be completely wrong and need to do something else.
i'm guessing this is something I would learn in Forms 101 if i had ever taken that type of course
Is there a way to use a variable in an SQL statement?
Something like this:
Code:
dim mysql1, newname newname = inputbox("direction on what to do go here?",,) mysql1 = "INSERT INTO FinalAll SELECT newname.* FROM newname;" DoCmd.RunSQL mysql1
Where newname would be a variable assigned from an input box.
I want to populate a single table from several different tables but have the user specify which tables to use.
I'm trying to write code which writes text into text boxes on a form depending of certain content of other text boxes. The names of the text boxes are all very similar
F.i. R1, R2, R3 ...... R12 if the content of these boxes are empty then the content of the corresponding text boxes VR1, IR1, VR2, IR2, VR3, IR3.......VR12, IR12 should also be empty.
In fact I am trying to write something like this
DO UNTIL i=12 if me.R(i).value = "" then me.VR(i).value = "" me.IR(i).value = "" endif LOOP
But this isn't working. The solution below works but isn't a very nice one, writing 12 times the same code
if me.R1.value = "" then me.VR1.value = "" me.IR1.value = "" endif
if me.R2.value = "" then me.VR2.value = "" me.IR2.value = "" endif
