February 24, 2010 at 1:06 pm
I admit to being a complete novice when it comes to SQL Server. Here's the situation. I use Autodesk Revit 2010 64bit for design work, and it can export an ODBC database through SQL Server. For that reason, I installed SQL Server Express 2008. I have just one problem. A Yes/No field in Revit generates a 1 or 0. When the exported database is loaded into Access, everything is fine but if I try to create a check box to represent the Yes/No value from Revit, Access uses a -1 for yes. When a database updated in Access is imported back into Revit, Revit can't see the Yes values defined in Access.
Since the transfer is happening through SQL Server, is it possible to have SQL Server modify the integer value to change the -1 to 1?
Access will recognize 1 as yes in the check boxes, but if you click the box to change it to no and then back to yes, it replaces the 1 with -1.
February 24, 2010 at 1:14 pm
You can use the function ABS ( numeric_expression ) to make any negative number positive.
So for example, if the SQL query is something like
SELECT column1, integerColumn FROM table1
just change it to
SELECT column1, ABS(integerColumn) FROM table1
-Jen M
MidnightDBA.com
February 24, 2010 at 1:18 pm
Thank you for the quick response. I did come across that, but I'm not sure where to apply it. I've been trying to edit the database setup through SQL Managment Studio 2008, but I am completely new to this.
Can you tell me where I can apply that modifier?
February 24, 2010 at 1:23 pm
You can update the data in the column
UPDATE <TableName>
SET <ColumnName> = ABS(<ColumnName>)
Read the SQL Books Online for all the database and function syntax.
I would highly recommend that you use SSMS to do all the database work, not all these other 3rd party apps. SSMS has many wonderful tools built in for all this
Andrew SQLDBA
February 24, 2010 at 1:25 pm
jcarney 24614 (2/24/2010)
Thank you for the quick response. I did come across that, but I'm not sure where to apply it. I've been trying to edit the database setup through SQL Managment Studio 2008, but I am completely new to this.Can you tell me where I can apply that modifier?
Are you looking to convert data already stored in the database to be stored as a positive integer?
Or just to display a negative integer as a positive integer?
Display is accomplished through a similar select as already shown.
To update the data, then you would use an update statement.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 24, 2010 at 1:32 pm
Ideally, the data would be converted each time it is passed through SQL Server. I looked at using update queries in Access, but I would have to write a query for each field and then setup a macro to run it automatically. Ultimately, I will have several hundred fields that will need to have this negative sign removed when the database is passed from Access back to Revit.
February 24, 2010 at 1:49 pm
jcarney 24614 (2/24/2010)
Ideally, the data would be converted each time it is passed through SQL Server. I looked at using update queries in Access, but I would have to write a query for each field and then setup a macro to run it automatically. Ultimately, I will have several hundred fields that will need to have this negative sign removed when the database is passed from Access back to Revit.
After you pass back from access to sql server, run a single update statement.
What is the datatype of the table in sql server?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 24, 2010 at 1:56 pm
The data type for the table varies depending on the column. There are several "nvarchar" columns but the ones I am focused on are set to "int".
I know it's annoying dealing with a newbie, but I haven't been able to find any documentation that tells me where to put in the ABS function.
Thanks for your help with this.
February 24, 2010 at 2:06 pm
1. How is the data imported/exported from SQL to access and vice versa?
2. How is the access database designed? Are there multiple forms which all use the checkboxes?
February 24, 2010 at 2:25 pm
jcarney 24614 (2/24/2010)
The data type for the table varies depending on the column. There are several "nvarchar" columns but the ones I am focused on are set to "int".I know it's annoying dealing with a newbie, but I haven't been able to find any documentation that tells me where to put in the ABS function.
Thanks for your help with this.
Andrew shows how to do the Update statement using the abs function. This is probably going to be the most straight forward solution for you.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 24, 2010 at 2:26 pm
1.) Data is manually exported from Revit to a "Machine Data Source" which is configured to be the SQL Server database.
2.) The data is imported into Access from a "linked ODBC" connection to the same SQL Server database.
3.) In Access there are forms which where the check box data can be manipulated. (Ultimately I will be attempting to set up email or web based exchange so that forms can be sent out with clients providing the necessary information that then would get updated into Access)
4.) Once the data is updated in Access and saved it is manually imported from the machine data source into Revit. In Revit, the model of the building is then able to tell the design team what services or features each room should have in them based on the data being returned from Access.
This process works fine with all of the different field types with the exception of these annoying check boxes.
February 24, 2010 at 2:51 pm
If you don't want to write update statements as indicated above, you have a couple of other options.
1. You can modify the forms in access so that they do not use boolean values. This can be a bit tricky, but in essence you would remove the data binding from the checkbox control. Then you must rely on VBA scripting to update the data, behind the scenes. (And also to populate the check boxes when the form is laoded.) This is a PITA, but it has the advantage of storing the data in the format that you want up front.
2. You can develop an SSIS package to change the values.
The update statements provided above are probably your best bet. After you practice with T-SQL a bit, you may find that you can create a loop to dynamically generate the update statement in a short amount of code.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply