QBF Form Select

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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) & ";"))
 
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.
 
Thank you for your input.
However I did try it that way and it still doesn't allow me to pick up
mutliple entries.
 
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...
 
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...
 
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...
 
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...
 
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...
 
Back
Top