Access conversion to SQL

Hi all.

I have an application that currently connects to an Access database, now the 
customers are asking for a MySql and MsSql server back end database.

MySql handles True and False values through the bit field without any 
problems, MsSql doesn't.  I can't even use the words True or False in an Sql 
statement.

This means I have to write two loads of code for every transaction where 
there is a true/false value involved, a complete pain.

It also gives me the problem of setting controls true/false value by 
checking a table field:
    chkAdmin.Checked = GetField("select admin from users where 
username='Bert';", "admin")
(Get field is a routine that loads a data record and returns the field as 
the second parameter)

As SQL never returns a true or false value in this way I have to write extra 
code for every check, or checked list, etc.

Is there a way around this?

Cheers,
Tull. 


0
tclancey
4/2/2007 4:42:25 PM
access.conversion 3037 articles. 0 followers. Follow

3 Replies
606 Views

Similar Articles

[PageSpeed] 18

> problems, MsSql doesn't.  I can't even use the words True or False
> in an sql statement.

I don't have any trouble using the words True and False
in a MSsql statement.

>     chkAdmin.Checked = GetField("select admin from users where
> username='Bert';", "admin")

That works for me: you will have to be more careful with the
way you write GetField.


Access/Jet/Intel/ and others use -1 as True.

MS SQL/ C/ Digital/ and others used +1 as True.

Access/Jet will correctly translate Boolean values as long as you are
careful to only use them as Boolean's (not as numbers).

Alternatively, you can choose to only use numbers (0,1 Null), so
that your SQL is clearer, more exact, and more portable, but then
you won't be able to depend on the sign of TRUE.

Many people choose to only use FALSE (note that it has the
same value in both systems). They use NOT FALSE instead of
TRUE.

Many people choose to only use numbers instead of Booleans.

It is a real problem, but nobody solves it by using "two loads
of code".  If you are doing that, you are doing something wrong.

(david)


"tclancey" <tull@idcodeware.co.uk> wrote in message
news:OKDimXUdHHA.208@TK2MSFTNGP05.phx.gbl...
> Hi all.
>
> I have an application that currently connects to an Access database, now
the
> customers are asking for a MySql and MsSql server back end database.
>
> MySql handles True and False values through the bit field without any
> problems, MsSql doesn't.  I can't even use the words True or False in an
Sql
> statement.
>
> This means I have to write two loads of code for every transaction where
> there is a true/false value involved, a complete pain.
>
> It also gives me the problem of setting controls true/false value by
> checking a table field:
>     chkAdmin.Checked = GetField("select admin from users where
> username='Bert';", "admin")
> (Get field is a routine that loads a data record and returns the field as
> the second parameter)
>
> As SQL never returns a true or false value in this way I have to write
extra
> code for every check, or checked list, etc.
>
> Is there a way around this?
>
> Cheers,
> Tull.
>
>


0
david
4/3/2007 10:27:37 AM
I'm not so sure that I agree with you, dude

I've definitely had some problems using True and False in SQL Server.. I
always convert them to an integer..


most of the times; I move a bunch of booleans into an integer-- I use
bitwise operators for this




<david@epsomdotcomdotau> wrote in message
news:%232lIznddHHA.4188@TK2MSFTNGP02.phx.gbl...
> > problems, MsSql doesn't.  I can't even use the words True or False
> > in an sql statement.
>
> I don't have any trouble using the words True and False
> in a MSsql statement.
>
> >     chkAdmin.Checked = GetField("select admin from users where
> > username='Bert';", "admin")
>
> That works for me: you will have to be more careful with the
> way you write GetField.
>
>
> Access/Jet/Intel/ and others use -1 as True.
>
> MS SQL/ C/ Digital/ and others used +1 as True.
>
> Access/Jet will correctly translate Boolean values as long as you are
> careful to only use them as Boolean's (not as numbers).
>
> Alternatively, you can choose to only use numbers (0,1 Null), so
> that your SQL is clearer, more exact, and more portable, but then
> you won't be able to depend on the sign of TRUE.
>
> Many people choose to only use FALSE (note that it has the
> same value in both systems). They use NOT FALSE instead of
> TRUE.
>
> Many people choose to only use numbers instead of Booleans.
>
> It is a real problem, but nobody solves it by using "two loads
> of code".  If you are doing that, you are doing something wrong.
>
> (david)
>
>
> "tclancey" <tull@idcodeware.co.uk> wrote in message
> news:OKDimXUdHHA.208@TK2MSFTNGP05.phx.gbl...
> > Hi all.
> >
> > I have an application that currently connects to an Access database, now
> the
> > customers are asking for a MySql and MsSql server back end database.
> >
> > MySql handles True and False values through the bit field without any
> > problems, MsSql doesn't.  I can't even use the words True or False in an
> Sql
> > statement.
> >
> > This means I have to write two loads of code for every transaction where
> > there is a true/false value involved, a complete pain.
> >
> > It also gives me the problem of setting controls true/false value by
> > checking a table field:
> >     chkAdmin.Checked = GetField("select admin from users where
> > username='Bert';", "admin")
> > (Get field is a routine that loads a data record and returns the field
as
> > the second parameter)
> >
> > As SQL never returns a true or false value in this way I have to write
> extra
> > code for every check, or checked list, etc.
> >
> > Is there a way around this?
> >
> > Cheers,
> > Tull.
> >
> >
>
>


0
Aaron
4/25/2007 11:32:39 PM
Im not sure I agree with you david

I've had problems using the constant true or false in SQL Server


select true = ERROR
select 'true' = 'true'

so how does this explicitly convert to 0 or 1 or -1?

<david@epsomdotcomdotau> wrote in message
news:%232lIznddHHA.4188@TK2MSFTNGP02.phx.gbl...
> > problems, MsSql doesn't.  I can't even use the words True or False
> > in an sql statement.
>
> I don't have any trouble using the words True and False
> in a MSsql statement.
>
> >     chkAdmin.Checked = GetField("select admin from users where
> > username='Bert';", "admin")
>
> That works for me: you will have to be more careful with the
> way you write GetField.
>
>
> Access/Jet/Intel/ and others use -1 as True.
>
> MS SQL/ C/ Digital/ and others used +1 as True.
>
> Access/Jet will correctly translate Boolean values as long as you are
> careful to only use them as Boolean's (not as numbers).
>
> Alternatively, you can choose to only use numbers (0,1 Null), so
> that your SQL is clearer, more exact, and more portable, but then
> you won't be able to depend on the sign of TRUE.
>
> Many people choose to only use FALSE (note that it has the
> same value in both systems). They use NOT FALSE instead of
> TRUE.
>
> Many people choose to only use numbers instead of Booleans.
>
> It is a real problem, but nobody solves it by using "two loads
> of code".  If you are doing that, you are doing something wrong.
>
> (david)
>
>
> "tclancey" <tull@idcodeware.co.uk> wrote in message
> news:OKDimXUdHHA.208@TK2MSFTNGP05.phx.gbl...
> > Hi all.
> >
> > I have an application that currently connects to an Access database, now
> the
> > customers are asking for a MySql and MsSql server back end database.
> >
> > MySql handles True and False values through the bit field without any
> > problems, MsSql doesn't.  I can't even use the words True or False in an
> Sql
> > statement.
> >
> > This means I have to write two loads of code for every transaction where
> > there is a true/false value involved, a complete pain.
> >
> > It also gives me the problem of setting controls true/false value by
> > checking a table field:
> >     chkAdmin.Checked = GetField("select admin from users where
> > username='Bert';", "admin")
> > (Get field is a routine that loads a data record and returns the field
as
> > the second parameter)
> >
> > As SQL never returns a true or false value in this way I have to write
> extra
> > code for every check, or checked list, etc.
> >
> > Is there a way around this?
> >
> > Cheers,
> > Tull.
> >
> >
>
>


0
Susie
5/8/2007 1:15:24 AM
Reply:

Similar Artilces:

How OMPM Scanner (offscan) Filter by Access/Modified Date ?
Hello, I have problem to inventory excel files on very big file server, but I believe there are so many documents we no longer need to maintain. I want to skip files if the access date or modified date longer than 6 month, but don't see the OMPM providing feature about it. I currently running OMPM since 2 weeks ago and running out of time for reporting to my manager. Please help me, this is my critical assignment. -- Eldi Munggaran ...

Sql to find record in a hierarchical chain
Hi, lets say I have a table with 2 fields: myTable Field 1 - ref Field 2 - parentRef Now in the structure there could be numerous chains until you get to the top of the hieratchy (where the ref field = parentRef field). ie ref parentRef 111 222 222 333 333 444 444 444 so in this 444 is top of the chain (there will be many other records as well that are nothing to with 444 and are part of their own hierarchy). So how do I easily relate 111 to 444. Ie how do i find the ultimate top parent for a given 'ref' field. Hope anyone can give me some pointers ...

comdlg32.ocx and Access 2007
I have several Access databases that were originally written using an Access version prior to Access 2007. I am in the process of converting the databases and installing them on new machines running Win7 and Access 2007. Over the years, one of the References I commonly used was comdlg32.ocx. It does not appear that either Win7 or Access 2007 installs that particular Active X file. I can copy it from an older machine, but that seems like a strange solution. Is comdlg32.ocx a "legacy" Active X file and has it been replaced with a newer (and differently named) Activ...

Excel / VBA / SQL DB
Anybody done any work with Excel / VBA / SQL DB? Can you give me some pointers on how I could do the following all in one VBA routine: 1) From a cell variable value (ie, user enters a customer code), I query table A and put the data into a worksheet starting from say cell A1. 2) A blank row is created after the last data line in point 1 above. 3) Using the same cell variable, query table B and put the data into the worksheet starting from the row after the blank row in point 2 above. and so on. Like I've said before I work with ASP all day and know about ADO connections, commands and...

Error 2455 Closing Access 2007 database with form open
I have a form with a subform that is requeried when you select a new key for the main form from a combo box. Everything works fine - usually. But sometimes if you have the form open when you close the database down you get the following error message (twice) in a pop up. You say OK (twice) and the database closes OK "2455 you entered an expression that has an invalid reference to the property form/report" If I close the form before the database I never get the error. If I do not touch the form before you close the database I don't get the error. If I update a field by t...

Inserting Excel into Access Reports
Office XP Have a great Access application that produces a nice template (headers & footers) report into which I'd like a spreadsheet inserted before going to the printer. In the past, I'd just print the Access reports, then reload them into the inkjet printer and run the Excel spreadsheets as needed. The heat of the new color laserjet turns the paper grey if it runs through too often, so it's time to get the reports printing on one pass. Any suggestions would be welcome. I've of course also got Word XP, MS Publisher XP, as well as Adobe Acrobat, if anyone thinks it m...

Simple Access counting queries
Hi, hoping someone can help a relative newbie with a pretty simple query. My database (Access 2007) has three tables: Customers Products Purchases (many-to-one links to both of the other tables, this is basically a linking table) I have two simple queries I'd like to get out of this database, but I'm a bit stuck on how to construct the SQL. Any direction you can give me would be helpful. 1. List of all customers who have purchased 2 or more products (or 3 or more products, or 4+, etc.) 2. List of all customers who have purchased both Product A and Product B (or A, B, and C, or B an...

How to get only the year in the date format in Access
How to get only the year in the date format I.e in the table in need to display only year E.g 2005 - should be display " 05" automatically Custom format the cell as: yy -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "yanu" <yanu@discussions.microsoft.com> wrote in message news:14CE9F60-F7B9-467A-8C16-71088C31BEBA@microsoft.com... > How to get only the year in the date form...

Expression Too Complex in Access 2000
Hi, Consider the following query: SELECT crTbl.acct_1, crTbl.amount, crTbl.date FROM crTbl WHERE (((crTbl.acct_1)="Supplies") AND ((crTbl.date) Between [Forms]![crReportOptionsFrm]![startDate] And [Forms]![crReportOptionsFrm]![endDate])); The query works fine on my own computer in Access 2002. When converted to Access 2000 and used on an other computer, I get the following error: "This expression is typed incorrectly or is too complex to be evaluated. Try simplifying the expression by assigning parts of the expression to variables." The problem is with the "Between...

groups detail section totals access 2003
Hi all, I know this can be done, but haven't figured out how yet. I have what basically is a summary report that my sql comes up with for the detail rows. I want to total these rows in the report and display immediately below the detail section. I don't really want to group anything, but want to treat the whole detail section as a group. That being said, how can I get a "group footer" on the designer so I can add my total columns. If I use "sorting and grouping", it starts grouping things and that is not what I want. I don't want to use the "page foo...

Time Conversion
I need help in converting time. I am using data from a time clock which currently formats time as 1.5. When I input this data into a cell how can I have it formated to read 1 hour and 30 minutes (1:30) T.I.A. Ed What you could do in an adjacent row or column is format them as Time and then using a formula, divide the input time by 24(hours in a day). >-----Original Message----- >I need help in converting time. I am using data from a time clock which >currently formats time as 1.5. When I input this data into a cell how can I >have it formated to read 1 hour and 30 minut...

Can I set SQL mirroring across WAN?
We have a 100 Mb dedicated DR link to another data center. Want to set up a SQL 2008 database to use mirroring from primary to a standby server at the remote data center. I did a ping test and here is the result. ping -n 100 -l 5120 <target IP> Ping statistics for <target IP> Packets: Sent = 100, Received = 99, Lost = 1 (1% loss), Approximate round trip times in milli-seconds: Minimum = 8ms, Maximum = 19ms, Average = 11ms is the network latency good enough for database mirroring? Does anyone know the min. requirement? Field experience will be fine too....

Remote access to another company's Outlook calendar?
Hello! I have one client on Exchange 2003 that wants to access the calendar of an employee (consultant?) at another company running Exchange (version not known yet). How can the remote company share this person's calendar with my client? He would need to access it and add appointments to it. Thank you for the help! Gregg Hill On Mon, 30 Aug 2004 22:15:24 -0700, "Gregg Hill" <bogus@nowhere.com> wrote: >Hello! > >I have one client on Exchange 2003 that wants to access the calendar of an >employee (consultant?) at another company running Exchange (version no...

Can't connect to Exchange server after VPN access
I can connect to my office Exchange server when in the office (LAN) but I cannot connect outside with Outlook. I can only use the webmail after entering a web VPN access: - First portal for VPN : https://xxx.yyy.com --> I enter my user_VPN/password_VPN - On the next web page, I have the choice for the webmail and it's a link as https://xxx.yyy.com/go/webmail.yyy.com~ssl where I can enter my user_mail/password_mail So I can't enter the url in RPC as every slash is forbidden In my Outlook, the Exchange server (EXC.yyy.com) is not reachable by ping (or tracert) and I can't ...

Access 2007 12-17-09
I am building a contact data base for my church. How do I get the phone field to automatically format like this (xxx) xxx-xxxx when the numbers are typed in the cell? -- Thank You In the form design view select the phone control and open the properties dialog. Goto the Data tab and use the Input Mask to define the format you wish to use. If you click on the ... to the right hand side on the property you will get another dialog where there are predefined input mask or you can define your own. For instance, I use !\(999") "000\-0000;0;_ to format my telepho...

quicken file conversion?
how can i convert the data for two mutual funds from quicken to money 2003. i don't want to convert all old data of every acct in quicken 2003. any ideas welcomed ...

SQL 2008 x64 SP1 SSRS
I've installed SQL Server 2008 x64 SP1 SSRS on a server running Windows Server 2008 x64 Standard and am having major issues. I installed and configured SSRS using documentation provided by our CRM application (ConnectWise). They appeared to be pretty standard options. For SSL I chose the certificate for mydomain.com. Inside the Reporting Services Configuration Manager it shows the following URLs: Web Service URL: http://machinename:80/ReportServer https://www.mydomain.com:443/ReportServer Report Manager URL: http://machinename:80/Reports https://www.mydomain.com:443/Reports ...

Can Entourage be used to access Google Groups
I am in the process of switching from PC to Mac. In Entourage, I see this news server. Can I get some of my newsgroups in Google Groups to show on Entourage or Mac Mail? Responses off group will be welcome if I have asked something everyone except me knows. Thanks, Michael Plog On 1/31/10 3:25 PM, Michael Plog wrote: > I am in the process of switching from PC to Mac. In Entourage, I see this > news server. Can I get some of my newsgroups in Google Groups to show on > Entourage or Mac Mail? > > Responses off group will be welcome if I have asked something e...

Publish A Workbook Onto The Web, Then Access Using My Web Browser.
How do I access my spreadsheet, or workbook, on the web once I've saved it as a webpage from the file menu? I've found that even though I've save my workbook as a webpage, it does not have an http:// address which I can type in my web browser to access the workbook from my home computer or from a remote computer. Should I have to sign up with an http:// service provider or can this all be done from using Microsoft Office XP without going through a third-party? -- sigmad You have to have a website first of all, otherwise, where do you want to host this page? "sigmad&q...

VBA and SQL Query
hi, i use GP 8.0 and i want to make a custom form for a specific need. i cannot use Dexterity for this change so i really need to use VBA. however in the vba form i want to make a few sql select queries to pull client names, and call a table view, and eventually run a few update queries. is that possible to do with VBA in GP 8.0 ? Yes. You need to create an ADO connection and execute through that. Go to Customersource and search for ADO and VBA. Somewhere, I think in one of the demo links, is a file containing sample code that show you how to do this, including use of the retrievegl...

link file from virtual windows xp to access 2007 on windows 7 plat
I am trying to link a paradox db file to access 2007 db. The paradox file resides on a virtual windows xp platform within the windows 7 platform of the physical machine. How do I map this field into access? -- cblackman ...

Unhandled exception at 0x10001e80 in EXCEL.EXE: 0xC0000005: Access violation reading location 0x10001e80.
Hi All, Does somebody know what's a shit is that? I installed XP from scratch, then all service packs and update, VS etc., finally I installed the Office 2000. When I close Excel (and Word as well) I get this error. Unhandled exception at 0x10001e80 in EXCEL.EXE: 0xC0000005: Access violation reading location 0x10001e80. I installed all service packs and updated over the office, no results. My friend told me that it's a big difference how did we install Office, in what order. If I install the Office exactly after Windows main installation, then install all other packages, updates, ...

Posting a Journal Entry directly Into SQL?
Currently, we using Integration Assistant 8.0 for Excel to import about 6 Simple Journal Entries per day. This was fairly easy for the first few weeks, but now it's just getting boring. Our process goes something like this: Run a custom SQL Query (that operates on both Great Plains and other data sources) that produces a result set that is cut and pasted as a formatted Journal Entry into Excel. Then, from Excel, we just run our already-built integration package which imports the batch into Great Plains. We've already figured out how to get some basic transaction data out of Gr...

Puzzling out the Navigation Pane (Access 2007)
Hi All, Been looking at the Access 2007 Navigation Pane as a replacement for the venerable Switchboard. Soo... I can create shortcuts to objects, put those shortcuts in Custom Categories and Groups and Disable the DesignView property of the shortcut. Ok. I can run an autoexec macro to hide all native categories. Fine. What's to prevent the end user from right clicking on the shortcut and enabling DesignView again? ...

administrator denied access to system manager
done a nice silly thing, accidentally changed the permissions on the administrator account to deny full access in the exchange organisation. i used the reg hack to access the security tab. i know that's why it's hidden to prevent things like this, so please dont remind me ;) thing is, although i can do most things from another admin account, i cant get access to that security tab, even with the reg hack for that user. so i cant undo the damage. is there any way or removing those permissions using another tool? i cant add the other amin account to the delegation wizard, it says...