Strings - variable-length string

W

WebDude

Hi :)

Using VBA (MS Access 2000) I would like to store a string of over 10,000
Characters ... but the usual "Dim sqlString as String" only seems to hold
1,800 characters then it bombs... However, I read that a "variable-length
string" can hold over 2 billion characters so, how does one declare a
"variable-length string" in vba?

Dim sqlString as VariableString?

Cheers,
WebDude
 
W

WebDude

Tis me again,
I got this from the Access Help,
on the Index tab, under Access Specifications:

---------------------------------------------------------------------------------------------
Microsoft Access database query specifications
---------------------------------------------------------------------------------------------
Number of enforced relationships
..............................................32 per table
Number of tables in a
query..................................................... 32
Number of fields in a
recordset.................................................255
Recordset size
.........................................................................1
gigabyte
Sort limit
.................................................................................255 characters in one or more fields
Number of levels of nested
queries............................................ 50
Number of characters in a cell in the query design grid ...............1,024
Number of characters for a parameter in a parameter query....... 255
Number of ANDs in a WHERE or HAVING clause......................... 40
Number of characters in an SQL statement...............................
approximately 64,000
-------------------------------------------------------------------------------------------------------


I dont think my query exceeds any of the limitations stated above.
My query is just a bunch of UNION ALL statements, that keep repeating,
see if you notice the pattern......


SELECT * FROM Volunteers_Inquiry ' <---- these are names of presaved queries
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_Applying
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_WaitingToBeAssigned
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_Assigned
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_onVacation
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_Retired
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_MIA
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_DoNotPlace
WHERE [VolunteerRoleClerical] Is Not Null

UNION ALL SELECT * FROM Volunteers_Inquiry
WHERE [VolunteerRoleCreativePlay] Is Not Null ' begins on a new field
UNION ALL SELECT * FROM Volunteers_Applying
WHERE [VolunteerRoleCreativePlay] Is Not Null
UNION ALL SELECT * FROM Volunteers_WaitingToBeAssigned
WHERE [VolunteerRoleCreativePlay] Is Not Null
UNION ALL SELECT * FROM Volunteers_Assigned
WHERE [VolunteerRoleCreativePlay] Is Not Null
UNION ALL SELECT * FROM Volunteers_onVacation
WHERE [VolunteerRoleCreativePlay] Is Not Null
UNION ALL SELECT * FROM Volunteers_Retired
WHERE [VolunteerRoleCreativePlay] Is Not Null
UNION ALL SELECT * FROM Volunteers_MIA
WHERE [VolunteerRoleCreativePlay] Is Not Null
UNION ALL SELECT * FROM Volunteers_DoNotPlace
WHERE [VolunteerRoleCreativePlay] Is Not Null;

' there are 9 more fields after
' so the process can repeat a total of 11 times.
' its up to the discretion of the user.

'in any event, i wonder if there is a way
'i can write down the Where statements just ONCE
'per block (above) instead of repeating it
'every second line.



Regards,
WebDude
 
D

Duane Hookom

Do you actually have that many separate but identical tables? If so, is
there a reason why you don't have just one with a status field?

--
Duane Hookom
MS Access MVP


WebDude said:
Tis me again,
I got this from the Access Help,
on the Index tab, under Access Specifications:

---------------------------------------------------------------------------------------------
Microsoft Access database query specifications
---------------------------------------------------------------------------------------------
Number of enforced relationships
.............................................32 per table
Number of tables in a
query..................................................... 32
Number of fields in a
recordset.................................................255
Recordset size
........................................................................1
gigabyte
Sort limit
................................................................................255
characters in one or more fields
Number of levels of nested
queries............................................ 50
Number of characters in a cell in the query design grid
...............1,024
Number of characters for a parameter in a parameter query....... 255
Number of ANDs in a WHERE or HAVING clause......................... 40
Number of characters in an SQL statement...............................
approximately 64,000
-------------------------------------------------------------------------------------------------------


