473,322 Members | 1,241 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

VB Access to Run SQL SELECT query

Hi,

Have a bit of a problem... I've created a form in Access and will use
the form for a user to query a table based on the selected fields.
The problem lies in that I was using checkboxes for the fields and
noticed that if I don't check a box, then the query will look
specifically in the table for where the applicable field is FALSE.
This isn't right however and decided that I should use a radio button
group so that the user can select either TRUE, FALSE, or Any. This
way the user can select all the records from a table without excluding
TRUE if the checkbox is not selected or FALSE if the checkbox is
selected. Now, because I'm using a radio group button and have to
have this "Any" selection, how do I write the code that would SELECT
from the table both TRUE and FALSE? I suppose excluding this field
from the WHERE part of the SQL statement will then find all the
records but I'm having trouble now with the SELECT statement and using
the DoCmd.RunSQL "SELECT * FROM tblWhatever WHERE ID=..." I'm also
unsure of how to pull in the fields from the form.

Using the DesignView to create a query in Access is easy but when you
have to code it with VB(A) due to the "Any" value makes this problem a
bit harder. Any tips is greatly appreciated.

Thanks,
Daryl

Jul 16 '07 #1
4 10552
On Mon, 16 Jul 2007 17:18:40 -0000, "Th**********@gmail.com"
<Th**********@gmail.comwrote:
>Hi,

Have a bit of a problem... I've created a form in Access and will use
the form for a user to query a table based on the selected fields.
The problem lies in that I was using checkboxes for the fields and
noticed that if I don't check a box, then the query will look
specifically in the table for where the applicable field is FALSE.
This isn't right however and decided that I should use a radio button
group so that the user can select either TRUE, FALSE, or Any. This
way the user can select all the records from a table without excluding
TRUE if the checkbox is not selected or FALSE if the checkbox is
selected. Now, because I'm using a radio group button and have to
have this "Any" selection, how do I write the code that would SELECT
from the table both TRUE and FALSE? I suppose excluding this field
from the WHERE part of the SQL statement will then find all the
records but I'm having trouble now with the SELECT statement and using
the DoCmd.RunSQL "SELECT * FROM tblWhatever WHERE ID=..." I'm also
unsure of how to pull in the fields from the form.

Using the DesignView to create a query in Access is easy but when you
have to code it with VB(A) due to the "Any" value makes this problem a
bit harder. Any tips is greatly appreciated.

Thanks,
Daryl
"Any" = *

Chuck
--

Jul 16 '07 #2
True is stroed as -1, false is stored as 0. That should give you the
info you need.
On Jul 16, 5:01 pm, Chuck <libb...@schoollink.netwrote:
On Mon, 16 Jul 2007 17:18:40 -0000, "The.Daryl...@gmail.com"

<The.Daryl...@gmail.comwrote:
Hi,
Have a bit of a problem... I've created a form in Access and will use
the form for a user to query a table based on the selected fields.
The problem lies in that I was using checkboxes for the fields and
noticed that if I don't check a box, then the query will look
specifically in the table for where the applicable field is FALSE.
This isn't right however and decided that I should use a radio button
group so that the user can select either TRUE, FALSE, or Any. This
way the user can select all the records from a table without excluding
TRUE if the checkbox is not selected or FALSE if the checkbox is
selected. Now, because I'm using a radio group button and have to
have this "Any" selection, how do I write the code that would SELECT
from the table both TRUE and FALSE? I suppose excluding this field
from the WHERE part of the SQL statement will then find all the
records but I'm having trouble now with the SELECT statement and using
the DoCmd.RunSQL "SELECT * FROM tblWhatever WHERE ID=..." I'm also
unsure of how to pull in the fields from the form.
Using the DesignView to create a query in Access is easy but when you
have to code it with VB(A) due to the "Any" value makes this problem a
bit harder. Any tips is greatly appreciated.
Thanks,
Daryl

"Any" = *

Chuck
--- Hide quoted text -

- Show quoted text -

Jul 16 '07 #3
On Jul 16, 5:06 pm, DavidB <je...@yahoo.comwrote:
True is stroed as -1, false is stored as 0. That should give you the
info you need.

