SunQuest
 
           Microsoft Access Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
 
User Name:
Password:
Remember me
Go Back   ASP Free Forums > Database > Microsoft Access Help
Reload this Page

Query Building

Discuss Query Building in the Microsoft Access Help forum on ASP Free.
Query Building Microsoft Access forum discussing questions and answers for the Access database. MS Access is used to handle small to medium database needs.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread ASP Free Forums Sponsor:
SlickEdit: Code in over 40 languages across 7 platforms. SlickEdit’s unmatched power, speed, and flexibility allows even the most accomplished developers to write better code faster. Download a free trial today!
  #1  
Old May 9th, 2008, 04:27 PM
Jaykappy Jaykappy is offline
Contributing User
ASP Free Novice (500 - 999 posts)
  
Join Date: Feb 2005
Posts: 649 Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 4 Days 20 h 54 m 11 sec
Reputation Power: 22
Query Building
Question:

I have 8 Textboxes making up 4 potential queries

Text1a Text1b
Text2a Text2b
Text3a Text3b
Text4a Text4b

The Text As are Fields names in a table
The Text Bs are attributes in the table

So I am trying to build aquery that will update a database table...

This example shows only 2 of the 4 above queariable items...
Ex.
Text1a = Type Text1b = x
Text2a = Layer Text2b = G
String needs to read:
Type = x and Layer = G

There are 2 parts I can see:
First:
I need to query the As to determine which ones have values. If they are going to be included in the string they have to have a value. So this is the first area of concern. Find out which ones have values, and ignore those that dont have values

Second:
If they have a value include them in the query/String. So this sort of goes with the first part because I have to know if they have a value then put them in the string

I want to build a string that will account for each series of textboxes.

If there are values in Text1a, Text2a, and Text3a then I need a string that can be later used to run a query to update the database or GET A COUNT of the total number of records that fit this query. Which will consist of three criteria
Field1 = x, field2 = c and Field3 = f

Each time there can be a different number of queries...from 1-4


Seems that a loop is needed but I am not sure...

Any thoughts...

Hope that made sense...
Reply With Quote
  #2  
Old May 11th, 2008, 08:01 PM
dykebert dykebert is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: May 2008
Posts: 71 dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level)dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level)dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level)dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level)dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level)dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 18 h 7 m 29 sec
Reputation Power: 21
If I understand your needs correctly, this should work.

Dim ndx as Integer
Dim critStr as String
Dim sql as String

critStr = ""

For ndx = 1 to 4

If len(me.Controls("Text" & Ndx & "a")) > 0 then

If len(critStr) > 0 then

critStr = critStr & " AND " & Me.Controls("Text" & ndx & "a") & " = " & Me.Controls("Text" & ndx & "b")

Else

critStr = Me.Controls("Text" & ndx & "a") & " = " & Me.Controls("Text" & ndx & "b")

End if

End if

Next

sql = "SELECT <field1>, <field2>, ... FROM <table> WHERE " & critStr


And that's it.
Comments on this post
Jaykappy agrees!
Reply With Quote
  #3  
Old May 13th, 2008, 08:34 AM
Jaykappy Jaykappy is offline
Contributing User
ASP Free Novice (500 - 999 posts)
  
Join Date: Feb 2005
Posts: 649 Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 4 Days 20 h 54 m 11 sec
Reputation Power: 22
Was out of the office yesterday...

But Fantastic...that was exaclty what I was looking for...

Did even think of running a For Next Loop to grab each of the textboxes....shows my inexperience

Works great...thank you....
Reply With Quote
  #4  
Old May 13th, 2008, 08:58 AM
Jaykappy Jaykappy is offline
Contributing User
ASP Free Novice (500 - 999 posts)
  
Join Date: Feb 2005
Posts: 649 Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 4 Days 20 h 54 m 11 sec
Reputation Power: 22
Thank you again...the "Text A" thing and "Text B" things were just examples...the textbox were not actually named that but it worked great for this example....
What I had to do was test the value of ndx and from that grab the specific "Text B" value

But other than that its working great....

Thanks Again....



Code:
Dim ndx As Integer
Dim critStr As String
Dim sql As String

critStr = ""

For ndx = 200 To 203

If Len(Me.Controls("Text" & ndx)) > 0 Then

        If Len(critStr) > 0 Then
        
            If ndx = 200 Then
                critStr = critStr & " AND " & Me.Controls("Text" & ndx) & " = " & Me.Controls("Text4")
            ElseIf ndx = 201 Then
                critStr = critStr & " AND " & Me.Controls("Text" & ndx) & " = " & Me.Controls("Text21")
            ElseIf ndx = 202 Then
                critStr = critStr & " AND " & Me.Controls("Text" & ndx) & " = " & Me.Controls("Text31")
            ElseIf ndx = 203 Then
                critStr = critStr & " AND " & Me.Controls("Text" & ndx) & " = " & Me.Controls("Text41")
            Else
            End If
        
        Else
        
        critStr = Me.Controls("Text" & ndx) & " = " & Me.Controls("Text4")
        
        End If

