query a number stored as text

[Guest]

I have a part number field that had to be defined as text due to the make up
of the part numbers. I need to query for a number and it will not find them.
How can I make the query come up with the value I am looking for?

 

[Guest]

Try this:

In the query criteria for part number, enter:

Like "*"& [Enter any part of part number]&"*"

This will prompt for input and will find all records
where part number contains the input string.

Hope this helps.

 

[Guest]

I still got no records. The field I am trying to query is text and I am
putting in a number and I get no records. The field needs to be Text due to
the fact some of the part numbers used are a mixture of letters and numbers.

This actually is in a subform of an order, could that be my problem?? I
just want to run a crosstab query to see my sales for a year on one part
number. Not to whom but just quantities. It will run if I let it loose, but
if I enter a part number or parameter in the criteria, I get nothing.

Thanx - Lee

Try this:

In the query criteria for part number, enter:

Like "*"& [Enter any part of part number]&"*"

This will prompt for input and will find all records
where part number contains the input string.

Hope this helps.

-----Original Message-----
I have a part number field that had to be defined as text due to the make up
of the part numbers. I need to query for a number and it will not find them.
How can I make the query come up with the value I am looking for?
.

 

[Ken Snell [MVP]]

Try one of these:

Like "*'" & [Enter any part of part number] & "'*"

or

Like "*" & CStr[Enter any part of part number]) & "*"

--

Ken Snell
<MS ACCESS MVP>

I still got no records. The field I am trying to query is text and I am
putting in a number and I get no records. The field needs to be Text due to
the fact some of the part numbers used are a mixture of letters and numbers.

This actually is in a subform of an order, could that be my problem?? I
just want to run a crosstab query to see my sales for a year on one part
number. Not to whom but just quantities. It will run if I let it loose, but
if I enter a part number or parameter in the criteria, I get nothing.

Thanx - Lee

Try this:

In the query criteria for part number, enter:

Like "*"& [Enter any part of part number]&"*"

This will prompt for input and will find all records
where part number contains the input string.

Hope this helps.

-----Original Message-----
I have a part number field that had to be defined as text due to the make up
of the part numbers. I need to query for a number and it will not find them.
How can I make the query come up with the value I am looking for?
.

 

[Guest]

Hi Ken,

Still nothing, I am using a query to display the data in the subform, I
tried using your two suggestions. Here may be a clue, if I do a find in that
table I also get nothing, but if I set "search field as formatted" it works
fine. Can I set a switch in my query to do that????

Thanx,
Lee

Try one of these:

Like "*'" & [Enter any part of part number] & "'*"

or

Like "*" & CStr[Enter any part of part number]) & "*"

--

Ken Snell
<MS ACCESS MVP>

I still got no records. The field I am trying to query is text and I am
putting in a number and I get no records. The field needs to be Text due to
the fact some of the part numbers used are a mixture of letters and numbers.

This actually is in a subform of an order, could that be my problem?? I
just want to run a crosstab query to see my sales for a year on one part
number. Not to whom but just quantities. It will run if I let it loose, but
if I enter a part number or parameter in the criteria, I get nothing.

Thanx - Lee

Try this:

In the query criteria for part number, enter:

Like "*"& [Enter any part of part number]&"*"

This will prompt for input and will find all records
where part number contains the input string.

Hope this helps.

-----Original Message-----
I have a part number field that had to be defined as text
due to the make up
of the part numbers. I need to query for a number and it
will not find them.
How can I make the query come up with the value I am
looking for?
.

 

[Ken Snell [MVP]]

I assume that the query is saved and stored. So let's try setting the data
type of the Parameter.

Open the query in design view.

Click Query | Parameters. You should see a 2-column window.

In the left column, type this string:
Enter any part of part number

In the right column, select Text from the dropdown list.

Click OK.

Save and close the query.

Now you've set the Parameter to be specifically a text input. See if that
helps.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Still nothing, I am using a query to display the data in the subform, I
tried using your two suggestions. Here may be a clue, if I do a find in that
table I also get nothing, but if I set "search field as formatted" it works
fine. Can I set a switch in my query to do that????

Thanx,
Lee

Try one of these:

Like "*'" & [Enter any part of part number] & "'*"

or

Like "*" & CStr[Enter any part of part number]) & "*"

--

Ken Snell
<MS ACCESS MVP>

I still got no records. The field I am trying to query is text and I am
putting in a number and I get no records. The field needs to be Text

due
to

the fact some of the part numbers used are a mixture of letters and numbers.

This actually is in a subform of an order, could that be my problem?? I
just want to run a crosstab query to see my sales for a year on one part
number. Not to whom but just quantities. It will run if I let it

loose,
but

if I enter a part number or parameter in the criteria, I get nothing.

Thanx - Lee

:

Try this:

In the query criteria for part number, enter:

Like "*"& [Enter any part of part number]&"*"

This will prompt for input and will find all records
where part number contains the input string.

Hope this helps.

-----Original Message-----
I have a part number field that had to be defined as text
due to the make up
of the part numbers. I need to query for a number and it
will not find them.
How can I make the query come up with the value I am
looking for?
.

 

[Guest]

Sorry Ken,
Now it pulls out everything like there were no parameters at all. It
doesn't matter what I put in the parameter, I get the same number of records.

So what I did is exported the entire query results, 156 lines, to Excel.
When I opened it in Excel, the line I was looking for had a number of 291 in
it as the part number, which it seems is equal to the number of 8972 in a
Access Text format.

I then did the query with the criteria equal to the number "291", and the
results I am looking for came up. The shipments of 8972 for the last year!!

So if there is some way that we can put that "Search as Formatted" into my
query, I would have what I need.

I hope you don't get gray hair like me,
Lee

I assume that the query is saved and stored. So let's try setting the data
type of the Parameter.

Open the query in design view.

Click Query | Parameters. You should see a 2-column window.

In the left column, type this string:
Enter any part of part number

In the right column, select Text from the dropdown list.

Click OK.

Save and close the query.

Now you've set the Parameter to be specifically a text input. See if that
helps.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Still nothing, I am using a query to display the data in the subform, I
tried using your two suggestions. Here may be a clue, if I do a find in that
table I also get nothing, but if I set "search field as formatted" it works
fine. Can I set a switch in my query to do that????

Thanx,
Lee

Try one of these:

Like "*'" & [Enter any part of part number] & "'*"

or

Like "*" & CStr[Enter any part of part number]) & "*"

--

Ken Snell
<MS ACCESS MVP>

I still got no records. The field I am trying to query is text and I am
putting in a number and I get no records. The field needs to be Text due
to
the fact some of the part numbers used are a mixture of letters and
numbers.

This actually is in a subform of an order, could that be my problem?? I
just want to run a crosstab query to see my sales for a year on one part
number. Not to whom but just quantities. It will run if I let it loose,
but
if I enter a part number or parameter in the criteria, I get nothing.

Thanx - Lee

:

Try this:

In the query criteria for part number, enter:

Like "*"& [Enter any part of part number]&"*"

This will prompt for input and will find all records
where part number contains the input string.

Hope this helps.

-----Original Message-----
I have a part number field that had to be defined as text
due to the make up
of the part numbers. I need to query for a number and it
will not find them.
How can I make the query come up with the value I am
looking for?
.

 