I dont think my query exceeds any of the limitations stated above.
My query is just a bunch of UNION ALL statements, that keep repeating,
see if you notice the pattern......


SELECT * FROM Volunteers_Inquiry ' <---- these are names of presaved
queries
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_Applying
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_WaitingToBeAssigned
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_Assigned
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_onVacation
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_Retired
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_MIA
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_DoNotPlace
WHERE [VolunteerRoleClerical] Is Not Null

UNION ALL SELECT * FROM Volunteers_Inquiry
WHERE [VolunteerRoleCreativePlay] Is Not Null ' begins on a new field
UNION ALL SELECT * FROM Volunteers_Applying
WHERE [VolunteerRoleCreativePlay] Is Not Null
UNION ALL SELECT * FROM Volunteers_WaitingToBeAssigned
WHERE [VolunteerRoleCreativePlay] Is Not Null
UNION ALL SELECT * FROM Volunteers_Assigned
WHERE [VolunteerRoleCreativePlay] Is Not Null
UNION ALL SELECT * FROM Volunteers_onVacation
WHERE [VolunteerRoleCreativePlay] Is Not Null
UNION ALL SELECT * FROM Volunteers_Retired
WHERE [VolunteerRoleCreativePlay] Is Not Null
UNION ALL SELECT * FROM Volunteers_MIA
WHERE [VolunteerRoleCreativePlay] Is Not Null
UNION ALL SELECT * FROM Volunteers_DoNotPlace
WHERE [VolunteerRoleCreativePlay] Is Not Null;

' there are 9 more fields after
' so the process can repeat a total of 11 times.
' its up to the discretion of the user.

'in any event, i wonder if there is a way
'i can write down the Where statements just ONCE
'per block (above) instead of repeating it
'every second line.



Regards,
WebDude
Click to expand...
 
W

WebDude

SELECT * FROM Volunteers_Inquiry
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_Applying
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_WaitingToBeAssigned
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_Assigned
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_onVacation
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_Retired
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_MIA
WHERE [VolunteerRoleClerical] Is Not Null

Do you actually have that many separate but identical tables? If so, is
there a reason why you don't have just one with a status field?
Click to expand...

Duane,
These are saved queries (not tables)..

"Volunteers_Inquiry"
"Volunteers_Applying"
"Volunteers_WaitingToBeAssigned"
"Volunteers_Assigned"
"Volunteers_onVacation"
...etc

Each Query is based on 1 to six UniqueTables and
(each query) has a slew of calculated fields.
Nonetheless, they (the queries) can be UNIONed cause
ive kept their number of fields identical.

I do UNION them on several
other forms but What makes this time more
difficult is the way im displaying the data..

Im listing people by the number they entered
in field1, then at the end of that list, listing people
by the number they put in field2, then at the end of that list
listing people by the number they put in field3..etc

(field1 would be [VolunteerRoleClerical])
(field2 would be [VolunteerRoleCreativePlay])
(..etc)

The user says which people to include on
the list AND which fields to include (by pressing buttons
on the form).


Cheers,
WebDude Awaaaaay
 
D

Duane Hookom

I believe your problem is un-normalized tables. Do you have fields in a
table like [VolunteerRoleClerical] [VolunteerRoleCreativePlay] ..etc? If so,
how do you start tracking new roles? You shouldn't have to create new fields
and modify code, union queries, forms, reports,...

A better system creates a record for each role for each person.

--
Duane Hookom
MS Access MVP


WebDude said:
SELECT * FROM Volunteers_Inquiry
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_Applying
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_WaitingToBeAssigned
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_Assigned
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_onVacation
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_Retired
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_MIA
WHERE [VolunteerRoleClerical] Is Not Null

Do you actually have that many separate but identical tables? If so, is
there a reason why you don't have just one with a status field?
Click to expand...

Duane,
These are saved queries (not tables)..

"Volunteers_Inquiry"
"Volunteers_Applying"
"Volunteers_WaitingToBeAssigned"
"Volunteers_Assigned"
"Volunteers_onVacation"
..etc

