MSQuery - Return Length Of String

May 21, 2012

I have an ODBC connection to a db2 database in MSQuery. I want to return the length of a string. I've tried Len(string) but it says its not in the library.

View 2 Replies


ADVERTISEMENT

Shorten String Length If Exceeds 31 Characters (max Sheet Name Length)

Mar 27, 2014

I've set up a filing system which saves sheets/ workbooks based on the value of a cell - Range("B1") Everything works great apart from when ThisFile String length exceeds 31 characters which you may know is the max useable character length for a sheet name - I had no idea! 8-0

Is there a way i can check if string length exceeds 31 characters then, if it does, shorten it to 31 characters?

[Code] .....

View 2 Replies View Related

Grouping And Then Summing In Either Msquery Or Back After The Data Has Been Extracted From Msquery

Nov 19, 2009

I need grouping and then summing in either msquery or back in excel after the data has been extracted from msquery.

I have external data that i have interogated in msquery, and I would like to to sum the amount column if possible.
I have two fields, first field = account number
2nd field = amount due

the first field could contain 5 sets of the same account number, but with different amounts in the Amount Due column. I would like to sum the amounts due for each of the account numbers in one column.

If i cannot do this in msquery, how do i then go about doing it back in excel. I know i can use subtotals, but then when the data updates from the external data source.

View 2 Replies View Related

Fixed String Length

Dec 1, 2009

I am trying to create a string of text that grabs info from other cells, which is easy so for example

=A1&A2&A3

would grab all the info from the 3 cells and merge them to create a string, but what I want to do is create a prefixed length.

Lets say A1, A2 and A3 would equal 10 characters in total but B1, B2 and B3 would equal 7 characters, these would be different lengths. so for a visual description I would like them to appear like this

At the moment

01MAIN-HELLO
01DESSERT-HELLO WORLD

Would like

01MAINXXX-HELLO
01DESSERT-HELLO WORLD

This would make both strings the same length and would make it easier for me to export to a custom file that needs a certain length prefix.

View 9 Replies View Related

Average Length Of A String?

Apr 8, 2013

I have a row of 1's and blanks, where there might be 10 consecutive 1's, followed by four consecutive blanks. I want to calculate the average length of a string of 1's in the row -- how this might be accomplished?

View 7 Replies View Related

How To Extract Variable Length String

Feb 9, 2014

i just wanna extract the date shown(last data until meet the first comma) in first column to another column.

column 1
abcde, 1234
abcd, 12345
abcdef, 123

column 2
1234
12345
123

View 3 Replies View Related

Editing String Based On Length

Sep 1, 2009

I get all my results from a CSV file. This isn’t a problem except for the first nine days of the month where excel cuts off the first 0 so 01012009 reads 1012009 and cannot be properly concatenated.

DateNameItem
1012009BobKeyboard
1012009SidMouse
1012009DaveMonitor
1012009EdMonitor
1012009AndrewKeyboard
1012009DanKeyboard
1012009BruceKeyboard

What I would like to do is have VB read the contents of the date cell and if it is less than eight characters, edit the cell by adding a zero at the beginning of the string. This needs to be a one off process for all cells. Can anyone come up with code that might make this happen?

View 9 Replies View Related

Length Of String Changes When Passed To Function

Sep 28, 2006

The following afterupdate procedure for my txtStart1 text box sends the value to the FormatTimeValue function that is coded in a module.

Private Sub txtStart1_AfterUpdate()
MsgBox Len(Trim(Me.txtStart1.vaue)) '<= outputs 1
Me.txtStart1.Value = FormatTimeValue(Trim(Me.txtStart1.Value))
End Sub

As you can see from the bolded text, the length of the value is 1 when outputted from the forms code.

However, when I pass it to the following function in a module it outputs 2 as the length.

Function FormatTimeValue(vTarget As Integer) As String

Dim TimeStr As String

If IsNumeric(vTarget) Then

MsgBox Len(vTarget) '<= outputs 2

Select Case Len(vTarget)

Case 1 ' e.g., user entered 1 so time should be 01:00
TimeStr = "0" & vTarget & ":00"

View 3 Replies View Related

Maximum Length Of Variant Or String

Aug 10, 2007

