Fill array - Help!

  • Thread starter Thread starter Lars Brownie
  • Start date Start date
L

Lars Brownie

I need to fill an array with 1, 2, or 3 search values, depending on whether
the 3 search fields in the form have a value. So if searchfields 2 and 3
have a value I should have an array of 2 values. If only search field 1 has
a value, the array should only have 1 value, etc. I need the array to loop
through it, to build a WHERE clause for a query.

I've been trying to figure this out for a few hours with no luck and I don't
understand the code I find on the internet. Seems I'm having a block. Can
someone put me in the right direction and possibly provide some sample code.

Thank you, Lars
 
Lars

This may be a matter of semantics...

In Access & VBA, an "array" refers to an in-memory collection. Based on
your description, I'm not clear if that's what you are doing.

It sounds like what you want to do is create a dynamic WHERE clause, based
on whether there are values in controls on a form (in Access, the thingies
on the form and report are called "controls", not "fields"). If so, you
don't need an "array" to do this.

Your code behind the <Search> button could include something like
(untested):

If Len(Me!YourFirstSearchControl)>0 Then
strWHERE = [YourTableFieldName] & " = " & ...

where the "..." indicates that you'll need to use suitable delimiters for
text or date/time values.

You could repeat this approach, changing the second line to something like:

strWHERE = strWHERE & ...

where the "..." indicates whatever you need to (dynamically) add.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks for the reaction, but it's more complicated than that. Depending on
user's choice there is AND or OR operator which have to be included in the
WHERE clause when there is more than one search field filled. With all 3
fields filled there have to be 2 of those operators added. It's important
that when a user for instance searches on field 1 and 2, he gets no values,
that he can delete the value in field 1, and press the Search button again.
It's that last part that's causing me problems. I had this code snippet,
which obviously doesn't take care of this last part:

If Not IsNull(fldSearch1) Then
stSQL2 = stSQL2 & "(" & strTb & "." & strFld & ") Like ""*" & fldSearch1
& "*"""
If Not IsNull(fldSearch2) Then
stSQL2 = stSQL2 & " " & strAndOr & " (" & strTb & "." & strFld & ")
Like ""*" & fldSearch2 & "*"""
If Not IsNull(fldSearch1) Then
stSQL2 = stSQL2 & " " & strAndOr & " (" & strTb & "." & strFld &
") Like ""*" & fldSearch3 & "*"""
End If
End If
End If

stSQL = stSQL & " WHERE ((" & stSQL2 & "))"

Lars

Jeff Boyce said:
Lars

This may be a matter of semantics...

In Access & VBA, an "array" refers to an in-memory collection. Based on
your description, I'm not clear if that's what you are doing.

It sounds like what you want to do is create a dynamic WHERE clause, based
on whether there are values in controls on a form (in Access, the thingies
on the form and report are called "controls", not "fields"). If so, you
don't need an "array" to do this.

Your code behind the <Search> button could include something like
(untested):

If Len(Me!YourFirstSearchControl)>0 Then
strWHERE = [YourTableFieldName] & " = " & ...

where the "..." indicates that you'll need to use suitable delimiters for
text or date/time values.

You could repeat this approach, changing the second line to something
like:

strWHERE = strWHERE & ...

where the "..." indicates whatever you need to (dynamically) add.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


Lars Brownie said:
I need to fill an array with 1, 2, or 3 search values, depending on
whether the 3 search fields in the form have a value. So if searchfields 2
and 3 have a value I should have an array of 2 values. If only search
field 1 has a value, the array should only have 1 value, etc. I need the
array to loop through it, to build a WHERE clause for a query.

I've been trying to figure this out for a few hours with no luck and I
don't understand the code I find on the internet. Seems I'm having a
block. Can someone put me in the right direction and possibly provide
some sample code.

Thank you, Lars
Click to expand...
Click to expand...
 
Lars

If this were mine to solve, I'd add in either
Debug.Print ...
statements or
Msgbox ...
statements or add a breakpoint so I could inspect what the code was doing at
each step of the process.