Each Query is based on 1 to six UniqueTables and
(each query) has a slew of calculated fields.
Nonetheless, they (the queries) can be UNIONed cause
ive kept their number of fields identical.

I do UNION them on several
other forms but What makes this time more
difficult is the way im displaying the data..

Im listing people by the number they entered
in field1, then at the end of that list, listing people
by the number they put in field2, then at the end of that list
listing people by the number they put in field3..etc

(field1 would be [VolunteerRoleClerical])
(field2 would be [VolunteerRoleCreativePlay])
(..etc)

The user says which people to include on
the list AND which fields to include (by pressing buttons
on the form).


Cheers,
WebDude Awaaaaay
Click to expand...
 
T

Tim Ferguson

SELECT * FROM Volunteers_Inquiry ' <---- these are names of presaved
queries WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_Applying
WHERE [VolunteerRoleClerical] Is Not Null
Click to expand...

I am not sure whether the extra SELECT keyword is illegal, but it sure is
unnecessary and at best confusing. Putting the ALL before the SELECT does
not make sense either, since the ALL is there to qualify which records to
SELECT -- it's the equivalent of "TOP 10 SELECT something..." which is
plainly nonsensical. A normal UNION command looks like


SELECT ALL OneField, TwoField FROM ATable

UNION ALL AField, BField FROM AnotherTable

UNION ALL Field1, Field2 FROM YetAnotherTable




And I would strongly echo what Duane says about revisiting your design.



B Wishes


Tim F
 
W

WebDude

Duane,

Thanks for taking the time
and replying to my posts :)




Our principle table is called "Volunteers" . And In that table, each record
includes the following usual fields; ID, FName,LName,telephone...(etc.)..
plus the following NUMBER fields;

[VolunteerRoleClerical]
[VolunteerRoleCreativePlay]
[VolunteerRoleClothingRoom]
[VolunteerRoleDriver]
[VolunteerRoleFamilySupport]
[VolunteerRoleTutor]
[VolunteerRoleOlderBrother]
[VolunteerRoleOlderSister]
[VolunteerRoleGraphicDesigner]
[VolunteerRoleProgramAnimation]
[VolunteerRoleOther]

For each field/role above the volunteer ranks his/her preference for that
role by entering a number (1 to 11) in that Numberfield. For example, a
volunteer might enter a "1" in the [VolunteerRoleDriver] numberfield meaning
that their first choice for a role would be a Driver. Then the same person
might enter a "2" in the [VolunteerRoleOlderSister] field indicating her
second choice would be an OlderSister role...etc etc. This also allows for
duplicate rankings say, if she wanted to be a Tutor just as much as an
OlderSister and ProgramAnimator.. then shed rank them all as "1". Or if she
just wanted to be a Driver, then shed put a "1" in the driver field and leave
all the other fields null.

Having said that, I would like to search for people who ranked OlderSister
(for example) as a role they might like, then sort those peolpe by the
ranking they gave. Then go down the list, calling people who put older sister
as their first choice, then calling those who put older sister as their
second choice.. etc. make sense right?

:)

However, even before getting to that, we group volunteers into 8 more
important categories.. (which we saved as queries for easy access)...

Volunteers_Inquiry
Volunteers_Applying
Volunteers_WaitingToBeAssigned
Volunteers_Assigned
Volunteers_onVacation
Volunteers_Retired
Volunteers_MIA
Volunteers_DoNotPlace

Each query above has several long calculated fields pulling information from
relationships with 5 other tables so we can generate nice looking reports and
graphs :-DDDDDDDD

So, id like to be able to call up all the Volunteers who ranked OlderSister
as a role theyd like.. within the "Applying" AND "WaitingToBeAssigned"
categories...yet, id still like the option of looking thru the "Retired"
group of volunteers for an OlderSister.. keep in mind we get over 100 NEW
volunteers every year which is why we created those 8 catagories/queries.



