If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? . |
#3
|
|||
|
|||
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? . |
#4
|
|||
|
|||
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? . |
#5
|
|||
|
|||
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? . |
#6
|
|||
|
|||
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? . |
#7
|
|||
|
|||
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? . |
#8
|
|||
|
|||
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? . |
#9
|
|||
|
|||
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? . |
#10
|
|||
|
|||
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? . |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
find a date on sheet 2 and count text in that column | jtinne | General Discussion | 4 | October 4th, 2004 09:06 PM |
record number in a query | Dorci | Running & Setting Up Queries | 1 | July 14th, 2004 09:52 PM |
Update query by 1/2 entered number | Eb1mom | Running & Setting Up Queries | 2 | July 9th, 2004 02:07 AM |
Text field causing trouble in query (continued) | KaiRich | Running & Setting Up Queries | 2 | May 28th, 2004 01:03 PM |
Enter Numbers as Text in Social Security Number Format | Frank Kabel | Worksheet Functions | 0 | March 18th, 2004 09:17 PM |