textbox as query criteria syntax

[sxschech]

How do I change this to get conditions from a form:
[tt]
WHERE WeekNo Is Not Null AND DISPLAY_STATUS<>"Cancelled" AND TERM [red]In ('200640','200740','200840')[/red]
[/tt]

I tried putting a textbox on the form called txtReportingTerms and entered this into it:

'200640','200740','200840'

Then modified the query as follows:
[tt]
WHERE WeekNo Is Not Null AND DISPLAY_STATUS<>"Cancelled" AND TERM [red]In ([forms]![frmMain]![txtReportingTerms]) [/red]
[/tt]

No results were returned. This is a regular query, not a VBA sql statement. Do I need to modify the query def?

Here is the entire query for reference. As can be seen in the query, I am referring to the form for data elements without a problem.

TRANSFORM Sum(Amdata.TOT) AS SumOfTOT
SELECT Amdata.STU_POP, forms!frmMain.txtCurrentTerm AS TERM, Amdata.COLLEGE_SORT, Amdata.COLLEGE, Amdata.PROGRAM, Amdata.STATUS, Amdata.DISPLAY_STATUS, Sum(Amdata.TOT) AS [Total Of TOT]
FROM Amdata INNER JOIN AMCrossHeader ON Amdata.WeekNo = AMCrossHeader.WeekNo
WHERE (((Amdata.WeekNo) Is Not Null) AND ((Amdata.DISPLAY_STATUS)<>"Cancelled") AND ((Amdata.TERM) In ('200640','200740','200840')))
GROUP BY Amdata.STU_POP, forms!frmMain.txtCurrentTerm, Amdata.COLLEGE_SORT, Amdata.COLLEGE, Amdata.PROGRAM, Amdata.STATUS, Amdata.DISPLAY_STATUS
ORDER BY Amdata.STU_POP, forms!frmMain.txtCurrentTerm, Amdata.COLLEGE_SORT, Amdata.PROGRAM, Amdata.STATUS
PIVOT AMCrossHeader.YrHeader In ("Yr0101","Yr0102","Yr0103","Yr0104","Yr0105","Yr0106","Yr0201","Yr0202",
"Yr0203","Yr0204","Yr0205","Yr0206","Yr0301","Yr0302","Yr0303","Yr0304",
"Yr0305","Yr0306");

 

[TheAceMan1]

How are sxschech . . .

[red]You need to start another thread![/red]

As you can see, this thread
strectches too far across the screen.

Try copying to [blue]NotePad 1st[/blue],
then copy into the forum . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[JerryKlmns]

TheAceMan1

I read it fine on my 19" monitor. But there are times that the body of the post expands over the right panes of the page.

Strange behavior on Mozilla Firebird 0.7

sxschech

Maybe it would be more convinent to show those values in a listbox which allows multiselection and apply this

Private Function SelectedValues() As String
On Error GoTo Err_cmdShow_Click
Dim my_In As String
Dim varItem As Variant
    
    For Each varItem In lstTerms.ItemsSelected
        my_In_ = my_In & ", '" & lstTerms.Column(1, varItem) & "'"
    Next varItem
    my_In = " AND ((Amdata.TERM) In (" & Mid(my_In, 3) & ")"
    If my_In = " AND ((Amdata.TERM) In ()" Then
        MsgBox "You didn 't select any Term at all!", vbCritical + vbOKOnly, App_Name
        my_In = ""
    End If
SelectedValues = my_In

Exit_cmdShow_Click:
    Exit Function

Err_cmdShow_Click:
'    Call RecordErrors("frmReports", "cmdShow_Click", Err.Number, Err.Description)
    MsgBox Err.Description, vbCritical, App_Name
    Resume Exit_cmdShow_Click

End Function

Now you have your values properly. If this query is for a report don't include Amdata.TERM in the WHERE clause of the query , but instead the SelectedValues at the WhereCondition of the report.

 

[MakeItSo]

1.) Sure your "term" is text?
Looks like a number field to me.
In that case: leave away the single quotes!
;-)

2.) Assuming term is a number field (if it isn't, add some single quotes again), you can put this in the click event of your "show query" button, whatever its name:

Dim strSQL as string, qry as String

qry="[Name of your query]"

strSQL="TRANSFORM ...." & _
" WHERE (((Amdata.WeekNo) Is Not Null) AND ((Amdata.DISPLAY_STATUS)<>"Cancelled") AND ((Amdata.TERM) In (" [b]& _
txtReportingTerms.text & ")))" & _[/b]
" GROUP BY ...
...
CurrentDb.QueryDefs(qry).SQL = strSQL
DoCmd.OpenQuery qry, acViewNormal

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[sxschech]

Hi, coming back to this issue...seeing how I have several queries that are using the same criteria for the Term portion (other parts of the where clause are a bit different and have different order bys), I'm wondering how I can put this together without having a bunch of SQL statements. I thought about using qd.sql with Replace(),

Sample

Set qd = db.QueryDefs("Amdata_Crosstab_ByWeek")
    If Forms!frmmain.CurrentTerm = 200840 Then
        If Mid(qd.SQL, InStr(qd.SQL, "TERM) In (") + 15, 2) = 30 Then
            qd.SQL = Replace(qd.SQL, "

but since I don't exactly know where/what I'm replacing it's been a bit of a puzzle. My goal on the form is that the user will enter the term year example: 2008 and indicate Spring or Fall. Behind the scenes, the results for the term will be:

If user enters 2008 and Spring (I envision a combo for Spring/Fall), the result that needs to go in the where clause would be ('200620', '200720', '200820')

If use enters 2008 and Fall, clause would be ('200630','200730','200830','200640','200740','200840')

For 2009 Spring would be ('200720', '200820', '200920')
Fall would be ('200730','200830','200930','200740','200840','200940')

User can also run historically, so if they want 2003 then it would be

Spring ('200120', '200220', '200320')
Fall ('200130','200230','200330','200140','200240','200340')


Any thoughts how I could update that part of the where clause, or do I need to have as suggested above, the SQL statement in the vba code and then edit each one using the method MakeItSo suggests. Since at the moment, I have 5 queries that need updating, that would mean having 5 sql statements in the vba code?

If you need to see the SQL or where clauses, let me know.

 

[sxschech]

I thought about it and what I am going to do is have the 5 queries without the Term in the Where clause and give them a name ending with _Source. Then have a SELECT * FROM X_Source Where Term in (...