Textbox info passed to query causing error

[Elysynn]

Good Afternoon,

I have a couple of dropdowns on a form set up for report selection. Based on the selections, a textbox changes values for the appropriate parameters in the query the reports are based off of.

Here's the problem: I refer to the textbox in the query as [forms]![frmReportCriteria]![txtTalkTimeParameter] The two parameters that can be passed are <10 or Between 10 And 30. This is the code that feeds the txtTalkTimeParameter box...

Private Sub cboReportGT10_Change()
    
    If Me.cboReportGT10.Value > 0 Then
    Me.txtTalkTimeParameter.Value = "BETWEEN 10 AND 30"
    Me.cboReportLT10 = ""
    Else
    Me.txtTalkTimeParameter = Null
    End If
    
End Sub
Private Sub cboReportLT10_Change()

    
    If Me.cboReportLT10.Value > 0 Then
    Me.txtTalkTimeParameter.Value = "<10"
    Me.cboReportGT10 = ""
    Else
    Me.txtTalkTimeParameter = Null
    End If

End Sub

This works fine for the <10 designation, but if it is the Between 10 and 30, I get an error stating "This Expression is typed incorrectly, or it is too complex to be evaluated, etc..." If I actually type Between 10 and 30 it works, but if I use the text box to do it, it doesn't. Any ideas?

Thanks,
Elysynn

 

[Remou]

Hi
I have tried a mock-up of the above and it seems to be working. Pehaps you could post the code / SQL for the query?

 

[Zion7]

Is it possible Elysynn, that you need a space before the "B"?

Me.txtTalkTimeParameter.Value = " BETWEEN 10 AND 30"

 

[Elysynn]

Zion7,
I tried the space, and that didn't seem to help.

Here is the SQL for the query:

SELECT tblNICEReleases.Start_Date, tblNICEReleases.Start_Time, tblNICEReleases.Stop_Date, tblNICEReleases.Stop_Time, tblNICEReleases.Calling_Party, tblNICEReleases.Answering_Login_ID, Int(Left([Answering_Login_ID],6)) AS EmpExtn, tblNICEReleases.Duration, tblNICEReleases.Agent_Talk_Time, tblNICEReleases.After_Call_Work, tblNICEReleases.Transferred, tblNICEReleases.Times_Held
FROM tblNICEReleases
WHERE (((tblNICEReleases.Start_Date) Between [forms]![frmReportCriteria]![txtBeginDate] And [forms]![frmReportCriteria]![txtEndDate]) AND ((tblNICEReleases.Agent_Talk_Time)=[forms]![frmReportCriteria]![txtTalkTimeParameter]) AND ((tblNICEReleases.Transferred)="N"));

I'm thinking it has something to do with the syntax of the
Me.txtTalkTimeParameter.Value = "BETWEEN 10 AND 30" designation... I'm just not sure what it is...

Thanks again for taking the time to help me out!
-Elysynn

 

[Remou]

I think you need to create an SQL string, and use it.

strSQL="SELECT tblNICEReleases.Start_Date, tblNICEReleases.Start_Time, tblNICEReleases.Stop_Date, tblNICEReleases.Stop_Time, tblNICEReleases.Calling_Party, tblNICEReleases.Answering_Login_ID, Int(Left([Answering_Login_ID],6)) AS EmpExtn, tblNICEReleases.Duration, tblNICEReleases.Agent_Talk_Time, tblNICEReleases.After_Call_Work, tblNICEReleases.Transferred, tblNICEReleases.Times_Held
FROM tblNICEReleases
WHERE (((tblNICEReleases.Start_Date) Between #" _
& [forms]![frmReportCriteria]![txtBeginDate] & "# And #" _
& [forms]![frmReportCriteria]![txtEndDate]) & "# AND ((tblNICEReleases.Agent_Talk_Time) " _
& [forms]![frmReportCriteria]![txtTalkTimeParameter]) _
& " AND ((tblNICEReleases.Transferred)="N"));"

Hope I have that right. This bit:
((tblNICEReleases.Agent_Talk_Time) " _
& [forms]![frmReportCriteria]![txtTalkTimeParameter]) _

Should then read either
((tblNICEReleases.Agent_Talk_Time) BETWEEN 10 AND 30
Or
((tblNICEReleases.Agent_Talk_Time) <10

 

[Zion7]

Interesting Remou, I agree with you, but on the same note, I would have expected "<10" to fail also, they way Elsynn had it?(with an "=").

You said you were able to get it to work with a mock example.
Is it worth showing, or basically it's just the same syntax as your last post?

Either way, it looks promoising...

 

[Remou]

Yes, Zion7, I too would expect <10 to fail. Maybe something changed. The mock up is a very simple query, using only the idea behind the question as I did not have the SQL at the time:

Dim qdf As DAO.QueryDef
Dim db As DAO.Database

SQL = "Select * From tblTable where intNumber " & Me.txtTalkTimeParameter
Set db = CurrentDb
        Set qdf = db.CreateQueryDef("NewQueryDef", SQL)
DoCmd.OpenQuery qdf.Name

 

[Elysynn]

I'm thinking the problem has something to do with <10 being a simple arithmetic expression where the Between statement is being carried over into the query as a string...

Remou - I may have to borrow your idea - does the code you posted actually create the query where I could go into Queries in the database window and view it? If so, I think I can use that as all of the other queries in the database feed off of the SQL I posted earlier.

Thanks again for the help!
-Elysynn

 

[Zion7]

Possibly Elysynn, might be a data type issue?

Sorry Remou, since I'm already here, I'll take the liberty to respond to Elysynn last question, if you don't mind.

Yes Elysynn, it will save provided you include a name(1st argument), to the CreateQueryDef method. In other words, it could be written like...

SQL = "Select * From tblTable where intNumber " & Me.txtTalkTimeParameter
Set db = CurrentDb
Set qdf = db.CreateQueryDef(, SQL)

...then the query will destroy after processing. And you won't be able to open for viewing,(DoCmd.OpenQuery qdf.Name)

 

[Remou]

Well, I find that <10 fails, but maybe there is some bit I'm missing.

 

[Elysynn]

Thanks, Zion7. I think I'm going to put that in my pocket for a back-up plan... Stubborn individual that I am, I'm still trying to figure out the problem with my first method as I need to keep the query in "storage" as it were for additional usage.

So, here is the inelegant solution that I came up with that works...

I created another text box and the parameter in the query now reads:
Between [forms]![frmReportCriteria]![txtTalkTimeParameter] And [forms]![frmReportCriteria]![txtTalkTimeParameter2]

I changed the code in the form to read as such:

Private Sub cboReportGT10_Change()
    Dim strParameterValue As String
       
    If Me.cboReportGT10.Value > 0 Then
    Me.txtTalkTimeParameter.Value = 10
    Me.txtTalkTimeParameter2.Value = 30
    Me.cboReportLT10 = ""
    Else
    Me.txtTalkTimeParameter = Null
    End If
    
End Sub
Private Sub cboReportLT10_Change()

    
    If Me.cboReportLT10.Value > 0 Then
    Me.txtTalkTimeParameter.Value = 0
    Me.txtTalkTimeParameter2.Value = 9
    Me.cboReportGT10 = ""
    Else
    Me.txtTalkTimeParameter = Null
    End If

End Sub

I did this because for some inexplicible reason, the "<10" option started giving me the same error. <shrug> However, this solution does the trick and serves the purpose for now.