Run Time Error 438

[KLahvic01]

Hi All -

I am receiveing a runtime error 438 during the following part of my code:

If Not IsNull(Me.txtfilterProdYear) Then
        strWhere = strWhere & "([MstrProdYear] Like ""*" & Me.txtfilterProdYear & "*"") AND "
    End If

I think its because the data in MstrProdYear is a number, which makes sense to me, but I am not sure what to change it to so the search code will recognize the numerical years.

Any help would be awesome.

Thanks - Ken

 

[mdlueck]

I suspect you are getting bit by:

Needing to use Chr() to embed quotes into a string
http://www.access-programmers.co.uk/forums/showthread.php?t=239397#post1221108

 

[KLahvic01]

I am a rookie with this stuff, can you provide a little detail on what the Char() means and how it would be implemented into this code?

Thank you in advance.

 

[mdlueck]

Read the post I linked to. Chr() returns the literal character specified by number. So in your case, you may use that to insert a literal double quote character where needed in the SQL string and not have those double quotes get whacked / interpreted by VBA

The code you end up with will only use real double quotes where VBA needs them, and where SQL needs them you will concatenate the Chr(34) function within the string being built.

 

[KLahvic01]

Ok, I tried adding the chr(34) to my code using the example in the link, but it doesnt work for me. I guess I am using the wrong syntax or something. Where in the original string would I replace with the chr(34)?

Ken

 

[mdlueck]

Where in the original string would I replace with the chr(34)?

Paste in what string you ended up with.

I will cook up what I think it should be as an update to this post ala air code.

Please try this...

strWhere = strWhere & "([MstrProdYear] Like " & Chr(34) & "*" & Me.txtfilterProdYear & "*" & Chr(34) & ") AND "

Oh, and I see you are using the * character as your wildcard. That works with DAO / Native Access. The % character is needed as the wildcard when working with ADO objects.

 

[KLahvic01]

My appologies, I usually am able to figure these things out after a little guidance but this one vexes me. Here is the string I put together that still populates the same error:

If Not IsNull(Me.txtfilterProdYear) Then
        strWhere = strWhere & "([MstrProdYear] Like & Chr(34)*" & Me.txtfilterProdYear & "* & Chr(34)) AND "
    End If

I know the Chr(34) should replace "", but the only place where there were "" is where I inserted the Chr(34), but yet the error rears its ugly head.

Thank you for your help, sir!

 

[KLahvic01]

I tried what you requested, even changing the wild card to % and still same error...

 

[mdlueck]

Try my sample code... you need a & on both sides of things you wish to concatenate.

 

[KLahvic01]

I have tried your sample, still receiving the error. I wonder, could it possibly be the fact that the search field is a numerical field, would that change the code at all? The same code works perfectly fine for text fields, but not for the number field (Year).

 

[mdlueck]

I wonder, could it possibly be the fact that the search field is a numerical field, would that change the code at all?

Now you fess up... "That's a horse of a different color!"

Simply drop the quotes surrounding the value being queried (the ones being strategically placed there with the Chr(34) codes). I thought you were dealing with a text field.

Text fields you must quote.
Numeric fields you must NOT quote.

 

[mdlueck]

Oh. seems LIKE on Numeric fields is a bit more complicated.

http://stackoverflow.com/questions/1108171/how-to-use-like-condition-in-sql-with-numeric-field

Yes, the first suggestion works in SQL Server at least...

SELECT [p].[id]
FROM [dbo].[parts] AS [p]
WHERE CONVERT(varchar(20), [p].[id]) LIKE '1%'

1
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
10
11
...

 

[KLahvic01]

Yes it just dawned on me that this is numeric not text. When you say drop the quotes, do you mean drop the Chr(34)'s out of the code to leave something like this:

If Not IsNull(Me.txtfilterProdYear) Then
        strWhere = strWhere & "([MstrProdYear] Like " & "*" & Me.txtfilterProdYear & "*" & ") AND "
    End If

If so, this didnt work either, same error...

 

[KLahvic01]

Now we are getting into uncharted territory with me. I suppose I dont need it to be 'Like'. All I want it to do is when I enter a year in the unbound text box, and click search, only records with that year populate.

I have 4 search criteria fields and each one will allow me to narrow down my searches, 3 text fields work fine, but the numeric field (Year) doesnt. Do you have any other suggestions for this to work?