if statement with null criteria

[talllight]

hey,

i am trying to create a query to get result which linked to a value from a form.

the following is an example of a database...

[FieldA]
A1
A1
B2
C3

in the criteria box of Field A,
i have
switch( [FormA]![field1] = "1" , "A1", [FormA]![field1] = "2", "B2", [FormA]![field1] = "3", "C3". [FormA]![field1] = "ALL", ***** )

i have a question. i am not sure what i should put after [FormA]![field1] = "ALL", if i want to get all records from the database.

Would anyone please let me?

Thank you!!!

[Stewart Ross]

hey, i am trying to create a query to get result which linked to a value from a form. the following is an example of a database...

[FieldA]
A1
A1
B2
C3

in the criteria box of Field A,
i have
switch( [FormA]![field1] = "1" , "A1", [FormA]![field1] = "2", "B2", [FormA]![field1] = "3", "C3". [FormA]![field1] = "ALL", ***** )

i have a question. i am not sure what i should put after [FormA]![field1] = "ALL", if i want to get all records from the database. ...

Hi Taillight. I doubt that this forum can help you when it seems that you have not yet got to grips with Access itself or database design in general.

If you were selecting fields from an Access table, it would not be a Switch statement that would be used. Instead you would have an SQL query of the form
[CODE=sql]Select [firstfield], [secondfield], ... , [lastfield] from [name of table] where (condition);[/CODE]
Within your switch statement you are trying things that are not going to work (such as [FormA]![field1] = "ALL") and that suggest your database/computing background is very, very limited.

I would recommend that you check out an introductory text in database design and try out a number of exercises in building databases to learn the basics first.

Regards

Stewart

[talllight]

Hi Taillight. I doubt that this forum can help you when it seems that you have not yet got to grips with Access itself or database design in general.

If you were selecting fields from an Access table, it would not be a Switch statement that would be used. Instead you would have an SQL query of the form
[CODE=sql]Select [firstfield], [secondfield], ... , [lastfield] from [name of table] where (condition);[/CODE]
Within your switch statement you are trying things that are not going to work (such as [FormA]![field1] = "ALL") and that suggest your database/computing background is very, very limited.

I would recommend that you check out an introductory text in database design and try out a number of exercises in building databases to learn the basics first.

Regards

Stewart

Hey Stewart,

Thank you for replying.
However, i guess you have misunderstood my question.

i have a dropbox in a form.
in the dropbox, i have, as the example, "1" or "2" or "3" or "ALL".
while i select "ALL" in the drop box and run the query, the result should include all the values from the table.

Let me know if you still do not understand what i want.

Thank you once again.
talllight

[Stewart Ross]

Hi Taillight. I see now what you want to do. I stand by the concerns I raised in my response, but I've said enough on those already... To answer your question, if you want to match all string entries in a particular field the criterion needed is

Like "*"

but this cannot be returned directly by the switch statement. You can precede your switch statement by Like and change the final asterisks to "*" to achieve the same effect:

like switch([FormA]![field1] = "1" , "A1", [FormA]![field1] = "2", "B2", [FormA]![field1] = "3", "C3". [b][FormA]![field1] = "ALL"[/b], "*" )

-Stewart

...However, i guess you have misunderstood my question.

i have a dropbox in a form.
in the dropbox, i have, as the example, "1" or "2" or "3" or "ALL".
while i select "ALL" in the drop box and run the query, the result should include all the values from the table.
...

[talllight]

Hi Taillight. I see now what you want to do. I stand by the concerns I raised in my response, but I've said enough on those already... To answer your question, if you want to match all string entries in a particular field the criterion needed is

Like "*"

but this cannot be returned directly by the switch statement. You can precede your switch statement by Like and change the final asterisks to "*" to achieve the same effect:

like switch([FormA]![field1] = "1" , "A1", [FormA]![field1] = "2", "B2", [FormA]![field1] = "3", "C3". [b][FormA]![field1] = "ALL"[/b], "*" )

-Stewart

Thank you for helping me... Stewart~! it works...
however, i have a further question... instead of using like "*", can i use another way to do it??
because if i use "like", it takes a very long time to run.... especially for a very huge table... ( i have a table for more than 7 billions records)

Thank you,
talllight

[NeoPa]

Subscribing .

[NeoPa]

For 7 billion records, it's worth being very clear about exactly what you have to work with. Most answers are general, but for extremes it's often better to work out a precise solution.

With large datasets the criteria can work better sometimes if put into a special separate table.