Switching Access to SQL

[Hlatigo]

I have a problem with transferring Access to SQL that I kinda pushed back onto the back burner but I can no longer procrastinate on the issue.

When my boss switch our Access database over to SQL our system was not working correctly because one of the queries (or something) showed a -1, 0 where SQL used 0, 1 as a true and false indicator.

The problem is, how do I change hundreds of queries that use -1, 0 over to the new system. I can’t really use find and replace or run a loop to change all the -1, 0 because there will be times where I really wanted a -1 or 0 in the code. Further more, if there is some code to change this properly, how would I make it run through all of our queries?

Any insight you can provide would be super great!


B

 

[Corticus]

Hi,

What do you mean by transfering your Access db to SQL?

Are you testing for true/false in the querries? Usually, if you have a field holding a true/false value, it's in a table. If its in a query, the query would be getting the value from a table.

Assuming you need to correct tables with -1/1 reversed;

Add a field to the table that need to have the -1/1 thing fixed. Set the data type to number. It will hold the new, correct boolean values which will be determined in an update query.

Make an update query in design view (just like a select query, but change query type to 'update'). Add the table that has the backwards -1/1. Double click on the field that is your primary key, double click on the field housing the boolean value you need to change, and double click on the new field for the new boolean value. These three fields should show up under 'field'.

Set the criteria for your primary key to the field name of the primary key. In other words, if the primary key field name is number, set the criteria to [number].

In 'update to', add this expression:
IIf([boolean]=-1,1)
where [boolean] is the field that contains the current boolean values that need to be changed.

Run the update query and that should add to your new column, a 1 where every -1 is. You can then delete the old yes/no, and rename the new one.

This is kind of tricky, but I just did it, so if your having trouble, I could send you an example, or you could just send me the db and I'll take a look.

HTH,
Corticus
This message was edited by Corticus on 2002-11-26 16:09

 

[Hlatigo]

Hey there!!

Thanks for replying. I havent tried your method yet but would love to see any example you might have for I am a very beginner at this.

 

[Corticus]

Post your e-mail. If you add it to your profile, you won't have to keep posting it.

Corticus

 

[Hlatigo]

Email has been posted.

And I spoke with my boss and it appears that we have already done what you posted. But you were able to confirm what we did because we were not 100% sure. What I was thinking is to write some sort of VB code that will search all my queries for Yes/No fields and once one is found to pull in the fields prior to that so I can see what is being done with the answer.

The reason I was thinking that is because depending on the answer I might be using it to contiue a calculation. Is there such a way to write code to loop through each query in the database?

 

[Corticus]

Hi,

I think the approach of trying to update all your querries with a loop is too complicated. All of your querries have to be getting data from tables, so I think you need to fix the values in the source tables. You could make an update query in the manner I described above, but include all the tables that have true/false fields.

Another thing, the formula I gave:
IIf([boolean]=-1,1)
Doesn't keep false values. It should be:
IIf([boolean]=-1,1,IIF([boolean]=0,0))

So zeros stay zero, and don't become null.

HTH,

 

[Hlatigo]

OK,

Let me give it a shot. Cross your fingers...hahaha

 

[Corticus]

Okay,

I sent you the sample. The db has one table, and one action query. You'll notice on the table there is a true/false field filled with data, and a new boolean field which is blank. When you run the update query, the blank field on the table will be updated with the new boolean values, -1 now 1, and 0 stays 0.

HTH,

 

[Hlatigo]

Hello Corticus!!

I never recieved the sample. Is it possible for you to send it again?

Thanks a bunch, hope you had a pleasant holiday.

B

 

[Corticus]

That's wierd, I sent it,

I've had some mail returned unsendable, though. Sorry, its on its way.

And yes, I'm fat and happy now

take it easy,