End If

Next

MsgBox critStr
Reply With Quote
  #5  
Old May 13th, 2008, 09:06 AM
Jaykappy Jaykappy is offline
Contributing User
ASP Free Novice (500 - 999 posts)
  
Join Date: Feb 2005
Posts: 649 Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 4 Days 20 h 54 m 11 sec
Reputation Power: 22
The Next question deals with the SQL statement....

In my form:
The first part the user selects from a dropdown box, they are chooseing a field name that exists in the table.

This updates the second dropdown with the unique values from that field.

After these two are updated the values are pushed to textboxes showing the query building.



I am running into issues with the SQL statement...wondering how I am going to populate the fields...

From my understanding the fields (<field1>, <field2>) in the SQL statement have to be in order of the "critStr" variable

Is this right?

sql = "SELECT <field1>, <field2>, ... FROM <table> WHERE " & critStr

Because the field order may differ from query to query...how do I populate <field1>, <field2>


Thanks Again
Reply With Quote
  #6  
Old May 13th, 2008, 09:08 AM
dykebert dykebert is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: May 2008
Posts: 71 dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level)dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level)dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level)dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level)dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level)dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 18 h 7 m 29 sec
Reputation Power: 21
Or you could have just renamed the textboxes.

Glad it's working.
Reply With Quote
  #7  
Old May 13th, 2008, 09:14 AM
dykebert dykebert is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: May 2008
Posts: 71 dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level)dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level)dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level)dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level)dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level)dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 18 h 7 m 29 sec
Reputation Power: 21
Nope the fields in the SELECT clause do NOT have to be in the same order as what is in the WHERE clause (critStr).

You can have fields in the WHERE clause that are not in the SELECT clause and vice versus.

If you are using GROUP BY then there are some restriction about using the same fields, but even then the order doesn't matter.
Reply With Quote
  #8  
Old May 13th, 2008, 09:17 AM
sync_or_swim's Avatar
sync_or_swim sync_or_swim is offline
Contributing User
Click here for more information.
  
Join Date: Mar 2006
Location: South Wales
Posts: 1,259 sync_or_swim User rank is Major General (70000 - 90000 Reputation Level)sync_or_swim User rank is Major General (70000 - 90000 Reputation Level)sync_or_swim User rank is Major General (70000 - 90000 Reputation Level)sync_or_swim User rank is Major General (70000 - 90000 Reputation Level)sync_or_swim User rank is Major General (70000 - 90000 Reputation Level)sync_or_swim User rank is Major General (70000 - 90000 Reputation Level)sync_or_swim User rank is Major General (70000 - 90000 Reputation Level)sync_or_swim User rank is Major General (70000 - 90000 Reputation Level)sync_or_swim User rank is Major General (70000 - 90000 Reputation Level)sync_or_swim User rank is Major General (70000 - 90000 Reputation Level)sync_or_swim User rank is Major General (70000 - 90000 Reputation Level)sync_or_swim User rank is Major General (70000 - 90000 Reputation Level)sync_or_swim User rank is Major General (70000 - 90000 Reputation Level)sync_or_swim User rank is Major General (70000 - 90000 Reputation Level) 
Time spent in forums: 3 Weeks 2 Days 23 h 8 m 7 sec
Reputation Power: 769
Hi,

Glad you solved your problem. This isn't really relevant but I just wanted to demonstrate the use of a select....case statement instead of ElseIf's. If there are many possible options I find that the select....case is a lot more readable and easier to amend/debug:
Code:
If Len(Me.Controls("Text" & ndx)) > 0 Then

        If Len(critStr) > 0 Then
        
            Select Case ndx
            	Case is = 200
            		critStr = critStr & " AND " & Me.Controls("Text" & ndx) & " = " & Me.Controls("Text4")
            	Case is = 201
	                critStr = critStr & " AND " & Me.Controls("Text" & ndx) & " = " & Me.Controls("Text21")
            	Case is = 202
	                critStr = critStr & " AND " & Me.Controls("Text" & ndx) & " = " & Me.Controls("Text31")
            	Case is = 203
	                critStr = critStr & " AND " & Me.Controls("Text" & ndx) & " = " & Me.Controls("Text41")
	    End Select
	    
        Else
        
	        critStr = Me.Controls("Text" & ndx) & " = " & Me.Controls("Text4")
        
        End If

End If
Reply With Quote
  #9  
Old May 13th, 2008, 09:31 AM
Jaykappy Jaykappy is offline
Contributing User
ASP Free Novice (500 - 999 posts)
  
Join Date: Feb 2005
Posts: 649 Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 4 Days 20 h 54 m 11 sec
Reputation Power: 22
This is very clinky and most can be added to the inside the original For Next loop...

