Function in QBE grid not returning correct data(?)

[i. dzhugashvili]

I'm hoping someone can help me here.

I'm having problems using a function to get the search criteria for a
column in the QBE grid. The column's data type is Long and the
problem is that if I want to search on more than one value my function
in the QBE correctly returns "1 Or 4" (for example), but something's
getting garbled and I'm not getting the same data back if I just enter
"1 Or 4" (w/o quotes) in the QBE grid.

My guess is that my function is returning a string and so that's
what's used in the QBE. I tried returning a Variant and I tried a
bunch of other stuff --- but nothing's worked!

Any suggestions would be greatly appreciated.

-id

[Larry Linson]

Try using "In(1,4)" as the criteria, without the quotes, of course.

Larry Linson
Microsoft Access MVP

"i. dzhugashvili" <id***********@ hotmail.com> wrote in message
news:13******** *************** ***@posting.goo gle.com...

I'm hoping someone can help me here.

I'm having problems using a function to get the search criteria for a
column in the QBE grid. The column's data type is Long and the
problem is that if I want to search on more than one value my function
in the QBE correctly returns "1 Or 4" (for example), but something's
getting garbled and I'm not getting the same data back if I just enter
"1 Or 4" (w/o quotes) in the QBE grid.

My guess is that my function is returning a string and so that's
what's used in the QBE. I tried returning a Variant and I tried a
bunch of other stuff --- but nothing's worked!

Any suggestions would be greatly appreciated.

-id

[i. dzhugashvili]

Hi Larry,

Thanks for your suggestion.

If I enter "In(1,4)" (w/o quotes) in the QBE grid it works fine of
course. If I try to return this value in the function used in the
grid I get one of two errors:

#1. If I return "In(1,4)" (with quotes) in my function as a String
or Variant, my query returns nothing.
#2. If I attempt to return "In(1,4)" (w/o quotes) I get a compile
error on my function because it doesn't know what to do with the In()
function. I tried specifying different return types for the function
(I was hopeful that Variant would help me out) but no luck.

Any other ideas?

thanks
-Jay
"Larry Linson" <la**********@n tpcug.org> wrote in message news:<6V******* **********@nwrd dc02.gnilink.ne t>...

Try using "In(1,4)" as the criteria, without the quotes, of course.

Larry Linson
Microsoft Access MVP

"i. dzhugashvili" <id***********@ hotmail.com> wrote in message
news:13******** *************** ***@posting.goo gle.com...

I'm hoping someone can help me here.

I'm having problems using a function to get the search criteria for a
column in the QBE grid. The column's data type is Long and the
problem is that if I want to search on more than one value my function
in the QBE correctly returns "1 Or 4" (for example), but something's
getting garbled and I'm not getting the same data back if I just enter
"1 Or 4" (w/o quotes) in the QBE grid.

My guess is that my function is returning a string and so that's
what's used in the QBE. I tried returning a Variant and I tried a
bunch of other stuff --- but nothing's worked!

Any suggestions would be greatly appreciated.

-id

[Pieter Linden]

Create a dummy query, and modify the query SQL as necessary. Then
save the query and finally run it.

Function MySQLStatement( Arg1, Arg2,Arg3...) <--- use an array?
dim strSQL as string
dim intCounter as integer

for intCounter = 1 to ubound(paramArr ay)
strSQL = "SELECT T1.F1, T1.F2... FROM T1 WHERE " &
paramArray(intc ounter) & " AND "
next intCounter

strSQL = LEft$(strSQL,le n$(strSQL)-5 'removes the ' AND '

strsql = strsql & ";"

End function

assuming that big mess works, you can then do something like modify
the SQL of a stored holder query.

dim qdf as querydef
set qdf=currentdb.q uerydefs("qryHo lder")
qdf.SQL = MySQLStatement( ...)
qdf.Save

then you could do something like
Docmd.openquery "qryHolder"

[i. dzhugashvili]

Thanks Larry, I'll certainly give it a try.

-id
"Larry Linson" <la**********@n tpcug.org> wrote in message news:<AH******* **********@nwrd dc01.gnilink.ne t>...

Yes. You can create the string containing the SQL (that's what SQL is, a
string of text to be interpreted by the database engine) and store it in a
public variable or in a field on a form ahead of time and pick it up in the
Report's Open event and replace the RecordSource. (The Report's Open event
is the only event that allows you to replace the Record Source.)

Larry Linson
Microsoft Access MVP
"i. dzhugashvili" <id***********@ hotmail.com> wrote in message
news:13******** *************** ***@posting.goo gle.com...

That's a good suggestion Pieter and I would have tried it except I
ended up modifying my function a little bit to return a Where criteria
which I applied to a DoCmd.OpenRepor t call. It seems to be working.
I usually don't use queries in Access except I wanted to use it as the
data source for a report. The report designer in Access is great. Do
you know if I can use your technique to create a query that can be
used as the data source for a report? How would bind the columns in
the query with fields in the report?
thanks again
-id


pi********@hotm ail.com (Pieter Linden) wrote in message

news:<bf******* *************** ****@posting.go ogle.com>...

Create a dummy query, and modify the query SQL as necessary. Then
save the query and finally run it.

Function MySQLStatement( Arg1, Arg2,Arg3...) <--- use an array?
dim strSQL as string
dim intCounter as integer

for intCounter = 1 to ubound(paramArr ay)
strSQL = "SELECT T1.F1, T1.F2... FROM T1 WHERE " &
paramArray(intc ounter) & " AND "
next intCounter

strSQL = LEft$(strSQL,le n$(strSQL)-5 'removes the ' AND '

strsql = strsql & ";"

End function

assuming that big mess works, you can then do something like modify
the SQL of a stored holder query.

dim qdf as querydef
set qdf=currentdb.q uerydefs("qryHo lder")
qdf.SQL = MySQLStatement( ...)
qdf.Save

then you could do something like
Docmd.openquery "qryHolder"