473,405 Members | 2,338 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,405 software developers and data experts.

Function in QBE grid not returning correct data(?)

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
Nov 12 '05 #1
4 3359
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.google.c om...
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

Nov 12 '05 #2
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**********@ntpcug.org> wrote in message news:<6V*****************@nwrddc02.gnilink.net>...
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.google.c om...
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

Nov 12 '05 #3
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(paramArray)
strSQL = "SELECT T1.F1, T1.F2... FROM T1 WHERE " &
paramArray(intcounter) & " AND "
next intCounter

strSQL = LEft$(strSQL,len$(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.querydefs("qryHolder")
qdf.SQL = MySQLStatement(...)
qdf.Save

then you could do something like
Docmd.openquery "qryHolder"
Nov 12 '05 #4
Thanks Larry, I'll certainly give it a try.

-id
"Larry Linson" <la**********@ntpcug.org> wrote in message news:<AH*****************@nwrddc01.gnilink.net>...
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.google.c om...
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.OpenReport 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********@hotmail.com (Pieter Linden) wrote in message

news:<bf**************************@posting.google. 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(paramArray)
strSQL = "SELECT T1.F1, T1.F2... FROM T1 WHERE " &
paramArray(intcounter) & " AND "
next intCounter

strSQL = LEft$(strSQL,len$(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.querydefs("qryHolder")
qdf.SQL = MySQLStatement(...)
qdf.Save

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

Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Snake | last post by:
I have a vb .net program which fills a data grid upon form load from an acccess database. This works great. Now, I have to add a combo box and use it to alter the underlying sql statement and...
1
by: john | last post by:
Relatively new to C coding, so any help would greatly be appreciated. I'm having problems try to return my string array from my parsing function. When I do a printf I am getting the correct value...
3
by: Martin Montgomery | last post by:
I have, for example, a property called myProperty. I would like, when using a property grid to display the property name as "My Property". Is this possible. Is there an attribute etc Thank ...
0
by: VM | last post by:
If I wanted to load an ascii file (which contains 400,000+ lines and each line is a table row) to a datatable and then display it to a datagrid, what would the best approach be? I initially had a...
5
by: IGotYourDotNet | last post by:
I'm pulling data out of an oracle db and populating a datagrid such as this cars doors Cylinders chevy 4 6 ford 2 8 how can i make it such as cars ...
117
by: phil-news-nospam | last post by:
Is there really any advantage to using DIV elements with float style properies, vs. the old method of TABLE and TR and TD? I'm finding that by using DIV, it still involves the same number of...
17
by: I.M. !Knuth | last post by:
Hi. I'm more-or-less a C newbie. I thought I had pointers under control until I started goofing around with this: ...
0
by: MikeCS | last post by:
Hi all I would like some help with this issue. I am new to VB 2005 (OK with VB6) My problem is that I cannot seem to return a structure from a function. Example: I defined a structure in a...
12
by: Googy | last post by:
Hi!! Can any one explain me the meaning of following notations clearly : 1. typedef char(*(*frpapfrc()))(); frpapfrc f; 2. typedef int (*(arr2d_ptr)()); arr2d_ptr p; 3. typedef int...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.