Syntax error in code.

[mafhobb]

I have this line of code in my db:

   DoCmd.OpenReport "TestReport", acViewPreview, , Me.lstsearch.Column(10) & " like '*" & "*" & txtsearch & "*" & "*'"

It is supposed to grab the data from a listbox (lstsearch), and match the string in txtsearch to the 10th column and then open and fill the report "TestReport" with it.

I get error 3075 "Syntax error (missing operator) in query expression '( like '*a**)'

"a" was the value in txtsearch at the time.

Something is missing in the code. what is it???

Thanks

Mafhobb

 

[JANR]

..... Me.lstsearch.Column(10) & " like " & "'*" & txtsearch & "*'"


JR

 

[mafhobb]

Slightly different error:

Syntax error (missing Operator) in query expression '( like '*a*')'

Mafhobb

 

[JANR]

Take out the single quotes, the output string should look like

Like *A*


Me.lstsearch.Column(10) & " like " & ""*" & txtsearch & "*""


JR

 

[mafhobb]

Run-Time error '13': Type Missmatch.

I thought the single quotes needed to be there because the variable txtsearch is a string. Is that not the case?

mafhobb

 

[JANR]

What exactly are you trying to do?

I see thay you attempt to open a report with a where-clause, and you pass on a valuestring from a listbox?

What is the value of Me.lstsearch.Column(10) ?

A where-clause must include the fieldname you wish to limit records to:

ex: [Product] Like *Apples*

not: Orange Like *Apples*

JR

 

[mafhobb]

The listbox is called "lstsearch"

Column 10 shows the value of a field called "CustomerID" (text string).

Since this is a listbox, I am using "Column(10)" to define what column in the box needs to match the search string (in txtsearch)

The report opens in preview mode showing only the same results as in the listbox.

All of this is to open a report showing the same results shown in the listbox, which are controlled by txtsearch and also (I have not got to this yet) a combo box "cmbperiod"

mafhobb

 

[ghudson]

Column 10 would then be Column(9) since the counter starts at 0.

 

[mafhobb]

You were right with the Column(9) thing. Here is where I am at:

  DoCmd.OpenReport "TestReport", acViewPreview, , Me.lstsearch.Column(9) & " like " & "'*" & Me.txtsearch & "*'"

Still getting the same error: Syntax Error (missing Operator) in query expression '( like '*a*')'

It is close, but not quite...

mafhobb

 

[mafhobb]

After trying many things, I arrived to

     DoCmd.OpenReport "TestReport", acViewPreview, , Me.lstsearch.Column(9) Like "'*" & Me.txtsearch & "*'"

This does not give any errors, however, it does not filter anything either. All records are present in the report, not just the ones that in column 9 are like txtsearch.

Any suggestions?

mafhobb

 

[JANR]

Add this line in your code:

MsgBox Me.lstsearch.Column(9) & " like " & "'*" & Me.txtsearch & "*'"

Does the msgbox give you the expected filter string you need to filter the report?

JR

 

[mafhobb]

Thanks JANR for the troubleshooting suggestion.


txtsearch input: a
lstbox output: 3 customers that have an "a" in their CustomerID; johnap01, JennyMcar, and Lanatu24
MsgBox output: johnap01 like '*a*'

txtsearch input: na
lstbox output: 2 customers that have that string in their CustomerID; johnap01; lanatu24
MsgBox output: johnap01 like '*na*'

txtsearch input: Jenny
lstbox output: 1 customer that has that string in their CustomerID; JennyMcar
MsgBox output: like '*Jenny*'

txtsearch input: atu
lstbox output: 1 customer that has that string in their CustomerID; lanatu24
MsgBox output: like '*atu*'

My observations:
1. It is interesting to see that when there is only one customer which matches the text string, the output of the message box is: like '*xxx*'. However when there is more than one customer with a matching string then the output is: xxxx like '*xxx"'. Why the difference?

2. When there is more than one customer with a matching string, only the first one in the lstbox actually shows up in the msgbox. The 2nd or 3rd does not.

mafhobb

 

[JANR]

Yes that's what I expected the msgbox would report, you are indead trying to filter a report with:

Orange Like *Apple*

see my post:

A where-clause must include the fieldname you wish to limit records to:

ex: [Product] Like *Apples*

not: Orange Like *Apples*

so change your openreport code to:

DoCmd.OpenReport "TestReport", acViewPreview, , "[CustomerID] Like " & "'*" & Me.txtSearch & "*'"

Provided that CustomerID is in the recordsource of your report.

As for your other question I haven't a good answer

JR