query problem

[Fay Yocum]

On a form I have an option group that based on which selection you make and
the date entered places a date with the logical symbols in front of the
date. for example <=#03/31/2005#. When I enter the parameter
[Forms]![frmReports]![txtTestResults] on the QBE I get the following error
message: "This expression is typed incorrectly, or it is too complex to
evaluated..."

If I type the same thing in manually I get the correct results. Here is the
code used to develop the results in the txtTestResults textbox.

Private Sub frmDates_AfterUpdate()
'Dim Dates As Integer
Select Case frmDates 'select the date range
Case 1
txtRosterStart.Visible = True
txtRosterEnd.Visible = False
lblRosterStart.Caption = "Date"
txtCriteria.Value = "<"
txtTestResults = txtCriteria & "#" & Format(txtRosterStart,
"mm/dd/yyyy") & "#"
stWhere = txtTestResults
Case 2
txtRosterStart.Visible = True
txtRosterEnd.Visible = False
lblRosterStart.Caption = "Date"
txtCriteria.Value = ">"
txtTestResults = txtCriteria & "#" & Format(txtRosterStart,
"mm/dd/yyyy") & "#"
Case 3
txtRosterStart.Visible = True
txtRosterEnd.Visible = False
lblRosterStart.Caption = "Start date"
txtCriteria.Value = "="
txtTestResults = txtCriteria & "#" & Format(txtRosterStart,
"mm/dd/yyyy") & "#"
Case 4
txtRosterStart.Visible = True
txtRosterEnd.Visible = False
lblRosterStart.Caption = "Date"
txtCriteria.Value = ">="
txtTestResults = txtCriteria & "#" & Format(txtRosterStart,
"mm/dd/yyyy") & "#"
Case 5
txtRosterStart.Visible = True
txtRosterEnd.Visible = False
lblRosterStart.Caption = "Date"
txtCriteria.Value = "<="
txtTestResults = txtCriteria & "#" & Format(txtRosterStart,
"mm/dd/yyyy") & "#" End Select

Fay

 

[Wayne Morgan]

If the value will always be a date, I believe you will have better luck if
you add a Parameters statement to the SQL of your query.

Example:
PARAMETERS [Forms]![frmReports]![txtTestResults] DateTime;
SELECT Table1.DateField FROM Table1
WHERE Table1.DateField <= [Forms]![frmReports]![txtTestResults];

This will tell the query the data type of the parameter. No # signs needed.

As far as adding the operator though, when you add it this way it is
considered to be part of the criteria, not an operator. So, Access is trying
to match the text string "<=#1/1/2005#" with the value of the field. Since
the field is a date field, they don't match. To do this, I recommend
rewriting the SQL of the query.

strSQL = "PARAMETERS [Forms]![frmReports]![txtRosterStart] DateTime;"
strSQL = strSQL & vbcrlf & "SELECT .... WHERE [DateField]"
strSQL = strSQL & " <= [Forms]![frmReports]![txtRosterStart]"
CurrentDb.QueryDefs("MyQuery").SQL = strSQL

Or, since your rewriting the SQL anyway, you could just include the value.

strSQL = "SELECT .... WHERE [DateField]"
strSQL = strSQL & " <= " & Me.txtRosterStart
CurrentDb.QueryDefs("MyQuery").SQL = strSQL

--
Wayne Morgan
MS Access MVP

On a form I have an option group that based on which selection you make
and the date entered places a date with the logical symbols in front of
the date. for example <=#03/31/2005#. When I enter the parameter
[Forms]![frmReports]![txtTestResults] on the QBE I get the following error
message: "This expression is typed incorrectly, or it is too complex to
evaluated..."

If I type the same thing in manually I get the correct results. Here is
the code used to develop the results in the txtTestResults textbox.

Private Sub frmDates_AfterUpdate()
'Dim Dates As Integer
Select Case frmDates 'select the date range
Case 1
txtRosterStart.Visible = True
txtRosterEnd.Visible = False
lblRosterStart.Caption = "Date"
txtCriteria.Value = "<"
txtTestResults = txtCriteria & "#" & Format(txtRosterStart,
"mm/dd/yyyy") & "#"
stWhere = txtTestResults
Case 2
txtRosterStart.Visible = True
txtRosterEnd.Visible = False
lblRosterStart.Caption = "Date"
txtCriteria.Value = ">"
txtTestResults = txtCriteria & "#" & Format(txtRosterStart,
"mm/dd/yyyy") & "#"
Case 3
txtRosterStart.Visible = True
txtRosterEnd.Visible = False
lblRosterStart.Caption = "Start date"
txtCriteria.Value = "="
txtTestResults = txtCriteria & "#" & Format(txtRosterStart,
"mm/dd/yyyy") & "#"
Case 4
txtRosterStart.Visible = True
txtRosterEnd.Visible = False
lblRosterStart.Caption = "Date"
txtCriteria.Value = ">="
txtTestResults = txtCriteria & "#" & Format(txtRosterStart,
"mm/dd/yyyy") & "#"
Case 5
txtRosterStart.Visible = True
txtRosterEnd.Visible = False
lblRosterStart.Caption = "Date"
txtCriteria.Value = "<="
txtTestResults = txtCriteria & "#" & Format(txtRosterStart,
"mm/dd/yyyy") & "#" End Select

Fay