Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-12-2019, 12:13 AM   #1
exaccess
Newly Registered User
 
Join Date: Apr 2013
Posts: 287
Thanks: 50
Thanked 1 Time in 1 Post
exaccess is on a distinguished road
Where clause does not work

Hi Experts,
My code does not work. It does not give any error message but simply ignores the WHERE parameter.
MembersTbl is an access table. Nom is a field of the table. ReportName is he name of a produced report. F01 is a string variable. V01 is also a sting variable. The WHERE clause is produced by a program which automates SQL production.

Code:
MembersTbl.NOM Like 'b*'
Code:
DoCmd.OpenReport [ReportName], acViewPreview, "F01 = #" & V01 & "#"
Help is highly appreciated.

exaccess is offline   Reply With Quote
Old 07-12-2019, 12:20 AM   #2
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,332
Thanks: 432
Thanked 790 Times in 766 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Where clause does not work

Strings are generally surrounded by single quotes '
Date are surrounded by hashes #
Numerics are not surrounded by anything
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 07-12-2019, 12:24 AM   #3
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,432
Thanks: 165
Thanked 1,736 Times in 1,706 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Where clause does not work

If both F01 and V01 (a date i assume? )are variables you'll need to concatenate them both - ideally into a string before using them, something like;

Code:
Dim strWhere as String

strWhere = F01 & " = #" & V01 & "#"
Debug.print strWhere
DoCmd.OpenReport [ReportName], acViewPreview, strWhere

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 07-12-2019, 12:26 AM   #4
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Where clause does not work

maybe you mean F01 is a fieldname.

DoCmd.OpenReport ReportName:=[ReportName], View:=acViewPreview, WhereCondition:="F01 = #" & Format(V01, "mm\/dd\/yyyy") & "#"
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 07-12-2019, 12:28 AM   #5
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,332
Thanks: 432
Thanked 790 Times in 766 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Where clause does not work

Minty,

Would that not be

Code:
strWhere = "F01 = #" & V01 & "#"
Quote:
Originally Posted by Minty View Post
If both F01 and V01 (a date i assume? )are variables you'll need to concatenate them both - ideally into a string before using them, something like;

Code:
Dim strWhere as String

strWhere = F01 & " = #" & V01 & "#"
Debug.print strWhere
DoCmd.OpenReport [ReportName], acViewPreview, strWhere
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 07-12-2019, 12:29 AM   #6
exaccess
Newly Registered User
 
Join Date: Apr 2013
Posts: 287
Thanks: 50
Thanked 1 Time in 1 Post
exaccess is on a distinguished road
Re: Where clause does not work

I am very sorry. Just trying to do to two things at the same time. The second code should be:
Code:
DoCmd.OpenReport [ReportName], acViewPreview, MembersTbl.NOM Like 'b*'
This is the code that does not work.
exaccess is offline   Reply With Quote
Old 07-12-2019, 12:35 AM   #7
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,332
Thanks: 432
Thanked 790 Times in 766 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Where clause does not work

Code:
"NOM Like 'b*'"
Do not really see the point of hard coding values though?

I would still construct it as Minty suggested, as then you can Debug.Print the string to see if the syntax is correct.?

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 07-12-2019, 12:47 AM   #8
exaccess
Newly Registered User
 
Join Date: Apr 2013
Posts: 287
Thanks: 50
Thanked 1 Time in 1 Post
exaccess is on a distinguished road
Re: Where clause does not work

Quote:
Originally Posted by Gasman View Post
Code:
"NOM Like 'b*'"
Do not really see the point of hard coding values though?

I would still construct it as Minty suggested, as then you can Debug.Print the string to see if the syntax is correct.?
This is the result of an automated SQL production. It produces
this code based on variables given by the user. The result is used by the system.
exaccess is offline   Reply With Quote
Old 07-12-2019, 12:52 AM   #9
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,332
Thanks: 432
Thanked 790 Times in 766 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Where clause does not work

Quote:
Originally Posted by exaccess View Post
This is the result of an automated SQL production. It produces
this code based on variables given by the user. The result is used by the system.
OK, still good practice to construct a string, especially if the criteria can be more complicated.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 07-12-2019, 05:33 AM   #10
exaccess
Newly Registered User
 
Join Date: Apr 2013
Posts: 287
Thanks: 50
Thanked 1 Time in 1 Post
exaccess is on a distinguished road
Re: Where clause does not work

OK. I think I have not really been able to explain the whole problem. Nevertheless I shall try all your useful suggestions and come back.
exaccess is offline   Reply With Quote
Old 07-12-2019, 05:42 AM   #11
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,220
Thanks: 91
Thanked 2,022 Times in 1,970 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Where clause does not work

exaccess,
I think I have not really been able to explain the whole problem.
Try again -simple, plain terms. Readers shouldn't have to guess what you are trying to do.

If explanations are complex, then show a sample of data in and expected result.
Good luck.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 07-12-2019, 05:55 AM   #12
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,332
Thanks: 432
Thanked 790 Times in 766 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Where clause does not work

Quote:
Originally Posted by exaccess View Post
OK. I think I have not really been able to explain the whole problem. Nevertheless I shall try all your useful suggestions and come back.
Explain how you get it from the SQL process.
You need as shown, a value like "NOM Like 'b*'"
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 07-12-2019, 06:48 AM   #13
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Where clause does not work

your code will not work because on the Docmd.OpenReport syntax, the third argument is a FilterName and the the WhereClause that you have.
The proper output should be:
Code:
DoCmd.OpenReport [ReportName], acViewPreview, , "MembersTbl.NOM Like 'b*'"

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Tags
report , sql , variable parameters , where clause

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL where clause within a SUM clause in the SELECT statement chris-uk-lad General 3 07-21-2008 05:01 AM
SQL where clause within a SUM clause in the SELECT statement chris-uk-lad General 1 07-21-2008 03:37 AM
Please help, simple radio button code wont work and I cant work out why andyj2k1 Forms 6 02-26-2007 04:33 AM
sql in vba doesn't work...resulting string does work in query builder alcifer_c Modules & VBA 3 04-07-2006 08:16 AM
Complex ORDER BY clause doesn't work danb Queries 2 09-23-2003 07:42 AM




All times are GMT -8. The time now is 11:31 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World