That would break it into more manageable pieces than trying to get it all at
once. In fact, I probably would just get a single field's dynamic SQL
expression working first, then try adding others.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Lars Brownie said:
Thanks for the reaction, but it's more complicated than that. Depending on
user's choice there is AND or OR operator which have to be included in the
WHERE clause when there is more than one search field filled. With all 3
fields filled there have to be 2 of those operators added. It's important
that when a user for instance searches on field 1 and 2, he gets no
values, that he can delete the value in field 1, and press the Search
button again. It's that last part that's causing me problems. I had this
code snippet, which obviously doesn't take care of this last part:

If Not IsNull(fldSearch1) Then
stSQL2 = stSQL2 & "(" & strTb & "." & strFld & ") Like ""*" &
fldSearch1 & "*"""
If Not IsNull(fldSearch2) Then
stSQL2 = stSQL2 & " " & strAndOr & " (" & strTb & "." & strFld & ")
Like ""*" & fldSearch2 & "*"""
If Not IsNull(fldSearch1) Then
stSQL2 = stSQL2 & " " & strAndOr & " (" & strTb & "." & strFld
& ") Like ""*" & fldSearch3 & "*"""
End If
End If
End If

stSQL = stSQL & " WHERE ((" & stSQL2 & "))"

Lars

Jeff Boyce said:
Lars

This may be a matter of semantics...

In Access & VBA, an "array" refers to an in-memory collection. Based on
your description, I'm not clear if that's what you are doing.

It sounds like what you want to do is create a dynamic WHERE clause,
based on whether there are values in controls on a form (in Access, the
thingies on the form and report are called "controls", not "fields"). If
so, you don't need an "array" to do this.

Your code behind the <Search> button could include something like
(untested):

If Len(Me!YourFirstSearchControl)>0 Then
strWHERE = [YourTableFieldName] & " = " & ...

where the "..." indicates that you'll need to use suitable delimiters for
text or date/time values.

You could repeat this approach, changing the second line to something
like:

strWHERE = strWHERE & ...

where the "..." indicates whatever you need to (dynamically) add.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


Lars Brownie said:
I need to fill an array with 1, 2, or 3 search values, depending on
whether the 3 search fields in the form have a value. So if searchfields
2 and 3 have a value I should have an array of 2 values. If only search
field 1 has a value, the array should only have 1 value, etc. I need the
array to loop through it, to build a WHERE clause for a query.

I've been trying to figure this out for a few hours with no luck and I
don't understand the code I find on the internet. Seems I'm having a
block. Can someone put me in the right direction and possibly provide
some sample code.

Thank you, Lars
Click to expand...
Click to expand...
Click to expand...
 
The code is already working, with the restriction that you have to have at
least field 1 entered. It's part of a function and the final result is a
union query over 5 different tables.

Lars

Jeff Boyce said:
Lars

If this were mine to solve, I'd add in either
Debug.Print ...
statements or
Msgbox ...
statements or add a breakpoint so I could inspect what the code was doing
at each step of the process.

That would break it into more manageable pieces than trying to get it all
at once. In fact, I probably would just get a single field's dynamic SQL
expression working first, then try adding others.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Lars Brownie said:
Thanks for the reaction, but it's more complicated than that. Depending
on user's choice there is AND or OR operator which have to be included in
the WHERE clause when there is more than one search field filled. With
all 3 fields filled there have to be 2 of those operators added. It's
important that when a user for instance searches on field 1 and 2, he
gets no values, that he can delete the value in field 1, and press the
Search button again. It's that last part that's causing me problems. I
had this code snippet, which obviously doesn't take care of this last
part:

If Not IsNull(fldSearch1) Then
stSQL2 = stSQL2 & "(" & strTb & "." & strFld & ") Like ""*" &
fldSearch1 & "*"""
If Not IsNull(fldSearch2) Then
stSQL2 = stSQL2 & " " & strAndOr & " (" & strTb & "." & strFld &
") Like ""*" & fldSearch2 & "*"""
If Not IsNull(fldSearch1) Then
stSQL2 = stSQL2 & " " & strAndOr & " (" & strTb & "." & strFld
& ") Like ""*" & fldSearch3 & "*"""
End If
End If
End If

stSQL = stSQL & " WHERE ((" & stSQL2 & "))"

Lars