On Jul 16, 5:01 pm, Chuck <libb...@schoollink.netwrote:
On Mon, 16 Jul 2007 17:18:40 -0000, "The.Daryl...@gmail.com"
<The.Daryl...@gmail.comwrote:
>Hi,
>Have a bit of a problem... I've created a form in Access and will use
>the form for a user to query a table based on the selected fields.
>The problem lies in that I was using checkboxes for the fields and
>noticed that if I don't check a box, then the query will look
>specifically in the table for where the applicable field is FALSE.
>This isn't right however and decided that I should use a radio button
>group so that the user can select either TRUE, FALSE, or Any. This
>way the user can select all the records from a table without excluding
>TRUE if the checkbox is not selected or FALSE if the checkbox is
>selected. Now, because I'm using a radio group button and have to
>have this "Any" selection, how do I write the code that would SELECT
>from the table both TRUE and FALSE? I suppose excluding this field
>from the WHERE part of the SQL statement will then find all the
>records but I'm having trouble now with the SELECT statement and using
>the DoCmd.RunSQL "SELECT * FROM tblWhatever WHERE ID=..." I'm also
>unsure of how to pull in the fields from the form.
>Using the DesignView to create a query in Access is easy but when you
>have to code it with VB(A) due to the "Any" value makes this problem a
>bit harder. Any tips is greatly appreciated.
>Thanks,
>Daryl
"Any" = *
Chuck
--- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -
Ahh, good stuff -Thanks David, Chuck. Didn't think about the "*"...
How would I write the code (in VB?) to set the values into proper SQL
format? I was reading about the DoCmd.RunSQL command but it does NOT
work for SELECT queries (only action). For example, I can't go into
SQL and start writing "IF combobox18.value = "True" THEN blank-
blank". I would have to write the code in VB to generate the proper
SQL statement but then how do I use the SELECT? Appreciate everything

Jul 17 '07 #4
Great, thanks for the repliedHow could I write a SELECT statement in
the VB code that would be appropriate to capture the wanted fields? I
tried using the DoCmd.RunSQL "SELECT..." but the RunSQL statement only
runs SQL action queries, not SELECT. Or is there a way to write "if"
statements in the SQL view of the query designer in MS Access?

Thanks a bunch, Daryl

DavidB wrote:
True is stroed as -1, false is stored as 0. That should give you the
info you need.
On Jul 16, 5:01 pm, Chuck <libb...@schoollink.netwrote:
On Mon, 16 Jul 2007 17:18:40 -0000, "The.Daryl...@gmail.com"

<The.Daryl...@gmail.comwrote:
>Hi,
>Have a bit of a problem... I've created a form in Access and will use
>the form for a user to query a table based on the selected fields.
>The problem lies in that I was using checkboxes for the fields and
>noticed that if I don't check a box, then the query will look
>specifically in the table for where the applicable field is FALSE.
>This isn't right however and decided that I should use a radio button
>group so that the user can select either TRUE, FALSE, or Any. This
>way the user can select all the records from a table without excluding
>TRUE if the checkbox is not selected or FALSE if the checkbox is
>selected. Now, because I'm using a radio group button and have to
>have this "Any" selection, how do I write the code that would SELECT
>from the table both TRUE and FALSE? I suppose excluding this field
>from the WHERE part of the SQL statement will then find all the
>records but I'm having trouble now with the SELECT statement and using
>the DoCmd.RunSQL "SELECT * FROM tblWhatever WHERE ID=..." I'm also
>unsure of how to pull in the fields from the form.
>Using the DesignView to create a query in Access is easy but when you
>have to code it with VB(A) due to the "Any" value makes this problem a
>bit harder. Any tips is greatly appreciated.
>Thanks,
>Daryl
"Any" = *

Chuck
--- Hide quoted text -

- Show quoted text -
Jul 17 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB...
7
by: Bob | last post by:
Currently I am using this statement to translate 3 fields in my db thru Visual Basic. I import the data from one table to another then call the IFF statements and the NewDate to translate the...
1
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a...
8
by: Alfonso Esteban Gonzalez Sencion | last post by:
I am trying to use Access as a front end for extracting information from an Oracle database. I started using linked tables but I am getting a very curious behaviour. When I consult the linked...
3
by: Dan | last post by:
I hate it when people think that their own misunderstandings are bugs in the program, but this time I think I've got something. If I run the following SQL code in Access 2000, I get unexpected...
3
by: StBond | last post by:
Hi everyone, I am new to Access and Visual Basic so things my be getting across a bit cloudy. I only started using VB for one week. I am having a little problem with the database that I am...
2
by: Arif | last post by:
Very strange problem: Executing my query against MS Access database using OleDbProvider for Access, I am getting the value for first two columns '0' instead of '1' in DataGrid. But if I connect to...
4
by: ShastriX | last post by:
Getting a weird error while trying out a query from Access 2003 on a SQL Server 2005 table. Want to compute the amount of leave taken by an emp during the year. Since an emp might be off for...
12
by: zwasdl | last post by:
Hi, I'm using MS Access to query against Oracle DB via ODBC. Is it possible to use HINT in Access? Thanks, Wei
0
MMcCarthy
by: MMcCarthy | last post by:
Rather than using the Access design view change the view to SQL. I am going to attempt to outline the general syntax used for SQL queries in Access. Angle brackets <> are used in place of some...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.