[Ken Snell [MVP]]

Post the SQL statement of the query that you're trying to use. And post some
example data from the table, along with the field names and their data
types.

--

Ken Snell
<MS ACCESS MVP>

Sorry Ken,
Now it pulls out everything like there were no parameters at all. It
doesn't matter what I put in the parameter, I get the same number of records.

So what I did is exported the entire query results, 156 lines, to Excel.
When I opened it in Excel, the line I was looking for had a number of 291 in
it as the part number, which it seems is equal to the number of 8972 in a
Access Text format.

I then did the query with the criteria equal to the number "291", and the
results I am looking for came up. The shipments of 8972 for the last year!!

So if there is some way that we can put that "Search as Formatted" into my
query, I would have what I need.

I hope you don't get gray hair like me,
Lee

I assume that the query is saved and stored. So let's try setting the data
type of the Parameter.

Open the query in design view.

Click Query | Parameters. You should see a 2-column window.

In the left column, type this string:
Enter any part of part number

In the right column, select Text from the dropdown list.

Click OK.

Save and close the query.

Now you've set the Parameter to be specifically a text input. See if that
helps.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Still nothing, I am using a query to display the data in the subform, I
tried using your two suggestions. Here may be a clue, if I do a find

in
that

table I also get nothing, but if I set "search field as formatted" it works
fine. Can I set a switch in my query to do that????

Thanx,
Lee

:

Try one of these:

Like "*'" & [Enter any part of part number] & "'*"

or

Like "*" & CStr[Enter any part of part number]) & "*"

--

Ken Snell
<MS ACCESS MVP>

I still got no records. The field I am trying to query is text

and I
am

putting in a number and I get no records. The field needs to be

Text
due

to
the fact some of the part numbers used are a mixture of letters and
numbers.

This actually is in a subform of an order, could that be my

problem??
I

just want to run a crosstab query to see my sales for a year on

one
part

number. Not to whom but just quantities. It will run if I let it loose,
but
if I enter a part number or parameter in the criteria, I get nothing.

Thanx - Lee

:

Try this:

In the query criteria for part number, enter:

Like "*"& [Enter any part of part number]&"*"

This will prompt for input and will find all records
where part number contains the input string.

Hope this helps.

-----Original Message-----
I have a part number field that had to be defined as text
due to the make up
of the part numbers. I need to query for a number and it
will not find them.
How can I make the query come up with the value I am
looking for?
.

 

[Guest]

