Concatenate String And Variable
Apr 3, 2006
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.
Any help would be appreciated.
View Replies
ADVERTISEMENT
Jul 11, 2014
I am trying to concatinate string in a loop to generate a dynamic SQL to compare 2 tables. But I am not getting the result I want
Code:
Set rs = CurrentDb.OpenRecordset("r1")
Set rs2 = CurrentDb.OpenRecordset("r1_old")
columnCount = CurrentDb.TableDefs("r1").Fields.count
strSQL = ""
For I = 0 To columnCount
[Code] .....
I am trying to achieve something that reads like the following
r1.Field(0) = r2.Field(0) AND
r1.Field(1) = r2.Field(1) AND
r1.Field(2) = r2.Field(2) AND
.....
View 2 Replies
View Related
Jan 9, 2008
Hello
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 ****
I would really appreciate some help.
Thank you :-)
View 6 Replies
View Related
Sep 10, 2013
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.
mFilename = "C:RPR AccessPDF Reports Emailed" & Format(Now(), "ddmmyyyy-hhnnss") & WorkorderID & ".pdf"
DoCmd.OutputTo acOutputReport, mReportName, acFormatPDF, mFilename, True
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.
View 8 Replies
View Related
Jun 19, 2015
How to I get a portion of a string into a variable?
I want to capture the table name of a recordsource.
The problem is sometimes the recordsource is "Select * from mytable where ID = 6" or "Select * from mytable" or "mytable"
I want to put "mytable" in a variable for later use.
View 11 Replies
View Related
Jul 22, 2013
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.
View 3 Replies
View Related
Aug 26, 2014
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
[Code]...
View 5 Replies
View Related
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))
View 3 Replies
View Related
Jul 16, 2013
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?
View 4 Replies
View Related
Feb 26, 2014
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?
View 7 Replies
View Related
Jul 14, 2014
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
View 1 Replies
View Related
Jan 26, 2005
Hi can anyone help me with this query?
'SQLTable "Resolutions Per Year", "SELECT ResName, COUNT(ResName) AS Total FROM Stats LEFT JOIN Resolutions ON (Stats.UserID = Resolutions.UserID AND Stats.ResID=Resolutions.ResID) WHERE Stats.UserId = '"&login&"' AND Date LIKE '%sYear%' GROUP BY Stats.UserID, ResName ORDER BY COUNT(ResName) DESC"
sYear is a variable
example sYear = 2003,2004,2005....
This does not work.
Is there anyway to assign the search string to be a variable?
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
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
Jun 7, 2013
MS-Access VBA code to separate numbers and string from an alphanumeric string.
Example:
Source: 598790abcdef2T
Output Required: 598790
Source: 5789065432abcdefghijklT
Output Required: 5789065432
View 13 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
Mar 12, 2007
Hi all,
I was looking for some help. I am trying to setup a table with a field for web address. People are entering www.website.com etc however I need them to make sure it starts with http:// Is their any way I can put validation on the field to make sure that this is entered? Or maybe I could use an input mask?
Any suggestions would be gratefully recieved.
Andy.
View 3 Replies
View Related
Mar 24, 2006
I have a problem I am working and and am not sure what the best approach is, hoping someone can lend me some insight:I have two fields on a table:SerialNumber....LocationI need to concatenate these two (no problem there). However my situation is such that my serial numbers vary in the number of digits. For example:serial - 55124 (five digits)serial - 552356 (six digits)serial - 5514235625 (ten digits)the serial number field is 10 characters and the serial numbers can be anywhere from one to ten in length (this is external data I am working with, not data I created)When I concatenate the two fields I need the location value to always be in the same place, so if I have a location called - TEST - I need the end result of my concatenate query to place the location always after the tenth place of the serial number position, like this:55124_____TEST552356____TEST5514235625TEST(Without the underscores)Don't ask me why...it's a long story.
View 2 Replies
View Related
Dec 9, 2004
I have a table that I would like to concatenate three name fields:
Last Name, First Name, Middle Name. I inserted a field between each one to create a space when the fields are brought together. The table looks like this:
Last Name, Blank 1, First Name, Blank 2, Middle Name
When these five fields are brought together they can not exceed 25 characters, so the middle name will cut off when the field reaches 25.
Any help will be appreciated.
View 1 Replies
View Related
Aug 27, 2006
Hoping someone can help me with this:
I have a table with two fields that I am wanting to concatenate. The two fields are: Order and Line#. The line numbers are sequential by tens, like this:
10, 20, 30...etc.
So I have order lines that are from two to four digits. Like this:
20
120
1020
All order numbers are six digits. I want to concatenate the order and line (easy to do) my problem is I want to append zeros in front of any line number less than four digits. For example, using the lines above and an order number of 111111 I want the result of the concatenation to be like this:
1111110020
1111110120
1111111020
Sorry to ask...but I don't know how to do it.
Thanks for looking!
View 3 Replies
View Related
Oct 17, 2006
I have two fields in a table.
client_id and property_id
client_id is an autonumber with a format of 0000000 (meaning the first record is 0000001)
property_id is made up from the first 4 letters of and address line (for example Smith Street would be shown as 'Smit')
I am trying to 'concatenate' the two id fields together, via an expression in a query, to get 'Smit / 0000001' but can only seem to get 'Smit / 1'
How can I solve this ?
View 4 Replies
View Related
Apr 6, 2007
My database has FirstName and LastName fields.
I would like to initialize a new field called UserID with the first letter of the first name concatenated to the last name. I have found references that point to using Left([FirstName],1)&[LastName] but am unsure if this can be done in the table definition or if it must be done using a query. Either way I could use some advise on initializing this fields since I have 3500 registered users.
Thanks
View 2 Replies
View Related