Data type issue

[DiamondDust]

I am trying to use a variable in an if statement expression. For example: If dcriteria Then
end if

dcriteria is equal to a field value which is of type memo.
in the field is criteria I want to use for the if statement. For example: dcode >= "80000" And
dcode <= &quot;8291*&quot;

My problem is I can't get the if statement to except the dcriteria. The error is &quot;Type Mismatch&quot;. Does anyone know a work around.

 

[psemianonymous]

So dcode is a field in a table? Are you working in a bound form, or working with a recordset? You can't just reference the fieldname without some sort of context.

In a form:

Me![fieldname]

In a recordset:

rstSomeVariableName![fieldname]

Note the prefixes.

 

[Golom]

The reason that you are getting the type mismatch is that

   If <criteria expression> Then

expects &quot;<criteria expression>&quot; to be a boolean or at least, something that it can convert to boolean. In your case it can't figure out how to convert the contents of your memo so it gives you the type mismatch.

 

[DiamondDust]

I am working with a recordset. Here is the flow.

dcriteria = rstSubmeasureServiceCriteria.Fields(&quot;criteria&quot
dcode = rstClaim.Fields(dcodetype) ' Example dcode would equal 80001

if dcriteria then ' Example dcriteria would equal dcode
end if ' >= &quot;80000&quot; And dcode <= &quot;8291*&quot;

This example I should execute the if statement, but I am getting type mismatch.

 

[psemianonymous]

So dcriteria is a string.

So you want to evaluate the string like it's an actual expression, so do this using

If Eval(dcriteria) Then

Read up on Eval() in the help files. Honestly, I don't recommend this sort of thing, but if it's how it works, it's how it works.


Pete

 

[DiamondDust]

Foolio12, why don't you recommend sort of thing? Is there something that I need to keep my eye on?

 

[psemianonymous]

It's just nasty to try an execute a string as if it were code.

Say, for example, that the value of criteria is &quot;insert text here&quot;, so when you run Eval(&quot;insert text here&quot, you get an error. If you, instead, use some sort of &quot;Criteria Type&quot;, instead of directly interpreting and executing the string, you can filter the &quot;bad input&quot; first by parsing the string--anything unexpected doesn't get executed.

But if it works, all the time, then there's nothing wrong with it. Now that I think about it, Eval() probably won't do what you need. But try it anyway.


Pete

 

[DiamondDust]

Here is the error message: Microsoft Access can't find the name 'dcode' you entered in the expression.

The value in the field I am using for the string is:
dcode >= &quot;80000&quot; And dcode <= &quot;8291*&quot;

 

[cmmrfrds]

If as Golom pointed out the field type is Memo, then you cannot perform a boolean operation such as =, >, <>, etc..

Try converting to a string and then you should be able to do the boolean operation.

Either.
Cstr(dtcriteria) >= something
Cvar(dtcriteria) >= something

Unless the Eval function does convert it to a string?

 

[DiamondDust]

It's taking the memo field in fine and converting to a string now the problem is that I am using a variable that is in the code in my string and it doesen't recognize the variable.

 

[psemianonymous]

Can you provide several examples of what the &quot;Criteria&quot; field may look like? If you can, please give some examples.

Can you still redesign the &quot;Criteria&quot; field? If so, consider changing the criteria from a direct criteria to a less 'flexible', more rigid &quot;criteria type&quot; + &quot;specific criteria&quot; sections.

Maybe I should stop offering to help--I haven't been of much use thus far.


Pete

 

[DiamondDust]

dcode = 1000
dcode >= 1000 and dcode <= 2000
dcode >= 1000 and dcode <= 2000 or dcode = 1000

You helping dude, it's probably something easy.

 

[psemianonymous]

It's not easy, from what I see.

What I hope you're trying to achieve is to filter out a specific query by using this criteria. What you can do to achieve this is build an SQL statement using the &quot;Criteria&quot; as the WHERE Clause of the SQL statement.

So you'd have something like:

strSQL = "SELECT * FROM tblName WHERE " & rst!Criteria
Forms!SomeForm.RecordSource = strSQL

But the multiple TYPES of criteria--just not going to work. I know what you're trying to do--you're trying to store as much programming logic as possible in the table, so that it is therefore easier to make edits to the program logic.

But it won't work this way. The closest thing VBA has to code interpretation is the Eval() function, which must attempt to call a function of some sort. So you can't use it in the middle of an If statement.


But you can get .. whatever you're trying to do.. you can do that, some other way. Construct a second recordset and use the criteria to build the SQL statement that is the data source for that second recordset. This is just one example.


You just can't evaluate a string like it was code.


Pete