Operator as variable.

[Guest]

In a query field I want to use syntax IIF x > y, however I want < as a
variable since I want to ask is x>y or is x<y depending on other things.
Storing < or > in a variable would save a lot of code, However I get invalid
syntax if I just use iif [x] [op] [y] where [op] contains the operator - any
ideas?

example: [T_OOH_MID_SIGN] has either < or > in it

IIf(DateDiff('n',Right([E_OPEN_DT],8),[T_OOH_MID_TIME]) [T_OOH_MID_SIGN]
0,1,2)

 

[Allen Browne]

You will need to create the query statement as a string variable in VBA,
concatenating the operator into the string.

You can assign the string to the SQL property of your query if you need to.
Example:
Dim strSql As String
strSql = "SELECT * FROM Table1 WHERE [Field1] " & _
Me.[T_OOH_MID_SIGN] & " 99;"
dbEngine(0)(0).QueryDefs("Query1").SQL = strSql

 

[Jeff Boyce]

Paul

You've described "how" you are trying to do something (embed an operator
inside a variable, then use it as part of an IIF() expression.

Now, how 'bout explaining "what" you are trying to accomplish -- there may
be alternate ways to do what really needs to happen. I'm guessing the
approach you are now using isn't, in and of itself, your end goal...

 

[Guest]

I am trying to workout SLAs. I have 2 tables, the 1st has the record info and
the second the SLA lookup, i.e. for record AAA I need to check if 09:30 is <
12:30 and for record CCC I need to check if 10:30 is after 15:30

KEY Time Function

AAA 09:30 E1
BBB 12:45 E1
CCC 10:30 E2

FUNCTION TABLE

KEY VARIABLE
E1 < 12:30
E2 > 12:30
E3 < 15:30

Hence for record AAA I need to

Paul

You've described "how" you are trying to do something (embed an operator
inside a variable, then use it as part of an IIF() expression.

Now, how 'bout explaining "what" you are trying to accomplish -- there may
be alternate ways to do what really needs to happen. I'm guessing the
approach you are now using isn't, in and of itself, your end goal...

--
Regards

Jeff Boyce
<Office/Access MVP>

In a query field I want to use syntax IIF x > y, however I want < as a
variable since I want to ask is x>y or is x<y depending on other things.
Storing < or > in a variable would save a lot of code, However I get invalid
syntax if I just use iif [x] [op] [y] where [op] contains the operator - any
ideas?

example: [T_OOH_MID_SIGN] has either < or > in it

IIf(DateDiff('n',Right([E_OPEN_DT],8),[T_OOH_MID_TIME]) [T_OOH_MID_SIGN]
0,1,2)

 

[Jeff Boyce]

Thanks, that makes it clearer. I'd probably go with something like what
Allen posted, dynamically creating the SQL string.

--
Regards

Jeff Boyce
<Office/Access MVP>

I am trying to workout SLAs. I have 2 tables, the 1st has the record info and
the second the SLA lookup, i.e. for record AAA I need to check if 09:30 is <
12:30 and for record CCC I need to check if 10:30 is after 15:30

KEY Time Function

AAA 09:30 E1
BBB 12:45 E1
CCC 10:30 E2

FUNCTION TABLE

KEY VARIABLE
E1 < 12:30
E2 > 12:30
E3 < 15:30

Hence for record AAA I need to

Paul

You've described "how" you are trying to do something (embed an operator
inside a variable, then use it as part of an IIF() expression.

Now, how 'bout explaining "what" you are trying to accomplish -- there may
be alternate ways to do what really needs to happen. I'm guessing the
approach you are now using isn't, in and of itself, your end goal...

--
Regards

Jeff Boyce
<Office/Access MVP>

In a query field I want to use syntax IIF x > y, however I want < as a
variable since I want to ask is x>y or is x<y depending on other things.
Storing < or > in a variable would save a lot of code, However I get invalid
syntax if I just use iif [x] [op] [y] where [op] contains the

operator -
any

ideas?

example: [T_OOH_MID_SIGN] has either < or > in it

IIf(DateDiff('n',Right([E_OPEN_DT],8),[T_OOH_MID_TIME]) [T_OOH_MID_SIGN]
0,1,2)

 

[Michel Walsh]

Hi,


Since you need and > and < at the same time, I mean, in the same statement,
something like:


.... WHERE iif( function="E1", x< y, x> y)

or


.... WHERE switch( function="e1", x < y, function="e2", x> y, ..., .... )


if you have more than just 2 'cases' .



Alternatively,


.... WHERE myPublicVBAFunction( x, y, "functionCode")



with your VBA function, in a standard module (not under a form, class, or
report) returning a Boolean accordingly to the supplied arguments.



Hoping it may help,
Vanderghast, Access MVP

I am trying to workout SLAs. I have 2 tables, the 1st has the record info
and
the second the SLA lookup, i.e. for record AAA I need to check if 09:30 is
<
12:30 and for record CCC I need to check if 10:30 is after 15:30

KEY Time Function

AAA 09:30 E1
BBB 12:45 E1
CCC 10:30 E2

FUNCTION TABLE

KEY VARIABLE
E1 < 12:30
E2 > 12:30
E3 < 15:30

Hence for record AAA I need to

Paul

You've described "how" you are trying to do something (embed an operator
inside a variable, then use it as part of an IIF() expression.

Now, how 'bout explaining "what" you are trying to accomplish -- there
may
be alternate ways to do what really needs to happen. I'm guessing the
approach you are now using isn't, in and of itself, your end goal...

--
Regards

Jeff Boyce
<Office/Access MVP>

In a query field I want to use syntax IIF x > y, however I want < as a
variable since I want to ask is x>y or is x<y depending on other
things.
Storing < or > in a variable would save a lot of code, However I get invalid
syntax if I just use iif [x] [op] [y] where [op] contains the
operator - any
ideas?

example: [T_OOH_MID_SIGN] has either < or > in it

IIf(DateDiff('n',Right([E_OPEN_DT],8),[T_OOH_MID_TIME])
[T_OOH_MID_SIGN]
0,1,2)

 

[Marshall Barton]

I am trying to workout SLAs. I have 2 tables, the 1st has the record info and
the second the SLA lookup, i.e. for record AAA I need to check if 09:30 is <
12:30 and for record CCC I need to check if 10:30 is after 15:30

KEY Time Function

AAA 09:30 E1
BBB 12:45 E1
CCC 10:30 E2

FUNCTION TABLE

KEY VARIABLE
E1 < 12:30
E2 > 12:30
E3 < 15:30

I think there are a couple of alternative ways to approach
this. Without evaluating things carefully, I think I would
change the function table to have three columns:

Functions Table:
Function Before After
E1 12:30
E2 12:30
E3 15:30

Then use a query like:

SELECT T.KEY, T.[Time] , T.Function
FROM yourtable As T INNER JOIN Functions As F
ON T.Function = F.Key
WHERE IIf(F.After Is Null, T.[Time] < F.Before, T.[Time] >
F.After)

If you don't like the idea of changing the Functions table,
you could try:

SELECT T.KEY, T.[Time] , T.Function
FROM yourtable As T INNER JOIN Functions As F
ON T.Function = F.Function
WHERE Eval("#" & T.[Time] & "#" & Replace(F.VARIABLE, " ",
"#") & "#")

But this latter one requires a space between the < and the
time in the Variable field. And It's probably a whole lot
slower than the other way.