Function in a query

  • Thread starter Thread starter Alastair
  • Start date Start date
A

Alastair

Dear All,

I am trying to create a function for use in a query that
returns values like:

1 or 2 or 3

such as,

Public Function varRetVal() as Variant
varRetVal = 1 or 2 or 3
End Function

but my question is to do with the return value because
Variant does not appear to work, nor does a string. What
return value would I use that would generate the numbers
and logical operator "Or" without treating it as text?

Thanks again...

Alastair
 
Alastair said:
Dear All,

I am trying to create a function for use in a query that
returns values like:

1 or 2 or 3

such as,

Public Function varRetVal() as Variant
varRetVal = 1 or 2 or 3
End Function

but my question is to do with the return value because
Variant does not appear to work, nor does a string. What
return value would I use that would generate the numbers
and logical operator "Or" without treating it as text?

Thanks again...

Alastair
Click to expand...

SQL and VBA, please.
 
Alastair,

Thank goodness for people in this group like Chris2 (who for people like
him, who cannot give a constructive comment to a question, we would be back
in the dark ages, makes people who provide valued feedback like valued
friends). Shame on you Chris2.

Anyway back to your problem Alastair. I would suggest that you create a
function that loops through all the possible values returning a string and
pass the value to a createQueryDef that can create the desired query with
your list of possible values. Have a look at the function below. I would
then bind the form to the query you created.

Public Function CreateMessageQuery() As Boolean
On Error Resume Next
Dim db As Database
Dim qdfTemp As QueryDef
Set db = CurrentDb
Dim sqlString As String
sqlString = "SELECT ID, Date, " & _
"FROM tblMessages " & _
"WHERE tblMessages.[Message for]= " & getMyData & ";"
With db
' Create temporary QueryDef.
.QueryDefs.Delete "qryMessageData"
Set qdfTemp = .CreateQueryDef("qryMessageData", sqlString)
End With
qdfTemp.Close
Set qdfTemp = Nothing
db.Close
Set db = Nothing
End Function

Good Luck.

Kagsy
 
Kagsy said:
Alastair,

Thank goodness for people in this group like Chris2 (who for people like
him, who cannot give a constructive comment to a question, we would be back
in the dark ages, makes people who provide valued feedback like valued
friends). Shame on you Chris2.
Click to expand...

It is you who are acting out of turn, and violating Netiquette
aplenty.

Without sufficient information, a question cannot be answered.
 
Chris2,

The principal guiding force behind netiquette, is to
provide assistance where needed and to give constructive
criticism where warranted. Your comments fail with this
basic premise. If the person posting gives an
unsatisfactory amount of information then say so, not try
to demean them and make them feel small. We do not need
your type on this site.

Kagsy
 
Back
Top