I'm trying to get a value from a spreadsheet to import into my MS Access database. Currently I am trimming the spaces/carriage returns from it but need to strip some more data from the value.
Example value being "123 Point 5 Finance and Accounting"
I want to use Mid (I think?) to remove the "123 Point 5" (it is always the same with no exceptions) but don't know how to use it as I don't know how to use multiple parameters within a string.
Somehow it doesn't work out. It should extract all records with Valid_from = 01.01.2013 and valid_to = 31.12.2013. The Year assignment works.
Code: Public Sub BEN() Dim strSQL As String Dim t As Date, s As Date DoCmd.SetWarnings False Year = Right(pricedate, 4) t = 1 / 1 / " & Year & " s = 12 / 31 / " & Year & " strSQL = "SELECT TRANSFER_PRICES.* INTO [TEMP] " & _ " FROM TRANSFER_PRICES " & _ " Where [Valid_from] = " & Format(t, "#mm/dd/yyyy#") & _ " AND [Valid_to] = " & Format(s, "#mm/dd/yyyy#") DoCmd.RunSQL strSQL DoCmd.SetWarnings True End Sub
I have set calendar control 12.0 up and everything works how I want it to (click date and peoples names in a table to the left to show scheduled meetings on that day). What I want is to add a string on top of the calendar. For example, this monday I would like for it to say "Memorial Day" on the physical calendar itself.
I would like to cut off the first 6 and last 2 Charaters in an after update event but not sure how, I cannot use mid as the length of the string may change but never the first 6 or last 2, can some one show me how it's done ...
I have made a system for managing service calls and fieldworks.Part of this is checking the boards we install/service are working correctly.When the engineer calls in, we check the board - enter serial number, via ODBC talks to main server DB and pulls back what is listed below, along with ID and date/time which is all displayed in a list box.
This works fine. In the string above are certain bits of information that need to be checked to ensure they are accurate and the board has been programmed correctly. What I want to do it, is when the user single clicks on the list box, it parses out the various sections of the above string and enters them into some text fields on the form. With some code these can then be checked to see if they are correct or not and alert the user if they are or not.
The checking part I can do, the part I am struggling with is parsing out the correct parts from the string.The parts will be the same parts required each time, and the string parts wont vary, just not sure how to go about it.
I am trying to set up some template emails using text someone has entered in a form with a variable indicated with a key word in brackets aka. [ChangeID] or [ChangeDate]. The field on the form is formatted as Rich Text so I am getting http code. (No problems yet) In the form the template is required, I lookup the template required and I get the string. I replace the brackets with the following
I am trying to use a textbox value to call vba code and can not get it to work.
I keep getting an error on the call str1 line.
Code:
Sub formscript() Dim str1 As String str1 = [Forms]![fscripts2]![t3] 'MsgBox str1 If str1 = "" Then str1 = "err1" Exit Sub Else Call str1 ' this is where it call the script based on value in textbox End If End Sub
There are so many threads on here for emailing a PDF version of a report. I've put together a nearly complete bit of VBA that will do what I want - except for one thing.
Basically I have a personnel table with an email field. I Also have a field that details if a person is available or not ("Home" means they are available)
I need to create a string that is comprised of all the email addresses of the personnel who are "Home". In another thread I read that this can be done using a SQL statement.
So this is the VBA that I have so far - I commented the SQL for now because I don't quite know how to use it here.
Also, I have created the string, called "emailsList"
Code: Private Sub Email_Button_Click() 'SELECT Personnel_Table.Email FROM Personnel_Table WHERE (((Personnel_Table.Status)="Home")); Dim emailsList As String DoCmd.SendObject acReport, "AWACT_Report", acFormatPDF, emailsList, , , "Training Update", "Attached is the newest Training Report.", True End Sub
I have spent the better half of the afternoon researching how to accomplish controlling a bold font in a string I am building and sending to Excel.
Code: ' set text for safety hazards and controls Concretetxt = "Concrete Demolition & vbCrLf & Hazards - dust, flying debris and skin Irritation. & vbCrLf & Control - Respirator, goggles, gloves, inspect equipment prior to use." Excavationtxt = "Excavations & vbCrLf & Hazards - Damage to underground wires, collapse, falling materials/equipment. & vbCrLf & Control - Ensure utilities have been marked and hand dig when in close proximity, shore properly, Keep clear when lowering materials."
This is a sample of how the strings will build. I will select a task like Concrete Demolition and string it together with other tasks I would like "Concrete Demolition" in bold with the blurb following it then the next task would follow with a bold heading.
My thought is now that it may be easier to control by putting this data into a table instead of building the strings.
I have a query that returns the string to use but I am not sure how I can actually use the returned value to do what I want.
e.g. my table: Partner ID Backup Field 5023949 UPC 501354 GTIN
my query will return 1 [backup field] value from the table above, and I then want to use that value to get another value that was previously declared in my code.
eg GTIN = "ABC123" UPC = "123ABC"
if my query returns the value "GTIN" then need to use the value of GTIN (ABC123) in my next part of the code.
msgbox GTIN would result in a message box ABC123
But I am not sure how to get the result value. I can say msgbox [backup field] but that returns "GTIN" not "ABC123"
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 have a command button on a continuous form(form 1) and I need this button to open another form(form 2) when I press on it. So far so good.
When I press the button, I need some VBA to open the form(form 2) , search for a particular table name based on the open form(form 1) current record and use that table name as the newly opened form (form 2) data source. I have ways to do most of those task but for one thing:
How do I make access search for a table name containing a particular string? Here's what I am working with:
Code: Private Sub Commande26_Click() On Error GoTo Err_Commande26_Click Dim stDocName As String Dim stLinkCriteria As String Dim stDataSource As String
I am trying to put together a vba adodb connection string to a remote server. It is the first time I am using adodb in this context. I can get msysobj.connect but how do I format that information into a connection object connection string?
and I'm trying to pull the max value of the number after the -e- for a given set of them. In this example, I'd want to return the number 70. I'm then going to use that to create the next ID and populate another field.
The IDs are not used as the primary key. And while the previous IDs used leading zeros inconsistently, new IDs will not have leading zeros.
Here's what I have so far, but it doesn't seem to pull the number after the -e- at all. I think this section here is the problem, even though the same logic works in a query:
Code: Public Function MaxArticleERef(hbID As Long) As Variant On Error GoTo err_handler Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSql As String Dim maxERef As Variant
This has been working fine - all the files that meet that criteria are deleted, but now I need the option to exclude a particular file from those to be deleted: the particular file would be identified by having some specific text in its filename. So for example I need to be able to delete all files meeting the criteria EXCEPT any files that have the string "summary" as part of the filename.
I have a column containing records of the timestamp of an event. I need to extract the date out of each of these records and put them in a separate table. The date and time of each record is contained within a bracket.
I have a form used to gather data around some supplier details, and we have a review check box with Boolean data. I'm trying to establish a filter button to filter un-checked boxes by specific suppliers from a drop-down list.
Although I've been able to run filters on suppliers and the review checks separately, together I get a "Type Mismatch" error. I thought it might be because of the Boolean data type, so I tried converting that to String but get the same issue.
I would like to convert 2013.11.13 which is a string into a date. This is what i have so far but it's not working.
2013 year
11 month 13 day of week Dim strDate As String, dteConvertedDate As Date strDate = 2013.11.13 dteConvertedDate = CDate(Mid$(strDate, 6, 2) & "/" & Left$(strDate, 4) & "/" & Right$(strDate, 8)) MsgBox (dteConvertedDate) End Sub
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
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'm trying to check a directory for a folder name which has a certain string in it (RecNum)
However the fso object filesys.FolderExists does not take an argument when looking for part of a folder name on a particular path.
What I basically want to do is look in a directory for a folder with the RecNum variable as part of the folder name. if it exists open it, if not create it.
Set filesys = CreateObject("Scripting.FileSystemObject") If filesys.FolderExists(targetdir) Then GoTo function_end MkDir targetdir If filesys.FolderExists(copypath) Then filesys.CopyFolder copypath, targetdir End If MsgBox ("folder created") function_end:
