SQL Query Results In A Variable
Apr 11, 2008
I've got such a simple SQL query, but it won't output the friggin results of the query!
I put the query in a "Query" in access, run it... and it outputs me a integer value. I'm running the following VBA code in "Form Load"
Private Sub Form_Load()
Dim strSQL As String
strSQL = "SELECT Count(*) FROM ContactDetails;"
MsgBox strSQL
What it does is on form load instead of popping up a msgbox saying 41 or whatever the integer value of that count query should be.... it outputs the query itself:
SELECT Count(*) FROM ContactDetails
Any ideas on how I can get the SQL query to run as a query and output the value into the msgbox?
View Replies
ADVERTISEMENT
Jan 20, 2014
I have a query which returns a list of reports I have created this session.
I want a button that when I click it runs the query and then stores the results into a variable.
I looked online and found a pretty good connection code and loop code to get the data but I'm having a hard time getting it to store into a variable.
What I want to do next is using code print the reports that were listed in the query.
That Means I need to be able to take that variable and pop the information off it, storing it into another variable (or if i can use an array just use that) and use that to concatenate into a command to print that report.
I am having problems with the syntax of this though. I think I'm just missing some key elements.
View 3 Replies
View Related
Aug 9, 2006
I'm trying to run an SQL statement to Sum the values of a table column. No problem. It would be useful for me to take that total and be able to set it equal to a variable within my VB code that's running, I just don't know how to do that, I'm very new to VB programming.
I cannot find help anywhere on the internet for this. :( I'd like to do something like this, just don't know how:
VariableName = Run.SQL ("Sum(tblName.Column) AS SumOfColumn FROM tblName")
I'm not a programmer by trade, so I am picking up things piece by piece, and just cannot find my way out of this task. Thanks for your help guys.
View 1 Replies
View Related
Feb 8, 2008
OK, this is a stumper.
I'm trying to determine if an employee changed departments in 2007.
We maintain a table that records every time an employee transfers, giving an effective date of the change. But, this table also records transferring to a different shift without a department change.
So, my logic was to look at what an employee's most recent assignment was as of 12/31/06 - this way, even if the employee's last transfer was June 3, 1827, it would still be picked up.
Then, I would see if there was any department change in 2007 by comparing any records recorded in this table in 2007 with the list from 2006 and pulling out those records that had different departments.
My first attempt was to use a query for 2006 and earlier with totals, grouping on the employee ID and grabbing the Max Effective date along with the department ID (org). Like so;
SELECT dbo_assignment_log.ppms_ID, Max(dbo_assignment_log.effectivedate) AS MaxOfeffectivedate, dbo_assignment_log.workingorg
FROM dbo_assignment_log
GROUP BY dbo_assignment_log.ppms_ID, dbo_assignment_log.workingorg
HAVING (((Max(dbo_assignment_log.effectivedate))<=#12/31/2006#))
ORDER BY dbo_assignment_log.ppms_ID, Max(dbo_assignment_log.effectivedate);
The result looked like this;
ppms_IDMaxOfeffectivedateworkingorg
289874/12/20022116
2898711/29/20062121
289885/31/19952116
289888/7/19962124
289896/1/20042114
2898911/22/20052176
289901/25/20022116
2899012/18/20022122
2899112/4/19922124
2899111/26/19972122
289919/27/20002155
289929/23/19932128
289937/27/20052134
All well and good. But, then I decided that I didn't want the org in the list since it was giving me extra records (because it was on Group By), so I took it out and ran the query again like this;
SELECT dbo_assignment_log.ppms_ID, Max(dbo_assignment_log.effectivedate) AS MaxOfeffectivedate
FROM dbo_assignment_log
GROUP BY dbo_assignment_log.ppms_ID
HAVING (((Max(dbo_assignment_log.effectivedate))<=#12/31/2006#))
ORDER BY dbo_assignment_log.ppms_ID, Max(dbo_assignment_log.effectivedate);
Much to my surprise, some records were lost.
ppms_IDMaxOfeffectivedate
2898911/22/2005
2899012/18/2002
289919/27/2000
289929/23/1993
289937/27/2005
289948/4/2004
289985/18/2005
289999/29/1999
290006/7/2000
290022/4/2005
290038/24/2005
290045/8/2002
290096/29/2005
What might cause it to drop, for example, the entries for IDs 28987 and 28988?
There are a lot of other fields in the table, but, as far as I know, they shouldn't matter.
Any ideas?
View 2 Replies
View Related
Oct 31, 2013
I am trying to figure out if this line of code is usable, but I don't know: DoCmd.RunSQL ("SELECT Max([BSRangeTbl].[Date]) AS MAXofDate FROM [BSRangeTbl];")
View 2 Replies
View Related
Apr 16, 2013
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.
View 2 Replies
View Related
Jul 8, 2013
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?
View 14 Replies
View Related
Oct 3, 2014
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
View 3 Replies
View Related
Apr 1, 2008
I used to queries ,1 to get items that are taken ( its all about sign in sign out for equipment) and other query is list of all items.
How can i make 3rd query which will give me all but taken items from query1?
(of course items from query 1 are in query2)
thx in advance
View 7 Replies
View Related
Aug 20, 2014
I look at a lot of files to see when they were last updated. I wanted to write a generic procedure to manage that so ..
Code:
Public fDate As Variant
Public vField As String
Public vFile As String
'GTSdata
vField = "txt_gts_data"
[Code] ....
What I hoped Me.vField would do is update the date field [txt_gts_data] on my form with the date the file was last saved.
i.e. me. txt_gts_data = fDate
What actually happens is the variable vfield gets updated from "txt_gts_data" to 19/08/2014 then later code falls over because the fieldname is lost .
Me.[vField] corrects itself to me.vField (and does not work)
Me!vfield falls over (cannot find the field vField, not surprising J)
How do I say update the contents of the variable, not the variable itself?
View 7 Replies
View Related
Apr 15, 2015
Runtime error '91'
Running Access 2010.
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.
View 4 Replies
View Related
Jul 4, 2005
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.
Thanks,
Don
View 1 Replies
View Related
Jul 21, 2005
Is it possible to use variable in a query criteria?
For example: DateAdd Func has 3 arguments (Arg. 1, Arg. 2, Arg.3) -> Arg.2 is Integer.
I want Arg.2 to be a variable and its value come from a Txt Box placed on a Form.
Thanks!
View 3 Replies
View Related
Aug 10, 2015
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.
View 14 Replies
View Related
Jun 29, 2005
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
View 4 Replies
View Related
Jun 5, 2006
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;
View 9 Replies
View Related
Oct 30, 2007
Hi All,
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.
Thanks
View 2 Replies
View Related
Feb 5, 2008
hello every one,
first, im apologize for my English.
I have form that runs until the user answer the all the questions.
I place the last question Id in listbox. do I can place the result of the query (max of question id) in a variant in VB or I must place it in listbox/combobox/textbox?
Tsur triger.
View 14 Replies
View Related
Dec 15, 2004
i am having trouble with my where clause.. ca someone take a peek please..
Note: gID is defined globally as integer and tblCheckReq.ID is defined as an auto-number (in table). Thanks in advance!
SELECT tblCheckReq.UserName, tblUsers.UserDept, tblCheckReq.PaymentMethod, tblCheckReq.PaymentDueDate, TRIM(tblCheckReq.Company & " " & tblCheckReq.TaxType & " " & tblCheckReq.PaymentType) AS DISTDETAIL, tblCheckReq.TaxYear, tblStateTaxDepts.To, tblStateTaxDepts.Street, Trim(tblStateTaxDepts.City & ", " & tblStateTaxDepts.State & " " & tblStateTaxDepts.Zip) AS FULLADDR, tblCheckReqAmtsToAccts.GLAccountNo, tblCheckReqAmtsToAccts.Amount, tblCheckReq.ID, tblCheckReq.Approvedby, tblUsers.SalaryCode, tblCheckReq.Date, tblStateTaxDepts.Dept, tblStateTaxDepts.To, tblCheckReq.TaxYear, tblUsers_1.SalaryCode, tblCheckReq.Company, tblCheckReq.TaxType, tblCheckReq.PaymentType
FROM (((tblCheckReq INNER JOIN tblCheckReqAmtsToAccts ON tblCheckReq.ID=tblCheckReqAmtsToAccts.ID) INNER JOIN tblUsers ON tblCheckReq.UserName=tblUsers.UserName) INNER JOIN tblStateTaxDepts ON (tblCheckReq.TaxType=tblStateTaxDepts.TaxType) AND (tblCheckReq.State=tblStateTaxDepts.StateAbrv)) INNER JOIN tblUsers AS tblUsers_1 ON tblCheckReq.Approvedby=tblUsers_1.UserName
WHERE (((tblCheckReq.ID)='" & gID & "'));
View 4 Replies
View Related
Mar 1, 2006
I have a saved Query that returns a single line and a single field. I want the data in that field to be a VBA var, or straight into a field in a different table.
And Ideas?
View 2 Replies
View Related
Aug 29, 2006
We're using MS Access 2003 on a MS Access 2000 database file. There are several queries, tables, forms within the database. When we dclick a form the "Enter Parameter Value" dialog box appears requesting input for "Enter Job ID#". The dialog box appears to be popping up due to settings within another query that is systematically called. We oblige and are taken to the results. When we click a dropdown box it is listing all the items in the database but it isnt filtering the contents based on the earlier provided parameter value as we'd hoped.
If I modify enter Design View and modify the dropdown box's RowSource SQL statement to include a WHERE clause all is fine but the provided value is fixed as opposed to a variable.
Is it possible to use the parameter value input from the dialog box as a variable for my dropdown box RowSource? I;ve already tried [MyExternalQuery].[Enter Job ID#] as a variable but no luck -- i just get another dialog box asking for input.
Im new to Access.
View 2 Replies
View Related
Sep 19, 2013
I have a form in which am gathering information from the user to populate a table(Customer Master List) and at the same time (btn_Copy_Click Event)transferring the info to another form(Case) populating another table(Customer_Call). It is working fairly well, My issue is that before transferring the info I need to run a check(SQL Query) to make sure the customer or actually the Well ID don't exist in the Customer_Call table. I am trying to do this in pieces .....
1) capturing the well_Id in a variable(WellID) in the first form and using that to build the sql string and query the Customer_Call table.
2)once that works place it in a if / else clause to copy or not with appropriate messages
With that I am stuck in step 1
It works up until Set rst = CurrentDb.OpenRecordset(strSQL) the i get Run-time error '3061' Too few parameters. Expected 1.
Code:
Dim WellID As String
Dim strModel As String
Dim strSQL As String
Dim rst As DAO.Recordset
WellID = Forms!f_Customer_Lookup.Well_ID
MsgBox WellID ' testing to see if it picks up the correct box in form
strSQL = "SELECT Customer_Call.[Cus_Well_ID] " & _
"FROM Customer_Call " & _
"WHERE Customer_Call.[Cus_Well_ID] = WellID;"
Set rst = CurrentDb.OpenRecordset(strSQL)
strModel = rst!Cus_Well_ID
rst.Close
MsgBox rst ' Testing to see if the strSQL captured the data
Set rst = Nothing
End Sub
View 8 Replies
View Related
Aug 18, 2013
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)...
View 6 Replies
View Related
Apr 23, 2006
I have a networked database. It is accessed with computers that have both Office 2002, and office 2003. I get the following error message on computers with Office 2003:
"Object variable or With block variable not set"
It happens occasionally and the problem generally fixes itself so I dont think there is anything wrong with my coding.
And when it happens on the computers with office 2003 the ones with office 2002 can open the database fine.
What can be causing this?
Thank you in advance
View 1 Replies
View Related
Dec 14, 2006
Good afternoon all,
The following block of code was working great until I came into work this morning. This routine is called from the OnClick event of several combo boxes. When it does, I recieve the error listed above (Object variable or With block variable not set) on line:
If Me.cbxAss_Filter <> "All" Then
Any ideas? I'm running on no sleep in the last 24 hours so I'm sure I'm missing something simple
The code itself checks the contents of a form and builds a string of conditions which I apply to the Form's filter property to filter records.
Code:Public Sub CreateFilter() '************************************************* **** 'Name: CreateFilter 'Purpose: Generate a string to filter the form 'Inputs: None 'Outputs: None 'Instigates: Me.Filter ' 'Updated: 11/30/06 'By: Chris Lounsbury '************************************************* **** 'Vars Dim strFilter Dim lngLength As Long strFilter = Null strFilter = "" 'Each filter box has its own check for contents If Me.cbxAss_Filter <> "All" Then strFilter = "assigned = '" & Me.cbxAss_Filter & "' AND " End If If Me.cbxAction_filter <> "All" Then strFilter = strFilter & "action = '" & Me.cbxAction_filter & "' AND " End If If Me.cbxStatus_filter <> "All" Then strFilter = strFilter & "status_rsrch = '" & Me.cbxStatus_filter & "' AND " End If If Me.Combo34 <> "All" Then strFilter = strFilter & "rims_flags = '" & Me.Combo34 & "' AND " End If If Me.cbxAAMB_filter <> "All" Then strFilter = strFilter & "aamb = '" & Me.cbxAAMB_filter & "' AND " End If 'Check if filter string was built If strFilter = "" Then Me.FilterOn = False Else 'Determine length of Filter String 'minus the trailing ' AND' lngLength = Len(strFilter) - 5 'Chop off ending ' AND' and set the form filter If lngLength <= 0 Then Else Me.Filter = Left(strFilter, lngLength) Me.FilterOn = True End If End If 'debug 'MsgBox (strFilter) End Sub
View 1 Replies
View Related
Aug 9, 2005
Hello,
I have the following query that I set up as a test, and it runs fine:
SELECT STATUSHISTORIE.*
FROM STATUSHISTORIE LEFT JOIN PROBLEM_DE ON STATUSHISTORIE.PROBLEM_ID = PROBLEM_DE.PROBLEMNR
WHERE (((STATUSHISTORIE.STATUSDATUM)<#1/1/2005#) AND ((PROBLEM_DE.DATENBEREICH)='SPMO') AND (((Left(([PROBLEM_DE].[MODULZUORDNUNG]),InStr([PROBLEM_DE].[MODULZUORDNUNG],"-")-2)))='K29') AND ((PROBLEM_DE.ERLSTAND)<>"WEIF"))
ORDER BY STATUSHISTORIE.PROBLEM_ID,
STATUSHISTORIE.STATUSDATUM;
I then set up two global variables ( a String and a Date) and respective functions to return them – ReturnE( ) and ReturnKW( ). Now my query looks like this, but takes ages to run:
SELECT STATUSHISTORIE.*
FROM STATUSHISTORIE LEFT JOIN PROBLEM_DE ON [STATUSHISTORIE].[PROBLEM_ID]=[ PROBLEM_DE].[PROBLEMNR]
WHERE (((STATUSHISTORIE.STATUSDATUM)<ReturnKW( ) ) AND ((PROBLEM_DE.DATENBEREICH)='SPMO') AND (((Left(([PROBLEM_DE].[MODULZUORDNUNG]),InStr([PROBLEM_DE].[MODULZUORDNUNG],"-")-2)))=ReturnE( ) ) AND ((PROBLEM_DE.ERLSTAND)<>"WEIF"))
ORDER BY [STATUSHISTORIE].[PROBLEM_ID], [STATUSHISTORIE].[STATUSDATUM];
My two public functions that return the global variables look like this:
Public gstrE As String 'global variable: contains E used for query
Public gdatKW As Date
Public Function ReturnE ()
ReturnE = gstrE
End Function
Public Function ReturnKW ()
ReturnKW = gdatKW
End Function
The tables are actually Views set up from an ODBC Data source. Can anyone please tell me why these global variables are causing the traffic jam? :)
Thanks in advance
J
View 6 Replies
View Related