"Duane Hookom"
I believe your problem is un-normalized tables. Do you have fields in a
table like [VolunteerRoleClerical] [VolunteerRoleCreativePlay] ..etc? If so,
how do you start tracking new roles? You shouldn't have to create new fields
and modify code, union queries, forms, reports,...
Click to expand...

We do not add new roles.
They have remained the same 11 roles
since we started thirty years ago.

A better system creates a record for each role for each person.
Click to expand...

Yes, i like that idea.
But subforms on forms look aweful,
and im thinking the sql might not be any easier
to write.


Thanks again Duane for taking the time to read this!!!!!!!


Cheers,
WebDude.



ps: if you know of a way to abbreviate this query, id appreciate it;


SELECT * FROM Volunteers_Inquiry
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_Applying
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_WaitingToBeAssigned
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_Assigned
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_onVacation
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_Retired
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_MIA
WHERE [VolunteerRoleClerical] Is Not Null
 
D

Douglas J. Steele

Tim Ferguson said:
SELECT * FROM Volunteers_Inquiry ' <---- these are names of presaved
queries WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_Applying
WHERE [VolunteerRoleClerical] Is Not Null
Click to expand...

I am not sure whether the extra SELECT keyword is illegal, but it sure is
unnecessary and at best confusing. Putting the ALL before the SELECT does
not make sense either, since the ALL is there to qualify which records to
SELECT -- it's the equivalent of "TOP 10 SELECT something..." which is
plainly nonsensical. A normal UNION command looks like


SELECT ALL OneField, TwoField FROM ATable

UNION ALL AField, BField FROM AnotherTable

UNION ALL Field1, Field2 FROM YetAnotherTable
Click to expand...

Um, I don't think so, Tim.

Each subquery in a UNION query needs to be able to stand alone.

And the reason for UNION ALL is that it doesn't eliminate duplicate rows, as
the normal UNION operator does.
And I would strongly echo what Duane says about revisiting your design.
Click to expand...

No question here!
 
W

WebDude

Tim Ferguson said:
I am not sure whether the extra SELECT keyword is illegal, but it sure is
unnecessary and at best confusing. Putting the ALL before the SELECT does
not make sense either, since the ALL is there to qualify which records to
SELECT -- it's the equivalent of "TOP 10 SELECT something..." which is
plainly nonsensical.
Click to expand...

Im not that smart, ill admit, but i went over this with another poster
months ago;

http://www.microsoft.com/office/com...ries&mid=de38d02b-edb2-4e5d-ade0-f13da61db543



A normal UNION command looks like

SELECT ALL OneField, TwoField FROM ATable

UNION ALL AField, BField FROM AnotherTable

UNION ALL Field1, Field2 FROM YetAnotherTable
Click to expand...

I.. dont want field1,field2.. i want all the fields from each table.
how would you then write that?

Cheers,
WebDude.


And I would strongly echo what Duane says about revisiting your design.
Click to expand...

I posted a lenghty reply to Duane,
wherein i elaborate on the details of
my design.
 
D

Duane Hookom

I, personally would normalize the tables before going on to anything else.

--
Duane Hookom
MS Access MVP


WebDude said:
Duane,

Thanks for taking the time
and replying to my posts :)




Our principle table is called "Volunteers" . And In that table, each
record
includes the following usual fields; ID, FName,LName,telephone...(etc.)..
plus the following NUMBER fields;

[VolunteerRoleClerical]
[VolunteerRoleCreativePlay]
[VolunteerRoleClothingRoom]
[VolunteerRoleDriver]
[VolunteerRoleFamilySupport]
[VolunteerRoleTutor]
[VolunteerRoleOlderBrother]
[VolunteerRoleOlderSister]
[VolunteerRoleGraphicDesigner]
[VolunteerRoleProgramAnimation]
[VolunteerRoleOther]

