passing a parameter based on multiple selections

J

javablood

Hi,

I have a form in which several choices are selected, one of which is a list
box in which the user can make multiple selections. I followed the various
suggestions to create a String that has the selected items and I set a
textbox in the form as that final String as follows:

Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant

' Build a list of the selections.
Set ctl = Me![lboSTATID]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & " OR " & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm

Me.tboCriteria = Criteria

However, when another form (based on a query with the passed
parameters)opens to show me the selection, there are no data.

The correct String for the query parameter appears to be correct, i.e.,
"item1" OR "item2" and if I replace the same String in the query instead of
using the [Forms]![frmSearch]![tboCriteria] the query works fine.

Where am I going wrong? Any help is appreciated.

Thanks,
 
V

vanderghast

The syntax

"Los Angeles" OR "Baltimore" OR "Dallas"


is only valid in very few circumstances. It is far better to have:

IN( "Los Angeles", "Baltimore", "Dallas" )


and even then, you have to specify which field has to satisfy this IN-ness
constraint in many cases.

So, without knowing HOW you use that criteria, I strongly suggest that, at
least, you change the OR to a coma, and in the end, use

Me.tboCriteria = " IN (" & criteria & ")"


It may help (but again, depends on what you really do with that tboCriteria
thing).



Vanderghast, Access MVP
 
J

javablood

Here is the SQL I am using :

PARAMETERS [Forms]![frmSearch]![cboGroup] Text ( 255 ),
[Forms]![frmSearch]![lbodatbegin] DateTime, [Forms]![frmSearch]![lbodatend]
DateTime, [Forms]![frmSearch]![cboChem] Text ( 255 ),
[Forms]![frmSearch]![cboSamptype] Text ( 255 ), [Forms]![frmSearch]![cboDet]
Bit, [Forms]![frmSearch]![tboCriteria] Text ( 255 );
SELECT tblHardage.STATION_ID, tblHardage.SAMPLE_DATE, tblHardage.PARAMETER,
tblHardage.VALUE, tblHardage.FLAG, tblHardage.UNITS, tblHardagePAR.F1_GROUP,
tblHardage.QC, tblHardageSiteIdentification.ID_Group_Explain,
tblHardagePAR.F1_GRPORDR, tblHardage.DETECTED
FROM tblHardageFLD INNER JOIN ((tblHardage INNER JOIN tblHardagePAR ON
tblHardage.PARAMETER = tblHardagePAR.PARAMETER) INNER JOIN
tblHardageSiteIdentification ON tblHardage.STATION_ID =
tblHardageSiteIdentification.STATION_ID) ON tblHardageFLD.ENTRY =
tblHardage.QC
WHERE (((tblHardage.STATION_ID)=[Forms]![frmSearch]![tboCriteria]) AND
((tblHardage.SAMPLE_DATE) Between [Forms]![frmSearch]![lbodatbegin] And
[Forms]![frmSearch]![lbodatend]) AND
((tblHardagePAR.F1_GROUP)=[Forms]![frmSearch]![cboChem]) AND
((tblHardage.QC)=[Forms]![frmSearch]![cboSamptype]) AND
((tblHardage.DETECTED) Like IIf([Forms]![frmSearch]![cboDet] Is
Null,"*",IIf([Forms]![frmSearch]![cboDet]=Yes,True,False))))
ORDER BY tblHardagePAR.F1_GRPORDR;

The tboCriteria is for the STATION_ID field from which I want to see the
records of all the locations that are in the tboCriteria string, e.g., "A" OR
"B" OR "C", etc. Does this help you help me?

I have not used the IN before and am not sure how. I am not an experienced
Access person.

Is there anything else I can provide to clear up what I am trying to do?

thanks,
--
javablood


vanderghast said:
The syntax

"Los Angeles" OR "Baltimore" OR "Dallas"


is only valid in very few circumstances. It is far better to have:

IN( "Los Angeles", "Baltimore", "Dallas" )


and even then, you have to specify which field has to satisfy this IN-ness
constraint in many cases.

So, without knowing HOW you use that criteria, I strongly suggest that, at
least, you change the OR to a coma, and in the end, use

