Choosing a range of data

[cclark]

I have a table with equip# column. The data in the equip# column looks
similiar to the following.

001
002...
999
A-001...
A-999
C-001...
C-999

I am trying to write one query that would allow me to choose the data that
begins with either all numbers (001 - 999) or by letter (A-001 to A-999) or
(C-001 to C-999).

I want to make a report that I can choose the range of data by either all
numbers or by the first letter.

The query would need to ask for a parameter to be inputted by the user.

 

[Allen Browne]

Set up the criteria in your query like this:

(Like [What initial letter] & "*") OR (Like "[0-9]*")

 

[cclark]

Thanks Allen.

I thought about creating 3 different queries to accomplish the task but what
I really want to do is use one query to accomplish it.

I want a user to click a cmd button, they are then prompted for a value,
they enter the value such as 'C', and then the report lists all equipment
that begin with 'C'. If they enter 'A' then the report lists all equipment
that begin with 'A'. I am not sure how I would do this if they want
equipment the begins with a number. I guess I will have to hard code 3
queries and create 3 cmd buttons. If you can shine more light on how I can
accomplish the task with one query and one cmd button, assistance is greatly
appreciated.

cclark

Set up the criteria in your query like this:

(Like [What initial letter] & "*") OR (Like "[0-9]*")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a table with equip# column. The data in the equip# column looks
similiar to the following.

001
002...
999
A-001...
A-999
C-001...
C-999

I am trying to write one query that would allow me to choose the data that
begins with either all numbers (001 - 999) or by letter (A-001 to A-999)
or
(C-001 to C-999).

I want to make a report that I can choose the range of data by either all
numbers or by the first letter.

The query would need to ask for a parameter to be inputted by the user.

 

[Allen Browne]

So if they leave the parameter box blank, they get the one starting with
just a number?
Like [What initial letter] & IIf([What initial letter]="", "-", Null) &
"###"

Personally, I hate those parameter boxes, and would do it with a form, where
there is a combo to select the inital letter. You build the WHERE string in
code based on whether the combo IsNull(), and then apply the string to
OpenReport, to the Filter of your form, or whatever.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen.

I thought about creating 3 different queries to accomplish the task but
what
I really want to do is use one query to accomplish it.

I want a user to click a cmd button, they are then prompted for a value,
they enter the value such as 'C', and then the report lists all equipment
that begin with 'C'. If they enter 'A' then the report lists all equipment
that begin with 'A'. I am not sure how I would do this if they want
equipment the begins with a number. I guess I will have to hard code 3
queries and create 3 cmd buttons. If you can shine more light on how I can
accomplish the task with one query and one cmd button, assistance is
greatly
appreciated.

cclark

Set up the criteria in your query like this:

(Like [What initial letter] & "*") OR (Like "[0-9]*")

I have a table with equip# column. The data in the equip# column looks
similiar to the following.

001
002...
999
A-001...
A-999
C-001...
C-999

I am trying to write one query that would allow me to choose the data that
begins with either all numbers (001 - 999) or by letter (A-001 to
A-999)
or
(C-001 to C-999).

I want to make a report that I can choose the range of data by either all
numbers or by the first letter.

The query would need to ask for a parameter to be inputted by the user

 

[cclark]

Thanks, So if I do it with a form... Or you saying that I will build the
WHERE string using modules(code based)?

cclark

So if they leave the parameter box blank, they get the one starting with
just a number?
Like [What initial letter] & IIf([What initial letter]="", "-", Null) &
"###"

Personally, I hate those parameter boxes, and would do it with a form, where
there is a combo to select the inital letter. You build the WHERE string in
code based on whether the combo IsNull(), and then apply the string to
OpenReport, to the Filter of your form, or whatever.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen.

I thought about creating 3 different queries to accomplish the task but
what
I really want to do is use one query to accomplish it.

I want a user to click a cmd button, they are then prompted for a value,
they enter the value such as 'C', and then the report lists all equipment
that begin with 'C'. If they enter 'A' then the report lists all equipment
that begin with 'A'. I am not sure how I would do this if they want
equipment the begins with a number. I guess I will have to hard code 3
queries and create 3 cmd buttons. If you can shine more light on how I can
accomplish the task with one query and one cmd button, assistance is
greatly
appreciated.

cclark

Set up the criteria in your query like this:

(Like [What initial letter] & "*") OR (Like "[0-9]*")

I have a table with equip# column. The data in the equip# column looks
similiar to the following.

001
002...
999
A-001...
A-999
C-001...
C-999

I am trying to write one query that would allow me to choose the data that
begins with either all numbers (001 - 999) or by letter (A-001 to
A-999)
or
(C-001 to C-999).

I want to make a report that I can choose the range of data by either all
numbers or by the first letter.

The query would need to ask for a parameter to be inputted by the

user

 

[Allen Browne]

Yes. There's a downloadable example here:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, So if I do it with a form... Or you saying that I will build the
WHERE string using modules(code based)?

cclark

So if they leave the parameter box blank, they get the one starting with
just a number?
Like [What initial letter] & IIf([What initial letter]="", "-", Null) &
"###"

Personally, I hate those parameter boxes, and would do it with a form, where
there is a combo to select the inital letter. You build the WHERE string in
code based on whether the combo IsNull(), and then apply the string to
OpenReport, to the Filter of your form, or whatever.

Thanks Allen.

I thought about creating 3 different queries to accomplish the task but
what
I really want to do is use one query to accomplish it.

