QBF Help!

M

Maver911

I'm having this issue with trying to bring in a mult-parameter string
via textbox into a query. Basically ive tried setting a string to hold
4 UPC's like this: 123456789 OR 987654321 OR 567894321 OR... etc and
setting that string to be shown in a textbox on my form. Then in my
append query, under the UPC criteria I put:
[Forms]![NameofForm]![MyTextBoxName]. Ive run this and i get only the
results for the first upc in the string. Does anyone have any ideas why
this would be doing this or even any ideas period that would help me
alter my scenerio. Any help would be greatly appreciated. Thank you
 
J

Jeff L

Try running the SQL from your form using a command button or something.

Docmd.RunSQL "Insert into YourTableName " & _
"Select YourFields" & _
"From YourTable2 " & _
"Where UPC IN(" & Me.MyTextBoxName & ")"

In your textbox on the form, separate your UPCs with a comma.

Hope that helps!
 
M

Maver911

Jeff said:
Try running the SQL from your form using a command button or something.

Docmd.RunSQL "Insert into YourTableName " & _
"Select YourFields" & _
"From YourTable2 " & _
"Where UPC IN(" & Me.MyTextBoxName & ")"

In your textbox on the form, separate your UPCs with a comma.

Hope that helps!

I'm having this issue with trying to bring in a mult-parameter string
via textbox into a query. Basically ive tried setting a string to hold
4 UPC's like this: 123456789 OR 987654321 OR 567894321 OR... etc and
setting that string to be shown in a textbox on my form. Then in my
append query, under the UPC criteria I put:
[Forms]![NameofForm]![MyTextBoxName]. Ive run this and i get only the
results for the first upc in the string. Does anyone have any ideas why
this would be doing this or even any ideas period that would help me
alter my scenerio. Any help would be greatly appreciated. Thank you
Click to expand...
Click to expand...


Thank you so much for your response. I have however tried that also.
here is what i have:

DoCmd.RunSQL "INSERT INTO [VOID - Concantenated UPC LIST] ( UPCCase,
SVItemCd, MaxofSVDescription, [Sub-Banner], [Store Num], Expr1, SVDC,
Region, Store, Address, City, [State/Prov] ) SELECT [UPC List].UPCCase,
[UPC List].SVItemCd, [UPC List].MaxOfSVDescription, [All in one store
reference].[Sub-Banner], [All in one store reference].[Store Num],
[Store Num] & [UPCCase] AS Expr1, [All in one store reference].SVDC,
[All in one store reference].Region, [All in one store
reference].Store, [All in one store reference].Address, [All in one
store reference].City, [All in one store reference].[State/Prov] FROM
[UPC List] LEFT JOIN (Subbanners LEFT JOIN [All in one store reference]
ON Subbanners.Subbanner = [All in one store reference].[Sub-Banner]) ON
[UPC List].SVItemCd = Subbanners.SVItemCd WHERE (([UPC List].UPCCase)
IN ([Forms]![Void Tool]![txttemp]));"

ALSO:: this is how i have the textbox formated by the string below:

[Forms]![Void Tool]![txttemp] = (123456789, 987654321, 567894321)
 
J

Jeff L