I have looked into the maximum length of a variant/string in vba and it appears to be 250 characters. I am running a macro which first lists all excel files in a folder, returning them to a sheet, then using a loop statement opens each one in turn extracting the information to a second summary sheet before closing it. The file path to the folder is ridiculously long and the macro stumbles. I used the =LEN(A1) formula to check if the file names were too long for the string, but the maximum file name length was 226 characters. I've tried both String and Variant to collect the file names but both have the same effect.

View 2 Replies View Related

Inserting Characters Into A String Of A Certain Length

Jun 6, 2008

I need to insert 00 if the string of text = 13 in a specific column.

eg. Column K can contain any amount of lines of data. The data will either be 15 or 13 characters in length. If its length is 15 its fine, if its 13 I need to get it to 15 characters.

The first 6 characters are letters so if the length is only 13 I need to add the 00 in after the last letter.

View 3 Replies View Related

String Parsing With Variable Length Strings

Mar 28, 2006

I am trying to split up a cell into numbers and charachters and place them in
separate columns, but the lenght of the number part varies as does the
content of the character part.

For example, one cell could be 5#, 10Tins, 4 lb, 100Pcs, etc.
I would like to be able to pull out the #, Tins, lb, and Pcs in to their
own column.

I have found this formula:
=LEFT(A1,FIND("-",A1,1)-1)

but it assumes some level of consistency, the "-" in the cell.

View 18 Replies View Related

String In Column A To Be Divided Into 2 Strings If Length > 50

Apr 8, 2013

I have a set of data in Which in column A is the name of organisation.

If string in col A is longer than 50 I need to split in and put in col B.

That would be simple however I need to do it in a smart manner: i.e. cut it to the nearest full word.

Example:

THIS EXAMPLE NAME IS TOO LONG TO FIT INTO 50-TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO STRINGS

Incorrect; length = 98

THIS EXAMPLE NAME IS TOO LONG TO FIT INTO 50-TEXT
Correct; trimmed down to 48.

My question is about formula that can detect spaces and depending on those trim the string down adequatly:
to 50 if 50th char is preceeded by space; if not then check where is the next space going towards left. Once you find it cut the string there.

What formula I can use to get desired effect?

View 9 Replies View Related

Extracting Text From A String With Variable Length

Jan 13, 2009

Hey I got a long String like this "[...] increase of x.xx% [...]".

I am trying to extract only the percentage number which can be of variable length, so maybe 900.99% or 9.99%.

I tried this formula:
=MID(G14,SEARCH("%",G14)-5,5)
but this one doesnt bring the right results as the percentage figure is often not exactly 5 characters long.

View 9 Replies View Related

Index Function - Limit On String Length

Jun 23, 2006

I have a spreadsheet with 2 sheets. The first just links to another file and pulls in the contents of a particular sheet, cell for cell. The second sheet is a formatted report that I'm creating to put that data into a different format for presenting to others. I'm using the Index and Match function along with range names. All is fine except where the string that should be returned is very long. When that happens, I get "######" all the way across the cell. It doesn't matter how wide I make the column or how tall I make the row, the "#s" do not go away. Is there a limit on the number of characters the Index function can return?

View 5 Replies View Related

Extract Number Of Variable Length From Text String

Jan 25, 2007

Imagine that I have this list of thousands of customers, who are listed in column B in the format "customer no+space+customer name".

Now I want to extract ONLY the customer no from the text string in column B and insert it as number value in column A on the same row.

Would have been easy with "Left" command, but as the customer numbers have variable length, I have a problem.

View 5 Replies View Related

String Length Too Long For Single Cell On Imported Data

Sep 13, 2013

I have a google earth KML file that I have converted to text, and through a bunch of manipulation have been able to pull a series of GPS coordinate strings into a single cell string. Unfortunately, the string data is beyond what excel 2007 can handle for a single cell. so my thought is to have excel pull each coordinate string into a separate cell with which i can then run a macro to develop a new KML dynamically. (changing multiple attributes based on a query to a database)

Each string of coords actually maps out a single region (path) on the KML, truth be told it is telecom rate center data, and each rate center will have numerous other variables applied to it depending on my company's voice network capability for a given rate center. Currently my only desire is to depict differently any rate centers that I'm able to deliver VOIP services to by showing them in a different color...but these change very often so it will support to be able to auto generate the map from time to time.

