Changing data type (2000)

Topic

New Reply

I started to build a new form in my database and found that I would first need to build a query to draw data from two different tables. To be a little more specific, I have two tables that I need to relate. One table (tbl_Catalog) has a field I call “BidWinnerID.” The second table (tbl_Bidders) has a field I call “BidderNbr.” The bidder number identifies a person who has signed up to bid on items at a fund-raiser auction. The Catalog table includes the BidWinnerID field to identify the bidder who won a given item (i.e., submitted the highest bid) in the auction.

I goofed when I started building the DB. The BidderNbr field in the Bidders table is data type Number (long integer). The BidWinnerID field in the Catalog table is a text field! When I spotted the problem, I immediately tried to change the text field to a number field. Unhappily, I have been unsuccessful so far because (as I’m sure you professionals already know) Access wants me to first delete all my relationships.

Well, I went first to the Relationships window, where I had one and only one relationship showing (between tbl_Catalog and tbl_Donors). I deleted that relationship with the plan to return and reestablish it. But Access keeps telling me over and over that I can’t change the data type until I’ve deleted relationships…. So I’ve gone into all my queries where relationships exist and deleted them. I can’t find any more relationships, but I keep seeing that error message.

OK, I repent for having selected the wrong data type in the first place–shoulda gone with Number in both cases from “jump street,” yes. But now…how can I fix my problem without dumping my entire database and starting over? I’ve got a couple of weeks of work invested in this thing, and I have just a couple of days before we have to put this thing to work.

Reply | Quote

Replies

Is your database split? If so, you may have a relationship set in the back-end that you need to alter. But you are probably already looking there. Another possibility is that the relationship window isn’t showing all the relationships. There is a button that will cause that to happen when you click it. That’s about all the things I can think of right off hand.

Reply | Quote

Thanks, Wendell. I clicked that little icon and found the “missing link.” Problem solved.

Reply | Quote

Just for clarity’s sake, to display ALL defined relationships in the Relations window, click the button whose icon I have attached. This probably is the button Wendell meant in his reply.

Reply | Quote

Lucas:

I’m glad you already found the hidden link, so this post is more for reference than anything else. It may be the case that you can’t or don’t want to change the data type for one of the fields you must use as a linked field in your query; it may not be practicable or it’s a linked table, or any of a number of other possibilities. You can get around the “Data type mismatch” error by editing the SQL generated by the query designer.

So, assume you still have your original table design, and you have joined the tables in Design View only to get the dreaded “Data type mismatch” error. No problem. Choose to view the SQL View, and you’ll see something like:

SELECT tbl_Catalog.BidWinnerID, tbl_Bidders.BidderNbr
FROM tbl_Catalog
INNER JOIN tbl_Bidders
ON tbl_Catalog.BidWinnerID = tbl_Bidders.BidderNbr;

You can use the conversion function CLng to transform the value in BidWinnerID on the fly to a number, and that number is used in the join. So you’d edit the SQL to be:

SELECT tbl_Catalog.BidWinnerID, tbl_Bidders.BidderNbr
FROM tbl_Catalog
INNER JOIN tbl_Bidders
ON CLng(tbl_Catalog.BidWinnerID) = tbl_Bidders.BidderNbr;

If you try to switch back to Design View after making the SQL edit, Access will yell at you saying it can’t represent the join graphically. If you’re happy with the results, though, no big whoop. Or simply delete CLng function and Access will display the graphical view again.

Reply | Quote

Shane, you’ve got me all excited now! While I (kinda) fixed the problem I wrote about at first, I now have a new situation that your expertise might help me solve.

I have constructed a form with a selector combo box. The user will enter the item number into that combo box to pull up the details about a particular item in the auction: Item number, item name, the winning bid, and the Bidder ID of the person who submitted the highest bid. The combo box will, of course, let the user select an item number from the dropdown list. It would really be nice for the items to appear in numerical order, but the “Program Number” field is text, and you know what that means!

So what I’m trying to do now is to get the list to sort numerically–1, 2, 3…, not 1, 10, 11…. But there’s more (there’s always more, y’know). My catalog has three major divisions: Live Auction, Silent Auction, and Raffle. The raffle items have no numbers and do not figure in this form at all. Silent Auction items are numbered 1, 2, 3…. The Live Auction items, however, have numbers like L1, L2, L3 and so on to distinguish them in the printed program from the Silent Auction items. So in this list, I have 1 and L1, 2 and L2…. At present, the drop-down list displays all the plain-vanilla numbers first, then those that have the letter L in front of them.

I do need the user to be able to key in, say, L8 and get that record. It works as is, so maybe I should just leave well enough alone…? I’ll rely on your advice and that of anyone else who’d like to comment. By the way, here’s the SQL from the query that made the table that is the datasource for this form.

SELECT tbl_Bidders.BidderID, tbl_Bidders.BidderNbr, tbl_Catalog.BidWinner, tbl_Catalog.BidderIDWin, tbl_Bidders.BidderFName, tbl_Bidders.BidderMI, tbl_Bidders.BidderLName, tbl_Bidders.BidderAddr1, tbl_Bidders.BidderAdd2, tbl_Bidders.CityID, tbl_Bidders.BidderCity, tbl_Bidders.BidderState, tbl_Bidders.BidderPhone, tbl_Catalog.ProgramNbr, tbl_Catalog.ItemName, tbl_Catalog.ItemStatedValue, tbl_Catalog.Donors, ([BidWinner]-[ItemStatedValue]) AS TaxBenefit
FROM tbl_Bidders INNER JOIN tbl_Catalog ON tbl_Bidders.BidderNbr = tbl_Catalog.BidderIDWin;

Reply | Quote

I’m glad you found it interesting, Lucas!

So, your users want to see the items in the combo box listed as 1, L1, 2, L2, 3, L3, etc., yes? I’d add a new column to the query that the combo box is based on, and use that column for sorting the combo box. To get it to sort properly, we’ll use the number if it’s a Silent Auction, and extract the number from Ln if it’s a Live Auction. For brevity’s sake, I’m going to assume you know how to edit the query a combo box is based on; if you don’t, post back up.

The new column you’d insert would look like this:
Sort: IIf(Left([ProgramNum],1)=”L”,CDbl(Right([ProgramNum],Len([ProgramNum])-1)),CDbl([ProgramNum]))

Logically, it reads: If the left most character in ProgramNum is a “L”, grab whatever is to the right of the “L”, and convert it to a number, Else, just convert ProgramNum to a number. Sort this new column, which I’ve creatively named “Sort”, ascending. Hide this column in the combo box, make sure your bound column is still correct, and it should work out. I’ve got a dummy table and form in an Access db that I can post up if it’ll help illustrate. Best of luck!

Reply | Quote

Thanks a heap, Shane! That did the trick! This business of adding “L” to that one series of numbers was not my idea, but I’ve had to work with it. Your advice made a big difference.

Reply | Quote