Wildcards

[Kerry Purdy]

Hi

I have created a query from form, I am able to use the * wildcard if I wish
so all is well so far.

What I would like to do is have the * as the default value in the combo box
so it will show all records unless I choose differently. I have tried
entering an * in the default value of the combo box but it doesn't return any
records, if i type the * manually it works - what am i missing?

Many thanks

Kerry

 

[Klatuu]

Here is an example that adds the work (All) to the combo. It is more
descriptive to the user than just an *
SELECT EmployeeNum FROM tbllkEmployee UNION Select "(All)" as Bogus From
tbllkEmployee UNION Select "(All)" as Bogus From tbllkEmployee;

Now, in your query criteria, you would use something like this:
Like IIf([forms]![zjunk]![combo3]="(All)","*",[forms]![zjunk]![combo3])

 

[Kerry Purdy]

Hiya

Many thanks for your reply, this option sounds great, much more user
friendly. Applying it tho I have got a little stuck.

Do I add the Union info to the SQL view of the query which works as the data
for my combo list of clients? or do I add this to the query which will give
me the client details I am after?

Here is the SQL view of the combo box query
SELECT [tbl Client Data].[C ID], [tbl Client Data].[Client Name], [tbl
Client Data].Division
FROM [tbl Client Data];


Many thanks for your time.

Kerry

Here is an example that adds the work (All) to the combo. It is more
descriptive to the user than just an *
SELECT EmployeeNum FROM tbllkEmployee UNION Select "(All)" as Bogus From
tbllkEmployee UNION Select "(All)" as Bogus From tbllkEmployee;

Now, in your query criteria, you would use something like this:
Like IIf([forms]![zjunk]![combo3]="(All)","*",[forms]![zjunk]![combo3])

Hi

I have created a query from form, I am able to use the * wildcard if I wish
so all is well so far.

What I would like to do is have the * as the default value in the combo box
so it will show all records unless I choose differently. I have tried
entering an * in the default value of the combo box but it doesn't return any
records, if i type the * manually it works - what am i missing?

Many thanks

Kerry

 

[Kerry Purdy]

Hi

Thanks very much for the tips. I am so very nearly almost there..

My combo box has the 3 columns C ID, Client Name & Division
C ID is showing a 0 at the top of the list
Client Name has (All) at the top of the list
Division has (All) at the top of the list

so, all is well with the combo thanks very much.

The query which is now looking at the combo for its data is now misbehaving

Option 1: -
WHERE IIf([c id]=[Forms]![Frm Reports SB]![ClientCombo],[Forms]![Frm Reports
SB]![ClientCombo],"*")

Will show a specific client if chosen from the combo but will not show any
records for (All)

Option 2:-
WHERE Like IIf([c id]=[Forms]![Frm Reports SB]![ClientCombo],[Forms]![Frm
Reports SB]![ClientCombo],"*")

Will show (All) but will still show every record when choosing a specific
client drom the combo.

I am so close, please, does anyone have any ideas.

Thanks so much for your time

Kerry

PMFBI

In addition to Klatuu's sage post,

Assuming......

[C ID] is type Long and no value
exists for it that is zero....

[Client Name] and Division are type Text....

the SQL for your combo box could be:

SELECT
[tbl Client Data].[C ID],
[tbl Client Data].[Client Name],
[tbl Client Data].Division
FROM
[tbl Client Data]
UNION
SELECT
0,
"(ALL)",
"(ALL)"
FROM
[tbl Client Data]
ORDER BY
[Client Name];

there is no clue by what field you want to
sort your combo box, but by using 0 and
"(ALL)," it should "rise to the top."

some alternatives for your main query's criteria

1) if bound column of combo box is 2nd column:

WHERE
[forms]![zjunk]![combo3] = "(All)"
OR
[Client Name]=[forms]![zjunk]![combo3]

2) if bound column of combo box is 1st column:

WHERE
[forms]![zjunk]![combo3] = 0
OR
[C ID]=[forms]![zjunk]![combo3]

Many thanks for your reply, this option sounds great, much more user
friendly. Applying it tho I have got a little stuck.

Do I add the Union info to the SQL view of the query which works as the
data
for my combo list of clients? or do I add this to the query which will
give
me the client details I am after?