Jeff Boyce said:
Lars

This may be a matter of semantics...

In Access & VBA, an "array" refers to an in-memory collection. Based on
your description, I'm not clear if that's what you are doing.

It sounds like what you want to do is create a dynamic WHERE clause,
based on whether there are values in controls on a form (in Access, the
thingies on the form and report are called "controls", not "fields").
If so, you don't need an "array" to do this.

Your code behind the <Search> button could include something like
(untested):

If Len(Me!YourFirstSearchControl)>0 Then
strWHERE = [YourTableFieldName] & " = " & ...

where the "..." indicates that you'll need to use suitable delimiters
for text or date/time values.

You could repeat this approach, changing the second line to something
like:

strWHERE = strWHERE & ...

where the "..." indicates whatever you need to (dynamically) add.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


I need to fill an array with 1, 2, or 3 search values, depending on
whether the 3 search fields in the form have a value. So if searchfields
2 and 3 have a value I should have an array of 2 values. If only search
field 1 has a value, the array should only have 1 value, etc. I need the
array to loop through it, to build a WHERE clause for a query.

I've been trying to figure this out for a few hours with no luck and I
don't understand the code I find on the internet. Seems I'm having a
block. Can someone put me in the right direction and possibly provide
some sample code.

Thank you, Lars
Click to expand...
Click to expand...
Click to expand...
 
Lars

I'm confused. I thought the code was NOT working, that being why you
posted.

I'll still suggest adding in a breakpoint or some other mechanism to
learn/discover why and where it isn't doing what you expect it to.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Lars Brownie said:
The code is already working, with the restriction that you have to have at
least field 1 entered. It's part of a function and the final result is a
union query over 5 different tables.

Lars

Jeff Boyce said:
Lars

If this were mine to solve, I'd add in either
Debug.Print ...
statements or
Msgbox ...
statements or add a breakpoint so I could inspect what the code was doing
at each step of the process.

That would break it into more manageable pieces than trying to get it all
at once. In fact, I probably would just get a single field's dynamic SQL
expression working first, then try adding others.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Lars Brownie said:
Thanks for the reaction, but it's more complicated than that. Depending
on user's choice there is AND or OR operator which have to be included
in the WHERE clause when there is more than one search field filled.
With all 3 fields filled there have to be 2 of those operators added.
It's important that when a user for instance searches on field 1 and 2,
he gets no values, that he can delete the value in field 1, and press
the Search button again. It's that last part that's causing me problems.
I had this code snippet, which obviously doesn't take care of this last
part:

If Not IsNull(fldSearch1) Then
stSQL2 = stSQL2 & "(" & strTb & "." & strFld & ") Like ""*" &
fldSearch1 & "*"""
If Not IsNull(fldSearch2) Then
stSQL2 = stSQL2 & " " & strAndOr & " (" & strTb & "." & strFld &
") Like ""*" & fldSearch2 & "*"""
If Not IsNull(fldSearch1) Then
stSQL2 = stSQL2 & " " & strAndOr & " (" & strTb & "." &
strFld & ") Like ""*" & fldSearch3 & "*"""
End If
End If
End If

stSQL = stSQL & " WHERE ((" & stSQL2 & "))"

Lars

"Jeff Boyce" <[email protected]> schreef in bericht
Lars

This may be a matter of semantics...

In Access & VBA, an "array" refers to an in-memory collection. Based
on your description, I'm not clear if that's what you are doing.

It sounds like what you want to do is create a dynamic WHERE clause,
based on whether there are values in controls on a form (in Access, the
thingies on the form and report are called "controls", not "fields").
If so, you don't need an "array" to do this.

Your code behind the <Search> button could include something like
(untested):

If Len(Me!YourFirstSearchControl)>0 Then
strWHERE = [YourTableFieldName] & " = " & ...

where the "..." indicates that you'll need to use suitable delimiters
for text or date/time values.

You could repeat this approach, changing the second line to something
like:

strWHERE = strWHERE & ...

where the "..." indicates whatever you need to (dynamically) add.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


