variable called has leading space.

[Ken]

I Add Criteria to an SQL Stmt with the following code:
strCriteria = strCriteria & "((Orders." & Ctl.Name & ") Like " & Chr(34) &
Chr(42) & str(Ctl.Value) & Chr(42) & Chr(34) & Chr(41)

The result looks like:

(((Orders.Section) Like "* 1009.18*"))

it is the only variable that doesn't return the records and I assume its
because of the space between the * and the 1. I don't know why it is there
and I can't get rid of it.
Any Ideas, am I missing something simple?
Thanks in advance for any help.

 

[Ken]

Got it, I should have kept digging.
Trim()

But I still don't get any results.

 

[Stefan Hoffmann]

hi Ken,

I Add Criteria to an SQL Stmt with the following code:
strCriteria = strCriteria & "((Orders." & Ctl.Name & ") Like " & Chr(34) &
Chr(42) & str(Ctl.Value) & Chr(42) & Chr(34) & Chr(41)

strCriteria = strCriteria & _
"Orders." & Ctl.Name & " Like '*" & CStr(Ctl.Value) & "*'"


mfG
--> stefan <--

 

[Ken]

Nice and clean, but still no records returned. Here goes the rest of my hair.

 

[Stefan Hoffmann]

hi Ken,

Nice and clean, but still no records returned. Here goes the rest of my hair.

Has strCriteria a trailing space before that line of code? What does
MsgBox strCriteria show?


mfG
--> stefan <--

 

[Ken]

I find that no Criteria returns any results with the "Like"
This is the SQL statement produced with a value that when set equal with
regular quotes returns records, but as follows does not.
Select * From Orders WHERE Orders.Subdivision Like '*(a)*' ORDER by
autonumber;

any way it is put together I still can't get results with the Orders. Section

this field is a number stored as text. It is built in a text box by
selections from three combo boxes. Example County 10 town 09 speedzone 18
= section 1009.18

 

[Stefan Hoffmann]

hi Ken,

I find that no Criteria returns any results with the "Like"
This is the SQL statement produced with a value that when set equal with
regular quotes returns records, but as follows does not.
Select * From Orders WHERE Orders.Subdivision Like '*(a)*' ORDER by
autonumber;

This will match any record with Subdivision values containing "(a)".

any way it is put together I still can't get results with the Orders. Section
this field is a number stored as text. It is built in a text box by
selections from three combo boxes. Example County 10 town 09 speedzone 18
= section 1009.18

strCrit = "Section LIKE '*" & CStr(cboCounty) & CStr(cboTown) & ".*'"


mfG
--> stefan <--

 

[Ken]

This section number is built at the form level and stored as the completed
text (1009.18) I added that bit of info just in case the construct was
adding something to the string. The table actually shows a value like
1009.18.

Something else that bugs me is this form is populated using ADO and another
data file on a network. When I store the data on my drive and link it to the
form file I can make a query to retrieve the data I want using the section as
criteria no problem at all. With the table linked and I use ADO I don't get
the results.

and Again the only criteria I can't get to work is the one that will be used
most often, the Section.

Just want to let you know how much I appreciate your input.

 

[Stefan Hoffmann]

hi Ken,

This section number is built at the form level and stored as the completed
text (1009.18) I added that bit of info just in case the construct was
adding something to the string. The table actually shows a value like
1009.18.

Try

strCriteria = strCriteria & _
"Orders." & Ctl.Name & " Like '*" & Ctl.Value & "*'"

The CStr() may convert the . to ,.

mfG
--> stefan <--

 

[Ken]

This is just annoying. I can't get the "Like" to work on any criteria SQL
below:
Select * From Orders WHERE Orders.Subdivision Like '*(a)*' ORDER by
autonumber;

I did try using "=" without having the CStr() and got results on everything
but the Section. I originally had it convert to string in case the criteria
entered was being interpreted as a number and the section in the table is
definately text.

 

[John Nurick]

Hi Ken,

Don't forget that Str() puts a space before a +ve number (to balance the
- before a -ve number). I.e.
"*" & Str(1234) & "*"
returns
* 1234*

Maybe use Format() instead?

 

[Stefan Hoffmann]

hi John,

Don't forget that Str() puts a space before a +ve number (to balance the
- before a -ve number). I.e.
"*" & Str(1234) & "*"
returns
* 1234*

Maybe use Format() instead?

Or CStr(), but that seems not to be his real problem.


mfG
--> stefan <--

 

[Ken]

Thanks John, I didn't know that even existed. But unfortunately it still
didn't return any results.

For anyone reading this, the table field has an input mask of 0000.00;;_
I wrote this program quite a while ago and I don't remember why I did this,
but would this make a difference?

 

[Robert Morley]

No, the Input Mask only enforces data going into the field to follow a
certain pattern; it has no effect on the data in a query.

Ummm...this is sort for Ken/Stefan, I guess...I'm a little rusty on ADO in a
Jet environment...should he be using "%" instead of "*" for a wildcard, or
does it follow the application's syntax?



Rob

 

[Ken]

I GOT IT!!!!!
Thanks so much for the help Rob and Stephan. I couldn't get results with
the "%" but I still left it in when I used "[ ]" around the Ctl.Name and it
worked so I'm leaving it.

I was working on the part of the program that checks for duplicates when
saving a record and noticed that it returned the Section. The brackets were
the only difference, and I think its because I was specifically asking for
orders.[Section] on that one that I used it.

 

[Robert Morley]

Oh crap. Yeah, I think Section is a reserved word, so that would be the
reason it was failing.


Rob

I GOT IT!!!!!
Thanks so much for the help Rob and Stephan. I couldn't get results with
the "%" but I still left it in when I used "[ ]" around the Ctl.Name and
it
worked so I'm leaving it.

I was working on the part of the program that checks for duplicates when
saving a record and noticed that it returned the Section. The brackets
were
the only difference, and I think its because I was specifically asking for
orders.[Section] on that one that I used it.

I Add Criteria to an SQL Stmt with the following code:
strCriteria = strCriteria & "((Orders." & Ctl.Name & ") Like " & Chr(34)
&
Chr(42) & str(Ctl.Value) & Chr(42) & Chr(34) & Chr(41)

The result looks like:

(((Orders.Section) Like "* 1009.18*"))

it is the only variable that doesn't return the records and I assume its
because of the space between the * and the 1. I don't know why it is
there
and I can't get rid of it.
Any Ideas, am I missing something simple?
Thanks in advance for any help.

 

[Stefan Hoffmann]

hi Robert,

Oh crap. Yeah, I think Section is a reserved word, so that would be the
reason it was failing.

Argh, didn't see this one.


mfG
--> stefan <--