Re: query a number stored as text
From: Brendan Reynolds (brenreyn)
Date: 10/12/04
- Next message: Dmiller444: "Using the IN operator and wildcard"
- Previous message: Rick B: "Re: subtracting hours"
- In reply to: Lee: "Re: query a number stored as text"
- Next in thread: Ken Snell [MVP]: "Re: query a number stored as text"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 12 Oct 2004 17:27:48 +0100
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. "Lee" <Lee@discussions.microsoft.com> wrote in message news:1708E379-9947-4E55-A059-43351AB3B7E3@microsoft.com... > Yes Brendan it is. > > Lee > > "Brendan Reynolds" wrote: > >> 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. >> >> >> "Lee" <Lee@discussions.microsoft.com> wrote in message >> news:45E02996-6C3A-4189-AABD-DA44B408BBD0@microsoft.com... >> > 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 >> > >> > "Ken Snell [MVP]" wrote: >> > >> >> 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> >> >> >> >> "Lee" <Lee@discussions.microsoft.com> wrote in message >> >> news:3AE834AC-B817-4733-B69B-49BD3D2F30AA@microsoft.com... >> >> > 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" <Lee@discussions.microsoft.com> wrote in message >> >> > > > news:5DD9D402-FF2E-4939-8654-73A0D7E254CD@microsoft.com... >> >> > > > > 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" <Lee@discussions.microsoft.com> wrote in message >> >> > > > > > news:394EA3BF-9344-4D84-AE49-1C75BEEBB786@microsoft.com... >> >> > > > > > > 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" <Lee@discussions.microsoft.com> wrote in message >> >> > > > > > > > news:F812790D-69A3-4F7F-BFE8-BB17CD5ED6C9@microsoft.com... >> >> > > > > > > > > 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 >> >> > > > > > > > > >> >> > > > > > > > > "anonymous@discussions.microsoft.com" 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? >> >> > > > > > > > > > >. >> >> > > > > > > > > > > >> >> > > > > > > > > > >> >> > > > > > > > >> >> > > > > > > > >> >> > > > > > > > >> >> > > > > > >> >> > > > > > >> >> > > > > > >> >> > > > >> >> > > > >> >> > > > >> >> >> >> >> >> >> >> >>
- Next message: Dmiller444: "Using the IN operator and wildcard"
- Previous message: Rick B: "Re: subtracting hours"
- In reply to: Lee: "Re: query a number stored as text"
- Next in thread: Ken Snell [MVP]: "Re: query a number stored as text"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
Loading
