query a number stored as text

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?

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?
.

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

" wrote:

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?
.

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

"Lee" wrote in message
...
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

" wrote:

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?
.

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

"Ken Snell [MVP]" wrote:

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

"Lee" wrote in message
...
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

" wrote:

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?
.

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

"Lee" wrote in message
...
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

"Ken Snell [MVP]" wrote:

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

"Lee" wrote in message
...
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

" wrote:

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?
.

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

"Ken Snell [MVP]" wrote:

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

"Lee" wrote in message
...
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

"Ken Snell [MVP]" wrote:

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

"Lee" wrote in message
...
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

" wrote:

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?
.

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

"Lee" wrote in message
...
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

"Ken Snell [MVP]" wrote:

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

"Lee" wrote in message
...
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

"Ken Snell [MVP]" wrote:

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

"Lee" wrote in message
...
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

" wrote:

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?
.

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

"Ken Snell [MVP]" wrote:

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

"Lee" wrote in message
...
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

"Ken Snell [MVP]" wrote:

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

"Lee" wrote in message
...
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

"Ken Snell [MVP]" wrote:

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

"Lee" wrote in message
...
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

" wrote:

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,

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


"Lee" wrote:

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

"Ken Snell [MVP]" wrote:

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

"Lee" wrote in message
...
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

"Ken Snell [MVP]" wrote:

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

"Lee" wrote in message
...
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

"Ken Snell [MVP]" wrote:

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

"Lee" wrote in message
...
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

" wrote:

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?
.