Here is the SQL view of the combo box query
SELECT [tbl Client Data].[C ID], [tbl Client Data].[Client Name], [tbl
Client Data].Division
FROM [tbl Client Data];


Many thanks for your time.

Kerry

Here is an example that adds the work (All) to the combo. It is more
descriptive to the user than just an *
SELECT EmployeeNum FROM tbllkEmployee UNION Select "(All)" as Bogus
From
tbllkEmployee UNION Select "(All)" as Bogus From tbllkEmployee;

Now, in your query criteria, you would use something like this:
Like IIf([forms]![zjunk]![combo3]="(All)","*",[forms]![zjunk]![combo3])

:

Hi

I have created a query from form, I am able to use the * wildcard if I
wish
so all is well so far.

What I would like to do is have the * as the default value in the combo
box
so it will show all records unless I choose differently. I have tried
entering an * in the default value of the combo box but it doesn't
return any
records, if i type the * manually it works - what am i missing?

Many thanks

Kerry

 

[Gary Walter]

I'm sorry but it sure looks like you
have what I suggested a solution for:

WHERE
[Forms]![Frm Reports SB]![ClientCombo] = 0
OR
[C ID] = [Forms]![Frm Reports SB]![ClientCombo]

did this not work?

Thanks very much for the tips. I am so very nearly almost there..

My combo box has the 3 columns C ID, Client Name & Division
C ID is showing a 0 at the top of the list
Client Name has (All) at the top of the list
Division has (All) at the top of the list

so, all is well with the combo thanks very much.

The query which is now looking at the combo for its data is now misbehaving

Option 1: -
WHERE IIf([c id]=[Forms]![Frm Reports SB]![ClientCombo],[Forms]![Frm Reports
SB]![ClientCombo],"*")

Will show a specific client if chosen from the combo but will not show any
records for (All)

Option 2:-
WHERE Like IIf([c id]=[Forms]![Frm Reports SB]![ClientCombo],[Forms]![Frm
Reports SB]![ClientCombo],"*")

Will show (All) but will still show every record when choosing a specific
client drom the combo.

I am so close, please, does anyone have any ideas.

Thanks so much for your time

Kerry

PMFBI

In addition to Klatuu's sage post,

Assuming......

[C ID] is type Long and no value
exists for it that is zero....

[Client Name] and Division are type Text....

the SQL for your combo box could be:

SELECT
[tbl Client Data].[C ID],
[tbl Client Data].[Client Name],
[tbl Client Data].Division
FROM
[tbl Client Data]
UNION
SELECT
0,
"(ALL)",
"(ALL)"
FROM
[tbl Client Data]
ORDER BY
[Client Name];

there is no clue by what field you want to
sort your combo box, but by using 0 and
"(ALL)," it should "rise to the top."

some alternatives for your main query's criteria

1) if bound column of combo box is 2nd column:

WHERE
[forms]![zjunk]![combo3] = "(All)"
OR
[Client Name]=[forms]![zjunk]![combo3]

2) if bound column of combo box is 1st column:

WHERE
[forms]![zjunk]![combo3] = 0
OR
[C ID]=[forms]![zjunk]![combo3]

Many thanks for your reply, this option sounds great, much more user
friendly. Applying it tho I have got a little stuck.

Do I add the Union info to the SQL view of the query which works as the
data
for my combo list of clients? or do I add this to the query which will
give
me the client details I am after?

Here is the SQL view of the combo box query
SELECT [tbl Client Data].[C ID], [tbl Client Data].[Client Name], [tbl
Client Data].Division
FROM [tbl Client Data];


Many thanks for your time.

Kerry


:

Here is an example that adds the work (All) to the combo. It is more
descriptive to the user than just an *
SELECT EmployeeNum FROM tbllkEmployee UNION Select "(All)" as Bogus
From
tbllkEmployee UNION Select "(All)" as Bogus From tbllkEmployee;

Now, in your query criteria, you would use something like this:
Like IIf([forms]![zjunk]![combo3]="(All)","*",[forms]![zjunk]![combo3])

:

Hi

I have created a query from form, I am able to use the * wildcard if I
wish
so all is well so far.

What I would like to do is have the * as the default value in the combo
box
so it will show all records unless I choose differently. I have tried
entering an * in the default value of the combo box but it doesn't
return any
records, if i type the * manually it works - what am i missing?

Many thanks

Kerry