Why i can't filter this Report !!!!!

[MrCC via AccessMonster.com]

Dear all

each time i run the following code :



Cond = "OwnerID =" & OwnID & " and " & "ID =" & TransID

DoCmd.OpenReport stDocName, acPreview, , Cond

it returns all the table records !!!!

it should return one record only

i am so sure that OwnID , TransID has a valid value a
and so sure that report table has the field name OwnerID and ID

any tips plz

thanks

bye

 

[Douglas J. Steele]

Are both OwnerID and ID numeric fields? If not, you'll need to put quotes
around one (or both) values. For instance, if OwnerID is text, use:

Cond = "OwnerID ='" & OwnID & "' and ID =" & TransID

Exagerated for clarity, that's

Cond = "OwnerID = ' " & OwnID & " ' and ID =" & TransID

If that still doesn't work, what exactly is in Cond?

Either put

Cond = "OwnerID =" & OwnID & " and " & "ID =" & TransID

Msgbox Cond
DoCmd.OpenReport stDocName, acPreview, , Cond

or

Cond = "OwnerID =" & OwnID & " and " & "ID =" & TransID

Debug.Print Cond
DoCmd.OpenReport stDocName, acPreview, , Cond

If you go the latter route, go to the Immediate Window (using Ctrl-G) and
see what's printed.

 

[derek]

Dear all

each time i run the following code :

Cond = "OwnerID =" & OwnID & " and " & "ID =" & TransID

DoCmd.OpenReport stDocName, acPreview, , Cond

it returns all the table records !!!!

it should return one record only

i am so sure that OwnID , TransID has a valid value a
and so sure that report table has the field name OwnerID and ID

any tips plz

thanks

bye

I agree with Douglas depending on the type of the data you are testing
you need to put quotes around it
srtings need a ' this is a string'
number usually not id= " & 1234
dates need #02/01/08#

in use you need

id = ' " & [field name] & " ' "
or
totalWidgets = " & [totWig]

or

duedate <= #" & date(now()) & "#"

Your question seems to imply that you are up against the dreaded Null

also do a Google on "msaccess and empty"

Ms access does weird things with the empty null and blank.

excerpt"
############################################################


If you want to check for a Null value in an SQL statement, use
WHERE fieldname IS NULL

Sometimes what may seem like empty strings, there may be spaces. So to
check for empty strings, strip off the spaces first using Trim().

WHERE Trim([fieldname]) = ''
anchor
posted 2005-Dec-18, 1pm AEST
User #20965 2175 posts
Bunce
Whirlpool Forums Addict
òoÍËa writes...
i thought so too - albeit it doesn't work

Well, what exactly are you trying to find?

If you want to find a space then search for " "
If you want to find an empty string, search for ""
If you want to search for NULL, then searcf or IS NULL. "

 

[Evi]

Dear all

each time i run the following code :

Cond = "OwnerID =" & OwnID & " and " & "ID =" & TransID

DoCmd.OpenReport stDocName, acPreview, , Cond

it returns all the table records !!!!

it should return one record only

i am so sure that OwnID , TransID has a valid value a
and so sure that report table has the field name OwnerID and ID

any tips plz

thanks

bye

Assuming TransID and OwnID are numbers:
From where are you getting the values OwnID and TransID?
Are they controls in a form?
If Yes, I would expect to see
Dim OwnID as Long

OwnID = Me.TheNameOfYourControl

If OwnID and TransID are the names of controls in your form then the
condition would read

Cond="[OwnerID]=" & Me.[OwnID] & " AND [ID]=" & Me.TransID

I sometimes find it helpful to check my code with a MsgBox so under the
above put
MsgBox Cond
And see what appears

Evi