I have the a while loop with gives the string as following
1|2|Joe|Mark
2|4|Mike|Smith
.
I successfully created the .mdb with the tables. But how do I insert the above string with delimiter as pipes?
Any Suggestions
Thanks
In Access 2007, in a table “Source” -> "UserName" column, I'm trying to truncate and extract value in the string:
a. Value within the parenthesis preceded by "".
b. Value outside the parenthesis preceded by “”
“Source” table contains the column ‘UserName’
“Final” table contains the columns ‘UserName’, “Expected_Output_1” and “Expected_Output_2”.
I tried using the following expressions to extract the output results, but unfortunately there’re not displaying the results in correct format.
For extracting results of “Expected_Output_1” I used: Mid([UserName],InStrRev([UserName],"")+1)
For extracting results of “Expected_Output_2” I used:
Mid(Mid([UserName],InStr([UserName],"(")+1,InStr([UserName],")")-InStr([UserName],"(")-1),InStrRev(Mid([UserName],InStr([UserName],"(")+1,InStr([UserName],")")-InStr([UserName],"(")-1),"")+1)
Attaching the database for your reference.
Expected results should match the snapshot attached or table “Final” in the attached DB
Example:
UserName = C103E000001 then the “Expected_Output_1” = E000001, “Expected_Output_2” = nothing
UserName = NAHoM\_INTRA_MTS (NAHoMDNAGAIntranetAdmin) then the “Expected_Output_1” = _INTRA_MTS, “Expected_Output_2” = DNAGAIntranetAdmin
Here is what I need to do:
I have 1 table that contains groups responsibilities, there is a field Code-group that the initiator uses to track the groups & their % of impact to the overall project hours. Sadly enough he enters the data as such (EX:
PROJECT1 GROUP1-25%, GROUP2-50%, GROUP3-25%.
The problem is that he has varied instances of groups listed in each record & wants me to be able to parse the data out regardless of how many groups are listed. I know how to parse data when there are a specific number of groups what I can't figure out is how to parse data regardless of how many groups is in the field.
Any help is totally appreciated.
Phoenix
I need to export a table to .txt.
I need to have the field names as well as the records.
Some records are part numbers with a leading zero like 043095.
Some records are part numbers with dashes and alpha's like 123456E-789.
I don't think I can use .csv because some records have a part number description uses comma's like "some text, more text, even more text".
I don't need all the hyphens and pipes for the grid work.
The problems I have so far are:Exporting to .txt without formatting doesn't bring over the field names (I know they made it this way) Is there a work around other than exporting the data and then adding all the field names in with a macro in Excel? I can do it but, what a pain.
I see dropping of leading zeros = 43095 in .txt and .xlsx
Exporting to Excel sometimes scrambles the part numbers and they come out like this 5.91E-09 when it should be 123456E-789 (so I'm losing data).
Exporting to Excel also puts qoutes in the description field like "A very large part" (extra clean up necessary).
easiest way to get rid of the hyphens and pipes and leave it in .txt form?
Information
8|REPORT BY THE COMMITTEE APPOINTED TO STUDY CLASSIFICATION, TRANSPORTATION, DISTRIBUTION STORAGE AND SALES OF PESTICIDES IN *******|***** *. *****|******** ** *** **********|****|||PA2|
Okay, so my office had this emergency data dump, we lost the library, the records were saved but to do so they had to be backed up as .t files :-s which means less formating then a txt file :eek: :eek: :eek: , anyway, my office is hopeful I can fix their problems, sorry bout the ***'s, I was told that I can not put that information up on the net for security/privacy purposes. Anyway, what I have to figure out to do is to take this 1 field and split it into 8 fields, these correspond with the | character. If you see 2 || or more together it means that field was empty. Anyway, I just need a way to break these into 8 seperate fields, any feed back would be great.
I have string
I want to print the string and no by report
for example:
dim s as string
s = "hi" & vbcrlf & vbcrlf
s = s & "my name is sharon"
now I want to print the string in the printer
with default font and default size.
is it possible?
thanks!
I have a string coming in from a text field that I am inserting into an SQL Query that I want to parse for
invalid SQL characters and adjust the string accordingly.
For example:
"Foo's Bar" should become "Foo's Bar"
"C:Blah" should become "C:\Blah"
This seems like it should be simple (at least it is in other languages ::grumble:, but I can't get it to
work in Access VB.
Here's my code:
Code:Function strSqlFormat(myStr)Dim Count As Integer For Count = 0 To Len(myStr)Dim myChar As StringmyChar = Mid(myStr, Count, 1)If myChar = "'" Or myChar = "" ThenDim newStr As Integer newStr = Mid(myStr, 0, Count + 1)newStr = newStr & ""newStr = newStr & Mid(myStr, Count + 1) myStr = newStrCount = Count + 1 End IfNext Count End Function
I get an error message at the line: "myChar = Mid(myStr, Count, 1)" saying "Invalid Procedure Call or
Argument". I've tried fixes like CStr(myStr) and CLng(Count) to make sure the parameters are the right
data types, but it still gave me the same error.
Can anyone figure this out? I'm a VB newbie but I'm experienced in Java, C++, C#, etc.
where do I add a closing string into this code and what do I write? The server said the database is not closing the access database correctly and i don't know what is wrong.
<configuration>
<appSettings>
<add key="connString" value="~/protected/pedalcardb.mdb" />
</appSettings>
<system.web>
<!-- DYNAMIC DEBUG COMPILATION
Set compilation debug="true" to enable ASPX debugging. Otherwise, setting this value to
false will improve runtime performance of this application.
Set compilation debug="true" to insert debugging symbols (.pdb information)
into the compiled page. Because this creates a larger file that executes
more slowly, you should set this value to true only when debugging and to
false at all other times. For more information, refer to the documentation about
debugging ASP.NET files.
-->
<compilation
defaultLanguage="c#"
debug="true"
/>
<!-- CUSTOM ERROR MESSAGES
Set customErrors mode="On" or "RemoteOnly" to enable custom error messages, "Off" to disable.
Add <error> tags for each of the errors you want to handle.
"On" Always display custom (friendly) messages.
"Off" Always display detailed ASP.NET error information.
"RemoteOnly" Display custom (friendly) messages only to users not running
on the local Web server. This setting is recommended for security purposes, so
that you do not display application detail information to remote clients.
-->
<customErrors defaultRedirect="error.htm" mode="RemoteOnly" />
<!-- AUTHENTICATION
This section sets the authentication policies of the application. Possible modes are "Windows",
"Forms", "Passport" and "None"
"None" No authentication is performed.
"Windows" IIS performs authentication (Basic, Digest, or Integrated Windows) according to
its settings for the application. Anonymous access must be disabled in IIS.
"Forms" You provide a custom form (Web page) for users to enter their credentials, and then
you authenticate them in your application. A user credential token is stored in a cookie.
"Passport" Authentication is performed via a centralized authentication service provided
by Microsoft that offers a single logon and core profile services for member sites.
-->
<authentication mode="Forms" />
<!-- AUTHORIZATION
This section sets the authorization policies of the application. You can allow or deny access
to application resources by user or role. Wildcards: "*" mean everyone, "?" means anonymous
(unauthenticated) users.
-->
<authorization>
<allow users="*" /> <!-- Allow all users -->
<!-- <allow users="[comma separated list of users]"
roles="[comma separated list of roles]"/>
<deny users="[comma separated list of users]"
roles="[comma separated list of roles]"/>
-->
</authorization>
<!-- APPLICATION-LEVEL TRACE LOGGING
Application-level tracing enables trace log output for every page within an application.
Set trace enabled="true" to enable application trace logging. If pageOutput="true", the
trace information will be displayed at the bottom of each page. Otherwise, you can view the
application trace log by browsing the "trace.axd" page from your web application
root.
-->
<trace
enabled="true"
requestLimit="10"
pageOutput="false"
traceMode="SortByTime"
localOnly="true"
/>
<!-- SESSION STATE SETTINGS
By default ASP.NET uses cookies to identify which requests belong to a particular session.
If cookies are not available, a session can be tracked by adding a session identifier to the URL.
To disable cookies, set sessionState cookieless="true".
-->
<sessionState
mode="InProc"
stateConnectionString="tcpip=127.0.0.1:42424"
sqlConnectionString="data source=127.0.0.1;Trusted_Connection=yes"
cookieless="false"
timeout="20"
/>
<!-- GLOBALIZATION
This section sets the globalization settings of the application.
-->
<globalization
requestEncoding="utf-8"
responseEncoding="utf-8"
/>
</system.web>
</configuration>
MS-Access VBA code to separate numbers and string from an alphanumeric string.
Example:
Source: 598790abcdef2T
Output Required: 598790
Source: 5789065432abcdefghijklT
Output Required: 5789065432
I have a question about connection strings. Basically I have an access front end and an access back end. It works fine for now, but when it gets rolled out, I don't want to have to manually relink the tables every time. Is it possible to hard-code the database connection strings for all the tables used in the solution as the paths won't change once deployed?
View 4 Replies View RelatedWhich function in access return the the last value in a string.
Period 1 Period 2 Period 3
10 20 30
I need the function to return 30.The reason behind this I have different periods for categories and my formula I'm using needs the ending value.
I have a question about connection strings. Basically I have an access front end and an access back end. It works fine for now, but when it gets rolled out, I don't want to have to manually relink the tables every time. Is it possible to hard-code the database connection strings for all the tables used in the solution as the paths won't change once deployed?
View 9 Replies View RelatedI 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] ....
I have two tables linked via Project ID as shown in the attached file. I need Module to concatenate Project sub types against Project Types.
View 3 Replies View RelatedI wrote a VBA code to get the field value of a csv file, then use it to update the database table. I paste some of my code below
Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim objUpdate As ADODB.Connection
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & theFolderName & ";Extended Properties='text;HDR=Yes;FMT=Delimited'"
[Code] ....
1. The target of this code is to set the [Section] value in the table
2. The [Section] value is at the 3rd column inside the csv file
3. By using the UPDATE query, it map the [Section] value to the corresponding [Program] and [Course] in the table
This code did the job. But the problem is the string of the [Section] value is like "2-22-01". But after update to the table, the value become "2/22/2001".
The value is a string text, not date. How can I prevent this happen ?
I just attached 2 files, 1 mdb and 1 txt (change the extension to csv before test).
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.
I am in the process of building a new database in SQL to replace my MS Access database. However, I will continue to use the Access forms, queries, and reports. The new tables will house much of the same data. In multiple tables I have hyperlinks that were created and added in the original Access tables. To import these hyperlinks into the new SQL tables I have converted them to 'Long Text' before exporting, thus changing them into strings.
For example:
Hyperlink - Email - Add Additional Mailbox to Outlook (2010) has been changed to:
Email - Add Additional Mailbox to Outlook (2010)#ServernameServerfolderDocumentationRea dy to GoOutlook TemplatesEmail - Add Additional Mailbox to Outlook (2010).oft#
The obvious issue that I am running into is that after the SQL database table has been linked to the Access database it still displays the entire string when I open the table. The form has a textbox and search button that is used as a search function. This runs a query that returns all "search results" for the desired information. Is there a way that the query can convert the string back into a hyperlink so that the query displays just Email - Add Additional Mailbox to Outlook (2010) as a hyperlink and not the entire string?
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 = ;
It worked in acces 2000, and now am using acces 2003. Anybody any clue why the string strVoorstel would not get the value? It just returns nothing.
Here's the code:
Dim strVoorstel As String
Dim lngTel As Long
Dim strQer As String
Dim strDa As String
Dim lngNum As Long
...........
Hi Folks,
i am using the sql below but i am having a little syntax error:
i am trying to refer to a variable called strRAGCol
strSQL = "SELECT tblSite.SiteID, tblSite.SiteRAG, tblSite.Active " _
& " FROM tblSite " _
& " WHERE (((tblSite.Active)=Yes)AND((tblSite.SiteRAG)= strRAGCol));"
Any help much appreciated.
Mark
The following SQL query is returning no records when I know for a fact there are some there! Can anyone please hlp me?!
Dim strSQL As String
strSQL = "SELECT * FROM tblHirer WHERE HirerSurname= 'Forms!frmFinanceProposal!Child845!Text430'"
rsFindDuplicates.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rsFindDuplicates.RecordCount
Also, I was wondering how I would put as 2nd filter on the recordset to equal a particular date?
Cheers
I had a query and the sql is:
SELECT Institutions.*, Institutions.merge, Institutions.Inst_type
FROM Institutions
WHERE (((Institutions.merge)=True) AND ((Institutions.Inst_type)=[forms]![merge_inst_type]![Inst_type]));
and I had a command button which process
MergeAllWord ("select * from merge_query_true")
but I am getting an error like this "make sure the sql is correct , sql was
select * from merge_query_true
select * from merge_query_true ---- is this code ok?
If I had cut the part "AND ((Institutions.Inst_type)=[forms]![merge_inst_type]![Inst_type])); from the sql it works fine....
WHat am I doing wrong...I cannot get the syntax correct...
Form the Query Builder:
SELECT tbl_GroupMembers.GroupNumber, tbl_GroupMembers.Email
FROM tbl_GroupMembers
WHERE (((tbl_GroupMembers.GroupNumber)="10"));
NEED IT TO BE IN THIS SQL STATEMENT:
Set rs = dbs.OpenRecordset("Select * From tbl_Groups", dbOpenDynaset)
I have a fax number field iwhich has a mask. +(000)-00-000-000 in a table of many records.
The problem is that every record has a number in the first part of country code +(001)which must have come there by mistakes, few records have complete and true fax numbers.
I need a query that can Update this Fax_Number field where the length of the string is less than 7 digits.
Some thing like UPDATE Contacts SET Contacts.Fax_Number = ""
WHERE ((Len("Fax_Number")>"6"));
How can i do this. Thanks.
I have a string:
Cars > Car Import > Car Import: Car Import
The first section is the Category, the second is the Product, the third is the SubProduct and the fourth is the keyword.
What is the best way to split each of these words into its own variable? I think I can do the first and end one but not the Car Import and Car Import one.
Thanks,
Dave
I have a 67 binary code string produced from a query which concatenates these 1's and 0's.
What I need to do is have access decide what a particular string value/range is and return the process name. e.g
11000000000000000000000000000000000001000010000000 00000000000000001. The process name would be COMP RESOLVED MSA
10100000000000000000000000000000000001000010000000 00000000000000001.The process name would be COMP UNRESOLVED MSA
00000000000000000111010000000110000000000000000000 00000000000000001
The process name would be MSA NEW
There could be about 60 different Process Names