Related Topics

Query Maximum String Length

same length query
Im a bit of a noob at this and im trying to work out how to show the hoses that have the same length as another one. This is as close as I got which all it shows is which hose lengths appear more than once. I used the Having count >1 because if it doesnt appear at least once it cant have another matching length. I think im thinking to hard about this.

SELECT hose.length
FROM hose
WHERE hose.length=(length)
GROUP BY length
HAVING Count(hose.length)&gt


Code to fill data with zeros or spaces
I've been looking for a VB code that can go through every row in my table and fill the cells with blank spaces or zeros to the maximum field length.

It would need to go through every column and row.

For example:

If I have a field that has a maximum field length of 10 characters and the cell has data which is 5 characters - I need the remainder to be filled with 5 blank spaces if its a text cell.

If I have a field that has a maximum field length of 15 characters and the cell has data which is 2 characters - I need the remainder to be filled with 13 blank spaces.


Indicate nonexistent data with a zero-length string
To indicate nonexistent data in a Microsoft Access database, you can enter a zero-length string into a Text or Memo field. In a Microsoft Access project that is connected to a Microsoft SQL Server database, you can enter a zero-length string in a field whose data type is varchar or nvarchar.


Selecting only fields with certain string length
Please could you tell me how to select only fields that have a specific string length (e.g. 8 characters)?


Extract numbers from text string strored in a field.
Anyone know how to create a query or function to extract numbers from a text string? The strings (containing both numbers and texts) are in one field in a table and are variable length. I would like to extract the numbers from the string and return only the numbers portion. The output portion will need to be placed into a different field.

Example, I have a table called "Furniture". Within this table there are two fields - the first field is called SerialNumber and the second field is called SCode. The first field contains the number and text strings and the second field is blank. I want to be able to run the query or function to extract the number portions from the SerialNumber field into the SCode field. The SerialNumber is variable length; the number and text string is also variable lenght. The total length can be up to 32 characters.

E.g. 123456ABCDEF
POUYTR12QQQQTQQQ


Converting Nulls Form Entries to Zero-Length Strings
My database is setup to not except nulls. So, on my forms, if I leave a field blank, I get an error about the Null entry. If I enter a "" in the field and tab out of it, it appears blank, but is a zero-length string entry. How can I automatically take all Null form field entries and convert them to zero-length string values before the save happens so that I don't get this error


All table query
I want to run a query that checks all tables/all fields for null or zero-length string values.


Avoiding divide by zero errors in Access
If you try to divide a number by zero in Access, you receive a #DIV/0 error if you are in a form or report, or a #ERROR error if you are in a query. To avoid a divide by zero error, you can use the IIF function to set the results field to a zero-length string ("") if the divisor in a calculation is zero. You can then search the field for any values that contain a zero-length string and flag the results. The following steps demonstrate this technique by creating a table and a bound form, inserting three text boxes, and entering an expression.


Querying field length
I have 2 tables. Table1 defines the permitted field lentghs for Table2

eg
Table1
NameOfField MaxLength
Field1 50
Field2 20
Field3 20
etc etc

Table2
Field1 Field2 Field3
Example 12345 54321

What I need to do is to query the data in Table2 to find any data that exceeds the maximum permitted field length and then shows which field is at fault.

There are more than 50 fields and hundreds of records, I'd like to avoid doing this manually


Maximum size for exporting a table
Is there a maximum size of the table that can be exported from Access to a text file? I have a table with a length of 519. After exported the table to text, the content seems to be truncated.


TRIM function not working.
Apparently there is a design issue which I don't understand. I have a table with an Account column (account number). It's of type TEXT and length 50.

For some reason Jet has it as a fixed-length column. How do I undo that? That is to say, even if I insert a 10-digit account number, Jet is storing a string of 50 characters (40 spaces).

I'm accessing the DB from VB.Net (actually C#.Net) but have the same problem even when I try it from Access VBA. Due to the fixed-width, even if I run this query,

UPDATE Posted SET Account = Trim(Account)

Jet ignores it - I still get a 50 char string when I run a select query from the table.

SELECT Account FROM Posted

The only way to get a 10-char string is this:

SELECT Trim(Account) FROM Posted

How did I get a fixed-width column?


Check Number of spaces in a string?
I'm trying to run a validation in some script to check a string and find how many of the characters (max string length of 50) are a space.

Thus 'ACC1 NEWTYPE FLEET' would
return a 3


Substring Query
I'm interested in searching a table with a string valued field for variable definition statements. The sub string would be "AS ." I think this might be done effectively using a LIKE statement

