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

if statement with null criteria

P: 4
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!!!
Feb 28 '08 #1
Share this Question
Share on Google+
6 Replies


Expert Mod 2.5K+
P: 2,545
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
Expand|Select|Wrap|Line Numbers
  1. Select [firstfield], [secondfield], ... , [lastfield] from [name of table] where (condition);
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
Feb 28 '08 #2

P: 4
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
Expand|Select|Wrap|Line Numbers
  1. Select [firstfield], [secondfield], ... , [lastfield] from [name of table] where (condition);
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
Feb 28 '08 #3

Expert Mod 2.5K+
P: 2,545
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
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. like switch([FormA]![field1] = "1" , "A1", [FormA]![field1] = "2", "B2", [FormA]![field1] = "3", "C3". [FormA]![field1] = "ALL", "*" )
-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.
...
Feb 28 '08 #4

P: 4
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
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. like switch([FormA]![field1] = "1" , "A1", [FormA]![field1] = "2", "B2", [FormA]![field1] = "3", "C3". [FormA]![field1] = "ALL", "*" )
-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
Feb 29 '08 #5

NeoPa
Expert Mod 15k+
P: 31,768
Subscribing .
Feb 29 '08 #6

NeoPa
Expert Mod 15k+
P: 31,768
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.
Feb 29 '08 #7

Post your reply

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