Your Where Clause is a little off.
WHERE (([UPC List].UPCCase) IN ([Forms]![Void Tool]![txttemp]));"
Should be
WHERE (([UPC List].UPCCase) IN (" & [Forms]![Void Tool]![txttemp] &
"));"

You can also lose the () from your text box on your form.

Hope that helps!




Jeff said:
Try running the SQL from your form using a command button or something.

Docmd.RunSQL "Insert into YourTableName " & _
"Select YourFields" & _
"From YourTable2 " & _
"Where UPC IN(" & Me.MyTextBoxName & ")"

In your textbox on the form, separate your UPCs with a comma.

Hope that helps!

I'm having this issue with trying to bring in a mult-parameter string
via textbox into a query. Basically ive tried setting a string to hold
4 UPC's like this: 123456789 OR 987654321 OR 567894321 OR... etc and
setting that string to be shown in a textbox on my form. Then in my
append query, under the UPC criteria I put:
[Forms]![NameofForm]![MyTextBoxName]. Ive run this and i get only the
results for the first upc in the string. Does anyone have any ideas why
this would be doing this or even any ideas period that would help me
alter my scenerio. Any help would be greatly appreciated. Thank you
Click to expand...
Click to expand...


Thank you so much for your response. I have however tried that also.
here is what i have:

DoCmd.RunSQL "INSERT INTO [VOID - Concantenated UPC LIST] ( UPCCase,
SVItemCd, MaxofSVDescription, [Sub-Banner], [Store Num], Expr1, SVDC,
Region, Store, Address, City, [State/Prov] ) SELECT [UPC List].UPCCase,
[UPC List].SVItemCd, [UPC List].MaxOfSVDescription, [All in one store
reference].[Sub-Banner], [All in one store reference].[Store Num],
[Store Num] & [UPCCase] AS Expr1, [All in one store reference].SVDC,
[All in one store reference].Region, [All in one store
reference].Store, [All in one store reference].Address, [All in one
store reference].City, [All in one store reference].[State/Prov] FROM
[UPC List] LEFT JOIN (Subbanners LEFT JOIN [All in one store reference]
ON Subbanners.Subbanner = [All in one store reference].[Sub-Banner]) ON
[UPC List].SVItemCd = Subbanners.SVItemCd WHERE (([UPC List].UPCCase)
IN ([Forms]![Void Tool]![txttemp]));"

ALSO:: this is how i have the textbox formated by the string below:

[Forms]![Void Tool]![txttemp] = (123456789, 987654321, 567894321)
Click to expand...
 
M

Maver911

Jeff said:
Your Where Clause is a little off.
WHERE (([UPC List].UPCCase) IN ([Forms]![Void Tool]![txttemp]));"
Should be
WHERE (([UPC List].UPCCase) IN (" & [Forms]![Void Tool]![txttemp] &
"));"

You can also lose the () from your text box on your form.

Hope that helps!




Jeff said:
Try running the SQL from your form using a command button or something.

Docmd.RunSQL "Insert into YourTableName " & _
"Select YourFields" & _
"From YourTable2 " & _
"Where UPC IN(" & Me.MyTextBoxName & ")"

In your textbox on the form, separate your UPCs with a comma.

Hope that helps!


Maver911 wrote:
I'm having this issue with trying to bring in a mult-parameter string
via textbox into a query. Basically ive tried setting a string to hold
4 UPC's like this: 123456789 OR 987654321 OR 567894321 OR... etc and
setting that string to be shown in a textbox on my form. Then in my
append query, under the UPC criteria I put:
[Forms]![NameofForm]![MyTextBoxName]. Ive run this and i get only the
results for the first upc in the string. Does anyone have any ideas why
this would be doing this or even any ideas period that would help me
alter my scenerio. Any help would be greatly appreciated. Thank you
Click to expand...


Thank you so much for your response. I have however tried that also.
here is what i have:

DoCmd.RunSQL "INSERT INTO [VOID - Concantenated UPC LIST] ( UPCCase,
SVItemCd, MaxofSVDescription, [Sub-Banner], [Store Num], Expr1, SVDC,
Region, Store, Address, City, [State/Prov] ) SELECT [UPC List].UPCCase,
[UPC List].SVItemCd, [UPC List].MaxOfSVDescription, [All in one store
reference].[Sub-Banner], [All in one store reference].[Store Num],
[Store Num] & [UPCCase] AS Expr1, [All in one store reference].SVDC,
[All in one store reference].Region, [All in one store
reference].Store, [All in one store reference].Address, [All in one
store reference].City, [All in one store reference].[State/Prov] FROM
[UPC List] LEFT JOIN (Subbanners LEFT JOIN [All in one store reference]
ON Subbanners.Subbanner = [All in one store reference].[Sub-Banner]) ON
[UPC List].SVItemCd = Subbanners.SVItemCd WHERE (([UPC List].UPCCase)
IN ([Forms]![Void Tool]![txttemp]));"

ALSO:: this is how i have the textbox formated by the string below:

[Forms]![Void Tool]![txttemp] = (123456789, 987654321, 567894321)
Click to expand...
Click to expand...

I think you may be on to something here.. now if i can just get it to
work. Now I get a datatype mismatch runtime error. ill look over my
tables to see if the relationships are links properly but i think the
fields are the same. any thoughts?
 
J

Jeff L

Is the UPC number data type set to number? You need double quotes
around each value if they are text.

Jeff said:
Your Where Clause is a little off.
WHERE (([UPC List].UPCCase) IN ([Forms]![Void Tool]![txttemp]));"
Should be
WHERE (([UPC List].UPCCase) IN (" & [Forms]![Void Tool]![txttemp] &
"));"

You can also lose the () from your text box on your form.

Hope that helps!




Jeff L wrote:
Try running the SQL from your form using a command button or something.

Docmd.RunSQL "Insert into YourTableName " & _
"Select YourFields" & _
"From YourTable2 " & _
"Where UPC IN(" & Me.MyTextBoxName & ")"

In your textbox on the form, separate your UPCs with a comma.

Hope that helps!


Maver911 wrote:
I'm having this issue with trying to bring in a mult-parameter string
via textbox into a query. Basically ive tried setting a string to hold
4 UPC's like this: 123456789 OR 987654321 OR 567894321 OR... etc and
setting that string to be shown in a textbox on my form. Then in my
append query, under the UPC criteria I put:
[Forms]![NameofForm]![MyTextBoxName]. Ive run this and i get only the
results for the first upc in the string. Does anyone have any ideas why
this would be doing this or even any ideas period that would help me
alter my scenerio. Any help would be greatly appreciated. Thank you


Thank you so much for your response. I have however tried that also.
here is what i have:

DoCmd.RunSQL "INSERT INTO [VOID - Concantenated UPC LIST] ( UPCCase,
SVItemCd, MaxofSVDescription, [Sub-Banner], [Store Num], Expr1, SVDC,
Region, Store, Address, City, [State/Prov] ) SELECT [UPC List].UPCCase,
[UPC List].SVItemCd, [UPC List].MaxOfSVDescription, [All in one store
reference].[Sub-Banner], [All in one store reference].[Store Num],
[Store Num] & [UPCCase] AS Expr1, [All in one store reference].SVDC,
[All in one store reference].Region, [All in one store
reference].Store, [All in one store reference].Address, [All in one
store reference].City, [All in one store reference].[State/Prov] FROM
[UPC List] LEFT JOIN (Subbanners LEFT JOIN [All in one store reference]
ON Subbanners.Subbanner = [All in one store reference].[Sub-Banner]) ON
[UPC List].SVItemCd = Subbanners.SVItemCd WHERE (([UPC List].UPCCase)
IN ([Forms]![Void Tool]![txttemp]));"

ALSO:: this is how i have the textbox formated by the string below:

[Forms]![Void Tool]![txttemp] = (123456789, 987654321, 567894321)
Click to expand...
Click to expand...

I think you may be on to something here.. now if i can just get it to
work. Now I get a datatype mismatch runtime error. ill look over my
tables to see if the relationships are links properly but i think the
fields are the same. any thoughts?
Click to expand...
 
Top