I have a query that has three fields: 1) Days 2) Bit Size 3) Feet. The Days field has a date range specified in the Criteria that comes from two unbound fields on a form. I have a second query based on the first: Two fields are Days with the Total as Min and Max. Three fields are Feet with the Total as Min, Mac, Avg. One field is Bit Size with the Criteria as a specified bit size (i.e. 6.125). These queries are used on a report, and all data is inputted from forms prior to the report.
The queries work as expected with a specified bit size. My problem is that while Bit Size is normally chosen from a list of sizes, a custom size can be inputted. I am unsure how I can specify the Criteria in Bit Size to be a custom size..? To compound things further, more than one custom size can be used.
A) I need a way to pick out the custom sizes from the first query and B) use them as the Criteria for the second query. If I use VBA, I assume I could complete B) by using a WHERE clause (if the custom size(s) has been defined)...
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 wan't to create special query who show only special package. It must work that.
1. Client logg in. System rember his chose. varPrawa="Client" and varIDLogin=5
2. Client click at button "Show Your Package". At that moment query haven't got any criteria so it show every package but it must show only package with IDClient=varIDLogin so only package with IDClient=5 it is easy to create. I tell more it is still easy to join other person but only with varPrawa="Client" i want to create one query who must select by.
WHEN varPrawa="Client" then show only package with IDClient=varIDLogin
WHEN varPrawa="Kurier" then show only package with IDKurier=varIDLogin
WHEN varPrawa="Spedytor" then show only package with IDSpedytor=varIDLogin
WHEN varPrawa="Administrator" then show all package without varIDLogin criteria.
I create subraport who use chosen package in my list and display all special data using varaible but my problem is how to create query whose can select and display only package of that Client or Kurier or Spedytor.
At the end
When Client is online he can see only his packages special IDClient (any IDKurier, IDSpedytor) When Kurier is online he can see only his packages special IDKurier (any IDClient, IDSpedytor) When Spedytor is online he can see only his packages special IDSpedytor (any IDClient, IDKurier) When Administrator is on line he can see all packages (any IDClient, IDKurier, IDSpedytor)
I am trying to use a criteria to filter out nulls in concatenated variable, which slows the query considerably.The part that makes it slow looks like this -
WHERE ... AND (([tbl1].[x] & [tbl1].[y]) <> "") AND ...
If I work them individually, it's not a problem, the query is fast, but I have to combine them first in order to produce the correct output.
I am trying to copy the parameter value that is entered to a variable so that it can be used to run other queries without typing it in over and over. Once my form is finished it will run 4 queries, and the current setup is that I would have to retype the parameter each time.
In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.
So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.
I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.
VBA in the loadform 'Application.TempVars.Add "varcountryselect", "*" SELECT in the listbox "lstlocationsperproject" SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);
VBA in the combobox Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0) Me.lstlocationsperproject.Requery
The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.
Any hints, how I have to use the * for getting the complete list on the initial view ?
I am creating a 2 level report to confirm an order. Main report already created, runs successfully called as subform/subreport under "OrderDetails" form. Linked to master using Order.ID. There are two versions of the confirmation report that have different layouts for different program types.
The hangup comes when I try to add a "Class Dates" subreport. It lists dates of individual classes and Skip dates. I have created the subreport as "srClassDates". When I add it to the main report, it lists the records. However, when I try to link it to the Main report, an error message box appears with the "object variable or With block variable not set".
I have tried rebuilding both the main and subreports, rebuilt the query, have not found anything that changes the result.
Linker has been working successfully on other subforms. Report with groupings works fine, but I need data from 2 tables both linked to order.id.
Ok, so now this is where it's getting more complicated... (though I guess this is where you guys usually start)
When you have a query, and you type in your inputbox thingy like so: http://reenen.fateback.com/image.jpg
PeriodEnd: [Enter period end date dd/mm/yyyy: ]
And you can have multiple, then it will prompt you for several of them.
Now this is not very tidy, so I'd rather do it on a form. How can I display the form as the query/report runs, and insert the values entered into the form as the inputs into my variables? (This case PeriodEnd)
Which continues as is for 10 or 11 queries named in a similar pattern, then prints a report. However, as the number of append queries is not set (they are generated in a different quantity for different purposes, it's complicated), I am not sure how to check how many there are with that style of name.
I use access 2010. I have a form that allows the user to assign products to operations. Each operation can produce multiple products and each product can be produced at multiple operations. I manage this many-to-many relationship through the use of a third table that stores primary keys for associated products / operations.
tblOperation tblProducts tblOperationProducts
The query im trying to design will populate a listbox on the form that contains products which are available to be added to a given operation. In addition to the listbox there is also a combobox that allows the user to select the operation they want to add products to. As the user selects an operation, the query behind the listbox should return records for all products except the ones that have already been assigned to the operation selected in the combobox.
So far I've been able to make the listbox return all products always or return products that haven't been assigned to any operation at all, but I'm struggling to formulate the logic in my mind on how to show only products that have no association (through the tblOperationProducts table) with the operation selected in the forms combobox.
This is the query I have right now, it's not working:
Code: Select tblProducts.ProductID From tblProducts EXCEPT SELECT tblProducts.ProductID FROM tblOperation RIGHT JOIN (tblProducts RIGHT JOIN tblOperationProductMM ON tblProducts.ProductID = tblOperationProductMM.ProductIDMM) ON tblOperation.OperationID = tblOperationProductMM.OperationIDMM WHERE (((tblOperation.OperationID)=[Forms]![frmOperationProducts]![cboOperation]));
Error 91 - Object variable or With block variable not set
I am getting this error telling me that an object variable is not set.
I know which variable it is but when I step through the debugger it sets the variable and all is fine? Issue is that public variable of a class is not getting set when the VBA Editor is not open?
This code runs fine the FIRST time, however trows up a message the SECOND time it is run.
The error is on the line ".Range"
I am trying to sort records which have been exported to Excel.
Dim LR As Integer LR = 5 Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True Set wbRef = xlApp.Workbooks.Add With wbRef
wbRef.Activate .Worksheets("Sheet1").Activate With ActiveSheet .Range("A2", .Cells(LR, "O").End(xlUp)).Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlYes End With end With
I am trying to write a query from a main table which will show records which have a date of less than or equal to a Variable (tempvar), however the variable (selected from a form) may be left blank in which case all records should be shown.
I have successfully used the following but the records returned are only the specific date choosen not that date and all prior
Like Nz(([TempVars]![tvcldate]),"*")
So I have tried the following but it doesn't work.
I have two reports running off of the same crosstab query. I copied one report to make the second report, then modified the second report to change the background of column fields satisfying certain conditions. These lines of code were added to the Detail_Format section, in color below. The report with the extra code lines does not error--the original report errors.
Code: Option Compare Database ' Constant for maximum number of columns EmployeeSales query would ' create plus 1 for a Totals column. Here, you have 9 employees. Const conTotalColumns = 11
[Code] .....
If I say OK (rather than debug) after the error message, I can then click the button for the report again and it runs without complaint. And, as I said, the report with the added code never errors.
I am using a query with a variable which select what records I want to use.
When I use the query from the query above it ask me for the variables again.
I do not want to input the variable again because I have the records I need but must use the 1st query for the secord query to build what I need.
How can I use the first query to build the 2nd query and not have to answer the variable input on the 2nd query? I already have the records needed.
Trust me, I need to build the 2nd query with the 1st query in order to do what I need to do. The first query select the dates I need, then the second query sums only the month and year and not the day of the month. However, I am asked for the dates again. I do not need for the user to put the dates in again. If you know how to do the operation all in one query, that would be great.
I am looking for the vba to have a query cycle through using records from another query as the parameter run and export for each of the records on the second query.
Table 1 has information with all clients query 1 has x, y, z being pulled > client used as parameter query 2 has the list of clients
Currently I have the coding to run the query and export however I am not sure how to get it to repeat for each client.
Code: Private Sub CmdInternalReports_Click() Dim xlApp As Excel.Application Dim rstDetails As DAO.Recordset Dim strTab As String Dim strDir As String Set xlApp = Nothing Set rstDetails = Nothing
[code]...
There can be upto 70 different clients or as little as 2 depending on the day. So the Query I have takes a list of the clients that receive the report and runs it against the imported table to only give the ones with data day.
PHP Code:
tblClientReport.Distro
Is where I have the report via client name linked the the distro list that should be pulled.
Hi, I am totally out of my depth on this one. I have managed to find ways to solve my other problems but this one won't seem to budge.
I have one large table ALLENGPUMP which has columns of data and other columns of lookups to associated tables. What I am trying to achieve is that by selecting a value from a combo box (which contains the results of: SELECT DISTINCT ALLENGPUMP.Coupling_Order_code FROM ALLENGPUMP; ) a list box will show all of the other columns from the table (and the associated tables).
In lstOther3 and lstOther4 the columns sought are in the same table and this will produce a result of multiple rows when the Coupling_Order_Code is apparent for more than one ID, as required.
lstOther5 contains the columns with lookups and only works when the Coupling_Order_Code returns only one ID. When more than one ID is returned it no longer functions and returns nothing. The best I have so far is shown below (this is from VBA) although lstOther5 only has one of the columns I am looking up whereas there are 5 in total for the data in 5 other tables.
So, to recap, the user chooses one of the list of Coupling_Order_Codes, this returns the ID for all rows that contains the Coupling_Order_Code. From these IDs I want a list box to show the data held in all of these rows, including the lookups to the associated tables.
Dim GvarCPLG_Ord As String
Private Sub cbCPLG_Ord_AfterUpdate() GvarCPLG_Ord = Me.cbCPLG_Ord
Me.lstOther3.RowSource = "SELECT DISTINCT ALLENGPUMP.ID, ALLENGPUMP.DRW_ID, ALLENGPUMP.FW_No, ALLENGPUMP.FLANGE_MOUNTING, ALLENGPUMP.SHAFT_DETAILS, ALLENGPUMP.SHAFT_LENGTH FROM ALLENGPUMP WHERE (((ALLENGPUMP.Coupling_Order_code)=""" & GvarCPLG_Ord & """))" Me.lstOther3.Requery Me.lstOther4.RowSource = "SELECT DISTINCT ALLENGPUMP.ID, ALLENGPUMP.DRW_ID, ALLENGPUMP.COUPLING_REF, ALLENGPUMP.Coupling_Order_code, ALLENGPUMP.DRAWING_No FROM ALLENGPUMP WHERE (((ALLENGPUMP.Coupling_Order_code)=""" & GvarCPLG_Ord & """))" Me.lstOther4.Requery Me.lstOther5.RowSource = "SELECT ALLENGPUMP.ENGMOD_ID, ENGMOD.ENGINE_Model FROM ENGMOD INNER JOIN ALLENGPUMP ON ENGMOD.ID = ALLENGPUMP.ENGMOD_ID WHERE (((ALLENGPUMP.ID) = (SELECT ALLENGPUMP.ID FROM ALLENGPUMP WHERE (((ALLENGPUMP.Coupling_Order_code)=""" & GvarCPLG_Ord & """)))))" Me.lstOther5.Requery End Sub
Any help at all would be very, very, very much appreciated. Thanks, Brodie
Is is possible to use the "LastOf" function to show the last (n) records in a query? Where (n) is asked for each time.
Example query show me the last 3 records...
SELECT tblEvent.eventID, tblEvent.event, Last(tblTransaction.transaction) AS LastOftransaction FROM tblEvent INNER JOIN tblTransaction ON tblEvent.eventID = tblTransaction.eventid GROUP BY tblEvent.eventID, tblEvent.event;
Basically I'm trying to filter records from a view in my listbox based on a value in a text box. As such...
Private Sub Form_Open(Cancel As Integer)
Me.SearchResults.RowSource = "SELECT * FROM PatientSearch WHERE Surname = " & Form_frmMain.txtName
End Sub
**Note that this the value I'm trying to pull is from a seperate form. My statement works fine as long as I don't use a variable ie. Me.SearchResults.RowSource = "SELECT * FROM PatientSearch WHERE Surname = 'Agnew' "
I'm sure it may be a simple syntax problem but I fairly new at this. Any help would be appreciated.