the raw data from the KML looks like this:

Code:

CLINTONVL

RATE_CNTRCLINTONVL
STATEPA]]>

ff000000
1
0
1

[Code] ..........

After doing my data import, i extracted via various manipulations, the rate center name (a common lookup value that stays constant across multiple databases), and the string of coordinates. this is where i run into trouble. i need to pull each coord into a separate cell assuming i won't run out of cells in the x coordinates to gather this data OR find a way to grab the data via another lookup to another document...not desireable.

I want my output to look like:

Code:
RC_NAMECOORDSLATAVoip?
ALIQUIPPA
80.334114,40.520974,0-80.327493,40.520023,0-80.323915,40.51957,0-80.31997800000001,
40.519933,0-80.316958,40.521221,0-80.314431,40.521445,0-80.314262,40.521332,0-80.31398,
40.521194,0-80.31315600000001,40.520832,0-80.313057,40.520794,0-80.312805,40.520641,

[Code] ........

First off, my import was jacked up by missing some comma's...this i can fix easily with the string importation and manipulation HOWEVER...i still run into the issue of string length OFTEN.

View 1 Replies View Related

Getting A String Of Hash Sign Because Character Length In A Cell Is Too Large

Apr 10, 2007

Is there a way to stop getting the error of a string of poind signd (which look like ################) whenever I'm trying to copy and paste information from another worksheet, or trying to link one sheet to another?

I've already tried resizing the rows and columns to their respective maximums but it doesn't seem to work.

View 4 Replies View Related

If Text Found In String Return String

Jan 31, 2008

Find a short text string in a column of longer text strings and when that short text string is found return the longer text string that matches.

View 3 Replies View Related

Formula Code To Find Arc Length From Chord Length

Sep 4, 2012

How to create a code formula to calculate the arc length from a given chord length?

If you know the radius of the major circle.

Say the chord is 50mm and major circle dia is 72mm (radius 36mm)

arc from chord.jpg

View 5 Replies View Related

Give Cell A Value Based On Length Of Row (variable Row Length)

Jan 9, 2010

My worksheet contains data with the reaction times on a psychological test. Each respondent in the test has 280 rows in my excel sheet.

The 'perfect' length of the row, is from A to M. When an error is made in the test, the length of the row will increase. So the error length can be A to AA.

For me it is important to analyse the error. So I would like to give a perfect row length, the value 1, and an error row length a value 2.

So, in conclusion:

If:
Cell length = A1 - M1? --> Copy A1 B1 C1 (A B Cof that row) to Sheet3, and give D1 in sheet 3 the value 1

Cell length >= A1 - M1? --> Copy A1 B1 C1 (A B C of that row) to Sheet3, and give D1 in sheet 3 the value 2

View 9 Replies View Related

How To Extract Substring From String Based On Substring Length

Jan 16, 2008

(I need to do this using VBA so that it can work as an excel macro and/or a vba function in access.)

How can I extract the first occurance of any substring over a certain character length from within a string ?

eg. My string may take any of the forms given below (or something similar), how can I extract the first set of characters that is at least (say) 4 characters long ? i.e. always get the 'Phillips' out of the below...

Phillips Homes Ltd
Mr T A Phillips
TA Phillips Homes Ltd
T A Phillips Homes Ltd
T. A. Phillips Homes Ltd

The names used are only an example (Phillips could be any name whatsoever).I am familiar with Search, Len, Left, Right, Mid, Instr etc. but just can't figure this one out.

View 9 Replies View Related

Can You Use IIF In MSQuery

Apr 13, 2009

Is it possible to create a new field in MS Query which uses an IF / IIF statement to determine it's value?

I am querying an Excel file/table, and have a field called 'Mo Nm' (month name) which stores the name of the month of a sales record, with the sales value stored in 'Sale Value'.

I'm trying to create 12 new fields in my query output to report the total sales by customer by month in a single record, rather than 1 record per month, and having to then do a Pivot Table on the result.

MSQuery is using a table called Data$ (the XL file has a sheet called 'Data', which stores the XL list being queried).

View 9 Replies View Related

Change MSQuery References

Jan 13, 2009

I have a spreadsheet that queries another spreadsheet, using MSQuery. The query is of a named range on another workbook. What I am trying to do is change the workbook reference, to another similar workbook, with the same range name. MSQuery is not easily letting me do this. I figured there was a way to simply change the file that the created table (in MSQuery) is using. There doesn't seem to be a way to do this.

The named range is "data." This is what the SQL view looks like: ...

View 9 Replies View Related

Date Format In Msquery

Jul 11, 2007

Here is part of the vba code for a query

"(Mr_Scrap.PART_NO='" & partno & "') AND (Mr_Scrap. DATE>={d '2006-12-25'}) AND (Mr_Scrap.DATE<={d '2007-07-04'})")

