"The expression is typed incorrectly" etc.

[Greyowlsl]

Hi guys,

Ive been getting that error (thread heading) when trying to run a query

The query runs when i open a form, is there anything wrong with these expressions?

Date Rcvd Field

Between (DateAdd("m",([Forms]![Repairs Recent]![text16]),Date())) And Date()

Product Type Field

[Forms]![Repairs Recent]![text18]

This is some code that i have within the form:

Private Sub Combo12_AfterUpdate()
If Me.Combo12 = "6 months" Then
Me.Text16 = "-6"
Else: Me.Text16 = "-2"
End If
Me.Requery
End Sub

Private Sub Combo14_AfterUpdate()
If Me.Combo14 = "PVE1200" Then
Me.Text18 = "Like PVE1200"
ElseIf Me.Combo14 = "PVE2500" Then
Me.Text18 = "Like PVE2500"
ElseIf Me.Combo14 = "LS & IRM" Then
Me.Text18 = "Like LS* or LIke IRM*"
ElseIf Me.Combo14 = "BKZ" Then
Me.Text18 = "Like BKZ*"
ElseIf Me.Combo14 = "Other" Then
Me.Text18 = "Not Like PVE* and Not Like LS* and Not Like IRM* and Not Like BKZ*"
Else
End If
Me.Requery
End Sub

Thanks,
Leon

 

[apr pillai]

Correct Syntax:

= "Like 'PVE2500'"

= "Like 'LS*' or LIke 'IRM*'"

 

[Greyowlsl]

Thanks Apr Pillai,

However this hasn't helped my issue with my original issue of incorrectly typed expression error.
I have done some trial and error and found that the issues is with the query expression:

Between (DateAdd("m",([Forms]![Repairs Recent]![text16]),Date())) And Date()

Is there anyway i can simplify this? another query maybe?

I tried using '<=' instead of the 'between' command but got the same error.
I also tried removing the form reference and just put in a fixed number (ie. -3), but this returned a blank datasheet

Also if i leave the query as it is but run it without having the form open at all then it will ask for parameters, but then just show another blank datasheet.

 

[Galaxiom]

You probably need to format the dates to delimited date strings.

Between Format(DateAdd("m",[Forms]![Repairs Recent]![text16],Date()),"\#mm\/dd\/yyyy\#")) And Format(Date(),"\#mm\/dd\/yyyy\#")

Personally I would also change the combo so that the text column was visible but the Bound Column held the number. Then you could refer directly to the combo without the need for [text16] or the AfterUpdate procedure.

 

[spikepl]

The second argument of DateAdd is a number, not the string that you are trying to pass.

As to text18: You cannot pass SQL code from a textbox to a query, only values, because the whole lot is wrapped in "" , so you wind up with

..... MyVariable "Like 'LS*'" and that will fail, because it should be MyVariable Like 'LS*'

You can create the SQL of the query on the fly, or use QueryDef and modify the query's SQL there.

 

[Galaxiom]

The second argument of DateAdd is a number, not the string that you are trying to pass.

I thought of that too but that might not be the issue. Sometimes Access will do an implicit datatype conversion. Failure to convert throws a mismatch error on the function. However it is possible the query gave up before it got to the function so never reached that error

As to text18: You cannot pass SQL code from a textbox to a query, only values

That is definitely an issue. Keywords, tablenames and fieldnames cannot be taken up from a textbox into an ordinary query while it is run. That stuff can only be concatenated into an SQL string which is what I assumed you were doing.