Hey people,
This should be any easy one to solve:
I need to split a field in my query into two separate fields, e.g fullname into a first and last name, so "John Smith" will appear in two columns, "John" and "Smith".
Thanks in advance.
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.
9853911264,W-AMR,3,2:320:0:52,MAIN STORE,3.57,0,18,001.004.041,0,0*75
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 have a txt file that I'd like to import, which contains answers to a test. The number of questions on the test varies year to year, but this year there are 110 (split in to two sections).
I'd like to import the file, so each answer is a field, and then score the answers against a 'correct' answer lookup table.
What's the best way of approaching this? I first thought of using vba to read the text file, split the text, and then use an SQL command to insert to to my table. This seems tedious (there are around 6500 respondents).
Anything else I could do instead?
Thanks,
Antro.
I've a code that can split strings. but now i want the last thing he split in a string named "name".
The string what is split is folder places like C:Usersinengine@mccain.comDocumentsTestvan e st.txt.
Now I want that text.txt comes in 'name'
I know there are many posts on this but still cannot find what I want ....
I have a string ....
... <surname>bloggs</surname> <fornames>Jane</fornames> etc.etc.
... <surname>williams</surname> <forenames>Jo</fornames> etc.etc.
In a query I know how to:
Find the Start and End Postions as follows:
StartSurnamePos: InStr([string],"<surname>") returns 19
EndSurnamePos: InStr([string],"</surname>") returns 34
I also know that by adding 8 to StartSurnamePos I can get Bloggs but how do I stop it there.
Using left, Right or Mid how do I pull out just
bloggs Jane
Williams Jo in seperate fields?
Thanks in advanced.
I have a table field which long ago was merged from several other fields. When the data was merged into the field it was delimited by "1." then "2." up to "5."
Example: MergedField = "1.Animal 2.Large 3.African 4.Grey 5.Long Nose"
I now want to split it appart in a query where "1.Animal" goes into expression1, "2.Large" goes into expression2, etc.
I need to base the text on where the one number begins and grab everything until the next number in the mergefield is detected.
Can someone show me the syntax for this.
Thanks!
I have a table where I want to break longer strings into three-character strings.
So... for example the data item "Forest," I'd like to generate "FOR" "ORE" "RES" "EST" and write all four to another table, allowing for duplication. Alternately, we could make a list and just count the unique 3-character strings. The end goal here is to measure the frequency of 3-character strings across a set of data.
how to do this with an Access query?
I have a query with approx 700,000 results. The records in this query all need to be emailed.
I need to split the query results up in to separate queries containing 100,000 or so records so they can be uploaded to the mailing website.
I'm not sure if I need to make a new table for it, or I'm missing something.
I'm struggling to get my database to auto split up a Query by a certain Column and automatically get it to export into separate Excel files.
I have got the export sorted, currently set to export the whole query, but i'm struggling to do the splitting up.
I'm trying to separate LastName, FirstName into separate fields : FirstName and LastName. The code I have is working for the most part. But I had to create a separate query with the replace statement to get rid of the ","... Looking at the data now, I had people with names like Mac Buren, Tony and I'm only picking up the Mac part in my query.
Code:
SELECT Left([Employee Name],InStr([Employee Name]," ")) AS qryLastName, Right([Employee Name],Len([Employee Name])-InStrRev([Employee Name]," ")) AS [First Name], Employees.*
FROM Employees
ORDER BY Left([Employee Name],InStr([Employee Name]," "));
I am trying to split the year into thirds instead of the quarter. I will be needing to set the criteria to only show me the previous year from the current year. Can this be done in one query?
View 6 Replies View RelatedMS-Access VBA code to separate numbers and string from an alphanumeric string.
Example:
Source: 598790abcdef2T
Output Required: 598790
Source: 5789065432abcdefghijklT
Output Required: 5789065432
Here's my problem. I need to generate a report that says how much of each individual product was produced and as well as the total produced for a specified category in a time period. Something like the following:
05 Catagory A: 02 Product AA, 01 Product AB, 02 Product AC
10 Category B: 07 Product BA, 03 Product BB
04 Category C: 01 Product CA, 01 Product CB, 01 Product CC, 01 Product CD
etc...
I currently have a query that queries a database and pulls out all products that were produced in a specified period and the categories they belong to and dump them into a local access table. Now what I need to do is search through the query results and count up how many of each product were produced (02 AA, 01 AB, etc...) and the totals for each category. The number of categories is pretty limited (6), but there are hundreds of product codes, so I need a way to do this without having to type in each induvidual product code as the requirement by which the query searches. Also, the product codes that get returned are different every day.
I was thinking something along the lines of take the product code of the first row and check for any others in the results that match and write that into another table. Then move onto row 2 and use its product code as a search parameter and search through the query results for any matches. Then continue that until the end of the query results. Can I do that? Is there a better way to achieve what I need?
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.
Hi all,
My name is Sarah and I'm working with a simple guestbook script that runs with an Access database. Right now the script displays all guestbook entries. I want to alter it so it only displays entries if a given person's name is "Bruce". This might sound goofy but if I can get this functionality then I'll be able to alter the script to build a bunch of simple scripts that I have planned.
Here's the code that displays all entries on the guestbook:
Code:<%'Dimension variablesDim adoCon 'Holds the Database Connection ObjectDim rsGuestbook'Holds the recordset for the records in the databaseDim strSQL'Holds the SQL query for the database'Create an ADO connection odjectSet adoCon = Server.CreateObject("ADODB.Connection")'Set an active connection to the Connection object using a DSN-less connectionadoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("guestbook.mdb")'Set an active connection to the Connection object using DSN connection'adoCon.Open "DSN=guestbook"'Create an ADO recordset objectSet rsGuestbook = Server.CreateObject("ADODB.Recordset")'Initialise the strSQL variable with an SQL statement to query the databasestrSQL = "SELECT tblComments.Name, tblComments.Comments FROM tblComments;"'Open the recordset with the SQL query rsGuestbook.Open strSQL, adoCon'Loop through the recordsetDo While not rsGuestbook.EOF'Write the HTML to display the current record in the recordsetResponse.Write ("<br>")Response.Write (rsGuestbook("Name"))Response.Write ("<br>")Response.Write (rsGuestbook("Comments"))Response.Write ("<br>")'Move to the next record in the recordsetrsGuestbook.MoveNextLoop'Reset server objectsrsGuestbook.CloseSet rsGuestbook = NothingSet adoCon = Nothing%>
My friend told me that I need to alter the query string to something like this, but I can't get it to work:
Code:SELECT tblComments.Name, tblComments.Comments FROM tblComments WHERE tblComments.Name = Bruce ;
As you can tell I'm not a programmer but I can usually alter scripts to get them to do what I want. If I could get this figured this out I would be the happiest woman alive.
Sarah
i have a string in VB (eg. "test")that will cahnge depending on the input, ihow can i search for the string and display the data into a form?
i currently have this
"SELECT [Tasks].Staff_Name, [Tasks].Project_Title, [Tasks].Percentage_Complete, [Tasks].Tracking_Number FROM [Tasks] WHERE [Tasks].Staff_Name like [forms].[Main].[test] ORDER BY [Tasks].Staff_Name;"
but how can i make it so it spits the data into its corresponding feilds onto a form? (just like it would be if i performed a wizard?)
I need to build up a string containing values separated by commas. The following almost does this.
I use an update query in which the "Update to" field for 'String field' contains
[String field] & IIf(Len([String field])<1,"",", ") & [Related table]![Value to append]
I would like to have the [Related table]![Value to append] in ascending order.
I.e. I would like the result to look like "A, B, C, F, H", not "B, C, H, F, A". Any suggestions as to how to apply the sort?
Hey,
Can someone pls tell me how can i get data from query to a string so i can use it in my VBA code?
I have a query that basicly creates me a uniqeu code and i need that code when i write new record in datasheet form? For example:
I have Query1 that has a field1 with data "AASSFF"
Now i need that data in my Table1 field1
THX, S
In a database that holds membership records, I have three years worth of info relating to membership dues paid. I want to extract who has not paid a membership fee for the last three years, so we can strike them off our records, i.e. I have members' personal details, but there are no records in the payments table, nothing has been entered because they just have not paid. Do I run a query looking for a zero length string? Just how do I achieve this? Have I done right in not entering anything?
Help appreciated!
Can i create a query that will have a string in the Criteria box, where that string is assigned a value in VB code?
for instance:
Dim xyz As String
xyz = "TV"
and I put in the Criteria Box WHERE xyz = "Pizza"
Hello, I have a query that returns a list of courses undertaken by certain employees. They are in the format NPTC CS 30; NPTC CS 31; NPTC 33; NPTC FTC 38; NPTC FTC 40 etc...
Anyway what I need to do with this is convert these results to a string and manipulate them in such a way that they can be displayed in a text box on the back of an ID card in the following format:
NPTC CS 30, 31, 33, NPTC FTC 38, 40... etc.
Because there is not enough room on the back of the ID card to use a subform to just display them in a list...
In MySQL I can use the LEFT(3, field_name) command, but I essentially want access to pull all records from a table where the the first three digits of a number field match those pre-selected by a form drop down.
This database is in Access 97. Is there a way to compare the first x number of digits using SQL only, or do I need to run each line through code first, and then check it (I really don't want to do that)?
example numbe rin field:
123456789
SQL checks to see if 123 matches value selected on a form.
Let's say I have a query like:
select [x]+[y] as A, [z]*[A] as B from MyTable.
I have found that sometimes this works and sometimes it doesn't, (Like, it works the first time I run it, but later quits working....)so I've gotten into the habit of repeating my calculations and writing it thus instead:
select [x]+[y] as A, [z]*([x]+[y]) as B from MyTable.
However, I now have some query fields that have so many calculations in them that I exceed the string length that Access allows for a query field. So is there a way to make the shorter SQL work consistently?
Any ideas appreciated.
Thanks!
PS: Here's the field I need to shorten:
AdjFcstDay: IIf(((Sum([AvgDaily])*First([forecastweeks])*5+nz(First([backlog]),0))/(First([forecastweeks])*5))+IIf([wiptotal]<(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev])))-[wiptotal])/5,IIf([wiptotal]>(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev])))-[wiptotal])/5,0))<0,0,(Sum([AvgDaily])*First([forecastweeks])*5+nz(First([backlog]),0))+IIf([wiptotal]<(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev])))-[wiptotal])/5,IIf([wiptotal]>(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev])))-[wiptotal])/5,0)))
I have this query:
INSERT INTO 1_2_06 ( Issue_ID, Assigned_To, ListType )
SELECT Issue_ID, Assigned_To, 'QPT'
FROM QPTActions
WHERE QPTActions.Entry_Date<=DateValue('1/2/2006') And QPTActions.Close_Date>DateValue('1/2/2006');
The Assigned_To values are a Lastname, Firstname format as they come out of the first database. But I need the comma stripped before they get entered into the INSERT database. Is it possible to do this on the fly?
Hello again.
I have a drop-down list that contains string values. Is it possible to construct a query that will sum the total number of times that the string value is selected? For example,
String Value Sum
================
Chicken 3
Beef 9
Pork 2
I have been trying to work on this for a while and cannot figure it out. Can someone shed some light on this issue? Thanks.
Hello everyone,
Simply put, I want to create a form to which I can enter a raw SQL statement (such as: SELECT * FROM contacts without having to create a new Access query.
The way i have tried to do this is by creating a form which uses an onclick function to pass the string variable (the query statement) to a sencond form and have the second form run the statement. I can pass the variable ok but I cant figure out how to the use the variable string in the second form to execute as the "Record Source" of the form.
Any ideas?
Thanks,
Curveo