Modules & VBA :: Inserting String Variable In SQL Statement
Dec 7, 2014
I am trying to write vba code to enter a form. A am using an SQL statement in vba to enter the form with it.
I want to include a string variable in the WHERE clause of the SQL statement. The string variable is introduced in the DIM statement before coming up to the SQL statement.
The following is the SQL statement. numrukarta is the string variable I want to introduce. I need to know the exact punctuation to be able to insert it into this statement.
StrSq1 = " SELECT Employees.NName, Employees.SSurname, WorkItems.IDcardNo, WorkItems.DDate, WorkItems.EntryTime, WorkItems.FinishTime, WorkItems.Roster" _
& " FROM Employees INNER JOIN WorkItems ON Employees.IDcardNo = WorkItems.IDcardNo" _
& " WHERE (((WorkItems.IDcardNo)= numrukarta) AND ((WorkItems.DDate)=Date()) AND ((WorkItems.FinishTime) Is Null);" AND (Not (WorkItems.Roster) Is Null))
I am trying to set the following variable that will be used to create a PDF file in an already existing folder and name the file WorkorderIDddmmyyyy-hhnnss.pdf
The following variable setting creates the filenameddmmyyyy-hhnnssWorkorderID.pdf.
I just can't figure out how to construct it correctly.
Also, is there a way to dynamically create a folder if the folder doesn't already exist? I currently hard code the folder name but would really like to create the folder name based upon some other variable.
I am currently trying to create an update query (building a SQL String in VBA for a command button click event) to update a variable's value into a table.
Basically, there are 2 tables, displayed in 2 sub froms within the same main form. (OldTable and NewTable for arguments sake)
The basic method I want to implement is that a user highlights a record in the subform of "NewTable". (This value is stored as a variable "NewJPNUM" This value is then to be inserted into the highlighted row (or rows) of table OldTable on command button click.
So the basic idea is a user highlights a row in one table and this value is stored as a variable "NewJPNUM" . The user then highlights a row or rows in "OldTable" and the value from variable "NewJPNUM" is then written to field "NewJPNUM" in "NewTable" on command button click.
I am not experienced with Access but have decent experience in Excel / VBA so not really sure of best practice methods etc.
I am amending some Code I found online for an audit table, I need to store additional information in the table that is associated with some forms but not others.
I have researched about putting optional variables in, but I read this only works with the type VARIANT.
Is there a way to make a string optional as my fields contain text?
here is my code so far:
Sub AuditChanges(IDField As String, UserAction As String, Optional UserID As String, Optional DeviceID As String, Optional SimID As String) On Error GoTo AuditChanges_Err Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim ctl As Control Dim datTimeCheck As Date
I am using a public function to feed a variable string to a query. So far I have got:
Code: Public Function ClientStreetModule(firstLVar As Variant, streetVar As Variant, newFL As Variant) As String Dim cslStr1 As String, newStreet As String newStreet = Right(streetVar, Len(streetVar) - Len(newFL))
[code]....
However, I only need to use newStreet as the true part of iif, in which instance all are longer. At least I think this is the problem. I realise I might need to use NZ but am not sure how. Why it is evaluating and giving errors for all records and not just when the iif criteria is true as I want it to?
I'm trying to run this very very simple code but I get a Run-time error '3450' Syntax error in query. Incomplete query clause. For sure the problem is the variable I used as Table parameter but I cannot understand where my error is :
Private Sub cmdRunCheck_Click() Dim strSQL As String Dim strTempTbl As String strTempTbl = "tblCheckDoubles" strSQL = "DELETE * FROM " & "'" & strTempTbl & "'" CurrentDb.Execute strSQL, dbFailOnError End Sub
What is clear is that if I use this line the Sub is working smoothly
strSQL = "DELETE * FROM tblCheckDoubles"
Please do not tell me that it is not possible to use a variable as Table parameter....
I'd like to expand me tree view automatically when a node matches a string variable from a table. In other words, when a node in a tree matches the given name (variable), the tree will expand all the way down to that name. I guess I need to use "For each node" and when the node and variable match, the tree structure will show up.
I have come up with something like this:
Code: For Each nd In Me.tree.Nodes If nd = level1 Then nd.Child.EnsureVisible Exit For End If Next nd Me.tree.SetFocus
But this only shows the first level of my tree ( btw. I have a 3 level tree). I get lost inside the FOR when I want to make use of another two variables - level2 and level 3
I am trying to build a new sql statement for a query using a string from items selected in a list box on a form. I have the string in place and keep getting syntax errors for the new sql statement. The code is below.
strSQL = " SELECT JG_tbl_LMEMP.DEPT_CODE" & _ " FROM JG_tbl_LMEMP" & _ " Group by JG_tbl_LMEMP.DEPT_CODE" & _ " Where JG_tbl_LMEMP.DEPT_Code IN(" & strCriteria & ")" qdf.SQL = strSQL
Can anyone tell me how I use a variable I have defined within a SQL string.
e.g.
***code start ***
Dim ComputerName as string Dim Sqlstr as string
ComputerName = Environ("ComputerName") Sqlstr = "INSERT INTO ComputerName ("ComputerName") SELECT ***This is where I need help including the variable ComputerName I defined above ****
Hey all, hope this question isn't too newbiefied :D .
I have a database where I must run 10 tests depending on which product I have selected (from a drop box). Each product requires different tests (of the 10) and I am hiding the tests that I do not need after the product is selected. The way I have it set up right now is I have 10 different If statements...one for each test on the "On Change" event.
I have been trying to reduce this down to one If statement nested inside a while loop, but my visual basic isn't very good. I have named the Test buttons Test3- Test13, and want to increment just the number of the test. However, when I try to plug in my concatenated string, I have problems. My code is as follows:
Dim testcount As Integer Dim testcheck as Boolean Dim testnumber as String
testcount = 3
While (testcount < 14)
testnumber = "Test" & testcount If Me.selectedproduct.Column(testcount) = True Me.testnumber.Visible = True Else: Me.testnumber.visible = False End If
testcount = testcount + 1 WEnd
Me.testnumber is obviously not an object on the Form...so I guess my real question is, how do I get visual basic to plugin the variable testnumber so that the code reads Me.Test3.Visible.
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
DoCmd.RunSQL "INSERT INTO " & StrTable & i & sqlstring
Next i
I am not sure how to get the table names stored in the string variables into the SQL statement. When I try the above it looks for a variable named StrTable, not StrTable1, StrTable2 etc.
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.
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 have a form (Datasheet). I need to define some variable in form as string, which can work with any event. E.G in column "A"on event after update, in column "B" on event after update...
I will try to explain with a simple example : form (datasheet) columns: "A", "B"
on event after update in coulmn "A", I could have some like this: (variable what I need to define) = 3
on event after update in coulmn "B", I could have some like this: If (variable what I need to define) = 3 then msgbox "ok" end if
Now I try to work around the problem and use another column "c" to keep the value from after update A - but I know that, it's bad solution -Right?
Anybody see anything wrong the syntax for the below query. I'm trying set up this query to pass a string to 'struser' for multiple users. I'm getting 'Syntax error in FROM clause'. Thanks ! ! !
Dim strSQL As String Dim struser As String struser = "MIKE" Set db = CurrentDb() Set qdf = db.QueryDefs("qryUser") strSQL = "SELECT Salable_Figures.Loan_Originator_Code," & _ "Salable_Figures.Loan_Originator_Name," & _ "Salable_Figures.Lastname_Sort," & _ "Salable_Figures.Team, Salable_Figures.Plan," & _ "Salable_Figures.Monthly_SCountOfLoan_Program_Code AS Monthly_SCount, " & _ "Salable_Figures.Monthly_SSumOfNote_Amount," & _ "Salable_Figures.Salable_Monthly_Goal," & _ "Salable_Figures.YTD_SCountOfLoan_Program_Code AS Yearly_SCount," & _ "Salable_Figures.YTD_SSumOfNote_Amount AS Yearly_SSumOfNote_Amount, " & _ "Salable_Figures.YTD_SSumOfNote_Amount AS Yearly_SSumOfNote_Amount," & _ "Salable_Figures.Yearly_Salable AS Yearly_Salable_Goal " & _ "FROM Salable_YTD_Figures INNER JOIN Salable_Figures" & _ "ON Salable_YTD_Figures.Loan_Originator_Code = Salable_Figures.Loan_Originator_Code " & _ "WHERE Salable_Figures.Loan_Originator_Code)=" & struser & _ "';'" ' Apply the new SQL statement to the query qdf.SQL = strSQL ' Open the query DoCmd.OpenQuery "qryUser"
I have a 3rd party database where I have no control over how the data is entered. I've been given the task of creating a Crystal report that would need to gather data from two databases. The link between the two databases in my report would be on a quote number. In one database, the quote number is in it's own field. In the 3rd party database, the quote number is stored in a memo field along with other data. Here's an example:
The data I'm after is C7875 T7761 T6895 T7152A *No data from the 5th row since the data after the "T" isn't numerical
So far I'm thinking of using an IIF statement to check for the existance of a C or T, then if true, use a nested IIF statement within the first to check for numeric, if true, use the Mid function to pull out the quote number.
My first concern is this could become a complicated IIF statement and was wondering if there was another direction I should be looking in acheiving this.
My second concern is if I go with this method, some of the quote numbers have a space after them, some of them have no space after them, some may even have a "/" after them. How would I go about accomplishing this?
In my statment below the ID is an autonumber. I placed it in the first part ofthe SQL string..but not where I have to variables referenced...
I have to add it to the SQL statement...Its not a varible although I can make it one by getting the largest number nad adding one... Do I have to make it a non-autonumber and do it like I said above
Or is there another way to treat the autonumber (ID) in the SQL INSERT INTO statement...