but think it gets me what I want...on each pass through the loop it a calculating how many have values..( varCount )

BAsed on this value I determin which SQL String to build...
AND I am using the values directly from the Textboxes in the form so I does not matter if they change etc...

HOW DO YOU SET A NUMERIC VARIABLE TO NULL????



Code:
For ndx = 200 To 203

Dim varCount As Long
'varCount = Nothing

If Len(Me.Controls("Text" & ndx)) > 0 Then

        If Len(critStr) > 0 Then
        
            varCount = varCount + 1
        
        Else

            varCount = 1
        End If

End If

Next


MsgBox varCount

Dim var200 As String
var200 = Text200
MsgBox var200

If varCount = 4 Then
    sql = "SELECT '" & Text200 & "', '" & Text201 & "', '" & Text202 & "', '" & Text203 & "' FROM dbo_Core2 WHERE " & critStr
ElseIf varCount = 3 Then
    sql = "SELECT '" & Text200 & "', '" & Text201 & "', '" & Text202 & "' FROM dbo_Core2 WHERE " & critStr
ElseIf varCount = 2 Then
    sql = "SELECT '" & Text200 & "', '" & Text201 & "' FROM dbo_Core2 WHERE " & critStr
ElseIf varCount = 1 Then
    sql = "SELECT '" & Text200 & "' FROM dbo_Core2 WHERE " & critStr
Else
    MsgBox " NOthing there"
End If

MsgBox sql
Reply With Quote
  #10  
Old May 13th, 2008, 09:34 AM
Jaykappy Jaykappy is offline
Contributing User
ASP Free Novice (500 - 999 posts)
  
Join Date: Feb 2005
Posts: 649 Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 4 Days 20 h 54 m 11 sec
Reputation Power: 22
Opps didnt see you last post...

Basically when I run this query my goal is to get a count of the amount of records that meet the criteria...

Thoughts on how to change the query to get that?


I know I aske din my last one but how do you set a numeric value to null?

THANKS FOR YOUR HELP

Will use that Case Statement....
Reply With Quote
  #11  
Old May 13th, 2008, 09:45 AM
Jaykappy Jaykappy is offline
Contributing User
ASP Free Novice (500 - 999 posts)
  
Join Date: Feb 2005
Posts: 649 Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 4 Days 20 h 54 m 11 sec
Reputation Power: 22
I am trying something like this:

MySQL2 = "SELECT '" & Text200 & "', '" & Text201 & "', '" & Text202 & "' FROM dbo_Core2 WHERE " & critStr
MsgBox MySQL2
DoCmd.RunSQL MySQL2


What I am getting in the message box is this:

SELECT 'ARTICLE', 'CATEG', 'DISPO' FROM dbo_Core2 WHERE ARTICLE = SHIRT AND CATEG = STORES AND DISPO = HOLD



I get this error:

Run Time Error 2342
A RunSQL action requires an argument consisting of an SQL statement...




THOUGHTS????
Reply With Quote
  #12  
Old May 13th, 2008, 09:57 AM
dykebert dykebert is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: May 2008
Posts: 71 dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level)dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level)dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level)dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level)dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level)dykebert User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 18 h 7 m 29 sec
Reputation Power: 21
gotta run, but quickly.

If all you want is a count of the records that meet the criteria then I'd use whatever ID field you have like this:

SELECT Count(ID) as numRecords FROM <table> WHERE <critStr>;

As to why the error get rid of the single quotes in the SQL statement. You have

MySQL2 = "SELECT '" & Text200 & "'" , '" " & ...

It should just be:

MySQL2 = "SELECT " & Text200 & ", " & ...

The single quotes are necessary only for Text VALUES not for field names.

Last but not least

Dim ndx as integer

ndx = NULL
Reply With Quote
  #13  
Old May 13th, 2008, 09:58 AM
Jaykappy Jaykappy is offline
Contributing User
ASP Free Novice (500 - 999 posts)
  
Join Date: Feb 2005
Posts: 649 Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 4 Days 20 h 54 m 11 sec
Reputation Power: 22
POSTED IN NEW EVENT
Last edited by Jaykappy : May 13th, 2008 at 11:53 AM.
Reply With Quote
  #14  
Old May 13th, 2008, 10:06 AM
Jaykappy Jaykappy is offline
Contributing User
ASP Free Novice (500 - 999 posts)
  
Join Date: Feb 2005
Posts: 649 Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 4 Days 20 h 54 m 11 sec
Reputation Power: 22
POSTED IN NEW EVENT
Last edited by Jaykappy : May 13th, 2008 at 11:52 AM.
Reply With Quote
  #15  
Old May 13th, 2008, 10:08 AM
Jaykappy Jaykappy is offline
Contributing User
ASP Free Novice (500 - 999 posts)
  
Join Date: Feb 2005
Posts: 649 Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)Jaykappy User rank is Sergeant (500 - 2000 Reputation Level)