I want a user to click a cmd button, they are then prompted for a
value,
they enter the value such as 'C', and then the report lists all equipment
that begin with 'C'. If they enter 'A' then the report lists all equipment
that begin with 'A'. I am not sure how I would do this if they want
equipment the begins with a number. I guess I will have to hard code 3
queries and create 3 cmd buttons. If you can shine more light on how I can
accomplish the task with one query and one cmd button, assistance is
greatly
appreciated.

cclark


Set up the criteria in your query like this:

(Like [What initial letter] & "*") OR (Like "[0-9]*")

I have a table with equip# column. The data in the equip# column
looks
similiar to the following.

001
002...
999
A-001...
A-999
C-001...
C-999

I am trying to write one query that would allow me to choose the
data
that
begins with either all numbers (001 - 999) or by letter (A-001 to
A-999)
or
(C-001 to C-999).

I want to make a report that I can choose the range of data by
either
all
numbers or by the first letter.

The query would need to ask for a parameter to be inputted by the

user

 

[cclark]

Thanks Allen.

You have a great web site. I downloaded the example today and will begin to
create the form later today. I'll keep you informed about my progress.

Thanks again.

cclark

Yes. There's a downloadable example here:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, So if I do it with a form... Or you saying that I will build the
WHERE string using modules(code based)?

cclark

So if they leave the parameter box blank, they get the one starting with
just a number?
Like [What initial letter] & IIf([What initial letter]="", "-",

Null)
&

"###"

Personally, I hate those parameter boxes, and would do it with a form, where
there is a combo to select the inital letter. You build the WHERE

string
in

code based on whether the combo IsNull(), and then apply the string to
OpenReport, to the Filter of your form, or whatever.

Thanks Allen.

I thought about creating 3 different queries to accomplish the task but
what
I really want to do is use one query to accomplish it.

I want a user to click a cmd button, they are then prompted for a
value,
they enter the value such as 'C', and then the report lists all equipment
that begin with 'C'. If they enter 'A' then the report lists all equipment
that begin with 'A'. I am not sure how I would do this if they want
equipment the begins with a number. I guess I will have to hard code 3
queries and create 3 cmd buttons. If you can shine more light on how

I
can

accomplish the task with one query and one cmd button, assistance is
greatly
appreciated.

cclark


Set up the criteria in your query like this:

(Like [What initial letter] & "*") OR (Like "[0-9]*")

I have a table with equip# column. The data in the equip# column
looks
similiar to the following.

001
002...
999
A-001...
A-999
C-001...
C-999

I am trying to write one query that would allow me to choose the
data
that
begins with either all numbers (001 - 999) or by letter (A-001 to
A-999)
or
(C-001 to C-999).

I want to make a report that I can choose the range of data by
either
all
numbers or by the first letter.

The query would need to ask for a parameter to be inputted by the

user

 

[cclark]

Thanks again Allen

I was able to create the new form that contained a combo to select the
initial letter or numbers. Looks a lot cleaner. Thanks.

Code Below:

Dim stDocName As String

Dim strWhere As String 'The criteria string.

If Me.cboFilter = -1 Then
strWhere = "equipment like ""A*""" 'Combo says Attachements
ElseIf Me.cboFilter = 0 Then
strWhere = "equipment like ""[C-L]*""" 'Combo says Compressors
ElseIf Me.cboFilter = 1 Then
strWhere = "equipment like ""[0-9]*""" 'Combo says Vehicles
End If

' Debug.Print strWhere

stDocName = "Equpment List"
DoCmd.OpenReport stDocName, acViewPreview, , strWhere


-- I also put a name in the combo called All Equipmet, when this is selected
strWhere is null and report shows all equipment.

Thanks

Thanks Allen.

You have a great web site. I downloaded the example today and will begin to
create the form later today. I'll keep you informed about my progress.

Thanks again.

cclark

Yes. There's a downloadable example here:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, So if I do it with a form... Or you saying that I will build the
WHERE string using modules(code based)?

cclark


So if they leave the parameter box blank, they get the one starting with
just a number?
Like [What initial letter] & IIf([What initial letter]="", "-", Null)
&
"###"

Personally, I hate those parameter boxes, and would do it with a form,
where
there is a combo to select the inital letter. You build the WHERE string
in
code based on whether the combo IsNull(), and then apply the string to
OpenReport, to the Filter of your form, or whatever.

Thanks Allen.

I thought about creating 3 different queries to accomplish the task but
what
I really want to do is use one query to accomplish it.

I want a user to click a cmd button, they are then prompted for a
value,
they enter the value such as 'C', and then the report lists all
equipment
that begin with 'C'. If they enter 'A' then the report lists all
equipment
that begin with 'A'. I am not sure how I would do this if they want
equipment the begins with a number. I guess I will have to hard

code

3

how

I

can
accomplish the task with one query and one cmd button, assistance is
greatly
appreciated.

cclark


Set up the criteria in your query like this:

(Like [What initial letter] & "*") OR (Like "[0-9]*")

I have a table with equip# column. The data in the equip# column
looks
similiar to the following.

001
002...
999
A-001...
A-999
C-001...
C-999

I am trying to write one query that would allow me to choose the
data
that
begins with either all numbers (001 - 999) or by letter (A-001 to
A-999)
or
(C-001 to C-999).

I want to make a report that I can choose the range of data by
either
all
numbers or by the first letter.

The query would need to ask for a parameter to be inputted by the
user

 

[Allen Browne]

Good news.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks again Allen

I was able to create the new form that contained a combo to select the
initial letter or numbers. Looks a lot cleaner. Thanks.

Code Below:

[snip]