For each field/role above the volunteer ranks his/her preference for that
role by entering a number (1 to 11) in that Numberfield. For example, a
volunteer might enter a "1" in the [VolunteerRoleDriver] numberfield
meaning
that their first choice for a role would be a Driver. Then the same
person
might enter a "2" in the [VolunteerRoleOlderSister] field indicating her
second choice would be an OlderSister role...etc etc. This also allows for
duplicate rankings say, if she wanted to be a Tutor just as much as an
OlderSister and ProgramAnimator.. then shed rank them all as "1". Or if
she
just wanted to be a Driver, then shed put a "1" in the driver field and
leave
all the other fields null.

Having said that, I would like to search for people who ranked OlderSister
(for example) as a role they might like, then sort those peolpe by the
ranking they gave. Then go down the list, calling people who put older
sister
as their first choice, then calling those who put older sister as their
second choice.. etc. make sense right?

:)

However, even before getting to that, we group volunteers into 8 more
important categories.. (which we saved as queries for easy access)...

Volunteers_Inquiry
Volunteers_Applying
Volunteers_WaitingToBeAssigned
Volunteers_Assigned
Volunteers_onVacation
Volunteers_Retired
Volunteers_MIA
Volunteers_DoNotPlace

Each query above has several long calculated fields pulling information
from
relationships with 5 other tables so we can generate nice looking reports
and
graphs :-DDDDDDDD

So, id like to be able to call up all the Volunteers who ranked
OlderSister
as a role theyd like.. within the "Applying" AND "WaitingToBeAssigned"
categories...yet, id still like the option of looking thru the "Retired"
group of volunteers for an OlderSister.. keep in mind we get over 100 NEW
volunteers every year which is why we created those 8 catagories/queries.



"Duane Hookom"
I believe your problem is un-normalized tables. Do you have fields in a
table like [VolunteerRoleClerical] [VolunteerRoleCreativePlay] ..etc? If
so,
how do you start tracking new roles? You shouldn't have to create new
fields
and modify code, union queries, forms, reports,...
Click to expand...

We do not add new roles.
They have remained the same 11 roles
since we started thirty years ago.

A better system creates a record for each role for each person.
Click to expand...

Yes, i like that idea.
But subforms on forms look aweful,
and im thinking the sql might not be any easier
to write.


Thanks again Duane for taking the time to read this!!!!!!!


Cheers,
WebDude.



ps: if you know of a way to abbreviate this query, id appreciate it;


SELECT * FROM Volunteers_Inquiry
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_Applying
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_WaitingToBeAssigned
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_Assigned
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_onVacation
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_Retired
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_MIA
WHERE [VolunteerRoleClerical] Is Not Null
Click to expand...
 
T

Tim Ferguson

Um, I don't think so, Tim.

Each subquery in a UNION query needs to be able to stand alone.
Click to expand...

Um, yes you're right. I did a bit of experimenting after I saw the same
syntax in another thread of yours, and it does make sense.

That'll teach me to check before posting... :)


All the best

Tim F
 
T

Tim Ferguson

Im not that smart, ill admit, but i went over this with another poster
months ago;

http://www.microsoft.com/office/community/en-us/default.mspx?dg=microso
ft.public.access.queries&mid=de38d02b-edb2-4e5d-ade0-f13da61db543
Click to expand...

Okay, point taken: I was wrong. It's the second ALL that is missing since
it's the default for SELECT anyway.
I.. dont want field1,field2.. i want all the fields from each table.
how would you then write that?
Click to expand...

I would _always_ specify the field list in UNION as well as in INSERT
statements. In priniciple, there is no way to know what order table
columns will be presented in: although it usually is the same every time,
can you afford your data to be manipulated on the basis of "usually"?

All the best


Tim F
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

String variable is limited to 256 characters 5
Define text record with fields of defined length 2
String Data Type 5
chop off last 5 characters of a string 9
how may characters in String data type? 4
Can't pass string >255 characters to Word document with VBA 3
Garbage in a string variable loaded from a field 2
String Variable Truncating At 255 Chars 3

Top