By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 1,689 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

Acces 2007 and expression builder

P: 11
Hello,

I have a problem with IF(IIf) statement in query expression builder(criteria field).
It should be like :
If [Forms]![frmReportManager]![cmbAgentName] = "" then criteria field should be filled [tblAgent]![AgentName] else criteria field should be filled [Forms]![frmReportManager]![cmbAgentName].

I have googled almost 2 days for now and I cant sort it out. Maybe its possible to do it in VB?

Many thanks for your help,
Artur
Feb 1 '09 #1
Share this Question
Share on Google+
23 Replies


DonRayner
Expert 100+
P: 489
Did you try it like this?
Expand|Select|Wrap|Line Numbers
  1. = IIf ([Forms]![frmReportManager]![cmbAgentName] = "",[tblAgent]![AgentName],[Forms]![frmReportManager]![cmbAgentName])
Feb 2 '09 #2

P: 11
I cant enter this, this displays error message: The expression you enterned contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation marks.
After clicking OK, the comma is selecter from criteria field?
What should I do dirrerently?

Thanks,
Artur
Feb 2 '09 #3

Expert 100+
P: 1,287
What is [tblAgent]![AgentName] supposed to be?
Feb 2 '09 #4

P: 11
@ChipR
[tblAgent]![AgentName] is a row named [AgentName] in [tblAgent] table.
If I put only [tblAgent]![AgentName] to criteria field, then it works like it should work if this [Forms]![frmReportManager]![cmbAgentName] = "". eg field is empty.

Thanks
Feb 2 '09 #5

DonRayner
Expert 100+
P: 489
@artur223
Try this then
Expand|Select|Wrap|Line Numbers
  1. = IIf (IsNull([Forms]![frmReportManager]![cmbAgentName]),[tblAgent]![AgentName],[Forms]![frmReportManager]![cmbAgentName]) 
Feb 2 '09 #6

P: 11
@DonRayner
Still the same error :(
Feb 2 '09 #7

FishVal
Expert 2.5K+
P: 2,653
This doesn't look like criteria. It looks pretty like calculated field.
Feb 2 '09 #8

DonRayner
Expert 100+
P: 489
Try this;

In a new module, place the following code
Expand|Select|Wrap|Line Numbers
  1. Public Function FormCriteria() as string
  2. If IsNull(forms!frmReportManager!cmbAgentName) Then
  3. FormCriteria = "*"
  4. Else
  5. FormCriteria = forms!frmReportManager!cmbAgentName
  6. End If
  7. End Function
Then use this as the criteria in your query

Expand|Select|Wrap|Line Numbers
  1. Like FormCriteria()
Feb 2 '09 #9

P: 11
@DonRayner



Are you trying to use "*" as a wildcard? AFAIK VB does not support wildcards.

Thanks
Feb 2 '09 #10

DonRayner
Expert 100+
P: 489
What is the value in your listbox when you get that error? place a messagebox command as the first line in the function to see what value you are passing to it.

Expand|Select|Wrap|Line Numbers
  1. msgbox forms!frmReportManager!cmbAgentName
On line 2 you could try changing it to

Expand|Select|Wrap|Line Numbers
  1. If IsNull(forms!frmReportManager!cmbAgentName) or forms!frmReportManager!cmbAgentName = "" Then
The function is developing a string statement to pass into the criteria of the query, the "*" part is just making the criteria statement to look like

Like *
Feb 2 '09 #11

P: 11
@DonRayner
Run time error '94'
Invalid use of null

@DonRayner
"You entered an expression that has no value"

If I add Forms!frmReportManager!cmbAgentName = "" to form load then I'm back at "You entered an expression that has no value"


Thanks
Feb 4 '09 #12

DonRayner
Expert 100+
P: 489
I think that we are just missing passing the quotes along with the string. Try changing the function to this

Expand|Select|Wrap|Line Numbers
  1. Public Function FormCriteria() as string 
  2. If IsNull(forms!frmReportManager!cmbAgentName) Then 
  3.     FormCriteria = "Like ""*""" 
  4. Else 
  5.     FormCriteria = "Like " & "" & forms!frmReportManager!cmbAgentName & ""
  6. End If 
  7. End Function
Feb 4 '09 #13

Expert 100+
P: 1,287
I think you mean:
FormCriteria = "Like """ & forms!frmReportManager!cmbAgentName & """"
Feb 4 '09 #14

P: 11
DonRayner, ChipR, sitll same "You entered an expression that has no value". :(

Thank you
Feb 4 '09 #15

Expert 100+
P: 1,287
This indicates that cmbAgentName has no value. Which means, if it is a combo box, it has no values in it's value list, or a null source data set. Is that possible?
Feb 4 '09 #16

DonRayner
Expert 100+
P: 489
@artur223
put this just before the end function and post back what the message box says.

Expand|Select|Wrap|Line Numbers
  1. MsgBox FormCriteria
Feb 4 '09 #17

P: 11
cmbAgentName gets its Row Source from "SELECT [tblAgent].[AgentID], [tblAgent].[AgentName] FROM tblAgent;" and Row Source Type is "Table/Query". But it has 2 records at the moment. But yes, by default it does not have a value. I tried to change Default value to 1 but same value error appears. If I remove this module then it shows Value 1 - first record from tblAgent.
Feb 4 '09 #18

P: 11
DonRayner , blank message box
Feb 4 '09 #19

DonRayner
Expert 100+
P: 489
@artur223
Please post back a copy of the code for your function as it's written now. As long as FormCriteria is spelled the same everywhere you should be seeing something in that message box.
Feb 4 '09 #20

P: 11

DonRayner
Expert 100+
P: 489
Is cmbAgentName a numeric field? If so that's the source of your problem. The code would have to be like this

Expand|Select|Wrap|Line Numbers
  1. Public Function FormCriteria() as string  
  2. If IsNull(forms!frmReportManager!cmbAgentName) Then  
  3.     FormCriteria = "#" 
  4. Else  
  5.     FormCriteria = forms!frmReportManager!cmbAgentName 
  6. End If  
  7. End Function 
Feb 5 '09 #22

P: 11
No, its not a numeric field. What is the VB code for changing text in the criteria field?

Thanks
Feb 6 '09 #23

100+
P: 675
I would go back to the original query design, and break the problem into little steps, hoping one fails so I could determine what is really wrong.
1) add some more fields and assign values. In blank columns, I would have X: [Forms]![frmReportManager]![cmbAgentName]. In another column: Y: [tblAgent]![AgentName] . Finally Z: [Forms]![frmReportManager]![cmbAgentName]. Run query and examine results in DataSheet View. This determines that the query is seeing what you think it should. If OK, then
2) add some more fields, gradually building to desired result, such as XX: IIf( [Forms]![frmReportManager]![cmbAgentName]="", -1,0)
3) YY: IIf(XX,Y,"False") and ZZ: IIf(XX,Z, "True")
4) XXX: IIf(XX,Y,Z)

I suspect that you will have to build the SQL statement in VBA, using the values in the textboxes, rather than pass the textbox controls to the query.

OldBirdman
Feb 6 '09 #24

Post your reply

Sign in to post your reply or Sign up for a free account.