Data Type Text to Number - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Data Type Text to Number

Katherine Bradshaw

4 years ago

I'm going back to correct something in my database and running into trouble.

I have a MemberPaymentT that at first had a PaymentType field (cash, check, credit, comp) as a text field. I managed to miss changing this when I created a PaymentTypeT with PaymentTypeID and PaymentType (1=cash, 2=check, 3=credit, 4=comp), because I had gone back and changed the text fields to their corresponding numbers without updating the data type. I only noticed the problem when I was trying to do a query and got a type join error trying to use PaymentType.

I changed the field name to PaymentTypeID and the data type to number. It seemed to work OK.

The trouble now is, I'm trying to update the queries that include the MemberPaymentT and getting an error message when I try to test-run the query 'Access can't find the name "MemberPaymentT.PaymentType"'.

This happens even if I have deleted every field related to member payments in the query. I have to click "OK" on 300+ error windows (the number of member payment records).

1. Can I get around the 300 clicks when testing a query?
2. What steps might I have forgotten in trying to update this data type in my database?

Scott Axton @Reply

4 years ago

Go into your query in design mode.  Change the PaymentType to PaymentTypeID (Access is still looking for the field you renamed.  Renaming as you have found out doesn't always propagate through out forms and queries of code.  One of the down falls.

As far as question 1 - you might be able to hit the Pause / Break key. but in this case I don't think that would work.
Unfortunately that gives you 2 alternatives.  1) click 300 times.  (be glad it wasn't 10k) or 2) go down to the task bar.  Right click and go to Task Manager.  Find Access - Right Click and click End Task.

NOT a great way to shut down your data base.  You have the potential of corrupting it but sometimes you don't have a choice.
This brings up another important reason to Work with a Copy first when making major changes AND Backup, Backup, Backup!

Katherine Bradshaw OP @Reply

4 years ago

Here's the goofy thing - the data type updated to PaymentTypeID in the MemberPayment table I could see in the query. I couldn't see any instances of any of the other tables having the old PaymentType. I deleted all the mentions of the PaymentType in the query itself, and STILL had the problem. Today I will try rebuilding the query from scratch to see if I missed something - and use a stripped-down data table so I don't have to go through all that crazy OKing of error messages. Yes, thank goodness it wasn't 10,000!)

I have backups, so it's no problem. (And a benefit of having a split database - you can just replace the backend!)

	Katherine Bradshaw OP @Reply 4 years ago
Aha! I was unsmart and didn't go fully downstream to start making the changes. When I've got queries based on queries, I need to be careful. I have a list of all my queries, but I think I also need to draw maps!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View