• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

can anyone assist me in writing VBA code for access to build queries

I have an access datatbase with mutliple fields in a table. in addition i have a form with different creiteria selection options

for example:
you can select wether you want clients that are labeled billable or non-billable

start date  and end date
 The above is on a form with paremeter boxes for the dates   and
radio buttons for the billable and non-billable status

how do i build a query to bring back all data based upon the criteria selected...


currently i have 2 command buttons
1) Build query     2) Run Query


behind the build query i have

Dim strSQL As String
if BuildSQLString(strSQL) Then
MsgBox "you query has been built"
Exit Sub
End If
MsgBox strSQL
CurrentDb.QueryDefs("qryshell").SQL = strSQL


i donot hae any code behind the other button

0
attachie
Asked:
attachie
  • 9
  • 9
1 Solution
 
attachieAuthor Commented:
Function BuildSQLString(strSQL As String) As Boolean
Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String

'strSELECT = "SELECT a.*, b.name, b.selling_company "
strSELECT = "SELECT a.document_no, a.order_ref_no, b.parent_code, a.product_line, a.state_code, " & _
"a.county_name,  a.product_code, a.date_ordered, a.qty_ordered, a.description, a.username, a.billable, " & _
"b.client_code, b.client_name, b.plan_type "
'
strWHERE = "a.client_code = b.client_code"
If All_Billable_option.Enabled = True Then
    strWHERE = strWHERE & " and a.billable = 'y' and a.billable ='n'"
   
ElseIf One_Billing_Class_option.Enabled = True Then
    strWHERE = strWHERE & " and a.billable = " & _
    "'" & select_type & "'"
    End If
0
 
tushar_compCommented:
hi,

u have the sql query i suppose
so u just have to make connection with access db and fire query
if u want db handling code in VBA
then just let me know
Tushar

 
0
 
attachieAuthor Commented:
i dont have the sql query...

i want to build the query depending upon the criteria selected....


is that possible
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
vb_jonasCommented:
Hi, what would you like to do with the data?

If you only like to show it as a query result you are almost there, just add:

DoCmd.OpenQuery "qryshell", acNormal, acEdit

If you have a subform you could do:

Me.SubreportQryShell.SourceObject = "Queries.qryshell"

0
 
vb_jonasCommented:
And your function should end something like this:

CurrentDb.QueryDefs("qryshell").SQL = strSELECT & " FROM a,b WHERE " & strWHERE

' if this went ok:
BuildSQLString = True
0
 
vb_jonasCommented:
You probably have a join between the tables a and b. Best way to build sql-strings is to do it with access query builder and click the SQL-button to see the SQL-string built.
0
 
attachieAuthor Commented:
i would like the data to show as a query result ...

should i put the    DoCmd.OpenQuery "qryshell", acNormal, acEdit   after all the text  so it would look like the following:

Function BuildSQLString(strSQL As String) As Boolean
Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String

'strSELECT = "SELECT a.*, b.name, b.selling_company "
strSELECT = "SELECT a.document_no, a.order_ref_no, b.parent_code, a.product_line, a.state_code, " & _
"a.county_name,  a.product_code, a.date_ordered, a.qty_ordered, a.description, a.username, a.billable, " & _
"b.client_code, b.client_name, b.plan_type "
'
strWHERE = "a.client_code = b.client_code"
If All_Billable_option.Enabled = True Then
    strWHERE = strWHERE & " and a.billable = 'y' and a.billable ='n'"
   
ElseIf One_Billing_Class_option.Enabled = True Then
    strWHERE = strWHERE & " and a.billable = " & _
    "'" & select_type & "'"
    End If
DoCmd.OpenQuery "qryshell", acNormal, acEdit  

-- do i have to declare query shell,  how does the query get assigned to the qryshell

0
 
attachieAuthor Commented:
let me try your suggestion
0
 
vb_jonasCommented:
yes, with your code there should exist a query named qryshell in the database. Could be empty though.
0
 
vb_jonasCommented:
Function BuildSQLString() As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String

'strSELECT = "SELECT a.*, b.name, b.selling_company "
strSELECT = "SELECT a.document_no, a.order_ref_no, b.parent_code, a.product_line, a.state_code, " & _
"a.county_name,  a.product_code, a.date_ordered, a.qty_ordered, a.description, a.username, a.billable, " & _
"b.client_code, b.client_name, b.plan_type "
'
strWHERE = "a.client_code = b.client_code"
If All_Billable_option.Enabled = True Then
    strWHERE = strWHERE & " and a.billable = 'y' and a.billable ='n'"
   
ElseIf One_Billing_Class_option.Enabled = True Then
    strWHERE = strWHERE & " and a.billable = " & _
    "'" & select_type & "'"
End If

CurrentDb.QueryDefs("qryshell").SQL = strSELECT & " FROM a,b WHERE " & strWHERE

' Should handle some errors, but we dont, so just set function result to be true:
BuildSQLString = True

End Function

--------------------------------------------

Sub CommandButton_Click
If BuildSQLString then
  DoCmd.OpenQuery "qryshell", acNormal, acEdit  
End If
End Sub
0
 
attachieAuthor Commented:
its saying it can not find query shell
0
 
attachieAuthor Commented:
i added the table it is pulling this data from, becuase i noticed i didnt specify


strFROM = "Historicaldatadump a , Client b"

0
 
vb_jonasCommented:
just add an empty query in the query designer and save it as qryshell.
0
 
vb_jonasCommented:
yes, and there should also be a join somewhere?
0
 
vb_jonasCommented:
sorry , your WHERE will due, but you need to insert the FROM,

change to:
CurrentDb.QueryDefs("qryshell").SQL = strSELECT & " FROM " & strFROM & " WHERE " & strWHERE
0
 
attachieAuthor Commented:
I am getting an error stating  " ITEM NOT FOUND IN COLLECTION" POINT TO :

CurrentDb.QueryDefs("qryshell").SQL = strSELECT & " FROM " & strFROM & " WHERE " & strWHERE

BELOW IS EXACTLY WHAT I HAVE IN THE FUNCTION :

Function BuildSQLString() As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String

'strSELECT = "SELECT a.*, b.name, b.selling_company "
strSELECT = "SELECT a.document_no, a.order_ref_no, b.parent_code, a.product_line, a.state_code, " & _
"a.county_name,  a.product_code, a.date_ordered, a.qty_ordered, a.description, a.username, a.billable, " & _
"b.client_code, b.client_name, b.plan_type "
'
strFROM = "Historicaldatadump a , Client b"
strWHERE = "a.client_code = b.client_code"
If All_Billable_option.Enabled = True Then
    strWHERE = strWHERE & " and a.billable = 'y' and a.billable ='n'"
   
ElseIf One_Billing_Class_option.Enabled = True Then
    strWHERE = strWHERE & " and a.billable = " & _
    "'" & select_type & "'"
End If

CurrentDb.QueryDefs("qryshell").SQL = strSELECT & " FROM " & strFROM & " WHERE " & strWHERE


' Should handle some errors, but we dont, so just set function result to be true:
BuildSQLString = True

End Function
0
 
vb_jonasCommented:
Then you havent added a query named qryshell. MS Access - Queries - New - Design - ... - Close - Save (give it the name qryshell) - OK
0
 
attachieAuthor Commented:
sHOULD I ADD BOTH TABLE TO THE SHELL
0
 
attachieAuthor Commented:
THAT WORKED FINE THANKS A MIL
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 9
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now