Topics Search

Query Maximum String Length

Query Maximum String Length
Views: 13
I am currently writing some VBA code which creates a query string in code and then copies it into the query for use on a form.

I seem to remember reading somewhere that there is a limit to the number of characters that can be used in a query string, somewhere about 2000 I think. Does anyone one have an exact figure for this?
Sponsored Links:

More topics


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.

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

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

NameOfField MaxLength
Field1 50
Field2 20
Field3 20
etc etc

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?