New to SQL - Help needed with simple query

[DrAvi via AccessMonster.com]

Hello,

I have some experience with Access, using it as Backend and Frontend. I'm
trying to build new project using SQL express as backend.

I got some troubles with a simple task: when ticking on option box placed on
Form, I want only TRUE results to return from a query (which is the source of
the form), and when not ticked –all results to be presented (TTUE and FALSE).

With a simple ACCDB as frontend, connected to SQL through ODBC database, I
get it quite easily, using simple criteria built with the builder, placed
under [FinalDealtWith] which is the field need to be checked in the table :
"IIf([Forms]![FrmNewFinalList_Sub]![Opt1]=-1,-1,[dbo_TblLoadNewFinal]!
[FinalDealtWith])"

How do I use SQL query in ADP that will do this simple task? Or, how can I
subject the criteria to cases happened on my frontend form?

And last question – what are the advantages of using ADP connected to SQL
over Accdb connected to SQL through ODBC database?


Thank you all,

DrAvi

 

[Sylvain Lafontaine]

The constant values True and False don't exist on SQL-Server. If you are
using a bit field, True will be stored as 1 by Access but with an integer
field, it will be stored as -1. The easiest way to deal with this would be
to test for "= 0" and "<> 0" when looking for either false and true values.

Also, always apply the latest service pack and hotfixe for whatever version
of Access you have and in the case of bit field, don't use nullable bit
field (set their nullable property to false (don't allow for null value) and
their default value to 0 (or 1)) as Access doesn't seem to like bit field
with a null value. Don't use the tri-state property for the checkbox either.

Finally, your last question would require a lengthy response, so I won't
answer it here at this moment but I will soon start a blog and this is one
of the questions that I will try to answer there.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[DrAvi via AccessMonster.com]

Hello Sylvain and thank you for the answer

The problem I'm trying to solve is focused on two issues that new to me:

1. How am I integrating "IF" or "IIF" to "WHERE" statement in SQL
2. How can I combine Variables which placed on my frontend form to the SQL
query code.

This is the WHERE statement, built in Accdb query:

"IIf([Forms]![FrmNewFinalList_Sub]![Opt1]=-1,-1,[dbo_TblLoadNewFinal]!
[FinalDealtWith])"

Could you suggest how my "WHERE" code line will be using SQL query?

Thank you, DrAvi

The constant values True and False don't exist on SQL-Server. If you are
using a bit field, True will be stored as 1 by Access but with an integer
field, it will be stored as -1. The easiest way to deal with this would be
to test for "= 0" and "<> 0" when looking for either false and true values.

Also, always apply the latest service pack and hotfixe for whatever version
of Access you have and in the case of bit field, don't use nullable bit
field (set their nullable property to false (don't allow for null value) and
their default value to 0 (or 1)) as Access doesn't seem to like bit field
with a null value. Don't use the tri-state property for the checkbox either.

Finally, your last question would require a lengthy response, so I won't
answer it here at this moment but I will soon start a blog and this is one
of the questions that I will try to answer there.

Hello,

[quoted text clipped - 24 lines]

DrAvi

 

[Sylvain Lafontaine]

For the IIF statements, we have to replace them with a CASE statement if you
are using these IIF in the SELECT part of the query. You have already made
a mention of CASE in your original post, so I thought that you were OK on
this.

Also, when using in the WHERE part, you don't have to use an IIF (Access) or
a CASE statement (T-SQL). If you are using an IIF statement in your current
WHERE statement in your Accdb query, this is probably an error of design.

Sending variables is more difficult; the solution depend on where you use
them (form, subform, combobox, etc.) and if you intend to use a stored
procedure or not.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

Hello Sylvain and thank you for the answer

The problem I'm trying to solve is focused on two issues that new to me:

1. How am I integrating "IF" or "IIF" to "WHERE" statement in SQL
2. How can I combine Variables which placed on my frontend form to the SQL
query code.

This is the WHERE statement, built in Accdb query:

"IIf([Forms]![FrmNewFinalList_Sub]![Opt1]=-1,-1,[dbo_TblLoadNewFinal]!
[FinalDealtWith])"

Could you suggest how my "WHERE" code line will be using SQL query?

Thank you, DrAvi

The constant values True and False don't exist on SQL-Server. If you are
using a bit field, True will be stored as 1 by Access but with an integer
field, it will be stored as -1. The easiest way to deal with this would
be
to test for "= 0" and "<> 0" when looking for either false and true
values.

Also, always apply the latest service pack and hotfixe for whatever
version
of Access you have and in the case of bit field, don't use nullable bit
field (set their nullable property to false (don't allow for null value)
and
their default value to 0 (or 1)) as Access doesn't seem to like bit field
with a null value. Don't use the tri-state property for the checkbox
either.

Finally, your last question would require a lengthy response, so I won't
answer it here at this moment but I will soon start a blog and this is one
of the questions that I will try to answer there.

Hello,

[quoted text clipped - 24 lines]

DrAvi

 

[chrise]

I had a similar problem but I wanted users to be able to search for
True, False or Both. My solution was to replace the CheckBox with a
ComboBox. The options were NULL as '%', True as 1, or False as 0.

 

[Sylvain Lafontaine]

Yes, it's a much better idea to use a combobox than a checkbox because ADP
seems to have some problem with the tri-state mode of checkboxes. Users can
also be confused with that, especially when used as a search condition /
filter.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[DrAvi via AccessMonster.com]

Hi both and thank you for the advices.

I'm still struggling with using commands on a form (no matter what kind of)
and filtering results in query.
Could you please write an example of the code lines of SQL query which show
results according to option selected on a form?

regards, DrAvi

 

[chrise]

Keep in mind there are always 10 different ways to accomplish the same
task. This was just the one that came to me.

In my database, I set the default value of all Columns that are going
to be represented as a Checkbox to 0 and NOT NULL. It's really
difficult to check for NULL, 0, or 1. When I think of a checkbox it's
either Checked (1) or UnChecked (0).

In the Properties of your combobox:

Column Count = 2
Column Widths = 0";1.166" (Only "False" and "True" are displayed)
Row Source Type = Value List
Row Source = 0;False;1;True
Bound Column = 1 (Value of Combobox is 0 or 1)
Limit to List = Yes


If IsNull(Me.cboTest) Then
strTest = "'%'" (Note there are single quotes around the
percent sign)
Else
strTest = Me.cboTest.Value
End If


Then I write a string for the SQL Statement as:

strSQL = "SELECT * FROM tblTest "
strSQL = strSQL & "WHERE Test Like " & strTest & "
strSQL = strSQL & "ORDER BY Test;"

Me.RecordSouce = strSQL
Me.Requery

To explain the WHERE line:

If the user does not select an option for the combobox it's value will
be NULL so the line should read:
WHERE Test Like '%' (This will return all values for Test)

If the user selects False in the combobox it's value will be 0
WHERE Test Like 0

If the user selects True in the combobox it's value will be 1
WHERE Test Like 1