I need to fill an array with 1, 2, or 3 search values, depending on
whether the 3 search fields in the form have a value. So if
searchfields 2 and 3 have a value I should have an array of 2 values.
If only search field 1 has a value, the array should only have 1 value,
etc. I need the array to loop through it, to build a WHERE clause for a
query.

I've been trying to figure this out for a few hours with no luck and I
don't understand the code I find on the internet. Seems I'm having a
block. Can someone put me in the right direction and possibly provide
some sample code.

Thank you, Lars
Click to expand...
Click to expand...
Click to expand...
 
Sorry for the confusing. The original code was working but I needed the
array to add the functionality that it was not mandatory to have the first
search field filled. I couldn't get the array to work. I found the
'colletions' option and now the new functionality is working as well.

Lars

Jeff Boyce said:
Lars

I'm confused. I thought the code was NOT working, that being why you
posted.

I'll still suggest adding in a breakpoint or some other mechanism to
learn/discover why and where it isn't doing what you expect it to.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Lars Brownie said:
The code is already working, with the restriction that you have to have
at least field 1 entered. It's part of a function and the final result is
a union query over 5 different tables.

Lars

Jeff Boyce said:
Lars

If this were mine to solve, I'd add in either
Debug.Print ...
statements or
Msgbox ...
statements or add a breakpoint so I could inspect what the code was
doing at each step of the process.

That would break it into more manageable pieces than trying to get it
all at once. In fact, I probably would just get a single field's
dynamic SQL expression working first, then try adding others.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for the reaction, but it's more complicated than that. Depending
on user's choice there is AND or OR operator which have to be included
in the WHERE clause when there is more than one search field filled.
With all 3 fields filled there have to be 2 of those operators added.
It's important that when a user for instance searches on field 1 and 2,
he gets no values, that he can delete the value in field 1, and press
the Search button again. It's that last part that's causing me
problems. I had this code snippet, which obviously doesn't take care of
this last part:

If Not IsNull(fldSearch1) Then
stSQL2 = stSQL2 & "(" & strTb & "." & strFld & ") Like ""*" &
fldSearch1 & "*"""
If Not IsNull(fldSearch2) Then
stSQL2 = stSQL2 & " " & strAndOr & " (" & strTb & "." & strFld &
") Like ""*" & fldSearch2 & "*"""
If Not IsNull(fldSearch1) Then
stSQL2 = stSQL2 & " " & strAndOr & " (" & strTb & "." &
strFld & ") Like ""*" & fldSearch3 & "*"""
End If
End If
End If

stSQL = stSQL & " WHERE ((" & stSQL2 & "))"

Lars

"Jeff Boyce" <[email protected]> schreef in bericht
Lars

This may be a matter of semantics...

In Access & VBA, an "array" refers to an in-memory collection. Based
on your description, I'm not clear if that's what you are doing.

It sounds like what you want to do is create a dynamic WHERE clause,
based on whether there are values in controls on a form (in Access,
the thingies on the form and report are called "controls", not
"fields"). If so, you don't need an "array" to do this.

Your code behind the <Search> button could include something like
(untested):

If Len(Me!YourFirstSearchControl)>0 Then
strWHERE = [YourTableFieldName] & " = " & ...

where the "..." indicates that you'll need to use suitable delimiters
for text or date/time values.

You could repeat this approach, changing the second line to something
like:

strWHERE = strWHERE & ...

where the "..." indicates whatever you need to (dynamically) add.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


I need to fill an array with 1, 2, or 3 search values, depending on
whether the 3 search fields in the form have a value. So if
searchfields 2 and 3 have a value I should have an array of 2 values.
If only search field 1 has a value, the array should only have 1
value, etc. I need the array to loop through it, to build a WHERE
clause for a query.

I've been trying to figure this out for a few hours with no luck and
I don't understand the code I find on the internet. Seems I'm having
a block. Can someone put me in the right direction and possibly
provide some sample code.

Thank you, Lars
Click to expand...
Click to expand...
Click to expand...
 

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

Shape Arrays VBA 0
Array values variable 4
Arrays and scattered cell values or another way? 16
How array allocation is implemented in c#? 1
Help with an array of control variables. 4
Reallocate array cells value from positive to negative 0
Array to Multiple Arrays 3
Insert value into other array values 4

Back
Top