why won't switch function won't work

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following in the criteria field in a query of mine to filter
records from a table:
Switch([Forms]![frmMapEx]![Combo11]=">=",>10000,[Forms]![frmMapEx]![Combo11]="<=",>100000,True,>0).

Why doesn't it work?

Thanks!
 
It doesn't work because you can't express conditions like that.

Try:
Switch([Forms]![frmMapEx]![Combo11]=">=",10000,[Forms]![frmMapEx]![Combo11]="<=",100000,True,0).
Click to expand...


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Pete M said:
I have the following in the criteria field in a query of mine to filter
records from a table:
Switch([Forms]![frmMapEx]![Combo11]=">=",>10000,[Forms]![frmMapEx]![Combo11]="<=",>100000,True,>0).

Why doesn't it work?

Thanks!
Click to expand...
 
Makes sense, but I'll reframe my question:

I am trying to the results of filter a query with a set of controls--combo
boxes and text boxes. The values in the combo boxes are ">=", and "<=", and
user inputs a number into the text boxes. My issue is that i'm having
trouble building an expression (in the criteria field of design view of my
query) which uses the values of my combo boxes (">=", and "<="). I've tried:

[Forms]![frmMapEx]![Combo11] & [Forms]![frmMapEx]![Text1]

where Combo11 = ">=", and Text1 = 0,

but i get an error?? What the right way to do this?

Thanks!

Pete
Douglas J. Steele said:
It doesn't work because you can't express conditions like that.

Try:
Switch([Forms]![frmMapEx]![Combo11]=">=",10000,[Forms]![frmMapEx]![Combo11]="<=",100000,True,0).
Click to expand...


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Pete M said:
I have the following in the criteria field in a query of mine to filter
records from a table:
Switch([Forms]![frmMapEx]![Combo11]=">=",>10000,[Forms]![frmMapEx]![Combo11]="<=",>100000,True,>0).

Why doesn't it work?

Thanks!
Click to expand...
Click to expand...
 
What's the error, and when do you get it?

How are you accessing the design view of the query? If it's from the
database window, then it is probably because MS Access does not
recognize what frmMapex is - the form is only in the [forms] collection
if the form is open.

John


Pete said:
Makes sense, but I'll reframe my question:

I am trying to the results of filter a query with a set of controls--combo
boxes and text boxes. The values in the combo boxes are ">=", and "<=", and
user inputs a number into the text boxes. My issue is that i'm having
trouble building an expression (in the criteria field of design view of my
query) which uses the values of my combo boxes (">=", and "<="). I've tried:

[Forms]![frmMapEx]![Combo11] & [Forms]![frmMapEx]![Text1]

where Combo11 = ">=", and Text1 = 0,

but i get an error?? What the right way to do this?

Thanks!

Pete
:

It doesn't work because you can't express conditions like that.

Try:

Switch([Forms]![frmMapEx]![Combo11]=">=",10000,[Forms]![frmMapEx]![Combo11]="<=",100000,True,0).
Click to expand...


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have the following in the criteria field in a query of mine to filter
records from a table:
Switch([Forms]![frmMapEx]![Combo11]=">=",>10000,[Forms]![frmMapEx]![Combo11]="<=",>100000,True,>0).

Why doesn't it work?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
My point is you cannot use the value in a control or function to control the
boolean operator being used (just as you can't change what tables or fields
your query uses by using a control or function to represent them)

I'm assuming that you're trying to put this into the Criteria grid in the
graphical query builder. Switch to the SQL View (under the View menu) and
you'll see why I say that: the equal sign is put in by default. The only way
to avoid having it preset for you is to put an actual operator in there
instead.

If you're trying to change the operator, the only way is to build the SQL
yourself in code. Take a look at the Query By Form example Duane Hookom has
at http://www.access.hookom.net/Samples.htm

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Pete M said:
Makes sense, but I'll reframe my question:

I am trying to the results of filter a query with a set of controls--combo
boxes and text boxes. The values in the combo boxes are ">=", and "<=",
and
user inputs a number into the text boxes. My issue is that i'm having
trouble building an expression (in the criteria field of design view of my
query) which uses the values of my combo boxes (">=", and "<="). I've
tried:

[Forms]![frmMapEx]![Combo11] & [Forms]![frmMapEx]![Text1]

where Combo11 = ">=", and Text1 = 0,

but i get an error?? What the right way to do this?

Thanks!

Pete
Douglas J. Steele said:
It doesn't work because you can't express conditions like that.

Try:
Switch([Forms]![frmMapEx]![Combo11]=">=",10000,[Forms]![frmMapEx]![Combo11]="<=",100000,True,0).
Click to expand...


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Pete M said:
I have the following in the criteria field in a query of mine to filter
records from a table:
Switch([Forms]![frmMapEx]![Combo11]=">=",>10000,[Forms]![frmMapEx]![Combo11]="<=",>100000,True,>0).

Why doesn't it work?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Thanks. took your advice and used code to make it work.

Douglas J. Steele said:
My point is you cannot use the value in a control or function to control the
boolean operator being used (just as you can't change what tables or fields
your query uses by using a control or function to represent them)

I'm assuming that you're trying to put this into the Criteria grid in the
graphical query builder. Switch to the SQL View (under the View menu) and
you'll see why I say that: the equal sign is put in by default. The only way
to avoid having it preset for you is to put an actual operator in there
instead.

If you're trying to change the operator, the only way is to build the SQL
yourself in code. Take a look at the Query By Form example Duane Hookom has
at http://www.access.hookom.net/Samples.htm

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Pete M said:
Makes sense, but I'll reframe my question:

I am trying to the results of filter a query with a set of controls--combo
boxes and text boxes. The values in the combo boxes are ">=", and "<=",
and
user inputs a number into the text boxes. My issue is that i'm having
trouble building an expression (in the criteria field of design view of my
query) which uses the values of my combo boxes (">=", and "<="). I've
tried:

[Forms]![frmMapEx]![Combo11] & [Forms]![frmMapEx]![Text1]

where Combo11 = ">=", and Text1 = 0,

but i get an error?? What the right way to do this?

Thanks!

Pete
Douglas J. Steele said:
It doesn't work because you can't express conditions like that.

Try:

Switch([Forms]![frmMapEx]![Combo11]=">=",10000,[Forms]![frmMapEx]![Combo11]="<=",100000,True,0).


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have the following in the criteria field in a query of mine to filter
records from a table:
Switch([Forms]![frmMapEx]![Combo11]=">=",>10000,[Forms]![frmMapEx]![Combo11]="<=",>100000,True,>0).

Why doesn't it work?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top