Me.tboCriteria = " IN (" & criteria & ")"


It may help (but again, depends on what you really do with that tboCriteria
thing).



Vanderghast, Access MVP


javablood said:
Hi,

I have a form in which several choices are selected, one of which is a
list
box in which the user can make multiple selections. I followed the
various
suggestions to create a String that has the selected items and I set a
textbox in the form as that final String as follows:

Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant

' Build a list of the selections.
Set ctl = Me![lboSTATID]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & " OR " & Chr(34) & ctl.ItemData(Itm)
_
& Chr(34)
End If
Next Itm

Me.tboCriteria = Criteria

However, when another form (based on a query with the passed
parameters)opens to show me the selection, there are no data.

The correct String for the query parameter appears to be correct, i.e.,
"item1" OR "item2" and if I replace the same String in the query instead
of
using the [Forms]![frmSearch]![tboCriteria] the query works fine.

Where am I going wrong? Any help is appreciated.

Thanks,
Click to expand...
Click to expand...
 
V

vanderghast

You can still use the IN( ) syntax, if your statement is used to open a form
and if you open the form using DoCmd.OpenForm, which has an optional
argument, whereCondition, like:


DoCmd.OpenForm "formName", WhereCondition := " Station_id IN('Los Angeles',
'New York', 'Dallas') "


but if your intention is to use the query in some other way, and that you
don't plan to write the whole SQL statement as a string, to produce what we
call an ad hoc query, then even the IN syntax cannot be used. You can use
InStr( ) or, more portable, the operator LIKE though, as

instead of

SELECT ... WHERE
(((tblHardage.STATION_ID)=[Forms]![frmSearch]![tboCriteria]) AND ...

use

SELECT ... WHERE (( "," & [Forms]![frmSearch]![tboCriteria] & "," ) LIKE
( "*," & station_id & ",*" )) AND ...



where tboCriteria will be filled with, say:

Los Angeles,New York,Dallas


(note that there is no space after the comas used as sub-string delimiter,
in this case).


Vanderghast, Access MVP




javablood said:
Here is the SQL I am using :

PARAMETERS [Forms]![frmSearch]![cboGroup] Text ( 255 ),
[Forms]![frmSearch]![lbodatbegin] DateTime,
[Forms]![frmSearch]![lbodatend]
DateTime, [Forms]![frmSearch]![cboChem] Text ( 255 ),
[Forms]![frmSearch]![cboSamptype] Text ( 255 ),
[Forms]![frmSearch]![cboDet]
Bit, [Forms]![frmSearch]![tboCriteria] Text ( 255 );
SELECT tblHardage.STATION_ID, tblHardage.SAMPLE_DATE,
tblHardage.PARAMETER,
tblHardage.VALUE, tblHardage.FLAG, tblHardage.UNITS,
tblHardagePAR.F1_GROUP,
tblHardage.QC, tblHardageSiteIdentification.ID_Group_Explain,
tblHardagePAR.F1_GRPORDR, tblHardage.DETECTED
FROM tblHardageFLD INNER JOIN ((tblHardage INNER JOIN tblHardagePAR ON
tblHardage.PARAMETER = tblHardagePAR.PARAMETER) INNER JOIN
tblHardageSiteIdentification ON tblHardage.STATION_ID =
tblHardageSiteIdentification.STATION_ID) ON tblHardageFLD.ENTRY =
tblHardage.QC
WHERE (((tblHardage.STATION_ID)=[Forms]![frmSearch]![tboCriteria]) AND
((tblHardage.SAMPLE_DATE) Between [Forms]![frmSearch]![lbodatbegin] And
[Forms]![frmSearch]![lbodatend]) AND
((tblHardagePAR.F1_GROUP)=[Forms]![frmSearch]![cboChem]) AND
((tblHardage.QC)=[Forms]![frmSearch]![cboSamptype]) AND
((tblHardage.DETECTED) Like IIf([Forms]![frmSearch]![cboDet] Is
Null,"*",IIf([Forms]![frmSearch]![cboDet]=Yes,True,False))))
ORDER BY tblHardagePAR.F1_GRPORDR;

The tboCriteria is for the STATION_ID field from which I want to see the
records of all the locations that are in the tboCriteria string, e.g., "A"
OR
"B" OR "C", etc. Does this help you help me?

I have not used the IN before and am not sure how. I am not an experienced
Access person.

Is there anything else I can provide to clear up what I am trying to do?

thanks,
--
javablood


vanderghast said:
The syntax

"Los Angeles" OR "Baltimore" OR "Dallas"


is only valid in very few circumstances. It is far better to have:

IN( "Los Angeles", "Baltimore", "Dallas" )


and even then, you have to specify which field has to satisfy this
IN-ness
constraint in many cases.

So, without knowing HOW you use that criteria, I strongly suggest that,
at
least, you change the OR to a coma, and in the end, use

Me.tboCriteria = " IN (" & criteria & ")"


It may help (but again, depends on what you really do with that
tboCriteria
thing).



Vanderghast, Access MVP


javablood said:
Hi,

I have a form in which several choices are selected, one of which is a
list
box in which the user can make multiple selections. I followed the
various
suggestions to create a String that has the selected items and I set a
textbox in the form as that final String as follows:

Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant

' Build a list of the selections.
Set ctl = Me![lboSTATID]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & " OR " & Chr(34) &
ctl.ItemData(Itm)
_
& Chr(34)
End If
Next Itm

Me.tboCriteria = Criteria

However, when another form (based on a query with the passed
parameters)opens to show me the selection, there are no data.

The correct String for the query parameter appears to be correct, i.e.,
"item1" OR "item2" and if I replace the same String in the query
instead
of
using the [Forms]![frmSearch]![tboCriteria] the query works fine.

Where am I going wrong? Any help is appreciated.

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
J

javablood

You learn something new everyday! Thanks to the likes of you! Thank you!
Thank you!

The WhereCondition worked, I just had to make sure I had the proper format
with the quotes and commas.

I am not sure what you meant "use the query in some other way". I did not
have the SQL statement in the event procedure but opened the form form the
event procedure. All along I thought I could pass the parameter from the
form once I had the tboCriteria set. Is that not the case?

thanks again!
--
javablood


vanderghast said:
You can still use the IN( ) syntax, if your statement is used to open a form
and if you open the form using DoCmd.OpenForm, which has an optional
argument, whereCondition, like:


DoCmd.OpenForm "formName", WhereCondition := " Station_id IN('Los Angeles',
'New York', 'Dallas') "


but if your intention is to use the query in some other way, and that you
don't plan to write the whole SQL statement as a string, to produce what we
call an ad hoc query, then even the IN syntax cannot be used. You can use
InStr( ) or, more portable, the operator LIKE though, as

instead of

SELECT ... WHERE
(((tblHardage.STATION_ID)=[Forms]![frmSearch]![tboCriteria]) AND ...

use

SELECT ... WHERE (( "," & [Forms]![frmSearch]![tboCriteria] & "," ) LIKE
( "*," & station_id & ",*" )) AND ...



where tboCriteria will be filled with, say:

Los Angeles,New York,Dallas


(note that there is no space after the comas used as sub-string delimiter,
in this case).


Vanderghast, Access MVP




javablood said:
Here is the SQL I am using :

PARAMETERS [Forms]![frmSearch]![cboGroup] Text ( 255 ),
[Forms]![frmSearch]![lbodatbegin] DateTime,
[Forms]![frmSearch]![lbodatend]
DateTime, [Forms]![frmSearch]![cboChem] Text ( 255 ),
[Forms]![frmSearch]![cboSamptype] Text ( 255 ),
[Forms]![frmSearch]![cboDet]
Bit, [Forms]![frmSearch]![tboCriteria] Text ( 255 );
SELECT tblHardage.STATION_ID, tblHardage.SAMPLE_DATE,
tblHardage.PARAMETER,
tblHardage.VALUE, tblHardage.FLAG, tblHardage.UNITS,
tblHardagePAR.F1_GROUP,
tblHardage.QC, tblHardageSiteIdentification.ID_Group_Explain,
tblHardagePAR.F1_GRPORDR, tblHardage.DETECTED
FROM tblHardageFLD INNER JOIN ((tblHardage INNER JOIN tblHardagePAR ON
tblHardage.PARAMETER = tblHardagePAR.PARAMETER) INNER JOIN
tblHardageSiteIdentification ON tblHardage.STATION_ID =
tblHardageSiteIdentification.STATION_ID) ON tblHardageFLD.ENTRY =
tblHardage.QC
WHERE (((tblHardage.STATION_ID)=[Forms]![frmSearch]![tboCriteria]) AND
((tblHardage.SAMPLE_DATE) Between [Forms]![frmSearch]![lbodatbegin] And
[Forms]![frmSearch]![lbodatend]) AND
((tblHardagePAR.F1_GROUP)=[Forms]![frmSearch]![cboChem]) AND
((tblHardage.QC)=[Forms]![frmSearch]![cboSamptype]) AND
((tblHardage.DETECTED) Like IIf([Forms]![frmSearch]![cboDet] Is
Null,"*",IIf([Forms]![frmSearch]![cboDet]=Yes,True,False))))
ORDER BY tblHardagePAR.F1_GRPORDR;

The tboCriteria is for the STATION_ID field from which I want to see the
records of all the locations that are in the tboCriteria string, e.g., "A"
OR
"B" OR "C", etc. Does this help you help me?

I have not used the IN before and am not sure how. I am not an experienced
Access person.

Is there anything else I can provide to clear up what I am trying to do?

thanks,
--
javablood


vanderghast said:
The syntax

"Los Angeles" OR "Baltimore" OR "Dallas"


is only valid in very few circumstances. It is far better to have:

IN( "Los Angeles", "Baltimore", "Dallas" )


and even then, you have to specify which field has to satisfy this
IN-ness
constraint in many cases.

So, without knowing HOW you use that criteria, I strongly suggest that,
at
least, you change the OR to a coma, and in the end, use

Me.tboCriteria = " IN (" & criteria & ")"


It may help (but again, depends on what you really do with that
tboCriteria
thing).



Vanderghast, Access MVP


Hi,

I have a form in which several choices are selected, one of which is a
list
box in which the user can make multiple selections. I followed the
various
suggestions to create a String that has the selected items and I set a
textbox in the form as that final String as follows:

Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant

' Build a list of the selections.
Set ctl = Me![lboSTATID]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & " OR " & Chr(34) &
ctl.ItemData(Itm)
_
& Chr(34)
End If
Next Itm

Me.tboCriteria = Criteria

However, when another form (based on a query with the passed
parameters)opens to show me the selection, there are no data.

The correct String for the query parameter appears to be correct, i.e.,
"item1" OR "item2" and if I replace the same String in the query
instead
of
using the [Forms]![frmSearch]![tboCriteria] the query works fine.

Where am I going wrong? Any help is appreciated.

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
V

vanderghast

As example, you can write a query statement as a string and use that string
as record SOURCE for a form, or for a ROW SOURCE ( list of a list box or
combo box). The string will be evaluated as if it is an original SQL
statement.


Vanderghast, Access MVP



javablood said:
You learn something new everyday! Thanks to the likes of you! Thank you!
Thank you!

The WhereCondition worked, I just had to make sure I had the proper format
with the quotes and commas.

I am not sure what you meant "use the query in some other way". I did not
have the SQL statement in the event procedure but opened the form form the
event procedure. All along I thought I could pass the parameter from the
form once I had the tboCriteria set. Is that not the case?

thanks again!
--
javablood


vanderghast said:
You can still use the IN( ) syntax, if your statement is used to open a
form
and if you open the form using DoCmd.OpenForm, which has an optional
argument, whereCondition, like:


DoCmd.OpenForm "formName", WhereCondition := " Station_id IN('Los
Angeles',
'New York', 'Dallas') "


but if your intention is to use the query in some other way, and that you
don't plan to write the whole SQL statement as a string, to produce what
we
call an ad hoc query, then even the IN syntax cannot be used. You can use
InStr( ) or, more portable, the operator LIKE though, as

instead of

SELECT ... WHERE
(((tblHardage.STATION_ID)=[Forms]![frmSearch]![tboCriteria]) AND ...

use

SELECT ... WHERE (( "," & [Forms]![frmSearch]![tboCriteria] & "," )
LIKE
( "*," & station_id & ",*" )) AND ...



where tboCriteria will be filled with, say:

Los Angeles,New York,Dallas


(note that there is no space after the comas used as sub-string
delimiter,
in this case).


Vanderghast, Access MVP




javablood said:
Here is the SQL I am using :

PARAMETERS [Forms]![frmSearch]![cboGroup] Text ( 255 ),
[Forms]![frmSearch]![lbodatbegin] DateTime,
[Forms]![frmSearch]![lbodatend]
DateTime, [Forms]![frmSearch]![cboChem] Text ( 255 ),
[Forms]![frmSearch]![cboSamptype] Text ( 255 ),
[Forms]![frmSearch]![cboDet]
Bit, [Forms]![frmSearch]![tboCriteria] Text ( 255 );
SELECT tblHardage.STATION_ID, tblHardage.SAMPLE_DATE,
tblHardage.PARAMETER,
tblHardage.VALUE, tblHardage.FLAG, tblHardage.UNITS,
tblHardagePAR.F1_GROUP,
tblHardage.QC, tblHardageSiteIdentification.ID_Group_Explain,
tblHardagePAR.F1_GRPORDR, tblHardage.DETECTED
FROM tblHardageFLD INNER JOIN ((tblHardage INNER JOIN tblHardagePAR ON
tblHardage.PARAMETER = tblHardagePAR.PARAMETER) INNER JOIN
tblHardageSiteIdentification ON tblHardage.STATION_ID =
tblHardageSiteIdentification.STATION_ID) ON tblHardageFLD.ENTRY =
tblHardage.QC
WHERE (((tblHardage.STATION_ID)=[Forms]![frmSearch]![tboCriteria]) AND
((tblHardage.SAMPLE_DATE) Between [Forms]![frmSearch]![lbodatbegin] And
[Forms]![frmSearch]![lbodatend]) AND
((tblHardagePAR.F1_GROUP)=[Forms]![frmSearch]![cboChem]) AND
((tblHardage.QC)=[Forms]![frmSearch]![cboSamptype]) AND
((tblHardage.DETECTED) Like IIf([Forms]![frmSearch]![cboDet] Is
Null,"*",IIf([Forms]![frmSearch]![cboDet]=Yes,True,False))))
ORDER BY tblHardagePAR.F1_GRPORDR;

The tboCriteria is for the STATION_ID field from which I want to see
the
records of all the locations that are in the tboCriteria string, e.g.,
"A"
OR
"B" OR "C", etc. Does this help you help me?

I have not used the IN before and am not sure how. I am not an
experienced
Access person.

Is there anything else I can provide to clear up what I am trying to
do?

thanks,
--
javablood


:

The syntax

"Los Angeles" OR "Baltimore" OR "Dallas"


is only valid in very few circumstances. It is far better to have:

IN( "Los Angeles", "Baltimore", "Dallas" )


and even then, you have to specify which field has to satisfy this
IN-ness
constraint in many cases.

So, without knowing HOW you use that criteria, I strongly suggest
that,
at
least, you change the OR to a coma, and in the end, use

Me.tboCriteria = " IN (" & criteria & ")"


It may help (but again, depends on what you really do with that
tboCriteria
thing).



Vanderghast, Access MVP


Hi,

I have a form in which several choices are selected, one of which is
a
list
box in which the user can make multiple selections. I followed the
various
suggestions to create a String that has the selected items and I set
a
textbox in the form as that final String as follows:

Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant

' Build a list of the selections.
Set ctl = Me![lboSTATID]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & " OR " & Chr(34) &
ctl.ItemData(Itm)
_
& Chr(34)
End If
Next Itm

Me.tboCriteria = Criteria

However, when another form (based on a query with the passed
parameters)opens to show me the selection, there are no data.

The correct String for the query parameter appears to be correct,
i.e.,
"item1" OR "item2" and if I replace the same String in the query
instead
of
using the [Forms]![frmSearch]![tboCriteria] the query works fine.

Where am I going wrong? Any help is appreciated.

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
J

javablood

Okay. I am familiar with writing the query statement as a string but how
does one use that string as a record Source for a form?
--
javablood


vanderghast said:
As example, you can write a query statement as a string and use that string
as record SOURCE for a form, or for a ROW SOURCE ( list of a list box or
combo box). The string will be evaluated as if it is an original SQL
statement.


Vanderghast, Access MVP



javablood said:
You learn something new everyday! Thanks to the likes of you! Thank you!
Thank you!

The WhereCondition worked, I just had to make sure I had the proper format
with the quotes and commas.

I am not sure what you meant "use the query in some other way". I did not
have the SQL statement in the event procedure but opened the form form the
event procedure. All along I thought I could pass the parameter from the
form once I had the tboCriteria set. Is that not the case?

thanks again!
--
javablood


vanderghast said:
You can still use the IN( ) syntax, if your statement is used to open a
form
and if you open the form using DoCmd.OpenForm, which has an optional
argument, whereCondition, like:


DoCmd.OpenForm "formName", WhereCondition := " Station_id IN('Los
Angeles',
'New York', 'Dallas') "


but if your intention is to use the query in some other way, and that you
don't plan to write the whole SQL statement as a string, to produce what
we
call an ad hoc query, then even the IN syntax cannot be used. You can use
InStr( ) or, more portable, the operator LIKE though, as

instead of

SELECT ... WHERE
(((tblHardage.STATION_ID)=[Forms]![frmSearch]![tboCriteria]) AND ...

use

SELECT ... WHERE (( "," & [Forms]![frmSearch]![tboCriteria] & "," )
LIKE
( "*," & station_id & ",*" )) AND ...



where tboCriteria will be filled with, say:

Los Angeles,New York,Dallas


(note that there is no space after the comas used as sub-string
delimiter,
in this case).


Vanderghast, Access MVP




Here is the SQL I am using :

PARAMETERS [Forms]![frmSearch]![cboGroup] Text ( 255 ),
[Forms]![frmSearch]![lbodatbegin] DateTime,
[Forms]![frmSearch]![lbodatend]
DateTime, [Forms]![frmSearch]![cboChem] Text ( 255 ),
[Forms]![frmSearch]![cboSamptype] Text ( 255 ),
[Forms]![frmSearch]![cboDet]
Bit, [Forms]![frmSearch]![tboCriteria] Text ( 255 );
SELECT tblHardage.STATION_ID, tblHardage.SAMPLE_DATE,
tblHardage.PARAMETER,
tblHardage.VALUE, tblHardage.FLAG, tblHardage.UNITS,
tblHardagePAR.F1_GROUP,
tblHardage.QC, tblHardageSiteIdentification.ID_Group_Explain,
tblHardagePAR.F1_GRPORDR, tblHardage.DETECTED
FROM tblHardageFLD INNER JOIN ((tblHardage INNER JOIN tblHardagePAR ON
tblHardage.PARAMETER = tblHardagePAR.PARAMETER) INNER JOIN
tblHardageSiteIdentification ON tblHardage.STATION_ID =
tblHardageSiteIdentification.STATION_ID) ON tblHardageFLD.ENTRY =
tblHardage.QC
WHERE (((tblHardage.STATION_ID)=[Forms]![frmSearch]![tboCriteria]) AND
((tblHardage.SAMPLE_DATE) Between [Forms]![frmSearch]![lbodatbegin] And
[Forms]![frmSearch]![lbodatend]) AND
((tblHardagePAR.F1_GROUP)=[Forms]![frmSearch]![cboChem]) AND
((tblHardage.QC)=[Forms]![frmSearch]![cboSamptype]) AND
((tblHardage.DETECTED) Like IIf([Forms]![frmSearch]![cboDet] Is
Null,"*",IIf([Forms]![frmSearch]![cboDet]=Yes,True,False))))
ORDER BY tblHardagePAR.F1_GRPORDR;

The tboCriteria is for the STATION_ID field from which I want to see
the
records of all the locations that are in the tboCriteria string, e.g.,
"A"
OR
"B" OR "C", etc. Does this help you help me?

I have not used the IN before and am not sure how. I am not an
experienced
Access person.

Is there anything else I can provide to clear up what I am trying to
do?

thanks,
--
javablood


:

The syntax

"Los Angeles" OR "Baltimore" OR "Dallas"


is only valid in very few circumstances. It is far better to have:

IN( "Los Angeles", "Baltimore", "Dallas" )


and even then, you have to specify which field has to satisfy this
IN-ness
constraint in many cases.

So, without knowing HOW you use that criteria, I strongly suggest
that,
at
least, you change the OR to a coma, and in the end, use

Me.tboCriteria = " IN (" & criteria & ")"


It may help (but again, depends on what you really do with that
tboCriteria
thing).



Vanderghast, Access MVP


Hi,

I have a form in which several choices are selected, one of which is
a
list
box in which the user can make multiple selections. I followed the
various
suggestions to create a String that has the selected items and I set
a
textbox in the form as that final String as follows:

Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant

' Build a list of the selections.
Set ctl = Me![lboSTATID]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & " OR " & Chr(34) &
ctl.ItemData(Itm)
_
& Chr(34)
End If
Next Itm

Me.tboCriteria = Criteria

However, when another form (based on a query with the passed
parameters)opens to show me the selection, there are no data.

The correct String for the query parameter appears to be correct,
i.e.,
"item1" OR "item2" and if I replace the same String in the query
instead
of
using the [Forms]![frmSearch]![tboCriteria] the query works fine.

Where am I going wrong? Any help is appreciated.

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
V

vanderghast

You specify the form's RecordSource property.

Me.RecordSource = "SELECT ... "


Sure, that has to make sense, I mean, unless you want to revisit each
control of the form to specify their ControlSource property too. I mean, if
a control source property is set to field CompanyName, then the form record
source should return a CompanyName:

Me.RecordSource = "SELECT CompanyName, ... "


else, you would get #Name? error in the control not finding its
ControlSource field.


While it can make sense to specify the Record Source in the Open event, it
would be weird to do it somewhere else (well, unless it is a really
'special' design).


Vanderghast, Access MVP


javablood said:
Okay. I am familiar with writing the query statement as a string but how
does one use that string as a record Source for a form?
--
javablood


vanderghast said:
As example, you can write a query statement as a string and use that
string
as record SOURCE for a form, or for a ROW SOURCE ( list of a list box or
combo box). The string will be evaluated as if it is an original SQL
statement.


Vanderghast, Access MVP



javablood said:
You learn something new everyday! Thanks to the likes of you! Thank
you!
Thank you!

The WhereCondition worked, I just had to make sure I had the proper
format
with the quotes and commas.

I am not sure what you meant "use the query in some other way". I did
not
have the SQL statement in the event procedure but opened the form form
the
event procedure. All along I thought I could pass the parameter from
the
form once I had the tboCriteria set. Is that not the case?

thanks again!
--
javablood


:



You can still use the IN( ) syntax, if your statement is used to open
a
form
and if you open the form using DoCmd.OpenForm, which has an optional
argument, whereCondition, like:


DoCmd.OpenForm "formName", WhereCondition := " Station_id IN('Los
Angeles',
'New York', 'Dallas') "