SELECT [Raw Data].Field1, *
FROM [Raw Data]
WHERE ((([Raw Data].Field1) Like "* As *"));

However this will return the entire string containing the sub string, I'm only interested in the variable type, which comes three charterers after the A in AS. I can't use the Mid function, at least not in a trivial way, since the length before the AS varies.


navigate between forms dynamically
I have developed a form to display the pareto analysis of a table ( by building a query initially and the making a form using chart wizard).

The item that appears as maximum I.e. the one on the left most side has another report about what causes this item to be maximum.

The item that occupies this 'maximum' place changes from time to time. thus it has to be dynamic. Something like what we have for mobile maybe where we select what we are interested in and that opens and once we are done we can go back to the earlier page.


continuing lines of code vba-access
I'm really new to having to do anything in visual basic. But, I have edited some code before that selects certain customers when a button is clicked on and then generates a report.

I don't know exactly how to continue code onto another line, as I reached my maximum length for the first line of customers. I thought I had it figured out, but I was getting a "run time error 7769 the filter would be too long error" after running it.

Here is an example of the code, how do I continue assigning customer names to the button click on another line of code, and is there a string length limit as far as how many customers I can assign to that button click?

Private Sub Command47_Click()
Me!Text2 = "='Cust1' or [CUS_CorpName]='Cust2' or [CUS_CorpName]='Cust3' or [CUS_CorpName]='Cust4' or [CUS_CorpName]='Cust5' "
strCriteria = "[SOD_RequiredDate] Between #" & Me![DTPicker8] & "# And #" & Me![DTPicker9] & "# And [CUS_CorpName]" & Me!Text2
' And [CUS_CorpName] = " & Me!Text


What variable type to use with memo field in VBA
on VBA Access 2003/07,
I have a VBA class module that loads a single record from 1 of 4 tables (via a main form and datasheet subforms) to another form.
Most of the fields are keys however 1 field is memo data type.

What data type is best to use for the variable / property procedure in the class module. String seems to work but are there any limits in string length in vba, or a better way of achieving this. I seem to remember reading somewhere that memo datatype fields the data is not stored in the access db only a reference, is there any way of using this and passing the reference to the text box

Also the data is passed to the form via an ADO recordset am I right in saying that there is no issue with string length in ADO recordsets


Calculating Average Length of Time
I have a query which when run calculates the average length of time, which works most of the time. However there are a few instances in which it is far from accurate, usually with overnight time spans such as 6pm to 3am.

The times are entered and stored in a medium time format. 10:00AM as an example.

The calculation that obtains the actual length of time is:

IIf([End_Time]<8,([End_Time]+24)-[Start_Time],[End_Time]-[Start_Time])

And returns the following as the length of time.

1/22/1900 7:30:00 AM 1/23/1900 8:15:00 AM 1/22/1900 9:30:00 AM

Would be translated into 7 and 1/2 hours.

I have a feeling that how this calculation is made is the cause of the error in its ability to accurately calculate the average time frames


Instr to find the position of the first Alpha character?
I need to find the position of the first alpha character in a string, the section of the string prior to the alpha character is variable in length, hence I need to be able to find the position to feed to a left/right/mid style expression to be able to break down the original string into useable parts.

I need to do this across circa 440k rows,


char datatype? (not varchar)
Does MS Access support the char datatype? I just don't mean strings/text in general, but a fixed length string - vs varchar?

I need to build a loadfile for our health care provider and their fixed format loadfile prefers that certain information start on certain columns. e.g. The first 9 chars are SSN, then the next 25 are for fname, the next 25 for lname.

123456789FIRSTNAME
LASTNAME
[end of line]

. the idea is to pad out the remaining characters with blank spaces, having the data values be left adjusted.

Of course I could grab the length of the characters and subtract that from the fields available length, and concat the needed spaces. but. well. I was wondering if I didn't have to.

All I see if the text datatype. Is there something more in Acc


Check length of string which includes asterisks
I want to enforce a minimum string length in a search. The following code gives the messagebox when the text box txtNameSearch is empty, but proceeds to fill the listbox with results if the text box has one or more characters in it.
Is there some special issue with asterisks in this context?

strName = Chr(34) & "*" & txtNameSearch & "*" & Chr(34) If Len(strName) < 5 Then Dim intMsgBoxResult As Integer intMsgBoxResult = MsgBox("Enter 3 or more letters of the name", vbOKOnly + vbExclamation, "Need More Letters!") Else 'create a record set and put it in a listbox