I would like to have the dates be from cells A2 and B2, but this does not work.

startdate = range("A2")
stopdate = range("B2")

"(Mr_Scrap.PART_NO='" & partno & "') AND (Mr_Scrap.DATE>=" & startdate & " ) AND (Mr_Scrap.DATE<=" & stopdate & " )")

View 9 Replies View Related

Data Source For Msquery Query

Nov 16, 2006

I have inherited a file that calls an Access database via Msquery. I can see the query that is being used with:

Data | Import External Data | Edit Query | Query Wizard

How can I determine the Access database that the query is using for its data source?

View 6 Replies View Related

Format Cells Returned By Msquery

Jan 31, 2007

I want to apply formatting to only the cells returned by MSQUERY.

I do not want to apply formatting to all 65535 rows on the basis they MIGHT be used by the returned data, I only want to use those that ARE.

View 4 Replies View Related

Oracle Date/Time As A Parameter In MSQuery

Nov 7, 2002

I am attempting to use MSQuery to extract records from an Oracle transaction table by passing user defined Date/Time field parameters.

So if the date range for the query is for Fiscal Period 10 of this year, my parameters would be:
[StartDate] = #2002/10/1#
[EndDate] = #2002/11/1#

Daily transaction activity is in the early morning hours ending at 6:00AM so my Criteria is:
Between [StartDate] + .25 and [EndDate] + .25
Note: The +.25 represents 1/4day = 6hrs (6:00AM)

If I use the hardcoded Start/End Dates, my query works fine, but if I try and
pass the dates as parameters I get "ORA-00932 inconsistent datatypes" error. And I can't seem to pass the Date/Time combination successfully....

If anyone has encountered a solution for how to pass a Date/Time Paremeter into MSQuery I would love to hear how you did it.
I'm starting to pull my few remaining hairs out!

[ This Message was edited by: Tuner on 2002-11-07 12:23 ]

[ This Message was edited by: Tuner on 2002-11-07 12:28 ]

[ This Message was edited by: Tuner on 2002-11-12 12:51 ]

View 6 Replies View Related

Multiple Parameters Using A Range Of Cells To MSQuery

Apr 17, 2003

how to pass multiple parameters using a range of cells to MSQuery? When I try to it tells me that I can select a single cell only. Anyone know of a new and improved sql driver to use with Excel?

View 9 Replies View Related

Disable Auto-calculate When Updating Queries Through MSQuery

May 21, 2009

Running XL07. Need to have one workbook pull data from several dozen others.

Have columns to the right that refer to the query table.

As I add in queries to other workbooks, the time to update each individual query goes up a lot; it feels as if the update time is increasing geometrically. I'm giving up after 2 hours, for query updates that used to take

View 9 Replies View Related

Simple Excel Using MSQuery To Access Data In One Of Tabs And Allow Updates

Jul 1, 2014

I'm trying to create a simple Excel spreadsheet (not looking for Access or complex relational database stuff) to have the following:

A tab with data about a person : name, date of birth, join date, and a list of sessions they attend (eg. Monday Morning, Monday afternoon etc).

A different tab in the same sheet, or a form to select from that list of people, and allow changes to the information, and the ability to save it back to the data tab. Ability to add new people or delete people would be useful.

I am looking to create this as a basis for managing sessions, creating a register etc....

Any simple spreadsheet which I can use as a starting point, or to use as a reference in getting it established?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved