QBF Form Select

H

HFlynn

I have a form setup that allows the users control certain search criteria. I
am looking for away to allow the user to place multiple selects in one of
the text boxes on the form.

Example would be Tax Id (999999999,999999998,)
I need some direction on where to look for this. Attaching the code I am
trying to use now.

Thank you.
where = Null
where = where & " AND InParam([Service Tin])'" = Me![ServProv] + "'"
where = where & " AND [Service Suffix]= '" + Me![PrvSuffix] + "'"
where = where & " AND [Service Spec]= '" + Me![SevSpec] + "'"

If Not IsNull(Me![Pd To]) Then
where = where & " AND [chpddt] between " + _
Me![Pd From] + " AND " & Me![Pd To] & ""
Else
where = where & " AND [chpddt] >= " + Me![Pd From] _
+ " "
End If

If Not IsNull(Me![Inc To]) Then
where = where & " AND [chinfr] between " + _
Me![Inc From] + " AND " & Me![Inc To] & ""
Else
where = where & " AND [chinfr] >= " + Me![Inc From] _
+ " "
End If

If Not IsNull(Me![Rec To]) Then
where = where & " AND [chrcdt] between " + _
Me![Rec From] + " AND " & Me![Rec To] & ""
Else
where = where & " AND [chrcdt] >= " + Me![Rec From] _
+ " "
End If

Set MyQueryDef = MyDatabase.CreateQueryDef("qyr_1ServicePrvSelect", _
"SELECT * INTO Tbl_ServicePrvSelect FROM (clmdet LEFT JOIN
Tbl_ServiceProvAppend ON clmdet.cdspno = Tbl_ServiceProvAppend.[Service Seq])
LEFT JOIN clmhdr ON (clmdet.cdwkno = clmhdr.chwkno) AND (clmdet.cdclno =
clmhdr.chclno)" & (" where " + Mid(where, 6) & ";"))
 
D

Duane Hookom

I would think you could use something like:
If Not IsNull(Me.txtTaxID) Then
Where = Where & " AND [TaxID] IN (" & me.txtTaxID & ") "
End If

This assumes TaxID is the name of the field and it is numeric.
 
H

HFlynn

Thank you for your input.
However I did try it that way and it still doesn't allow me to pick up
mutliple entries.
 
D

Duane Hookom

What do you mean when you say "doesn't allow me to pick up multiple
entries"?
What type of values are users entering? Is the field text or numeric?

--
Duane Hookom
MS Access MVP


HFlynn said:
Thank you for your input.
However I did try it that way and it still doesn't allow me to pick up
mutliple entries.



HFlynn said:
I have a form setup that allows the users control certain search
criteria. I
am looking for away to allow the user to place multiple selects in one
of
the text boxes on the form.

Example would be Tax Id (999999999,999999998,)
I need some direction on where to look for this. Attaching the code I am
trying to use now.

Thank you.
where = Null
where = where & " AND InParam([Service Tin])'" = Me![ServProv] +
"'"
where = where & " AND [Service Suffix]= '" + Me![PrvSuffix] + "'"
where = where & " AND [Service Spec]= '" + Me![SevSpec] + "'"

If Not IsNull(Me![Pd To]) Then
where = where & " AND [chpddt] between " + _
Me![Pd From] + " AND " & Me![Pd To] & ""
Else
where = where & " AND [chpddt] >= " + Me![Pd From] _
+ " "
End If

If Not IsNull(Me![Inc To]) Then
where = where & " AND [chinfr] between " + _
Me![Inc From] + " AND " & Me![Inc To] & ""
Else
where = where & " AND [chinfr] >= " + Me![Inc From] _
+ " "
End If

If Not IsNull(Me![Rec To]) Then
where = where & " AND [chrcdt] between " + _
Me![Rec From] + " AND " & Me![Rec To] & ""
Else
where = where & " AND [chrcdt] >= " + Me![Rec From] _
+ " "
End If

Set MyQueryDef = MyDatabase.CreateQueryDef("qyr_1ServicePrvSelect",
_
"SELECT * INTO Tbl_ServicePrvSelect FROM (clmdet LEFT JOIN
Tbl_ServiceProvAppend ON clmdet.cdspno = Tbl_ServiceProvAppend.[Service
Seq])
LEFT JOIN clmhdr ON (clmdet.cdwkno = clmhdr.chwkno) AND (clmdet.cdclno =
clmhdr.chclno)" & (" where " + Mid(where, 6) & ";"))
Click to expand...
Click to expand...
 
H

HFlynn

it is numeric values; however, I have tired to input on the form two tax id
numbers. 999999999,999999998
I have tried to use a comma and an or inbetween the id numbers; however, no
matter which way I have tried to enter these it will either produce and empty
table or only read the first entry. I am basically looking for away to build
a form without having a popup box so the user can enter multiple criteria in
one field.

Hope that is not confusing. Thank you for your assistance.

Duane Hookom said:
What do you mean when you say "doesn't allow me to pick up multiple
entries"?
What type of values are users entering? Is the field text or numeric?

--
Duane Hookom
MS Access MVP


HFlynn said:
Thank you for your input.
However I did try it that way and it still doesn't allow me to pick up
mutliple entries.



HFlynn said:
I have a form setup that allows the users control certain search
criteria. I
am looking for away to allow the user to place multiple selects in one
of
the text boxes on the form.

Example would be Tax Id (999999999,999999998,)
I need some direction on where to look for this. Attaching the code I am
trying to use now.

Thank you.
where = Null
where = where & " AND InParam([Service Tin])'" = Me![ServProv] +
"'"
where = where & " AND [Service Suffix]= '" + Me![PrvSuffix] + "'"
where = where & " AND [Service Spec]= '" + Me![SevSpec] + "'"

If Not IsNull(Me![Pd To]) Then
where = where & " AND [chpddt] between " + _
Me![Pd From] + " AND " & Me![Pd To] & ""
Else
where = where & " AND [chpddt] >= " + Me![Pd From] _
+ " "
End If

If Not IsNull(Me![Inc To]) Then
where = where & " AND [chinfr] between " + _
Me![Inc From] + " AND " & Me![Inc To] & ""
Else
where = where & " AND [chinfr] >= " + Me![Inc From] _
+ " "
End If

If Not IsNull(Me![Rec To]) Then
where = where & " AND [chrcdt] between " + _
Me![Rec From] + " AND " & Me![Rec To] & ""
Else
where = where & " AND [chrcdt] >= " + Me![Rec From] _
+ " "
End If

Set MyQueryDef = MyDatabase.CreateQueryDef("qyr_1ServicePrvSelect",
_
"SELECT * INTO Tbl_ServicePrvSelect FROM (clmdet LEFT JOIN
Tbl_ServiceProvAppend ON clmdet.cdspno = Tbl_ServiceProvAppend.[Service
Seq])
LEFT JOIN clmhdr ON (clmdet.cdwkno = clmhdr.chwkno) AND (clmdet.cdclno =
clmhdr.chclno)" & (" where " + Mid(where, 6) & ";"))
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

What do you see if you open "qyr_1ServicePrvSelect" in sql view? Do you see
what you expect? Could you share the SQL view with us?
--
Duane Hookom
MS Access MVP
--

HFlynn said:
it is numeric values; however, I have tired to input on the form two tax
id
numbers. 999999999,999999998
I have tried to use a comma and an or inbetween the id numbers; however,
no
matter which way I have tried to enter these it will either produce and
empty
table or only read the first entry. I am basically looking for away to
build
a form without having a popup box so the user can enter multiple criteria
in
one field.

Hope that is not confusing. Thank you for your assistance.

Duane Hookom said:
What do you mean when you say "doesn't allow me to pick up multiple
entries"?
What type of values are users entering? Is the field text or numeric?

--
Duane Hookom
MS Access MVP


HFlynn said:
Thank you for your input.
However I did try it that way and it still doesn't allow me to pick up
mutliple entries.



:

I have a form setup that allows the users control certain search
criteria. I
am looking for away to allow the user to place multiple selects in
one
of
the text boxes on the form.

Example would be Tax Id (999999999,999999998,)
I need some direction on where to look for this. Attaching the code I
am
trying to use now.

Thank you.
where = Null
where = where & " AND InParam([Service Tin])'" = Me![ServProv] +
"'"
where = where & " AND [Service Suffix]= '" + Me![PrvSuffix] +
"'"
where = where & " AND [Service Spec]= '" + Me![SevSpec] + "'"

If Not IsNull(Me![Pd To]) Then
where = where & " AND [chpddt] between " + _
Me![Pd From] + " AND " & Me![Pd To] & ""
Else
where = where & " AND [chpddt] >= " + Me![Pd From] _
+ " "
End If

If Not IsNull(Me![Inc To]) Then
where = where & " AND [chinfr] between " + _
Me![Inc From] + " AND " & Me![Inc To] & ""
Else
where = where & " AND [chinfr] >= " + Me![Inc From] _
+ " "
End If

If Not IsNull(Me![Rec To]) Then
where = where & " AND [chrcdt] between " + _
Me![Rec From] + " AND " & Me![Rec To] & ""
Else
where = where & " AND [chrcdt] >= " + Me![Rec From] _
+ " "
End If

Set MyQueryDef =
MyDatabase.CreateQueryDef("qyr_1ServicePrvSelect",
_
"SELECT * INTO Tbl_ServicePrvSelect FROM (clmdet LEFT JOIN
Tbl_ServiceProvAppend ON clmdet.cdspno =
Tbl_ServiceProvAppend.[Service
Seq])
LEFT JOIN clmhdr ON (clmdet.cdwkno = clmhdr.chwkno) AND (clmdet.cdclno
=
clmhdr.chclno)" & (" where " + Mid(where, 6) & ";"))
Click to expand...
Click to expand...
Click to expand...
 
H

HFlynn

This is when using the form. It pulls blank
SELECT * INTO Tbl_ServicePrvSelect
FROM (clmdet LEFT JOIN Tbl_ServiceProvAppend ON clmdet.cdspno =
Tbl_ServiceProvAppend.[Service Seq]) LEFT JOIN clmhdr ON (clmdet.cdclno =
clmhdr.chclno) AND (clmdet.cdwkno = clmhdr.chwkno)
WHERE (((Tbl_ServiceProvAppend.[Service Tin]) In
((Tbl_ServiceProvAppend.[Service Tin])=751826221 Or
(Tbl_ServiceProvAppend.[Service Tin])=752682335)) AND ((clmhdr.chpddt)
Between 20050801 And 20050831));

This is the sample of how it reads if I hard code it. This one
populates the table.

SELECT * INTO Tbl_ServicePrvSelect
FROM (clmdet LEFT JOIN Tbl_ServiceProvAppend ON clmdet.cdspno =
Tbl_ServiceProvAppend.[Service Seq]) LEFT JOIN clmhdr ON (clmdet.cdclno =
clmhdr.chclno) AND (clmdet.cdwkno = clmhdr.chwkno)
WHERE (((Tbl_ServiceProvAppend.[Service Tin])="751826221" Or
(Tbl_ServiceProvAppend.[Service Tin])="752682335") AND ((clmhdr.chpddt)
Between 20050801 And 20050831));



Duane Hookom said:
What do you see if you open "qyr_1ServicePrvSelect" in sql view? Do you see
what you expect? Could you share the SQL view with us?
--
Duane Hookom
MS Access MVP
--

HFlynn said:
it is numeric values; however, I have tired to input on the form two tax
id
numbers. 999999999,999999998
I have tried to use a comma and an or inbetween the id numbers; however,
no
matter which way I have tried to enter these it will either produce and
empty
table or only read the first entry. I am basically looking for away to
build
a form without having a popup box so the user can enter multiple criteria
in
one field.

Hope that is not confusing. Thank you for your assistance.

Duane Hookom said:
What do you mean when you say "doesn't allow me to pick up multiple
entries"?
What type of values are users entering? Is the field text or numeric?

--
Duane Hookom
MS Access MVP


Thank you for your input.
However I did try it that way and it still doesn't allow me to pick up
mutliple entries.



:

I have a form setup that allows the users control certain search
criteria. I
am looking for away to allow the user to place multiple selects in
one
of
the text boxes on the form.

Example would be Tax Id (999999999,999999998,)
I need some direction on where to look for this. Attaching the code I
am
trying to use now.

Thank you.
where = Null
where = where & " AND InParam([Service Tin])'" = Me![ServProv] +
"'"
where = where & " AND [Service Suffix]= '" + Me![PrvSuffix] +
"'"
where = where & " AND [Service Spec]= '" + Me![SevSpec] + "'"

If Not IsNull(Me![Pd To]) Then
where = where & " AND [chpddt] between " + _
Me![Pd From] + " AND " & Me![Pd To] & ""
Else
where = where & " AND [chpddt] >= " + Me![Pd From] _
+ " "
End If

If Not IsNull(Me![Inc To]) Then
where = where & " AND [chinfr] between " + _
Me![Inc From] + " AND " & Me![Inc To] & ""
Else
where = where & " AND [chinfr] >= " + Me![Inc From] _
+ " "
End If

If Not IsNull(Me![Rec To]) Then
where = where & " AND [chrcdt] between " + _
Me![Rec From] + " AND " & Me![Rec To] & ""
Else
where = where & " AND [chrcdt] >= " + Me![Rec From] _
+ " "
End If

Set MyQueryDef =
MyDatabase.CreateQueryDef("qyr_1ServicePrvSelect",
_
"SELECT * INTO Tbl_ServicePrvSelect FROM (clmdet LEFT JOIN
Tbl_ServiceProvAppend ON clmdet.cdspno =
Tbl_ServiceProvAppend.[Service
Seq])
LEFT JOIN clmhdr ON (clmdet.cdwkno = clmhdr.chwkno) AND (clmdet.cdclno
=
clmhdr.chclno)" & (" where " + Mid(where, 6) & ";"))
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Maybe you should post your code, a sample value entered into your text box,
and your resulting SQL view.