TRANSFORM Sum(buyselldetails.Qty) AS SumOfQty
SELECT buyselldetails.PartNumber, Sum(buyselldetails.Qty) AS [Total Of Qty]
FROM buyselldetails
GROUP BY buyselldetails.PartNumber
ORDER BY buyselldetails.PartNumber
PIVOT Format([ShipDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

OrderID Qty PartNumber NetPrice ShipQty ShipDate
20 1 8972 $93.00 1 7 /20/2004
19 2 8972 $93.00 2 7 /20/2004
374 2 8972 $93.00 2 9 /29/2004
244 6 8972 $93.00 6 9 /1 /2004
285 1 8972 $93.00 1 9 /28/2004
274 13 8972 $93.00 8 9 /3 /2004
275 5 8972 $93.00 5 9 /28/2004
217 1 900-030 $12.95 1 8 /25/2004
335 1 900-031 $38.30 1 9 /22/2004
217 1 900-031 $38.32 1 8 /25/2004

Auto Num Text Curr Num Date
Num

The data types are on the bottom,

Thanx,
Lee

Post the SQL statement of the query that you're trying to use. And post some
example data from the table, along with the field names and their data
types.

--

Ken Snell
<MS ACCESS MVP>

Sorry Ken,
Now it pulls out everything like there were no parameters at all. It
doesn't matter what I put in the parameter, I get the same number of records.

So what I did is exported the entire query results, 156 lines, to Excel.
When I opened it in Excel, the line I was looking for had a number of 291 in
it as the part number, which it seems is equal to the number of 8972 in a
Access Text format.

I then did the query with the criteria equal to the number "291", and the
results I am looking for came up. The shipments of 8972 for the last year!!

So if there is some way that we can put that "Search as Formatted" into my
query, I would have what I need.

I hope you don't get gray hair like me,
Lee

I assume that the query is saved and stored. So let's try setting the data
type of the Parameter.

Open the query in design view.

Click Query | Parameters. You should see a 2-column window.

In the left column, type this string:
Enter any part of part number

In the right column, select Text from the dropdown list.

Click OK.

Save and close the query.

Now you've set the Parameter to be specifically a text input. See if that
helps.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Still nothing, I am using a query to display the data in the subform, I
tried using your two suggestions. Here may be a clue, if I do a find in
that
table I also get nothing, but if I set "search field as formatted" it
works
fine. Can I set a switch in my query to do that????

Thanx,
Lee

:

Try one of these:

Like "*'" & [Enter any part of part number] & "'*"

or

Like "*" & CStr[Enter any part of part number]) & "*"

--

Ken Snell
<MS ACCESS MVP>

I still got no records. The field I am trying to query is text and I
am
putting in a number and I get no records. The field needs to be Text
due
to
the fact some of the part numbers used are a mixture of letters and
numbers.

This actually is in a subform of an order, could that be my problem??
I
just want to run a crosstab query to see my sales for a year on one
part
number. Not to whom but just quantities. It will run if I let it
loose,
but
if I enter a part number or parameter in the criteria, I get nothing.

Thanx - Lee

:

Try this:

In the query criteria for part number, enter:

Like "*"& [Enter any part of part number]&"*"

This will prompt for input and will find all records
where part number contains the input string.

Hope this helps.

-----Original Message-----
I have a part number field that had to be defined as text
due to the make up
of the part numbers. I need to query for a number and it
will not find them.
How can I make the query come up with the value I am
looking for?
.

 

[Guest]

Ken,

In looking further, when I query on = 8972 I get nothing, if I query on =291
I get all the data relating to the part number 8972. It seems since the
partnumber field is actually a text field, it pulls the record id in the
parts table which is 291.

The thing I need to do is be able to plug in a part number in that query and
get the sales figures. Is there some way to show that 8972 is a number in a
text field. Like in the old Excel days of putting '8972 and it would left
justify, and recognize the number as a alpha numeric.

Thanx, Lee

TRANSFORM Sum(buyselldetails.Qty) AS SumOfQty
SELECT buyselldetails.PartNumber, Sum(buyselldetails.Qty) AS [Total Of Qty]
FROM buyselldetails
GROUP BY buyselldetails.PartNumber
ORDER BY buyselldetails.PartNumber
PIVOT Format([ShipDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

OrderID Qty PartNumber NetPrice ShipQty ShipDate
20 1 8972 $93.00 1 7 /20/2004
19 2 8972 $93.00 2 7 /20/2004
374 2 8972 $93.00 2 9 /29/2004
244 6 8972 $93.00 6 9 /1 /2004
285 1 8972 $93.00 1 9 /28/2004
274 13 8972 $93.00 8 9 /3 /2004
275 5 8972 $93.00 5 9 /28/2004
217 1 900-030 $12.95 1 8 /25/2004
335 1 900-031 $38.30 1 9 /22/2004
217 1 900-031 $38.32 1 8 /25/2004

Auto Num Text Curr Num Date
Num

The data types are on the bottom,

Thanx,
Lee

Post the SQL statement of the query that you're trying to use. And post some
example data from the table, along with the field names and their data
types.

--

Ken Snell
<MS ACCESS MVP>

Sorry Ken,
Now it pulls out everything like there were no parameters at all. It
doesn't matter what I put in the parameter, I get the same number of records.

So what I did is exported the entire query results, 156 lines, to Excel.
When I opened it in Excel, the line I was looking for had a number of 291 in
it as the part number, which it seems is equal to the number of 8972 in a
Access Text format.

I then did the query with the criteria equal to the number "291", and the
results I am looking for came up. The shipments of 8972 for the last year!!

So if there is some way that we can put that "Search as Formatted" into my
query, I would have what I need.

I hope you don't get gray hair like me,
Lee

:

I assume that the query is saved and stored. So let's try setting the data
type of the Parameter.

Open the query in design view.

Click Query | Parameters. You should see a 2-column window.

In the left column, type this string:
Enter any part of part number

In the right column, select Text from the dropdown list.

Click OK.

Save and close the query.

Now you've set the Parameter to be specifically a text input. See if that
helps.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Still nothing, I am using a query to display the data in the subform, I
tried using your two suggestions. Here may be a clue, if I do a find in
that
table I also get nothing, but if I set "search field as formatted" it
works
fine. Can I set a switch in my query to do that????

Thanx,
Lee

:

Try one of these:

Like "*'" & [Enter any part of part number] & "'*"

or

Like "*" & CStr[Enter any part of part number]) & "*"

--

Ken Snell
<MS ACCESS MVP>

I still got no records. The field I am trying to query is text and I
am
putting in a number and I get no records. The field needs to be Text
due
to
the fact some of the part numbers used are a mixture of letters and
numbers.

This actually is in a subform of an order, could that be my problem??
I
just want to run a crosstab query to see my sales for a year on one
part
number. Not to whom but just quantities. It will run if I let it
loose,
but
if I enter a part number or parameter in the criteria, I get nothing.

Thanx - Lee

:

Try this:

In the query criteria for part number, enter:

Like "*"& [Enter any part of part number]&"*"

This will prompt for input and will find all records
where part number contains the input string.

Hope this helps.

-----Original Message-----
I have a part number field that had to be defined as text
due to the make up
of the part numbers. I need to query for a number and it
will not find them.
How can I make the query come up with the value I am
looking for?
.

 

[Ken Snell [MVP]]

Lee - I don't see any parameter or WHERE clause in the SQL statement that
you posted. Can you explain further what you're doing when you "query on =
8972"? What field are you filtering based on that value? And where are you
putting that criterion in this SQL statement?

--

Ken Snell
<MS ACCESS MVP>

Ken,

In looking further, when I query on = 8972 I get nothing, if I query on =291
I get all the data relating to the part number 8972. It seems since the
partnumber field is actually a text field, it pulls the record id in the
parts table which is 291.

The thing I need to do is be able to plug in a part number in that query and
get the sales figures. Is there some way to show that 8972 is a number in a
text field. Like in the old Excel days of putting '8972 and it would left
justify, and recognize the number as a alpha numeric.

Thanx, Lee

TRANSFORM Sum(buyselldetails.Qty) AS SumOfQty
SELECT buyselldetails.PartNumber, Sum(buyselldetails.Qty) AS [Total Of Qty]
FROM buyselldetails
GROUP BY buyselldetails.PartNumber
ORDER BY buyselldetails.PartNumber
PIVOT Format([ShipDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

OrderID Qty PartNumber NetPrice ShipQty ShipDate
20 1 8972 $93.00 1 7 /20/2004
19 2 8972 $93.00 2 7 /20/2004
374 2 8972 $93.00 2 9 /29/2004
244 6 8972 $93.00 6 9 /1 /2004
285 1 8972 $93.00 1 9 /28/2004
274 13 8972 $93.00 8 9 /3 /2004
275 5 8972 $93.00 5 9 /28/2004
217 1 900-030 $12.95 1 8 /25/2004
335 1 900-031 $38.30 1 9 /22/2004
217 1 900-031 $38.32 1 8 /25/2004

Auto Num Text Curr Num Date
Num

The data types are on the bottom,

Thanx,
Lee

Post the SQL statement of the query that you're trying to use. And post some
example data from the table, along with the field names and their data
types.

--

Ken Snell
<MS ACCESS MVP>

Sorry Ken,
Now it pulls out everything like there were no parameters at all. It
doesn't matter what I put in the parameter, I get the same number of
records.

So what I did is exported the entire query results, 156 lines, to Excel.
When I opened it in Excel, the line I was looking for had a number of 291
in
it as the part number, which it seems is equal to the number of 8972 in a
Access Text format.

I then did the query with the criteria equal to the number "291", and the
results I am looking for came up. The shipments of 8972 for the last
year!!

So if there is some way that we can put that "Search as Formatted" into my
query, I would have what I need.

I hope you don't get gray hair like me,
Lee

:

I assume that the query is saved and stored. So let's try setting the
data
type of the Parameter.

Open the query in design view.

Click Query | Parameters. You should see a 2-column window.

In the left column, type this string:
Enter any part of part number

In the right column, select Text from the dropdown list.

Click OK.

Save and close the query.

Now you've set the Parameter to be specifically a text input. See if
that
helps.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Still nothing, I am using a query to display the data in the subform,
I
tried using your two suggestions. Here may be a clue, if I do a find
in
that
table I also get nothing, but if I set "search field as formatted" it
works
fine. Can I set a switch in my query to do that????

Thanx,
Lee

:

Try one of these:

Like "*'" & [Enter any part of part number] & "'*"

or

Like "*" & CStr[Enter any part of part number]) & "*"

--

Ken Snell
<MS ACCESS MVP>

I still got no records. The field I am trying to query is text
and I
am
putting in a number and I get no records. The field needs to be
Text
due
to
the fact some of the part numbers used are a mixture of letters
and
numbers.

This actually is in a subform of an order, could that be my
problem??
I
just want to run a crosstab query to see my sales for a year on
one
part
number. Not to whom but just quantities. It will run if I let it
loose,
but
if I enter a part number or parameter in the criteria, I get
nothing.

Thanx - Lee

:

Try this:

In the query criteria for part number, enter:

Like "*"& [Enter any part of part number]&"*"

This will prompt for input and will find all records
where part number contains the input string.

Hope this helps.

-----Original Message-----
I have a part number field that had to be defined as text
due to the make up
of the part numbers. I need to query for a number and it
will not find them.
How can I make the query come up with the value I am
looking for?
.

 

[Guest]

Ken,

When I run this query, below I get all the information for all the
shipments. When I add criteria to the query (the where clause), that is when
I start having the problems. If I put in =8972 for the part number I get
nothing. If I put in =291 I get all the info pretaining to the part number
8972, because 291 is the id for part number 8972. I just don't know why it
will not literally take the 8972 and give me the results. Again I think it
may have something to do the fact that the part number field is a text field,
rather than a number only.
My goal is to set up a menu item to check sales of one product at a time.
Just enter the part number and see the annual usage for a part.
Needless to say I cannot get it to work.
Lee

Lee - I don't see any parameter or WHERE clause in the SQL statement that
you posted. Can you explain further what you're doing when you "query on =
8972"? What field are you filtering based on that value? And where are you
putting that criterion in this SQL statement?

--

Ken Snell
<MS ACCESS MVP>

Ken,

In looking further, when I query on = 8972 I get nothing, if I query on =291
I get all the data relating to the part number 8972. It seems since the
partnumber field is actually a text field, it pulls the record id in the
parts table which is 291.

The thing I need to do is be able to plug in a part number in that query and
get the sales figures. Is there some way to show that 8972 is a number in a
text field. Like in the old Excel days of putting '8972 and it would left
justify, and recognize the number as a alpha numeric.

Thanx, Lee

TRANSFORM Sum(buyselldetails.Qty) AS SumOfQty
SELECT buyselldetails.PartNumber, Sum(buyselldetails.Qty) AS [Total Of Qty]
FROM buyselldetails
GROUP BY buyselldetails.PartNumber
ORDER BY buyselldetails.PartNumber
PIVOT Format([ShipDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

OrderID Qty PartNumber NetPrice ShipQty ShipDate
20 1 8972 $93.00 1 7 /20/2004
19 2 8972 $93.00 2 7 /20/2004
374 2 8972 $93.00 2 9 /29/2004
244 6 8972 $93.00 6 9 /1 /2004
285 1 8972 $93.00 1 9 /28/2004
274 13 8972 $93.00 8 9 /3 /2004
275 5 8972 $93.00 5 9 /28/2004
217 1 900-030 $12.95 1 8 /25/2004
335 1 900-031 $38.30 1 9 /22/2004
217 1 900-031 $38.32 1 8 /25/2004

Auto Num Text Curr Num Date
Num

The data types are on the bottom,

Thanx,
Lee

:

Post the SQL statement of the query that you're trying to use. And post some
example data from the table, along with the field names and their data
types.

--

Ken Snell
<MS ACCESS MVP>

Sorry Ken,
Now it pulls out everything like there were no parameters at all. It
doesn't matter what I put in the parameter, I get the same number of
records.

So what I did is exported the entire query results, 156 lines, to Excel.
When I opened it in Excel, the line I was looking for had a number of 291
in
it as the part number, which it seems is equal to the number of 8972 in a
Access Text format.

I then did the query with the criteria equal to the number "291", and the
results I am looking for came up. The shipments of 8972 for the last
year!!

So if there is some way that we can put that "Search as Formatted" into my
query, I would have what I need.

I hope you don't get gray hair like me,
Lee

:

I assume that the query is saved and stored. So let's try setting the
data
type of the Parameter.

Open the query in design view.

Click Query | Parameters. You should see a 2-column window.

In the left column, type this string:
Enter any part of part number

In the right column, select Text from the dropdown list.

Click OK.

Save and close the query.

Now you've set the Parameter to be specifically a text input. See if
that
helps.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Still nothing, I am using a query to display the data in the subform,
I
tried using your two suggestions. Here may be a clue, if I do a find
in
that
table I also get nothing, but if I set "search field as formatted" it
works
fine. Can I set a switch in my query to do that????

Thanx,
Lee

:

Try one of these:

Like "*'" & [Enter any part of part number] & "'*"

or

Like "*" & CStr[Enter any part of part number]) & "*"

--

Ken Snell
<MS ACCESS MVP>

I still got no records. The field I am trying to query is text
and I
am
putting in a number and I get no records. The field needs to be
Text
due
to
the fact some of the part numbers used are a mixture of letters
and
numbers.

This actually is in a subform of an order, could that be my
problem??
I
just want to run a crosstab query to see my sales for a year on
one
part
number. Not to whom but just quantities. It will run if I let it
loose,
but
if I enter a part number or parameter in the criteria, I get
nothing.

Thanx - Lee

:

Try this:

In the query criteria for part number, enter:

Like "*"& [Enter any part of part number]&"*"

This will prompt for input and will find all records
where part number contains the input string.

Hope this helps.

-----Original Message-----
I have a part number field that had to be defined as text
due to the make up
of the part numbers. I need to query for a number and it
will not find them.
How can I make the query come up with the value I am
looking for?
.

 

[Brendan Reynolds]

Is the part number field a lookup field?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Ken,

When I run this query, below I get all the information for all the
shipments. When I add criteria to the query (the where clause), that is
when
I start having the problems. If I put in =8972 for the part number I get
nothing. If I put in =291 I get all the info pretaining to the part
number
8972, because 291 is the id for part number 8972. I just don't know why
it
will not literally take the 8972 and give me the results. Again I think
it
may have something to do the fact that the part number field is a text
field,
rather than a number only.
My goal is to set up a menu item to check sales of one product at a time.
Just enter the part number and see the annual usage for a part.
Needless to say I cannot get it to work.
Lee

Lee - I don't see any parameter or WHERE clause in the SQL statement that
you posted. Can you explain further what you're doing when you "query on
=
8972"? What field are you filtering based on that value? And where are
you
putting that criterion in this SQL statement?

--

Ken Snell
<MS ACCESS MVP>

Ken,

In looking further, when I query on = 8972 I get nothing, if I query on =291
I get all the data relating to the part number 8972. It seems since
the
partnumber field is actually a text field, it pulls the record id in
the
parts table which is 291.

The thing I need to do is be able to plug in a part number in that
query and
get the sales figures. Is there some way to show that 8972 is a number
in a
text field. Like in the old Excel days of putting '8972 and it would
left
justify, and recognize the number as a alpha numeric.

Thanx, Lee


:

TRANSFORM Sum(buyselldetails.Qty) AS SumOfQty
SELECT buyselldetails.PartNumber, Sum(buyselldetails.Qty) AS [Total
Of Qty]
FROM buyselldetails
GROUP BY buyselldetails.PartNumber
ORDER BY buyselldetails.PartNumber
PIVOT Format([ShipDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

OrderID Qty PartNumber NetPrice ShipQty ShipDate
20 1 8972 $93.00 1 7 /20/2004
19 2 8972 $93.00 2 7 /20/2004
374 2 8972 $93.00 2 9 /29/2004
244 6 8972 $93.00 6 9 /1 /2004
285 1 8972 $93.00 1 9 /28/2004
274 13 8972 $93.00 8 9 /3 /2004
275 5 8972 $93.00 5 9 /28/2004
217 1 900-030 $12.95 1 8 /25/2004
335 1 900-031 $38.30 1 9 /22/2004
217 1 900-031 $38.32 1 8 /25/2004

Auto Num Text Curr Num
Date
Num

The data types are on the bottom,

Thanx,
Lee

:

Post the SQL statement of the query that you're trying to use. And post some
example data from the table, along with the field names and their
data
types.

--

Ken Snell
<MS ACCESS MVP>

Sorry Ken,
Now it pulls out everything like there were no parameters at all. It
doesn't matter what I put in the parameter, I get the same number
of
records.

So what I did is exported the entire query results, 156 lines, to Excel.
When I opened it in Excel, the line I was looking for had a
number of 291
in
it as the part number, which it seems is equal to the number of
8972 in a
Access Text format.

I then did the query with the criteria equal to the number "291", and the
results I am looking for came up. The shipments of 8972 for the last
year!!

So if there is some way that we can put that "Search as
Formatted" into my
query, I would have what I need.

I hope you don't get gray hair like me,
Lee

:

I assume that the query is saved and stored. So let's try
setting the
data
type of the Parameter.

Open the query in design view.

Click Query | Parameters. You should see a 2-column window.

In the left column, type this string:
Enter any part of part number

In the right column, select Text from the dropdown list.

Click OK.

Save and close the query.

Now you've set the Parameter to be specifically a text input.
See if
that
helps.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Still nothing, I am using a query to display the data in the subform,
I
tried using your two suggestions. Here may be a clue, if I
do a find
in
that
table I also get nothing, but if I set "search field as formatted" it
works
fine. Can I set a switch in my query to do that????

Thanx,
Lee

:

Try one of these:

Like "*'" & [Enter any part of part number] & "'*"

or

Like "*" & CStr[Enter any part of part number]) & "*"

--

Ken Snell
<MS ACCESS MVP>

I still got no records. The field I am trying to query
is text
and I
am
putting in a number and I get no records. The field
needs to be
Text
due
to
the fact some of the part numbers used are a mixture of letters
and
numbers.

This actually is in a subform of an order, could that be
my
problem??
I
just want to run a crosstab query to see my sales for a year on
one
part
number. Not to whom but just quantities. It will run if
I let it
loose,
but
if I enter a part number or parameter in the criteria, I
get
nothing.

Thanx - Lee

:

Try this:

In the query criteria for part number, enter:

Like "*"& [Enter any part of part number]&"*"

This will prompt for input and will find all records
where part number contains the input string.

Hope this helps.

-----Original Message-----
I have a part number field that had to be defined as
text
due to the make up
of the part numbers. I need to query for a number and
it
will not find them.
How can I make the query come up with the value I am
looking for?
.

 

[Guest]

Yes Brendan it is.

Lee

Is the part number field a lookup field?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Ken,

When I run this query, below I get all the information for all the
shipments. When I add criteria to the query (the where clause), that is
when
I start having the problems. If I put in =8972 for the part number I get
nothing. If I put in =291 I get all the info pretaining to the part
number
8972, because 291 is the id for part number 8972. I just don't know why
it
will not literally take the 8972 and give me the results. Again I think
it
may have something to do the fact that the part number field is a text
field,
rather than a number only.
My goal is to set up a menu item to check sales of one product at a time.
Just enter the part number and see the annual usage for a part.
Needless to say I cannot get it to work.
Lee

Lee - I don't see any parameter or WHERE clause in the SQL statement that
you posted. Can you explain further what you're doing when you "query on
=
8972"? What field are you filtering based on that value? And where are
you
putting that criterion in this SQL statement?

--

Ken Snell
<MS ACCESS MVP>

Ken,

In looking further, when I query on = 8972 I get nothing, if I query on
=291
I get all the data relating to the part number 8972. It seems since
the
partnumber field is actually a text field, it pulls the record id in
the
parts table which is 291.

The thing I need to do is be able to plug in a part number in that
query
and
get the sales figures. Is there some way to show that 8972 is a number
in
a
text field. Like in the old Excel days of putting '8972 and it would
left
justify, and recognize the number as a alpha numeric.

Thanx, Lee


:

TRANSFORM Sum(buyselldetails.Qty) AS SumOfQty
SELECT buyselldetails.PartNumber, Sum(buyselldetails.Qty) AS [Total
Of
Qty]
FROM buyselldetails
GROUP BY buyselldetails.PartNumber
ORDER BY buyselldetails.PartNumber
PIVOT Format([ShipDate],"mmm") In

("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

OrderID Qty PartNumber NetPrice ShipQty ShipDate
20 1 8972 $93.00 1 7 /20/2004
19 2 8972 $93.00 2 7 /20/2004
374 2 8972 $93.00 2 9 /29/2004
244 6 8972 $93.00 6 9 /1 /2004
285 1 8972 $93.00 1 9 /28/2004
274 13 8972 $93.00 8 9 /3 /2004
275 5 8972 $93.00 5 9 /28/2004
217 1 900-030 $12.95 1 8 /25/2004
335 1 900-031 $38.30 1 9 /22/2004
217 1 900-031 $38.32 1 8 /25/2004

Auto Num Text Curr Num
Date
Num

The data types are on the bottom,

Thanx,
Lee

:

Post the SQL statement of the query that you're trying to use. And
post some
example data from the table, along with the field names and their
data
types.

--

Ken Snell
<MS ACCESS MVP>

Sorry Ken,
Now it pulls out everything like there were no parameters at all.
It
doesn't matter what I put in the parameter, I get the same number
of
records.

So what I did is exported the entire query results, 156 lines, to
Excel.
When I opened it in Excel, the line I was looking for had a
number
of 291
in
it as the part number, which it seems is equal to the number of
8972
in a
Access Text format.

I then did the query with the criteria equal to the number "291",
and the
results I am looking for came up. The shipments of 8972 for the
last
year!!

So if there is some way that we can put that "Search as
Formatted"
into my
query, I would have what I need.

I hope you don't get gray hair like me,
Lee

:

I assume that the query is saved and stored. So let's try
setting
the
data
type of the Parameter.

Open the query in design view.

Click Query | Parameters. You should see a 2-column window.

In the left column, type this string:
Enter any part of part number

In the right column, select Text from the dropdown list.

Click OK.

Save and close the query.

Now you've set the Parameter to be specifically a text input.
See
if
that
helps.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Still nothing, I am using a query to display the data in the
subform,
I
tried using your two suggestions. Here may be a clue, if I
do a
find
in
that
table I also get nothing, but if I set "search field as
formatted" it
works
fine. Can I set a switch in my query to do that????

Thanx,
Lee

:

Try one of these:

Like "*'" & [Enter any part of part number] & "'*"

or

Like "*" & CStr[Enter any part of part number]) & "*"

--

Ken Snell
<MS ACCESS MVP>

I still got no records. The field I am trying to query
is
text
and I
am
putting in a number and I get no records. The field
needs
to be
Text
due
to
the fact some of the part numbers used are a mixture of
letters
and
numbers.

This actually is in a subform of an order, could that be
my
problem??
I
just want to run a crosstab query to see my sales for a
year on
one
part
number. Not to whom but just quantities. It will run if
I
let it
loose,
but
if I enter a part number or parameter in the criteria, I
get
nothing.

Thanx - Lee

:

Try this:

In the query criteria for part number, enter:

Like "*"& [Enter any part of part number]&"*"

This will prompt for input and will find all records
where part number contains the input string.

Hope this helps.

-----Original Message-----
I have a part number field that had to be defined as
text
due to the make up
of the part numbers. I need to query for a number and
it
will not find them.
How can I make the query come up with the value I am
looking for?
.

 

[Brendan Reynolds]

Then, although it displays the text value "8972", the number 291 is what is
actually stored in the field. If you want to search, sort, or filter using
the text value, the thing to do is to include the text field in the query by
joining the lookup table to the main table. Your query is quite complex, so
let me try to illustrate this with a simpler example. In the Northwind
sample database, the Orders table includes a lookup field EmployeeID.
Although this field displays the full name of each employee, what is
actually stored is the employee's numeric EmployeeID. If I want the
employees names rather than their IDs in a query, so that I can, for
example, find all Nancy Davolio's orders, rather than all of employee 1's
orders, I need to join the Employees table to the Orders table like so ...

SELECT Orders.OrderDate, Employees.LastName, Employees.FirstName
FROM Employees INNER JOIN Orders ON Employees.EmployeeID =
Orders.EmployeeID;

You might also want to take a look at the notes on "The Evils of Lookup
Fields" at the following URL ...
http://www.mvps.org/access/lookupfields.htm

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Yes Brendan it is.

Lee

Is the part number field a lookup field?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Ken,

When I run this query, below I get all the information for all the
shipments. When I add criteria to the query (the where clause), that
is
when
I start having the problems. If I put in =8972 for the part number I
get
nothing. If I put in =291 I get all the info pretaining to the part
number
8972, because 291 is the id for part number 8972. I just don't know
why
it
will not literally take the 8972 and give me the results. Again I
think
it
may have something to do the fact that the part number field is a text
field,
rather than a number only.
My goal is to set up a menu item to check sales of one product at a
time.
Just enter the part number and see the annual usage for a part.
Needless to say I cannot get it to work.
Lee

:

Lee - I don't see any parameter or WHERE clause in the SQL statement
that
you posted. Can you explain further what you're doing when you "query
on
=
8972"? What field are you filtering based on that value? And where are
you
putting that criterion in this SQL statement?

--

Ken Snell
<MS ACCESS MVP>

Ken,

In looking further, when I query on = 8972 I get nothing, if I query
on
=291
I get all the data relating to the part number 8972. It seems since
the
partnumber field is actually a text field, it pulls the record id in
the
parts table which is 291.

The thing I need to do is be able to plug in a part number in that
query
and
get the sales figures. Is there some way to show that 8972 is a
number
in
a
text field. Like in the old Excel days of putting '8972 and it
would
left
justify, and recognize the number as a alpha numeric.

Thanx, Lee


:

TRANSFORM Sum(buyselldetails.Qty) AS SumOfQty
SELECT buyselldetails.PartNumber, Sum(buyselldetails.Qty) AS
[Total
Of
Qty]
FROM buyselldetails
GROUP BY buyselldetails.PartNumber
ORDER BY buyselldetails.PartNumber
PIVOT Format([ShipDate],"mmm") In

("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

OrderID Qty PartNumber NetPrice ShipQty ShipDate
20 1 8972 $93.00 1 7 /20/2004
19 2 8972 $93.00 2 7 /20/2004
374 2 8972 $93.00 2 9 /29/2004
244 6 8972 $93.00 6 9 /1 /2004
285 1 8972 $93.00 1 9 /28/2004
274 13 8972 $93.00 8 9 /3 /2004
275 5 8972 $93.00 5 9 /28/2004
217 1 900-030 $12.95 1 8 /25/2004
335 1 900-031 $38.30 1 9 /22/2004
217 1 900-031 $38.32 1 8 /25/2004

Auto Num Text Curr Num
Date
Num

The data types are on the bottom,

Thanx,
Lee

:

Post the SQL statement of the query that you're trying to use.
And
post some
example data from the table, along with the field names and
their
data
types.

--

Ken Snell
<MS ACCESS MVP>

Sorry Ken,
Now it pulls out everything like there were no parameters at
all.
It
doesn't matter what I put in the parameter, I get the same
number
of
records.

So what I did is exported the entire query results, 156 lines,
to
Excel.
When I opened it in Excel, the line I was looking for had a
number
of 291
in
it as the part number, which it seems is equal to the number
of
8972
in a
Access Text format.

I then did the query with the criteria equal to the number
"291",
and the
results I am looking for came up. The shipments of 8972 for
the
last
year!!

So if there is some way that we can put that "Search as
Formatted"
into my
query, I would have what I need.

I hope you don't get gray hair like me,
Lee

:

I assume that the query is saved and stored. So let's try
setting
the
data
type of the Parameter.

Open the query in design view.

Click Query | Parameters. You should see a 2-column window.

In the left column, type this string:
Enter any part of part number

In the right column, select Text from the dropdown list.

Click OK.

Save and close the query.

Now you've set the Parameter to be specifically a text
input.
See
if
that
helps.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Still nothing, I am using a query to display the data in
the
subform,
I
tried using your two suggestions. Here may be a clue, if
I
do a
find
in
that
table I also get nothing, but if I set "search field as
formatted" it
works
fine. Can I set a switch in my query to do that????

Thanx,
Lee

:

Try one of these:

Like "*'" & [Enter any part of part number] & "'*"

or

Like "*" & CStr[Enter any part of part number]) & "*"

--

Ken Snell
<MS ACCESS MVP>

I still got no records. The field I am trying to
query
is
text
and I
am
putting in a number and I get no records. The field
needs
to be
Text
due
to
the fact some of the part numbers used are a mixture
of
letters
and
numbers.

This actually is in a subform of an order, could that
be
my
problem??
I
just want to run a crosstab query to see my sales for
a
year on
one
part
number. Not to whom but just quantities. It will run
if
I
let it
loose,
but
if I enter a part number or parameter in the criteria,
I
get
nothing.

Thanx - Lee

:

Try this:

In the query criteria for part number, enter:

Like "*"& [Enter any part of part number]&"*"

This will prompt for input and will find all
records
where part number contains the input string.

Hope this helps.

-----Original Message-----
I have a part number field that had to be defined
as
text
due to the make up
of the part numbers. I need to query for a number
and
it
will not find them.
How can I make the query come up with the value I
am
looking for?
.

 

[Ken Snell [MVP]]

ah, good catch, Brendan.... I had completely ignored this possibility!

--

Ken Snell
<MS ACCESS MVP>

Is the part number field a lookup field?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Ken,

When I run this query, below I get all the information for all the
shipments. When I add criteria to the query (the where clause), that is
when
I start having the problems. If I put in =8972 for the part number I get
nothing. If I put in =291 I get all the info pretaining to the part
number
8972, because 291 is the id for part number 8972. I just don't know why
it
will not literally take the 8972 and give me the results. Again I think
it
may have something to do the fact that the part number field is a text
field,
rather than a number only.
My goal is to set up a menu item to check sales of one product at a time.
Just enter the part number and see the annual usage for a part.
Needless to say I cannot get it to work.
Lee

Lee - I don't see any parameter or WHERE clause in the SQL statement that
you posted. Can you explain further what you're doing when you "query on
=
8972"? What field are you filtering based on that value? And where are
you
putting that criterion in this SQL statement?

--

Ken Snell
<MS ACCESS MVP>

Ken,

In looking further, when I query on = 8972 I get nothing, if I query on
=291
I get all the data relating to the part number 8972. It seems since
the
partnumber field is actually a text field, it pulls the record id in
the
parts table which is 291.

The thing I need to do is be able to plug in a part number in that
query
and
get the sales figures. Is there some way to show that 8972 is a number
in
a
text field. Like in the old Excel days of putting '8972 and it would
left
justify, and recognize the number as a alpha numeric.

Thanx, Lee


:

TRANSFORM Sum(buyselldetails.Qty) AS SumOfQty
SELECT buyselldetails.PartNumber, Sum(buyselldetails.Qty) AS [Total
Of
Qty]
FROM buyselldetails
GROUP BY buyselldetails.PartNumber
ORDER BY buyselldetails.PartNumber
PIVOT Format([ShipDate],"mmm") In

("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

OrderID Qty PartNumber NetPrice ShipQty ShipDate
20 1 8972 $93.00 1 7 /20/2004
19 2 8972 $93.00 2 7 /20/2004
374 2 8972 $93.00 2 9 /29/2004
244 6 8972 $93.00 6 9 /1 /2004
285 1 8972 $93.00 1 9 /28/2004
274 13 8972 $93.00 8 9 /3 /2004
275 5 8972 $93.00 5 9 /28/2004
217 1 900-030 $12.95 1 8 /25/2004
335 1 900-031 $38.30 1 9 /22/2004
217 1 900-031 $38.32 1 8 /25/2004

Auto Num Text Curr Num
Date
Num

The data types are on the bottom,

Thanx,
Lee

:

Post the SQL statement of the query that you're trying to use. And
post some
example data from the table, along with the field names and their
data
types.

--

Ken Snell
<MS ACCESS MVP>

Sorry Ken,
Now it pulls out everything like there were no parameters at all.
It
doesn't matter what I put in the parameter, I get the same number
of
records.

So what I did is exported the entire query results, 156 lines, to
Excel.
When I opened it in Excel, the line I was looking for had a
number
of 291
in
it as the part number, which it seems is equal to the number of
8972
in a
Access Text format.

I then did the query with the criteria equal to the number "291",
and the
results I am looking for came up. The shipments of 8972 for the
last
year!!

So if there is some way that we can put that "Search as
Formatted"
into my
query, I would have what I need.

I hope you don't get gray hair like me,
Lee

:

I assume that the query is saved and stored. So let's try
setting
the
data
type of the Parameter.

Open the query in design view.

Click Query | Parameters. You should see a 2-column window.

In the left column, type this string:
Enter any part of part number

In the right column, select Text from the dropdown list.

Click OK.

Save and close the query.

Now you've set the Parameter to be specifically a text input.
See
if
that
helps.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Still nothing, I am using a query to display the data in the
subform,
I
tried using your two suggestions. Here may be a clue, if I
do a
find
in
that
table I also get nothing, but if I set "search field as
formatted" it
works
fine. Can I set a switch in my query to do that????

Thanx,
Lee

:

Try one of these:

Like "*'" & [Enter any part of part number] & "'*"

or

Like "*" & CStr[Enter any part of part number]) & "*"

--

Ken Snell
<MS ACCESS MVP>

I still got no records. The field I am trying to query
is
text
and I
am
putting in a number and I get no records. The field
needs
to be
Text
due
to
the fact some of the part numbers used are a mixture of
letters
and
numbers.

This actually is in a subform of an order, could that be
my
problem??
I
just want to run a crosstab query to see my sales for a
year on
one
part
number. Not to whom but just quantities. It will run if
I
let it
loose,
but
if I enter a part number or parameter in the criteria, I
get
nothing.

Thanx - Lee

:

Try this:

In the query criteria for part number, enter:

Like "*"& [Enter any part of part number]&"*"

This will prompt for input and will find all records
where part number contains the input string.

Hope this helps.

-----Original Message-----
I have a part number field that had to be defined as
text
due to the make up
of the part numbers. I need to query for a number and
it
will not find them.
How can I make the query come up with the value I am
looking for?
.

 

[Guest]

Thanx to you both for your help with this problem. I figured it was
something I did wrong. I will give it a try and let you know how it goes.

Ken, did you go gray???

Lee

ah, good catch, Brendan.... I had completely ignored this possibility!

--

Ken Snell
<MS ACCESS MVP>

Is the part number field a lookup field?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Ken,

When I run this query, below I get all the information for all the
shipments. When I add criteria to the query (the where clause), that is
when
I start having the problems. If I put in =8972 for the part number I get
nothing. If I put in =291 I get all the info pretaining to the part
number
8972, because 291 is the id for part number 8972. I just don't know why
it
will not literally take the 8972 and give me the results. Again I think
it
may have something to do the fact that the part number field is a text
field,
rather than a number only.
My goal is to set up a menu item to check sales of one product at a time.
Just enter the part number and see the annual usage for a part.
Needless to say I cannot get it to work.
Lee

:

Lee - I don't see any parameter or WHERE clause in the SQL statement that
you posted. Can you explain further what you're doing when you "query on
=
8972"? What field are you filtering based on that value? And where are
you
putting that criterion in this SQL statement?

--

Ken Snell
<MS ACCESS MVP>

Ken,

In looking further, when I query on = 8972 I get nothing, if I query on
=291
I get all the data relating to the part number 8972. It seems since
the
partnumber field is actually a text field, it pulls the record id in
the
parts table which is 291.

The thing I need to do is be able to plug in a part number in that
query
and
get the sales figures. Is there some way to show that 8972 is a number
in
a
text field. Like in the old Excel days of putting '8972 and it would
left
justify, and recognize the number as a alpha numeric.

Thanx, Lee


:

TRANSFORM Sum(buyselldetails.Qty) AS SumOfQty
SELECT buyselldetails.PartNumber, Sum(buyselldetails.Qty) AS [Total
Of
Qty]
FROM buyselldetails
GROUP BY buyselldetails.PartNumber
ORDER BY buyselldetails.PartNumber
PIVOT Format([ShipDate],"mmm") In

("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

OrderID Qty PartNumber NetPrice ShipQty ShipDate
20 1 8972 $93.00 1 7 /20/2004
19 2 8972 $93.00 2 7 /20/2004
374 2 8972 $93.00 2 9 /29/2004
244 6 8972 $93.00 6 9 /1 /2004
285 1 8972 $93.00 1 9 /28/2004
274 13 8972 $93.00 8 9 /3 /2004
275 5 8972 $93.00 5 9 /28/2004
217 1 900-030 $12.95 1 8 /25/2004
335 1 900-031 $38.30 1 9 /22/2004
217 1 900-031 $38.32 1 8 /25/2004

Auto Num Text Curr Num
Date
Num

The data types are on the bottom,

Thanx,
Lee

:

Post the SQL statement of the query that you're trying to use. And
post some
example data from the table, along with the field names and their
data
types.

--

Ken Snell
<MS ACCESS MVP>

Sorry Ken,
Now it pulls out everything like there were no parameters at all.
It
doesn't matter what I put in the parameter, I get the same number
of
records.

So what I did is exported the entire query results, 156 lines, to
Excel.
When I opened it in Excel, the line I was looking for had a
number
of 291
in
it as the part number, which it seems is equal to the number of
8972
in a
Access Text format.

I then did the query with the criteria equal to the number "291",
and the
results I am looking for came up. The shipments of 8972 for the
last
year!!

So if there is some way that we can put that "Search as
Formatted"
into my
query, I would have what I need.

I hope you don't get gray hair like me,
Lee

:

I assume that the query is saved and stored. So let's try
setting
the
data
type of the Parameter.

Open the query in design view.

Click Query | Parameters. You should see a 2-column window.

In the left column, type this string:
Enter any part of part number

In the right column, select Text from the dropdown list.

Click OK.

Save and close the query.

Now you've set the Parameter to be specifically a text input.
See
if
that
helps.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Still nothing, I am using a query to display the data in the
subform,
I
tried using your two suggestions. Here may be a clue, if I
do a
find
in
that
table I also get nothing, but if I set "search field as
formatted" it
works
fine. Can I set a switch in my query to do that????

Thanx,
Lee

:

Try one of these:

Like "*'" & [Enter any part of part number] & "'*"

or

Like "*" & CStr[Enter any part of part number]) & "*"

--

Ken Snell
<MS ACCESS MVP>

I still got no records. The field I am trying to query
is
text
and I
am
putting in a number and I get no records. The field
needs
to be
Text
due
to
the fact some of the part numbers used are a mixture of
letters
and
numbers.

This actually is in a subform of an order, could that be
my
problem??
I
just want to run a crosstab query to see my sales for a
year on
one
part
number. Not to whom but just quantities. It will run if
I
let it
loose,
but
if I enter a part number or parameter in the criteria, I
get
nothing.

Thanx - Lee

:

Try this:

In the query criteria for part number, enter:

Like "*"& [Enter any part of part number]&"*"

This will prompt for input and will find all records
where part number contains the input string.

Hope this helps.

-----Original Message-----
I have a part number field that had to be defined as
text
due to the make up
of the part numbers. I need to query for a number and
it
will not find them.
How can I make the query come up with the value I am
looking for?
.

 

[Brendan Reynolds]

Ah, but it was the questions you asked that elicited the information that
drew me toward that conclusion, Ken!

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

ah, good catch, Brendan.... I had completely ignored this possibility!

--

Ken Snell
<MS ACCESS MVP>

Is the part number field a lookup field?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Ken,

When I run this query, below I get all the information for all the
shipments. When I add criteria to the query (the where clause), that
is
when
I start having the problems. If I put in =8972 for the part number I get
nothing. If I put in =291 I get all the info pretaining to the part
number
8972, because 291 is the id for part number 8972. I just don't know
why
it
will not literally take the 8972 and give me the results. Again I
think
it
may have something to do the fact that the part number field is a text
field,
rather than a number only.
My goal is to set up a menu item to check sales of one product at a time.
Just enter the part number and see the annual usage for a part.
Needless to say I cannot get it to work.
Lee

:

Lee - I don't see any parameter or WHERE clause in the SQL statement that
you posted. Can you explain further what you're doing when you "query on
=
8972"? What field are you filtering based on that value? And where are
you
putting that criterion in this SQL statement?

--

Ken Snell
<MS ACCESS MVP>

Ken,

In looking further, when I query on = 8972 I get nothing, if I query on
=291
I get all the data relating to the part number 8972. It seems since
the
partnumber field is actually a text field, it pulls the record id in
the
parts table which is 291.

The thing I need to do is be able to plug in a part number in that
query
and
get the sales figures. Is there some way to show that 8972 is a number
in
a
text field. Like in the old Excel days of putting '8972 and it
would
left
justify, and recognize the number as a alpha numeric.

Thanx, Lee


:

TRANSFORM Sum(buyselldetails.Qty) AS SumOfQty
SELECT buyselldetails.PartNumber, Sum(buyselldetails.Qty) AS
[Total
Of
Qty]
FROM buyselldetails
GROUP BY buyselldetails.PartNumber
ORDER BY buyselldetails.PartNumber
PIVOT Format([ShipDate],"mmm") In

("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

OrderID Qty PartNumber NetPrice ShipQty ShipDate
20 1 8972 $93.00 1 7 /20/2004
19 2 8972 $93.00 2 7 /20/2004
374 2 8972 $93.00 2 9 /29/2004
244 6 8972 $93.00 6 9 /1 /2004
285 1 8972 $93.00 1 9 /28/2004
274 13 8972 $93.00 8 9 /3 /2004
275 5 8972 $93.00 5 9 /28/2004
217 1 900-030 $12.95 1 8 /25/2004
335 1 900-031 $38.30 1 9 /22/2004
217 1 900-031 $38.32 1 8 /25/2004

Auto Num Text Curr Num
Date
Num

The data types are on the bottom,

Thanx,
Lee

:

Post the SQL statement of the query that you're trying to use. And
post some
example data from the table, along with the field names and
their
data
types.

--

Ken Snell
<MS ACCESS MVP>

Sorry Ken,
Now it pulls out everything like there were no parameters at all.
It
doesn't matter what I put in the parameter, I get the same number
of
records.

So what I did is exported the entire query results, 156 lines, to
Excel.
When I opened it in Excel, the line I was looking for had a
number
of 291
in
it as the part number, which it seems is equal to the number
of
8972
in a
Access Text format.

I then did the query with the criteria equal to the number "291",
and the
results I am looking for came up. The shipments of 8972 for the
last
year!!

So if there is some way that we can put that "Search as
Formatted"
into my
query, I would have what I need.

I hope you don't get gray hair like me,
Lee

:

I assume that the query is saved and stored. So let's try
setting
the
data
type of the Parameter.

Open the query in design view.

Click Query | Parameters. You should see a 2-column window.

In the left column, type this string:
Enter any part of part number

In the right column, select Text from the dropdown list.

Click OK.

Save and close the query.

Now you've set the Parameter to be specifically a text
input.
See
if
that
helps.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Still nothing, I am using a query to display the data in the
subform,
I
tried using your two suggestions. Here may be a clue, if
I
do a
find
in
that
table I also get nothing, but if I set "search field as
formatted" it
works
fine. Can I set a switch in my query to do that????

Thanx,
Lee

:

Try one of these:

Like "*'" & [Enter any part of part number] & "'*"

or

Like "*" & CStr[Enter any part of part number]) & "*"

--

Ken Snell
<MS ACCESS MVP>

I still got no records. The field I am trying to
query
is
text
and I
am
putting in a number and I get no records. The field
needs
to be
Text
due
to
the fact some of the part numbers used are a mixture
of
letters
and
numbers.

This actually is in a subform of an order, could that be
my
problem??
I
just want to run a crosstab query to see my sales for a
year on
one
part
number. Not to whom but just quantities. It will run if
I
let it
loose,
but
if I enter a part number or parameter in the criteria, I
get
nothing.

Thanx - Lee

:

Try this:

In the query criteria for part number, enter:

Like "*"& [Enter any part of part number]&"*"

This will prompt for input and will find all records
where part number contains the input string.

Hope this helps.

-----Original Message-----
I have a part number field that had to be defined
as
text
due to the make up
of the part numbers. I need to query for a number and
it
will not find them.
How can I make the query come up with the value I am
looking for?
.

 

[Ken Snell [MVP]]

Ah, but it was the questions you asked that elicited the information that
drew me toward that conclusion, Ken!

< g >

 

[Ken Snell [MVP]]

Thanx to you both for your help with this problem. I figured it was
something I did wrong. I will give it a try and let you know how it goes.

Ken, did you go gray???

Lee

Too late... I already was gray!