Query criteria from textbox Help 2

[Herdrich]

I have a text box that uses this code to append numbers together for a criteria for a query.

Dim AddText As String
Dim NewText As String
Dim strOr As String

If Forms!Test!Type2.Value = "" Then
   strOr = ""
   Oldtext = Forms!Test!Type2.Value
   AddText = Me.QuestionID
   NewText = Oldtext & strOr & AddText
   Forms!Test!Type2.Value = NewText
Else
   strOr = " or "
   Oldtext = Forms!Test!Type2.Value
   AddText = Me.QuestionID
   NewText = Oldtext & strOr & AddText
   Forms!Test!Type2.Value = NewText
End If

Then in my qurey criteria i have Forms!Test!Type2

This has the correct output if only one number is in the field ex 10 but once the text box changes from that to "10 or 30" it stops working. Although if i past "10 or 30" into the criteria box it works correctly. Any ideas on how to make this work correctly?

Thanks,
Glenn

 

[Andrzejek]

Dim AddText As String
Dim NewText As String
Dim strOr As String

If Forms!Test!Type2.Value = "" Then
   strOr = ""[blue]
   Oldtext = Forms!Test!Type2.Value
   AddText = Me.QuestionID
   NewText = Oldtext & strOr & AddText
   Forms!Test!Type2.Value = NewText[/blue]
Else
   strOr = " or "[blue]
   Oldtext = Forms!Test!Type2.Value
   AddText = Me.QuestionID
   NewText = Oldtext & strOr & AddText
   Forms!Test!Type2.Value = NewText[/blue]
End If

Since [blue]BLUE[/blue] part of you code is on both IF and ELSE, why repeat it?

Dim AddText As String
Dim NewText As String
Dim strOr As String

If Forms!Test!Type2.Value <> "" Then
   strOr = " or "
End If
[blue]
Oldtext = Forms!Test!Type2.Value
AddText = Me.QuestionID
NewText = Oldtext & strOr & AddText
Forms!Test!Type2.Value = NewText[/blue]

Could you show us the SQL that uses your "10 or 30" criteria part?

Have fun.

---- Andy

 

[dhookom]

You can't pass "OR", "AND", "IN", ">", ... or any other operator into the query criteria through a control on a form.

If it did work, I would still change the code to:

If Forms!Test!Type2.Value[red][b] & ""[/b][/red] = "" Then

Is the query the record source of a report or form?

Duane
Hook'D on Access
MS Access MVP

 

[Herdrich]

I repeated it because when i had it the other way it kept putting OR in front of the first append no matter what so i just changed it to that. I was missing the <> so that should solve that problem.

This is the SQL code i am using. Not being able to pass the operator into the query is the problem. Is their a way to get around that?

SELECT Questions.[QuestionID], Questions.[Question], Questions.[Answer]
FROM Questions
WHERE (((Questions.[QuestionID])=[Forms]![Test]![Type]));

The query is taking information from a table of questions and the questionID is appended to Forms!Test!type every time a question is answered wrong. Any ideas on how to go about this a different way if this isnt possible?

 

[dhookom]

Is this actually SQL "code" or the SQL view of a saved query? I would probably modify the SQL property of a saved query.

Get the numeric values into a string variable like "10,30"

Dim strSQL as String
strSQL = "SELECT QuestionID, Question, Answer FROM Questions " & _
  "WHERE QuestionID IN (" & strVariable & ")"
CurrentDb.QueryDefs("qselYourQueryName").SQL = strSQL

Duane
Hook'D on Access
MS Access MVP

 

[Herdrich]

I switched my if than statement to be more efficient and changed it to 10,30 instead of 10 or 30 like dhookom recommended. I dont know how currentdb.querydefs works so i tried the bellow code. Do you know the problem with the code bellow or can you tell me how to use your code properly

I tried this but ended up with Run-time error 3075

Dim strwhere As String
Dim strSQL As String

strwhere = Forms!Test!Type2.Value

strSQL = "SELECT * FROM QuestionsWrong WHERE QuestionID IN (" & strwhere & ")"

DoCmd.OpenReport "Questions", acViewPreview, , strSQL

 

[dhookom]

Dim strwhere As String
Dim strSQL As String

strwhere = Forms!Test!Type2.Value

strSQL = "QuestionID IN (" & strwhere & ")"

DoCmd.OpenReport "Questions", acViewPreview, , strSQL

If this code is running in the Test form, then I would use:

Dim strwhere As String
strwhere = "QuestionID IN (" & Me.Type2 & ")"
DoCmd.OpenReport "Questions", acViewPreview, , strWhere

Duane
Hook'D on Access
MS Access MVP

 

[Herdrich]

Dang I should have known that answer I have used code like this before I guess I was just getting frustrated with it. Thank you for all of your help.