Where clause does not work (1 Viewer)

exaccess

Registered User.
Local time
Today, 18:09
Joined
Apr 21, 2013
Messages
287
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:09
Joined
Sep 21, 2011
Messages
14,044
Strings are generally surrounded by single quotes '
Date are surrounded by hashes #
Numerics are not surrounded by anything
 

Minty

AWF VIP
Local time
Today, 17:09
Joined
Jul 26, 2013
Messages
10,355
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:09
Joined
May 7, 2009
Messages
19,169
maybe you mean F01 is a fieldname.

DoCmd.OpenReport ReportName:=[ReportName], View:=acViewPreview, WhereCondition:="F01 = #" & Format(V01, "mm\/dd\/yyyy") & "#"
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:09
Joined
Sep 21, 2011
Messages
14,044
Minty,

Would that not be

Code:
strWhere = "F01 = #" & V01 & "#"

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
 

exaccess

Registered User.
Local time
Today, 18:09
Joined
Apr 21, 2013
Messages
287
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:09
Joined
Sep 21, 2011
Messages
14,044
Code:
"NOM Like 'b*'"

Do not really see the point of hard coding values though?:confused:

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

exaccess

Registered User.
Local time
Today, 18:09
Joined
Apr 21, 2013
Messages
287
Code:
"NOM Like 'b*'"

Do not really see the point of hard coding values though?:confused:

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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:09
Joined
Sep 21, 2011
Messages
14,044
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.?
 

exaccess

Registered User.
Local time
Today, 18:09
Joined
Apr 21, 2013
Messages
287
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.
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:09
Joined
Jan 23, 2006
Messages
15,364
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:09
Joined
Sep 21, 2011
Messages
14,044
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*'"
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:09
Joined
May 7, 2009
Messages
19,169
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, [COLOR="Blue"],[/COLOR] "MembersTbl.NOM Like 'b*'"
 

Users who are viewing this thread

Top Bottom