but if your intention is to use the query in some other way, and that
you
don't plan to write the whole SQL statement as a string, to produce
what
we
call an ad hoc query, then even the IN syntax cannot be used. You can
use
InStr( ) or, more portable, the operator LIKE though, as

instead of

SELECT ... WHERE
(((tblHardage.STATION_ID)=[Forms]![frmSearch]![tboCriteria]) AND ...

use

SELECT ... WHERE (( "," & [Forms]![frmSearch]![tboCriteria] & "," )
LIKE
( "*," & station_id & ",*" )) AND ...



where tboCriteria will be filled with, say:

Los Angeles,New York,Dallas


(note that there is no space after the comas used as sub-string
delimiter,
in this case).


Vanderghast, Access MVP




Here is the SQL I am using :

PARAMETERS [Forms]![frmSearch]![cboGroup] Text ( 255 ),
[Forms]![frmSearch]![lbodatbegin] DateTime,
[Forms]![frmSearch]![lbodatend]
DateTime, [Forms]![frmSearch]![cboChem] Text ( 255 ),
[Forms]![frmSearch]![cboSamptype] Text ( 255 ),
[Forms]![frmSearch]![cboDet]
Bit, [Forms]![frmSearch]![tboCriteria] Text ( 255 );
SELECT tblHardage.STATION_ID, tblHardage.SAMPLE_DATE,
tblHardage.PARAMETER,
tblHardage.VALUE, tblHardage.FLAG, tblHardage.UNITS,
tblHardagePAR.F1_GROUP,
tblHardage.QC, tblHardageSiteIdentification.ID_Group_Explain,
tblHardagePAR.F1_GRPORDR, tblHardage.DETECTED
FROM tblHardageFLD INNER JOIN ((tblHardage INNER JOIN tblHardagePAR
ON
tblHardage.PARAMETER = tblHardagePAR.PARAMETER) INNER JOIN
tblHardageSiteIdentification ON tblHardage.STATION_ID =
tblHardageSiteIdentification.STATION_ID) ON tblHardageFLD.ENTRY =
tblHardage.QC
WHERE (((tblHardage.STATION_ID)=[Forms]![frmSearch]![tboCriteria])
AND
((tblHardage.SAMPLE_DATE) Between [Forms]![frmSearch]![lbodatbegin]
And
[Forms]![frmSearch]![lbodatend]) AND
((tblHardagePAR.F1_GROUP)=[Forms]![frmSearch]![cboChem]) AND
((tblHardage.QC)=[Forms]![frmSearch]![cboSamptype]) AND
((tblHardage.DETECTED) Like IIf([Forms]![frmSearch]![cboDet] Is
Null,"*",IIf([Forms]![frmSearch]![cboDet]=Yes,True,False))))
ORDER BY tblHardagePAR.F1_GRPORDR;

The tboCriteria is for the STATION_ID field from which I want to see
the
records of all the locations that are in the tboCriteria string,
e.g.,
"A"
OR
"B" OR "C", etc. Does this help you help me?

I have not used the IN before and am not sure how. I am not an
experienced
Access person.

Is there anything else I can provide to clear up what I am trying to
do?

thanks,
--
javablood


:

The syntax

"Los Angeles" OR "Baltimore" OR "Dallas"


is only valid in very few circumstances. It is far better to have:

IN( "Los Angeles", "Baltimore", "Dallas" )


and even then, you have to specify which field has to satisfy this
IN-ness
constraint in many cases.

So, without knowing HOW you use that criteria, I strongly suggest
that,
at
least, you change the OR to a coma, and in the end, use

Me.tboCriteria = " IN (" & criteria & ")"


It may help (but again, depends on what you really do with that
tboCriteria
thing).



Vanderghast, Access MVP


Hi,

I have a form in which several choices are selected, one of which
is
a
list
box in which the user can make multiple selections. I followed
the
various
suggestions to create a String that has the selected items and I
set
a
textbox in the form as that final String as follows:

Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant

' Build a list of the selections.
Set ctl = Me![lboSTATID]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) &
Chr(34)
Else
Criteria = Criteria & " OR " & Chr(34) &
ctl.ItemData(Itm)
_
& Chr(34)
End If
Next Itm

Me.tboCriteria = Criteria

However, when another form (based on a query with the passed
parameters)opens to show me the selection, there are no data.

The correct String for the query parameter appears to be correct,
i.e.,
"item1" OR "item2" and if I replace the same String in the query
instead
of
using the [Forms]![frmSearch]![tboCriteria] the query works fine.

Where am I going wrong? Any help is appreciated.

Thanks,
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

Combo Box / Query 7
Multi Select List Not Working 1
Need help in printing vba code 0
How to Flip-Flop Two Queries Built From Only One List box 6
How to get a List Box with nothing Chosen to be All Chosen 2
How to turn Code Into Function 22
Multiselect listbox 5
Using Variable as criteria 4

Top