Assigning A Variable To "LIKE" Condition Search String
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 Replies
ADVERTISEMENT
Jul 19, 2013
Is the following possible? If so - how?
I have a series of several hundred variables being assigned values.
If the value assigned to the variable results in an error, I would like to assign a default value to the variable which caused the error.
How do I reference the variable, or line (or any info I can work with), which caused the error?
View 2 Replies
View Related
Mar 17, 2015
I have a query which working fine:
Code:
strSQL = "Select [nazwa], [id_wycena_pre], [Format_(X)], [Format_(Y)], [kolory p], [kolory t], [nazwa_id], [iloscstron], [numerarkusza], [naklad_pracy], [Kolory P], [Kolory T] from tblGoraZleceniaNowaWyceny where [id_wycena_pre]=" & Forms!frmWycenyObszarRoboczy!ID_wycena_pre & " and [nazwa]='" & "środek" & "' or [nazwa]='" & "okładka" & "'"
Set rst = CurrentDb.OpenRecordset(strSQL)...
But now I would like to add next condition to this query
Code:
where [kolory p] is not null or [kolory t] is not null
- these fields are string
I don't know how to write this condition and add to my exiting strSQL string
View 4 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
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 2 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
Oct 27, 2006
I have a column called CPU_S within a table called workstation that contains sample text like P111 933
I want to use the update command to search the CPU_S column for entries that contain this in there string then add P3 to a column called CPU_N
So far I have the code below but I don't know how to search a column entry for a specific string within a string. Can this be done and how?
UPDATE workstations SET CPU_N = "P4"
WHERE CPU_S = ;
View 3 Replies
View Related
Mar 25, 2006
I'm trying to create a query that searches the string in a field, for a substring that the user specifies when the query is run. This is the SQL code:
SELECT tblStatement.Date, tblStatement.[Payment Type], tblStatement.Details, tblStatement.[Paid Out], tblStatement.[Paid In]
FROM tblStatement
WHERE (((tblStatement.Details) Like "**"))
ORDER BY tblStatement.Date;
between those two asterixes on the WHERE line, I'd like a string that the user has been prompted for. I've tried this:
WHERE (((tblStatement.Details) Like "*[Enter a String]*"))
But that fails to work, I guess because its in quotations. If I simply had Like [Enter a String], then I get the prompt, but I'll need to enter a string which exactly matches what I am looking for. In other words, I need a query which will search say, "abcdefg" for the user defined input: "abc", which will return that result. I just dont know how to get this to work :s
View 1 Replies
View Related
Sep 11, 2006
I have been hunting around to find exactly what I need but cant seem to find it...
what I need is a way to do a string search within a particular field (surname) and then filter by that string?????
Any ideas or point me to the right thread will be much appreciated.
View 1 Replies
View Related
Apr 11, 2006
I have a table containing names. Unfortunately, the first and middle name are in the same field, and not all have middle names. I need to separate them out in a report. Is it possible to search the field and break at the first space?
Any help would be greatly appreciated!
Thanks
View 1 Replies
View Related
Apr 25, 2007
Hi, I have a command button that opens a pdf file. However i will be distributing the database as a runtime package and need to account for people having different versions of adobe reader so need to search for the filepath string where Acord32.exe is found. This is the code I have but I am stuck! Ay help aprpeciated!
Private Sub Command4_Click()
Dim stAppName As String
Dim stPathName As String
Dim fs As Object
Set fs = Application.FileSearch
With fs
.LookIn = "C:Program Files"
.SearchSubFolders = True
.FileName = "AcroRd32.exe"
.Execute
????? stAppName = .FoundFiles
Set fs = Nothing
End With
'specify path name for version of adobe acrord32.exe
'stAppName = "C:Program FilesAdobeReader 8.0ReaderAcroRd32.exe "
stPathName = GetIniSetting("C:WINDOWSSSI_DL3_PROGRS.ini", "DIR", "REPORT_FILELOCATION") & "HOOF.pdf"
Shell stAppName & stPathName, vbMaximizedFocus
End Sub
View 1 Replies
View Related
Sep 11, 2012
I have the following in Column A
url.com?valueA
url.com?valueA&valueB
url.com?valueC
I need a function that
If string "ends with" valueA or ValueC, then replace it with ValueD
View 2 Replies
View Related
Feb 2, 2012
I need to find a way to search through all of the queries in my Access 2007 database to change the names of more than a few variables. There are close to 900 queries I need to search through many of which may use the variables as criteria, so if I miss even 1 instance I might screw myself completely.
I found this post and it tells me exactly what I need to do. Only problem is the post was written in 2002, and I've followed the below instructions only to have it not work.
As a contractor working predominantly on databases I haven't developed, it can be a daunting task trying to find references to tables, fields or functions within a list of several hundred queries.
The following code is attached to a command button on a simple form listing all queries in the database. The record source for the form is
SELECT DateCreate, Name FROM MSysobjects WHERE Type=5 ORDER BY DateCreate DESC
There is a text box where the user enters the text to search for. The code builds a table containing the SQL string of every query, then searches for the required text, and displays those that match.
Your form also needs a procedure the set the record source back to the default.
There are off-the-shelf applications such as Speed Ferret which perform this sort of function, however some employers are too cheap to purchase them!
Code:
sub cmdFilter_Click()
Dim db As Database
Dim rs As Recordset
Dim rsFilter As Recordset
Dim tdf As TableDef
Dim strSQL As String
Dim strQdf As String
On Error GoTo ErrorHandler
[Code] ....
View 11 Replies
View Related
Mar 25, 2014
I need to loop through text files and replace the new lines following the from, to, copy and subject fields with tabs and place the associated text on the same line as the from, to, copy and subject fields.
I start out with this. See below.
From:
ABC COrp@abc.com
To:
XYZ Inc.@xyz.com
Copy:
Me@abc.com; Myself@abc.com; Irene@xyz.com
Subject:
NOthing much
I need it to be like this.
From:ABC COrp@abc.com
To:XYZ Inc.@xyz.com
Copy:Me@abc.com; Myself@abc.com; Irene@xyz.com
Subject:NOthing much
How can I do this in access?
View 14 Replies
View Related
May 20, 2014
I am trying to write some code to search for similar strings. I am creating a database with records that all contain street addresses. These addresses may have more than one record attached to it, and we would like for folders to be created containing the records with similar street addresses. Problem is, all the existing records are from an excel spreadsheet that did not contain any data validation, so there are several instances of:
123 Street
123 st
123 street job 1
123 st job 2
etc....
So I am trying to write code to prevent this from happening in the future, by searching the database for a similar street address and asking the user if this is the address they are trying to enter. I have been trying to do this with the DLookUp function, as such:
Private Sub ProjectName_AfterUpdate()
Dim stLink, pName As String
pName = Me.ProjectName
stLink = DLookup("[ProjectName]", "tblMaster", "[ProjectName] LIKE '" & pName & "%'")
If IsNull(stLink) Or stLink = "" Then
[Code] ....
I have worded the stLink line different ways, and have used (*) instead of (%) but nothing is working. The CODE is working, as in no errors, but it is not finding a similar project that I know is present.
View 14 Replies
View Related
Jul 18, 2013
Access 2007
I can't figure out how to replace a period that is in the middle of a string and end up with 10 digits. For example 55.5555 would be 5500005555. I can use replace() but the tricky part is I have to end up with 10 digits.
Ultimately what I'm trying to do is - when a user enters 55.5555, 555.5, 5.5 or any variation they will be able to find the corresponding record. So a wildcard for the search or the replacement of the "." with enough zeros for 10 digits.
Here is what I'm using now - i making them enter the full 10 digit number but would like to give them the ability to use the period in place of the zeros.
Function Search()
Dim lssql As String
Dim lsSn As Recordset
Dim db As Database
Dim lsMessage As String
Dim sMsg As String
Dim vRetVal As Variant
Set db = CurrentDb()
[Code] .....
View 2 Replies
View Related
Jan 16, 2014
I import a CSV field which has not preserved the CR/LF when it was exported from BCM Remedy. There is no setting for that. The memo field prints on my report like this:
A custom solution would be developed, that once implemented, could become the standard product in similar situation. Normally would assign to Network Engineering, but will work with Ray and the IPT Team to cost out the solution and get approval to proceed. 2012/05/24 10:44:28 AM PCOLLINS Sent to Ray Massie for review to determine if a solution needs to be proposed, or if they can wait for the National IPT solution to be ready in 2013.
I want to add VBA code that inserts a CR/LF in the memo field before all but the first occurance of a string that looks like a date, the first occurance doesn't need it. I will do it right after I import the CSV file into the table, so it happens only once, and it always prints and displays the CR/LF.
The memo field is called "NBS Update" and the table is called "CCRR Remedy Data"Here is what I have, but don't actually know what to put in to find the date and add a CR/LF:
Code:
Dim db As DAO.Database
Code:
Dim db As DAO.Recordset
Dim srtSQL As String
[code]...
View 14 Replies
View Related
Mar 11, 2014
I have an unbound form (named frmReportSearch) with unbound text & combo boxes providing the criteria for a query (named qSeqStreets). The form / query utilize 4 optional fields as search criteria plus date from / to. The results are returned via a report (named rptSeqStreets). The whole operation worked perfectly, however I realized I needed to change one of the criterion to a multivalued field. The change in the table (named Tasks) worked perfectly. I used three checkboxes (named chkA, chkB and chkC) to allow the user to select any combination of the 3 choices, including none (to be treated as no filter on [fldShifts]).
The three options in the field (named fldShifts) are "A" "B" and "C". I am able to manually run the query from design view by typing in the criteria "A" Or "B"... "A" Or "B" Or "C"... and any combination of the three options in the criteria box and running the query. I am using the following code under the OK button's OnClick. The Code below has other items related to all the options .... I didn't want to give partial code so you may understand better:
Code:
Private Sub btnOK_Click()
Dim strShift As String
Dim strA As String
Dim strB As String
Dim strC As String
[Code] .....
My problem is that the query criteria needs to be entered into the criteria box with quotes and separated by "Or" depending on if multiple checkboxes are selected.
I can get the results to show correctly in the textbox, however I imagine the query is adding an extra set of ""s to the string so rather than "A" Or "B" .. it is getting ""A" Or "B"". My query Sql and even design mode are pretty complex, so I wouldn't know how to use the sql in VBA without blowing some fuses.
View 1 Replies
View Related
Dec 2, 2013
I'm trying to search a for string within a subform to find information stored on the mainform to which the particular subform belongs.
The problem is that the subform is generated from a query which uses a number from the main form to generate.
So the subform record is only generated when the correct mainform record associated with it is loaded.
Now to solve my problem I've made a new query that brings up ALL the results that could be generated by the main form and from that I can search to find my search term I'm after and read off the ID number to tie it back to the mainform.
But all of this is done manually, I want a way to do all this using VBA in a way that the user can't edit any records as they are doing it.
View 3 Replies
View Related