I don't see anything in your SQL like "Tax Id (999999999,999999998,)"

--
Duane Hookom
MS Access MVP


HFlynn said:
This is when using the form. It pulls blank
SELECT * INTO Tbl_ServicePrvSelect
FROM (clmdet LEFT JOIN Tbl_ServiceProvAppend ON clmdet.cdspno =
Tbl_ServiceProvAppend.[Service Seq]) LEFT JOIN clmhdr ON (clmdet.cdclno =
clmhdr.chclno) AND (clmdet.cdwkno = clmhdr.chwkno)
WHERE (((Tbl_ServiceProvAppend.[Service Tin]) In
((Tbl_ServiceProvAppend.[Service Tin])=751826221 Or
(Tbl_ServiceProvAppend.[Service Tin])=752682335)) AND ((clmhdr.chpddt)
Between 20050801 And 20050831));

This is the sample of how it reads if I hard code it. This one
populates the table.

SELECT * INTO Tbl_ServicePrvSelect
FROM (clmdet LEFT JOIN Tbl_ServiceProvAppend ON clmdet.cdspno =
Tbl_ServiceProvAppend.[Service Seq]) LEFT JOIN clmhdr ON (clmdet.cdclno =
clmhdr.chclno) AND (clmdet.cdwkno = clmhdr.chwkno)
WHERE (((Tbl_ServiceProvAppend.[Service Tin])="751826221" Or
(Tbl_ServiceProvAppend.[Service Tin])="752682335") AND ((clmhdr.chpddt)
Between 20050801 And 20050831));



Duane Hookom said:
What do you see if you open "qyr_1ServicePrvSelect" in sql view? Do you
see
what you expect? Could you share the SQL view with us?
--
Duane Hookom
MS Access MVP
--

HFlynn said:
it is numeric values; however, I have tired to input on the form two
tax
id
numbers. 999999999,999999998
I have tried to use a comma and an or inbetween the id numbers;
however,
no
matter which way I have tried to enter these it will either produce and
empty
table or only read the first entry. I am basically looking for away to
build
a form without having a popup box so the user can enter multiple
criteria
in
one field.

Hope that is not confusing. Thank you for your assistance.

:

What do you mean when you say "doesn't allow me to pick up multiple
entries"?
What type of values are users entering? Is the field text or numeric?

--
Duane Hookom
MS Access MVP


Thank you for your input.
However I did try it that way and it still doesn't allow me to pick
up
mutliple entries.



:

I have a form setup that allows the users control certain search
criteria. I
am looking for away to allow the user to place multiple selects in
one
of
the text boxes on the form.

Example would be Tax Id (999999999,999999998,)
I need some direction on where to look for this. Attaching the code
I
am
trying to use now.

Thank you.
where = Null
where = where & " AND InParam([Service Tin])'" =
Me![ServProv] +
"'"
where = where & " AND [Service Suffix]= '" + Me![PrvSuffix] +
"'"
where = where & " AND [Service Spec]= '" + Me![SevSpec] + "'"

If Not IsNull(Me![Pd To]) Then
where = where & " AND [chpddt] between " + _
Me![Pd From] + " AND " & Me![Pd To] & ""
Else
where = where & " AND [chpddt] >= " + Me![Pd From] _
+ " "
End If

If Not IsNull(Me![Inc To]) Then
where = where & " AND [chinfr] between " + _
Me![Inc From] + " AND " & Me![Inc To] & ""
Else
where = where & " AND [chinfr] >= " + Me![Inc From] _
+ " "
End If

If Not IsNull(Me![Rec To]) Then
where = where & " AND [chrcdt] between " + _
Me![Rec From] + " AND " & Me![Rec To] & ""
Else
where = where & " AND [chrcdt] >= " + Me![Rec From] _
+ " "
End If

Set MyQueryDef =
MyDatabase.CreateQueryDef("qyr_1ServicePrvSelect",
_
"SELECT * INTO Tbl_ServicePrvSelect FROM (clmdet LEFT JOIN
Tbl_ServiceProvAppend ON clmdet.cdspno =
Tbl_ServiceProvAppend.[Service
Seq])
LEFT JOIN clmhdr ON (clmdet.cdwkno = clmhdr.chwkno) AND
(clmdet.cdclno
=
clmhdr.chclno)" & (" where " + Mid(where, 6) & ";"))
Click to expand...
Click to expand...
Click to expand...
 
Top