How to Format Character String to Make it Compatible with Criteria in a Query

[atoy]

Good day to every one

Weird question i guest,

I'm using a function as a query criteria, that function simply get the value of a global variable all this worked of but is seams that ms access does not
understand

this an example (it refers to what it beside the quotation marks)

property = "Between 1 And 98"

When i type Between 1 And 98 as a criteria in the query design it works OK.

I can't figure what wrong.

Thanks for your help.

[topher23]

Could you post the full text of the Query you're trying to build? Are you building this query in code? What is providing the "Between 1 and 98" string to the query?

[atoy]

Hi topher23

Thanks to give me a hand on this.

May be my explanation was not quite clear.

I build the query in query design.

I used a function to get the value of a global variable which as "Between 1 And 98" as a value.

I'm getting a Data type mismatch in criteria expression error at the execution when i get the value from the function call.

When i type the same value by hand as i criteria in the query design the query worked ok.

i tried to put single quotation " ' Between 1 and 98 ' " it did not work either.




Thanks.

[ajalwaysus]

You need to post the code you are using, if you don't it will be next to impossible to help you. Look at it from our view, we do not know what your code looks like, so how can we tell you what is wrong with it. If I give you an error I have but don't give you the code that generates the error, how could you answer.

-AJ

[atoy]

In the main module i have :

Public priority99 As Variant

'function that i call in the query design

Function getPriorities() As Variant
         getPriorities = priority99
End Function

'this is where i assign the value to the priority99 global variable
'If a checkbox is clicked or not the corresponding value is affected to the 'global variable

If CheckAllPriotiy.Value = -1 Then
    priority99 = "Between 1 And 99"
Else
    priority99 = "Between 1 And 98"
End If

thanks

[topher23]

Okay, the way you're trying to go about this isn't going to work. By putting your criteria into a string variable, you're asking Access to evaluate the string as the criteria. Access is looking at

 WHERE property = "Between 1 and 98"

and trying to match [property] to the string "Between 1 and 98". property is obviously not a string field, so you're getting a type mismatch.

If you want to do something like this using Query Designer, you need to set 2 global variables. For illustration, I'll call them intStartNum and intEndNum. These are Integer values. Once intStartNum and intEndNum are set, your query will be

WHERE property Between intStartNum and intEndNum

I know of no other way to do this other than building the query from SQL in code, which is probably beyond your capabilities at this time.

[atoy]

code in Main module :

Public priority99 As String

'function that i call in the query design in criteria field

Function getPriorities() As String
         getPriorities = priority99
End Function

'Code in the form that is setting value to the global variable

If CheckAllPriotiy.Value = -1 Then
    priority99 = "Between 1 And 99"
Else
    priority99 = "Between 1 And 98"
End If

thanks

[ajalwaysus]

Where is the code that calls getPriorities() in it?

-Aj

[topher23]

If what you've posted indicates the only values for your variable, then you actually have less work to do.

Change your variables to work like this:

 Public priority99 as Integer

Function getPriorities() As Integer
getPriorities = priority99
End Function

'Code in the form that is setting value to the global variable

If CheckAllPriority.Value = -1 Then
priority99 = 99
Else
priority99 = 98
End If

Now, in your query, set your criteria to

WHERE property Between 1 And getPriorities()

EDIT:
Sorry, I posted the SQL version of the query. In query designer, put

Between 1 and getPriorities()

in the criteria for the field.

[atoy]

sorry i posted twice

this is an image of where i called the function

[atoy]

topher23 you have put the finger on it!

In the criteria in query design i put

Between 1 And getPriorities() as proposed by you and it worked. Yes!

Once you know it that it's very logical... can't pass a string to a field with a number type. I was getting confused by the between i guest.

I would like to thanks you very much for you help .

Have an excellent day.

[topher23]

No problem. You can thank me by clicking "choose as best answer." It's our rating system, you see. :)

[atoy]

It is done Topher23, thanks once more.