Seeking Help With VBA and SQL Instant Query

R

ryguy7272

Hello! I am relatively new to Access development work. I have been doing
excel programming for a few yeas now, and understand that tool pretty well,
but using Access is a completely different experience. Anyway, I am
currently using a sample that I found at this site:

http://www.fontstuff.com/access/acctut18.htm

The file at the bottom of the page is: AccessAndSQL5_2000.zip

I mostly got it working for my particular scenario, thanks to some great
people on this DG. I still haven’t figured out two things.

1) How can I choose the columns that displayed in the query?

I am using this piece of code now:
strSQL = "SELECT tblTFI.* FROM tblTFI " & _
"WHERE tblTFI.[Broker] " & strBroker & _
"AND tblTFI.[Prod] " & strProd & _
"AND tblTFI.[Status] " & strStatus & ";"

I know the SELECT tblTFI.* part selects ALL columns form the table. I have
about 50 columns in the table, but want to display only about 12 or so. I
would really like to display certain columns, such as ‘Project Name’,
‘Broker’, ‘Project’, ‘Sub-Project’, ‘Status’, and a few others, but not ALL
columns.

2) How can I modify the code to ask me for a date range, such as a
beginning date and an ending date? Right now I build a query based on one of
three criteria: Broker, Product, or Status. I suspect I will have to add
something on the form, like a check box, that causes the date-range question
pop up after the query based on the three criteria is entered, and just
before the query is actually built. Else, if the box is not checked, just
return everything that matches the query criteria.

The code that I am running right now is listed below:
Option Compare Database
Option Explicit

' This code uses ADO and ADOX and is suitable for Access 2000 (and later).
' A reference must be set to Microsoft ADO Ext. 2.7 for DDL and Security.

Private Sub cmdOK_Click()
On Error GoTo cmdOK_Click_Err
Dim blnQueryExists As Boolean
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
Dim varItem As Variant
Dim strBroker As String
Dim strProd As String
Dim strProdCondition As String
Dim strStatus As String
Dim strSQL As String
blnQueryExists = False
Set cat.ActiveConnection = CurrentProject.Connection
For Each qry In cat.Views
If qry.Name = "SelQuery" Then
blnQueryExists = True
Exit For
End If
Next qry
' Create the query if it does not already exist
If blnQueryExists = False Then
cmd.CommandText = "SELECT * FROM tblStaff"
cat.Views.Append "SelQuery", cmd
End If
Application.RefreshDatabaseWindow
' Turn off screen updating
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "SelQuery") = acObjStateOpen
Then
DoCmd.Close acQuery, "SelQuery"
End If
' Build criteria string for Broker
For Each varItem In Me.lstBroker.ItemsSelected
strBroker = strBroker & ",'" & Me.lstBroker.ItemData(varItem) & "'"
Next varItem
If Len(strBroker) = 0 Then
strBroker = "Like '*'"
Else
strBroker = Right(strBroker, Len(strBroker) - 1)
strBroker = "IN(" & strBroker & ")"
End If

' Build criteria string for Prod
For Each varItem In Me.lstProd.ItemsSelected
strProd = strProd & ",'" & Me.lstProd.ItemData(varItem) & "'"
Next varItem
If Len(strProd) = 0 Then
strProd = "Like '*'"
Else
strProd = Right(strProd, Len(strProd) - 1)
strProd = "IN(" & strProd & ")"
End If

' Build criteria string for Status
For Each varItem In Me.lstStatus.ItemsSelected
strStatus = strStatus & ",'" & Me.lstStatus.ItemData(varItem) & "'"
Next varItem
If Len(strStatus) = 0 Then
strStatus = "Like '*'"
Else
strStatus = Right(strStatus, Len(strStatus) - 1)
strStatus = "IN(" & strStatus & ")"
End If

strSQL = "SELECT tblTFI.* FROM tblTFI " & _
"WHERE tblTFI.[Broker] " & strBroker & _
"AND tblTFI.[Prod] " & strProd & _
"AND tblTFI.[Status] " & strStatus & ";"


' Apply the SQL statement to the stored query
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("SelQuery").Command
cmd.CommandText = strSQL
Set cat.Views("SelQuery").Command = cmd
Set cat = Nothing
' Open the Query
DoCmd.OpenQuery "SelQuery"

cmdOK_Click_Exit:
DoCmd.Echo True
Exit Sub
cmdOK_Click_Err:
MsgBox "An unexpected error hass occurred." _
& vbCrLf & "Procedure: cmdOK_Click" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description:" & Err.DESCRIPTION _
, vbCritical, "Error"
Resume cmdOK_Click_Exit
End Sub

The SQL in the query looks like this:
SELECT tblTFI.*
FROM tblTFI
WHERE tblTFI.[Broker] Like '*'AND tblTFI.[Prod] Like '*'AND tblTFI.[Status]
Like '*';


If someone could please help me out with this I’d reapply appreciate it!! I
am looking forward to learning more about Access, but I’m a little scared of
the tool too!

Regards,
Ryan---
 
J

Jim Burke in Novi

To SELECT specific fields you simply specify each field name separated by a
comma, e.g.

SELECT field, field2, field3
FROM tableName
WHERE ....

What I would do with the dates (someone might have a better suggestion) is
create two date fields on the form that the user can enter start and end
date. Have a button that runs the query and 'ignores' dates, in which case
they don't enter them, and a button that is for running the query using the
dates. Behind the scenes in the VBA code I would have two fields defined
called startDate and endDate, declared as dates (e.g. dim startDate as Date).
If the user clicks the button that 'ignores' the dates I would set these to
something like:

startDate = #1/1/1900#
enDate = #1/1/2200#

where all dates you can possibly encounter will fall within the range. WIth
the other button that says use the dates I'd use:

startDate = "#" & txtStartDate & "#" (the text box with the start date)
endDate = "#" & txtENdDAte & "#" (the text box with the end date)

The '#' delimiters are needed for date values when you hardcode them in an
SQL statement. Then the part of your WHERE clause that selects by date would
say:

" WHERE dateField BETWEEN " & startDate & " AND " & endDate

Also, in code you included, your WHERE clause needs '=' signs, e.g.

"WHERE tblTFI.[Broker] = " & strBroker

and if it's a text field then you also need to surround the value with
quotes, e.g.

"WHERE tblTFI.[Broker] = '" & strBroker & "' " ...

And you also need to make sure you hav spaces in your SQL between all
keywords, values, etc., e.g.

strSQL = "SELECT tblTFI.* FROM tblTFI " & _
" WHERE tblTFI.[Broker] =" & strBroker & _
" AND tblTFI.[Prod] =" & strProd & _
" AND tblTFI.[Status]= " & strStatus & ";"
Click to expand...


Hope this helps and hope it's not too complicated for your purposes! There
may be a better way.

ryguy7272 said:
Hello! I am relatively new to Access development work. I have been doing
excel programming for a few yeas now, and understand that tool pretty well,
but using Access is a completely different experience. Anyway, I am
currently using a sample that I found at this site:

http://www.fontstuff.com/access/acctut18.htm

The file at the bottom of the page is: AccessAndSQL5_2000.zip

I mostly got it working for my particular scenario, thanks to some great
people on this DG. I still haven’t figured out two things.

1) How can I choose the columns that displayed in the query?

I am using this piece of code now:
strSQL = "SELECT tblTFI.* FROM tblTFI " & _
"WHERE tblTFI.[Broker] " & strBroker & _
"AND tblTFI.[Prod] " & strProd & _
"AND tblTFI.[Status] " & strStatus & ";"

I know the SELECT tblTFI.* part selects ALL columns form the table. I have
about 50 columns in the table, but want to display only about 12 or so. I
would really like to display certain columns, such as ‘Project Name’,
‘Broker’, ‘Project’, ‘Sub-Project’, ‘Status’, and a few others, but not ALL
columns.

2) How can I modify the code to ask me for a date range, such as a
beginning date and an ending date? Right now I build a query based on one of
three criteria: Broker, Product, or Status. I suspect I will have to add
something on the form, like a check box, that causes the date-range question
pop up after the query based on the three criteria is entered, and just
before the query is actually built. Else, if the box is not checked, just
return everything that matches the query criteria.

The code that I am running right now is listed below:
Option Compare Database
Option Explicit

' This code uses ADO and ADOX and is suitable for Access 2000 (and later).
' A reference must be set to Microsoft ADO Ext. 2.7 for DDL and Security.

Private Sub cmdOK_Click()
On Error GoTo cmdOK_Click_Err
Dim blnQueryExists As Boolean
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
Dim varItem As Variant
Dim strBroker As String
Dim strProd As String
Dim strProdCondition As String
Dim strStatus As String
Dim strSQL As String
blnQueryExists = False
Set cat.ActiveConnection = CurrentProject.Connection
For Each qry In cat.Views
If qry.Name = "SelQuery" Then
blnQueryExists = True
Exit For
End If
Next qry
' Create the query if it does not already exist
If blnQueryExists = False Then
cmd.CommandText = "SELECT * FROM tblStaff"
cat.Views.Append "SelQuery", cmd
End If
Application.RefreshDatabaseWindow
' Turn off screen updating
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "SelQuery") = acObjStateOpen
Then
DoCmd.Close acQuery, "SelQuery"
End If
' Build criteria string for Broker
For Each varItem In Me.lstBroker.ItemsSelected
strBroker = strBroker & ",'" & Me.lstBroker.ItemData(varItem) & "'"
Next varItem
If Len(strBroker) = 0 Then
strBroker = "Like '*'"
Else
strBroker = Right(strBroker, Len(strBroker) - 1)
strBroker = "IN(" & strBroker & ")"
End If

' Build criteria string for Prod
For Each varItem In Me.lstProd.ItemsSelected
strProd = strProd & ",'" & Me.lstProd.ItemData(varItem) & "'"
Next varItem
If Len(strProd) = 0 Then
strProd = "Like '*'"
Else
strProd = Right(strProd, Len(strProd) - 1)
strProd = "IN(" & strProd & ")"
End If

' Build criteria string for Status
For Each varItem In Me.lstStatus.ItemsSelected
strStatus = strStatus & ",'" & Me.lstStatus.ItemData(varItem) & "'"
Next varItem
If Len(strStatus) = 0 Then
strStatus = "Like '*'"
Else
strStatus = Right(strStatus, Len(strStatus) - 1)
strStatus = "IN(" & strStatus & ")"
End If

strSQL = "SELECT tblTFI.* FROM tblTFI " & _
"WHERE tblTFI.[Broker] " & strBroker & _
"AND tblTFI.[Prod] " & strProd & _
"AND tblTFI.[Status] " & strStatus & ";"


' Apply the SQL statement to the stored query
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("SelQuery").Command
cmd.CommandText = strSQL
Set cat.Views("SelQuery").Command = cmd
Set cat = Nothing
' Open the Query
DoCmd.OpenQuery "SelQuery"

cmdOK_Click_Exit:
DoCmd.Echo True
Exit Sub
cmdOK_Click_Err:
MsgBox "An unexpected error hass occurred." _
& vbCrLf & "Procedure: cmdOK_Click" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description:" & Err.DESCRIPTION _
, vbCritical, "Error"
Resume cmdOK_Click_Exit
End Sub

The SQL in the query looks like this:
SELECT tblTFI.*
FROM tblTFI
WHERE tblTFI.[Broker] Like '*'AND tblTFI.[Prod] Like '*'AND tblTFI.[Status]
Like '*';


If someone could please help me out with this I’d reapply appreciate it!! I
am looking forward to learning more about Access, but I’m a little scared of
the tool too!

Regards,
Ryan---
Click to expand...
 
R

ryguy7272

Thanks for the assist there Jim. The Select suggestion worked great.

So, I guess that's half of it; now I am fiddling around with the dates thing
and I think I am totally lost.

I entered two ListBoxes; one is named lstBegin and the other is named
lstEnd. I went from design mode to run mode, and now I get this message.
An Unexpected Error Occurred.
Procedure: cmdOK_Click
Error Number: 3128
Error Description: Could not update; currently locked.

This is what I get when I simply try to run one of the former queries, using
Broker or Status, which was working fine before I popped those dates in
there. I don't know what to do now. Does anyone know what would cause this
error?

This is soooo easy in Excel!!


--
RyGuy


Jim Burke in Novi said:
To SELECT specific fields you simply specify each field name separated by a
comma, e.g.

SELECT field, field2, field3
FROM tableName
WHERE ....

What I would do with the dates (someone might have a better suggestion) is
create two date fields on the form that the user can enter start and end
date. Have a button that runs the query and 'ignores' dates, in which case
they don't enter them, and a button that is for running the query using the
dates. Behind the scenes in the VBA code I would have two fields defined
called startDate and endDate, declared as dates (e.g. dim startDate as Date).
If the user clicks the button that 'ignores' the dates I would set these to
something like:

startDate = #1/1/1900#
enDate = #1/1/2200#

where all dates you can possibly encounter will fall within the range. WIth
the other button that says use the dates I'd use:

startDate = "#" & txtStartDate & "#" (the text box with the start date)
endDate = "#" & txtENdDAte & "#" (the text box with the end date)

The '#' delimiters are needed for date values when you hardcode them in an
SQL statement. Then the part of your WHERE clause that selects by date would
say:

" WHERE dateField BETWEEN " & startDate & " AND " & endDate

Also, in code you included, your WHERE clause needs '=' signs, e.g.

"WHERE tblTFI.[Broker] = " & strBroker

and if it's a text field then you also need to surround the value with
quotes, e.g.

"WHERE tblTFI.[Broker] = '" & strBroker & "' " ...

And you also need to make sure you hav spaces in your SQL between all
keywords, values, etc., e.g.

strSQL = "SELECT tblTFI.* FROM tblTFI " & _
" WHERE tblTFI.[Broker] =" & strBroker & _
" AND tblTFI.[Prod] =" & strProd & _
" AND tblTFI.[Status]= " & strStatus & ";"
Click to expand...


Hope this helps and hope it's not too complicated for your purposes! There
may be a better way.

ryguy7272 said:
Hello! I am relatively new to Access development work. I have been doing
excel programming for a few yeas now, and understand that tool pretty well,
but using Access is a completely different experience. Anyway, I am
currently using a sample that I found at this site:

http://www.fontstuff.com/access/acctut18.htm

The file at the bottom of the page is: AccessAndSQL5_2000.zip

I mostly got it working for my particular scenario, thanks to some great
people on this DG. I still haven’t figured out two things.

1) How can I choose the columns that displayed in the query?

I am using this piece of code now:
strSQL = "SELECT tblTFI.* FROM tblTFI " & _
"WHERE tblTFI.[Broker] " & strBroker & _
"AND tblTFI.[Prod] " & strProd & _
"AND tblTFI.[Status] " & strStatus & ";"

I know the SELECT tblTFI.* part selects ALL columns form the table. I have
about 50 columns in the table, but want to display only about 12 or so. I
would really like to display certain columns, such as ‘Project Name’,
‘Broker’, ‘Project’, ‘Sub-Project’, ‘Status’, and a few others, but not ALL
columns.

2) How can I modify the code to ask me for a date range, such as a
beginning date and an ending date? Right now I build a query based on one of
three criteria: Broker, Product, or Status. I suspect I will have to add
something on the form, like a check box, that causes the date-range question
pop up after the query based on the three criteria is entered, and just
before the query is actually built. Else, if the box is not checked, just
return everything that matches the query criteria.

The code that I am running right now is listed below:
Option Compare Database
Option Explicit

' This code uses ADO and ADOX and is suitable for Access 2000 (and later).
' A reference must be set to Microsoft ADO Ext. 2.7 for DDL and Security.

Private Sub cmdOK_Click()
On Error GoTo cmdOK_Click_Err
Dim blnQueryExists As Boolean
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
Dim varItem As Variant
Dim strBroker As String
Dim strProd As String
Dim strProdCondition As String
Dim strStatus As String
Dim strSQL As String
blnQueryExists = False
Set cat.ActiveConnection = CurrentProject.Connection
For Each qry In cat.Views
If qry.Name = "SelQuery" Then
blnQueryExists = True
Exit For
End If
Next qry
' Create the query if it does not already exist
If blnQueryExists = False Then
cmd.CommandText = "SELECT * FROM tblStaff"
cat.Views.Append "SelQuery", cmd
End If
Application.RefreshDatabaseWindow
' Turn off screen updating
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "SelQuery") = acObjStateOpen
Then
DoCmd.Close acQuery, "SelQuery"
End If
' Build criteria string for Broker
For Each varItem In Me.lstBroker.ItemsSelected
strBroker = strBroker & ",'" & Me.lstBroker.ItemData(varItem) & "'"
Next varItem
If Len(strBroker) = 0 Then
strBroker = "Like '*'"
Else
strBroker = Right(strBroker, Len(strBroker) - 1)
strBroker = "IN(" & strBroker & ")"
End If

' Build criteria string for Prod
For Each varItem In Me.lstProd.ItemsSelected
strProd = strProd & ",'" & Me.lstProd.ItemData(varItem) & "'"
Next varItem
If Len(strProd) = 0 Then
strProd = "Like '*'"
Else
strProd = Right(strProd, Len(strProd) - 1)
strProd = "IN(" & strProd & ")"
End If

' Build criteria string for Status
For Each varItem In Me.lstStatus.ItemsSelected
strStatus = strStatus & ",'" & Me.lstStatus.ItemData(varItem) & "'"
Next varItem
If Len(strStatus) = 0 Then
strStatus = "Like '*'"
Else
strStatus = Right(strStatus, Len(strStatus) - 1)
strStatus = "IN(" & strStatus & ")"
End If

strSQL = "SELECT tblTFI.* FROM tblTFI " & _
"WHERE tblTFI.[Broker] " & strBroker & _
"AND tblTFI.[Prod] " & strProd & _
"AND tblTFI.[Status] " & strStatus & ";"


' Apply the SQL statement to the stored query
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("SelQuery").Command
cmd.CommandText = strSQL
Set cat.Views("SelQuery").Command = cmd
Set cat = Nothing
' Open the Query
DoCmd.OpenQuery "SelQuery"

cmdOK_Click_Exit:
DoCmd.Echo True
Exit Sub
cmdOK_Click_Err:
MsgBox "An unexpected error hass occurred." _
& vbCrLf & "Procedure: cmdOK_Click" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description:" & Err.DESCRIPTION _
, vbCritical, "Error"
Resume cmdOK_Click_Exit
End Sub

The SQL in the query looks like this:
SELECT tblTFI.*
FROM tblTFI
WHERE tblTFI.[Broker] Like '*'AND tblTFI.[Prod] Like '*'AND tblTFI.[Status]
Like '*';


If someone could please help me out with this I’d reapply appreciate it!! I
am looking forward to learning more about Access, but I’m a little scared of
the tool too!

Regards,
Ryan---
Click to expand...
Click to expand...
 
J

Jim Burke in Novi

Without seeing what's in your code now it's hard to say. But you probably
want to use text boxes and not listboxes. Listboxes require special
processing in order to refer to the values selected in them, while a textbos
is just referred to directly. The fact that you're using a listbox may be the
problem. If you create a textbox called txtStartDate and use that for the
start date, then in your SQL you would just refer to it as I showed you
before:

"WHERE dateField BETWEEN #" & txtStartDate & "# AND #" & txtEndDate & "#"

With a listbox you have to popualte the list box with values froma table or
query or else type them in yourself. Yhe user then has to select a value from
the lstibox and you then have to refer to the listboxes ItemsSelect property.
If youwant a list of dates for them to choose from, use a combobox and not
a,listbox, if the user will type in the data use a textbox.


ryguy7272 said:
Thanks for the assist there Jim. The Select suggestion worked great.

So, I guess that's half of it; now I am fiddling around with the dates thing
and I think I am totally lost.

I entered two ListBoxes; one is named lstBegin and the other is named
lstEnd. I went from design mode to run mode, and now I get this message.
An Unexpected Error Occurred.
Procedure: cmdOK_Click
Error Number: 3128
Error Description: Could not update; currently locked.

This is what I get when I simply try to run one of the former queries, using
Broker or Status, which was working fine before I popped those dates in
there. I don't know what to do now. Does anyone know what would cause this
error?

This is soooo easy in Excel!!


--
RyGuy


Jim Burke in Novi said:
To SELECT specific fields you simply specify each field name separated by a
comma, e.g.

SELECT field, field2, field3
FROM tableName
WHERE ....

What I would do with the dates (someone might have a better suggestion) is
create two date fields on the form that the user can enter start and end
date. Have a button that runs the query and 'ignores' dates, in which case
they don't enter them, and a button that is for running the query using the
dates. Behind the scenes in the VBA code I would have two fields defined
called startDate and endDate, declared as dates (e.g. dim startDate as Date).
If the user clicks the button that 'ignores' the dates I would set these to
something like:

startDate = #1/1/1900#
enDate = #1/1/2200#

where all dates you can possibly encounter will fall within the range. WIth
the other button that says use the dates I'd use:

startDate = "#" & txtStartDate & "#" (the text box with the start date)
endDate = "#" & txtENdDAte & "#" (the text box with the end date)

The '#' delimiters are needed for date values when you hardcode them in an
SQL statement. Then the part of your WHERE clause that selects by date would
say:

" WHERE dateField BETWEEN " & startDate & " AND " & endDate

Also, in code you included, your WHERE clause needs '=' signs, e.g.

"WHERE tblTFI.[Broker] = " & strBroker

and if it's a text field then you also need to surround the value with
quotes, e.g.

"WHERE tblTFI.[Broker] = '" & strBroker & "' " ...

And you also need to make sure you hav spaces in your SQL between all
keywords, values, etc., e.g.

strSQL = "SELECT tblTFI.* FROM tblTFI " & _
" WHERE tblTFI.[Broker] =" & strBroker & _
" AND tblTFI.[Prod] =" & strProd & _
" AND tblTFI.[Status]= " & strStatus & ";"
Click to expand...


Hope this helps and hope it's not too complicated for your purposes! There
may be a better way.

ryguy7272 said:
Hello! I am relatively new to Access development work. I have been doing
excel programming for a few yeas now, and understand that tool pretty well,
but using Access is a completely different experience. Anyway, I am
currently using a sample that I found at this site:

http://www.fontstuff.com/access/acctut18.htm

The file at the bottom of the page is: AccessAndSQL5_2000.zip

I mostly got it working for my particular scenario, thanks to some great
people on this DG. I still haven’t figured out two things.

1) How can I choose the columns that displayed in the query?

I am using this piece of code now:
strSQL = "SELECT tblTFI.* FROM tblTFI " & _
"WHERE tblTFI.[Broker] " & strBroker & _
"AND tblTFI.[Prod] " & strProd & _
"AND tblTFI.[Status] " & strStatus & ";"

I know the SELECT tblTFI.* part selects ALL columns form the table. I have
about 50 columns in the table, but want to display only about 12 or so. I
would really like to display certain columns, such as ‘Project Name’,
‘Broker’, ‘Project’, ‘Sub-Project’, ‘Status’, and a few others, but not ALL
columns.

2) How can I modify the code to ask me for a date range, such as a
beginning date and an ending date? Right now I build a query based on one of
three criteria: Broker, Product, or Status. I suspect I will have to add
something on the form, like a check box, that causes the date-range question
pop up after the query based on the three criteria is entered, and just
before the query is actually built. Else, if the box is not checked, just
return everything that matches the query criteria.

The code that I am running right now is listed below:
Option Compare Database
Option Explicit

' This code uses ADO and ADOX and is suitable for Access 2000 (and later).
' A reference must be set to Microsoft ADO Ext. 2.7 for DDL and Security.

Private Sub cmdOK_Click()
On Error GoTo cmdOK_Click_Err
Dim blnQueryExists As Boolean
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
Dim varItem As Variant
Dim strBroker As String
Dim strProd As String
Dim strProdCondition As String
Dim strStatus As String
Dim strSQL As String
blnQueryExists = False
Set cat.ActiveConnection = CurrentProject.Connection
For Each qry In cat.Views
If qry.Name = "SelQuery" Then
blnQueryExists = True
Exit For
End If
Next qry
' Create the query if it does not already exist
If blnQueryExists = False Then
cmd.CommandText = "SELECT * FROM tblStaff"
cat.Views.Append "SelQuery", cmd
End If
Application.RefreshDatabaseWindow
' Turn off screen updating
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "SelQuery") = acObjStateOpen
Then
DoCmd.Close acQuery, "SelQuery"
End If
' Build criteria string for Broker
For Each varItem In Me.lstBroker.ItemsSelected
strBroker = strBroker & ",'" & Me.lstBroker.ItemData(varItem) & "'"
Next varItem
If Len(strBroker) = 0 Then
strBroker = "Like '*'"
Else
strBroker = Right(strBroker, Len(strBroker) - 1)
strBroker = "IN(" & strBroker & ")"
End If

' Build criteria string for Prod
For Each varItem In Me.lstProd.ItemsSelected
strProd = strProd & ",'" & Me.lstProd.ItemData(varItem) & "'"
Next varItem
If Len(strProd) = 0 Then
strProd = "Like '*'"
Else
strProd = Right(strProd, Len(strProd) - 1)
strProd = "IN(" & strProd & ")"
End If

' Build criteria string for Status
For Each varItem In Me.lstStatus.ItemsSelected
strStatus = strStatus & ",'" & Me.lstStatus.ItemData(varItem) & "'"
Next varItem
If Len(strStatus) = 0 Then
strStatus = "Like '*'"
Else
strStatus = Right(strStatus, Len(strStatus) - 1)
strStatus = "IN(" & strStatus & ")"
End If

strSQL = "SELECT tblTFI.* FROM tblTFI " & _
"WHERE tblTFI.[Broker] " & strBroker & _
"AND tblTFI.[Prod] " & strProd & _
"AND tblTFI.[Status] " & strStatus & ";"


' Apply the SQL statement to the stored query
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("SelQuery").Command
cmd.CommandText = strSQL
Set cat.Views("SelQuery").Command = cmd
Set cat = Nothing
' Open the Query
DoCmd.OpenQuery "SelQuery"

cmdOK_Click_Exit:
DoCmd.Echo True
Exit Sub
cmdOK_Click_Err:
MsgBox "An unexpected error hass occurred." _
& vbCrLf & "Procedure: cmdOK_Click" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description:" & Err.DESCRIPTION _
, vbCritical, "Error"
Resume cmdOK_Click_Exit
End Sub

The SQL in the query looks like this:
SELECT tblTFI.*
FROM tblTFI
WHERE tblTFI.[Broker] Like '*'AND tblTFI.[Prod] Like '*'AND tblTFI.[Status]
Like '*';


If someone could please help me out with this I’d reapply appreciate it!! I
am looking forward to learning more about Access, but I’m a little scared of
the tool too!

Regards,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

Jim Burke in Novi said:
Without seeing what's in your code now it's hard to say. But you probably
want to use text boxes and not listboxes. Listboxes require special
processing in order to refer to the values selected in them, while a
textbos
is just referred to directly.
Click to expand...

This is correct only if you're using a listbox with MultiSelect property set
to Extended or Simple. If set to None, then you reference the selected item
in the listbox by the ListBox's Value property, the same as you do for a
TextBox.
 
S

Steve Sanford

Hi Ryan,

I tried running the code you posted and it threw an error for me also. I
couldn't get rid of the error... it seems like it is a known problem with A2K
and ADOX.

I don't use ADO (yet) or ADOX, so I rewrote the Sub using DAO. You will need
to set a reference to "Microsoft DAO 3.6 Object Library". (I have A2K, your
version might be different)

Look for comments that begin with '######## toward the bottom of the code.
There are 3 places in the code will need to be changed: the first is the
names of the fields you want to see in the query. The other two are the names
of the date fields.

'==== beg code======
Private Sub cmdOK_Click()
On Error GoTo cmdOK_Click_Err

Dim db As DAO.Database
Dim qry As QueryDef
Dim blnQueryExists As Boolean
Dim strBroker As String
Dim strProd As String
Dim strProdCondition As String
Dim strStatus As String
Dim strSQL As String
Dim varItem As Variant
Dim tmpDate As Variant
Dim tmpDateB As Variant
Dim tmpDateE As Variant

Set db = CurrentDb
blnQueryExists = False

' check for specific query
For Each qry In db.QueryDefs
If qry.Name = "SelQuery" Then
blnQueryExists = True
Exit For
End If
Next qry

' set default SQL for query
strSQL = "SELECT [Broker], [Prod], [Status] FROM tblTFI"

' create query "SelQuery" if it doesn't exist
If Not blnQueryExists Then
Set qry = db.CreateQueryDef("SelQuery", strSQL)
End If
Application.RefreshDatabaseWindow

' Turn off screen updating
DoCmd.Echo False

' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "SelQuery") = acObjStateOpen Then
DoCmd.Close acQuery, "SelQuery"
End If

' Build criteria string for Broker
For Each varItem In Me.lstBroker.ItemsSelected
strBroker = strBroker & ",'" & Me.lstBroker.ItemData(varItem) & "'"
Next varItem
If Len(strBroker) = 0 Then
strBroker = "Like '*'"
Else
strBroker = right(strBroker, Len(strBroker) - 1)
strBroker = "IN(" & strBroker & ")"
End If

' Build criteria string for Prod
For Each varItem In Me.lstProd.ItemsSelected
strProd = strProd & ",'" & Me.lstProd.ItemData(varItem) & "'"
Next varItem
If Len(strProd) = 0 Then
strProd = "Like '*'"
Else
strProd = right(strProd, Len(strProd) - 1)
strProd = "IN(" & strProd & ")"
End If

' Build criteria string for Status
For Each varItem In Me.lstStatus.ItemsSelected
strStatus = strStatus & ",'" & Me.lstStatus.ItemData(varItem) & "'"
Next varItem
If Len(strStatus) = 0 Then
strStatus = "Like '*'"
Else
strStatus = right(strStatus, Len(strStatus) - 1)
strStatus = "IN(" & strStatus & ")"
End If

'----------------------------------------
'Create SQL string
'######## add your field names here
strSQL = "SELECT [Broker], [Prod], [Status] FROM tblTFI" & _
" WHERE tblTFI.[Broker] " & strBroker & _
" AND tblTFI.[Prod] " & strProd & _
" AND tblTFI.[Status] " & strStatus

' Build criteria string for Dates
tmpDateB = Me.lstBegin
tmpDateE = Me.lstEnd

'check if end date is > begin date and both not NULL
If Not IsNull(tmpDateB) And Not IsNull(tmpDateE) Then
If tmpDateB > tmpDateE Then
tmpDate = tmpDateE
tmpDateE = tmpDateB
tmpDateB = tmpDate
End If
End If

'######## change [YourBEGINDate] to your field name
If Not IsNull(tmpDateB) Then
strSQL = strSQL & " AND [YourBEGINDate] >= #" & CDate(tmpDateB) & "#"
End If

'######## change [YourENDDate] to your field name
If Not IsNull(tmpDateE) Then
strSQL = strSQL & " AND [YourENDDate] <= #" & CDate(tmpDateE) & "#"
End If

strSQL = strSQL & ";"
'----------------------------------------

qry.SQL = strSQL
' DoCmd.OpenQuery "SelQuery"


cmdOK_Click_Exit:
' Turn on screen updating
DoCmd.Echo True
Exit Sub
cmdOK_Click_Err:
MsgBox "An unexpected error hass occurred." _
& vbCrLf & "Procedure: cmdOK_Click" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description:" & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_Exit
End Sub
'==== end code======


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


ryguy7272 said:
Thanks for the assist there Jim. The Select suggestion worked great.

So, I guess that's half of it; now I am fiddling around with the dates thing
and I think I am totally lost.

I entered two ListBoxes; one is named lstBegin and the other is named
lstEnd. I went from design mode to run mode, and now I get this message.
An Unexpected Error Occurred.
Procedure: cmdOK_Click
Error Number: 3128
Error Description: Could not update; currently locked.

This is what I get when I simply try to run one of the former queries, using
Broker or Status, which was working fine before I popped those dates in
there. I don't know what to do now. Does anyone know what would cause this
error?

This is soooo easy in Excel!!


--
RyGuy


Jim Burke in Novi said:
To SELECT specific fields you simply specify each field name separated by a
comma, e.g.

SELECT field, field2, field3
FROM tableName
WHERE ....

What I would do with the dates (someone might have a better suggestion) is
create two date fields on the form that the user can enter start and end
date. Have a button that runs the query and 'ignores' dates, in which case
they don't enter them, and a button that is for running the query using the
dates. Behind the scenes in the VBA code I would have two fields defined
called startDate and endDate, declared as dates (e.g. dim startDate as Date).
If the user clicks the button that 'ignores' the dates I would set these to
something like:

startDate = #1/1/1900#
enDate = #1/1/2200#

where all dates you can possibly encounter will fall within the range. WIth
the other button that says use the dates I'd use:

startDate = "#" & txtStartDate & "#" (the text box with the start date)
endDate = "#" & txtENdDAte & "#" (the text box with the end date)

The '#' delimiters are needed for date values when you hardcode them in an
SQL statement. Then the part of your WHERE clause that selects by date would
say:

" WHERE dateField BETWEEN " & startDate & " AND " & endDate

Also, in code you included, your WHERE clause needs '=' signs, e.g.

"WHERE tblTFI.[Broker] = " & strBroker

and if it's a text field then you also need to surround the value with
quotes, e.g.

"WHERE tblTFI.[Broker] = '" & strBroker & "' " ...

And you also need to make sure you hav spaces in your SQL between all
keywords, values, etc., e.g.

strSQL = "SELECT tblTFI.* FROM tblTFI " & _
" WHERE tblTFI.[Broker] =" & strBroker & _
" AND tblTFI.[Prod] =" & strProd & _
" AND tblTFI.[Status]= " & strStatus & ";"
Click to expand...


Hope this helps and hope it's not too complicated for your purposes! There
may be a better way.

ryguy7272 said:
Hello! I am relatively new to Access development work. I have been doing
excel programming for a few yeas now, and understand that tool pretty well,
but using Access is a completely different experience. Anyway, I am
currently using a sample that I found at this site:

http://www.fontstuff.com/access/acctut18.htm

The file at the bottom of the page is: AccessAndSQL5_2000.zip

I mostly got it working for my particular scenario, thanks to some great
people on this DG. I still haven’t figured out two things.

1) How can I choose the columns that displayed in the query?

I am using this piece of code now:
strSQL = "SELECT tblTFI.* FROM tblTFI " & _
"WHERE tblTFI.[Broker] " & strBroker & _
"AND tblTFI.[Prod] " & strProd & _
"AND tblTFI.[Status] " & strStatus & ";"

I know the SELECT tblTFI.* part selects ALL columns form the table. I have
about 50 columns in the table, but want to display only about 12 or so. I
would really like to display certain columns, such as ‘Project Name’,
‘Broker’, ‘Project’, ‘Sub-Project’, ‘Status’, and a few others, but not ALL
columns.

2) How can I modify the code to ask me for a date range, such as a
beginning date and an ending date? Right now I build a query based on one of
three criteria: Broker, Product, or Status. I suspect I will have to add
something on the form, like a check box, that causes the date-range question
pop up after the query based on the three criteria is entered, and just
before the query is actually built. Else, if the box is not checked, just
return everything that matches the query criteria.

The code that I am running right now is listed below:
Option Compare Database
Option Explicit

' This code uses ADO and ADOX and is suitable for Access 2000 (and later).
' A reference must be set to Microsoft ADO Ext. 2.7 for DDL and Security.

Private Sub cmdOK_Click()
On Error GoTo cmdOK_Click_Err
Dim blnQueryExists As Boolean
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
Dim varItem As Variant
Dim strBroker As String
Dim strProd As String
Dim strProdCondition As String
Dim strStatus As String
Dim strSQL As String
blnQueryExists = False
Set cat.ActiveConnection = CurrentProject.Connection
For Each qry In cat.Views
If qry.Name = "SelQuery" Then
blnQueryExists = True
Exit For
End If
Next qry
' Create the query if it does not already exist
If blnQueryExists = False Then
cmd.CommandText = "SELECT * FROM tblStaff"
cat.Views.Append "SelQuery", cmd
End If
Application.RefreshDatabaseWindow
' Turn off screen updating
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "SelQuery") = acObjStateOpen
Then
DoCmd.Close acQuery, "SelQuery"
End If
' Build criteria string for Broker
For Each varItem In Me.lstBroker.ItemsSelected
strBroker = strBroker & ",'" & Me.lstBroker.ItemData(varItem) & "'"
Next varItem
If Len(strBroker) = 0 Then
strBroker = "Like '*'"
Else
strBroker = Right(strBroker, Len(strBroker) - 1)
strBroker = "IN(" & strBroker & ")"
End If

' Build criteria string for Prod
For Each varItem In Me.lstProd.ItemsSelected
strProd = strProd & ",'" & Me.lstProd.ItemData(varItem) & "'"
Next varItem
If Len(strProd) = 0 Then
strProd = "Like '*'"
Else
strProd = Right(strProd, Len(strProd) - 1)
strProd = "IN(" & strProd & ")"
End If

' Build criteria string for Status
For Each varItem In Me.lstStatus.ItemsSelected
strStatus = strStatus & ",'" & Me.lstStatus.ItemData(varItem) & "'"
Next varItem
If Len(strStatus) = 0 Then
strStatus = "Like '*'"
Else
strStatus = Right(strStatus, Len(strStatus) - 1)
strStatus = "IN(" & strStatus & ")"
End If

strSQL = "SELECT tblTFI.* FROM tblTFI " & _
"WHERE tblTFI.[Broker] " & strBroker & _
"AND tblTFI.[Prod] " & strProd & _
"AND tblTFI.[Status] " & strStatus & ";"


' Apply the SQL statement to the stored query
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("SelQuery").Command
cmd.CommandText = strSQL
Set cat.Views("SelQuery").Command = cmd
Set cat = Nothing
' Open the Query
DoCmd.OpenQuery "SelQuery"

cmdOK_Click_Exit:
DoCmd.Echo True
Exit Sub
cmdOK_Click_Err:
MsgBox "An unexpected error hass occurred." _
& vbCrLf & "Procedure: cmdOK_Click" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description:" & Err.DESCRIPTION _
, vbCritical, "Error"
Resume cmdOK_Click_Exit
End Sub

The SQL in the query looks like this:
SELECT tblTFI.*
FROM tblTFI
WHERE tblTFI.[Broker] Like '*'AND tblTFI.[Prod] Like '*'AND tblTFI.[Status]
Like '*';


If someone could please help me out with this I’d reapply appreciate it!! I
am looking forward to learning more about Access, but I’m a little scared of
the tool too!

Regards,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
R

ryguy7272

Wow!! This is very powerful stuff!! I think I am almost there, but it seems
like there is still one open item. It doesn't seem like the dates affect the
results of the query at all.

For the beginning date I added a ListBox and named it:
lstBegin
The row source is:
SELECT tblTFI.ID, tblTFI.[DATE REC] FROM tblTFI;
Multi Select = Simple

For the ending date I added another ListBox and named it:
lstEnd
The row source is:
SELECT tblTFI.ID, tblTFI.[FINAL DATE] FROM tblTFI;
Multi Select = Simple

The SQL is:
'----------------------------------------
'Create SQL string
'######## add your field names here
strSQL = "SELECT [Broker], [Prod], [Status], [Date Rec], [Final Date] FROM
tblTFI" & _
" WHERE tblTFI.[Broker] " & strBroker & _
" AND tblTFI.[Prod] " & strProd & _
" AND tblTFI.[Status] " & strStatus

As far as I can tell, the strings for each are:
tmpDateB = Me.lstBegin
tmpDateE = Me.lstEnd

And, these are used in the query here:
'######## change [YourBEGINDate] to your field name
If Not IsNull(tmpDateB) Then
strSQL = strSQL & " AND [YourBEGINDate] >= #" & CDate(tmpDateB) & "#"
End If

'######## change [YourENDDate] to your field name
If Not IsNull(tmpDateE) Then
strSQL = strSQL & " AND [YourENDDate] <= #" & CDate(tmpDateE) & "#"
End If

So, anyway, it seems like when I select the dates from the ListBoxes, it
doesn't affect the query results at all. I can certainly post all the code,
but I think the problem is in the parts that I posted here somewhere... I
certainly don't have anything against TextBoxes, and I would be eager to use
those to capture the info for the beginning and end dates, but I was afraid
to do that because I don't know how Access will handle the query if the data
that a user inputs is not one of the actual dates in the list. Maybe that
would be easier, and if the date is not in the list, just pick the next lower
date for the begin date and the next higher date for the end date.

BTW, I uncommented the line with:
DoCmd.OpenQuery "SelQuery"
Now the query results open automatically.

I'm going to fiddle with it a little more. In the meantime, if anyone has a
solution to help me resolve those date issues, please post back.

Thanks so much for everything!!
Ryan--

--
RyGuy


Steve Sanford said:
Hi Ryan,

I tried running the code you posted and it threw an error for me also. I
couldn't get rid of the error... it seems like it is a known problem with A2K
and ADOX.

I don't use ADO (yet) or ADOX, so I rewrote the Sub using DAO. You will need
to set a reference to "Microsoft DAO 3.6 Object Library". (I have A2K, your
version might be different)

Look for comments that begin with '######## toward the bottom of the code.
There are 3 places in the code will need to be changed: the first is the
names of the fields you want to see in the query. The other two are the names
of the date fields.

'==== beg code======
Private Sub cmdOK_Click()
On Error GoTo cmdOK_Click_Err

Dim db As DAO.Database
Dim qry As QueryDef
Dim blnQueryExists As Boolean
Dim strBroker As String
Dim strProd As String
Dim strProdCondition As String
Dim strStatus As String
Dim strSQL As String
Dim varItem As Variant
Dim tmpDate As Variant
Dim tmpDateB As Variant
Dim tmpDateE As Variant

Set db = CurrentDb
blnQueryExists = False

' check for specific query
For Each qry In db.QueryDefs
If qry.Name = "SelQuery" Then
blnQueryExists = True
Exit For
End If
Next qry

' set default SQL for query
strSQL = "SELECT [Broker], [Prod], [Status] FROM tblTFI"

' create query "SelQuery" if it doesn't exist
If Not blnQueryExists Then
Set qry = db.CreateQueryDef("SelQuery", strSQL)
End If
Application.RefreshDatabaseWindow

' Turn off screen updating
DoCmd.Echo False

' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "SelQuery") = acObjStateOpen Then
DoCmd.Close acQuery, "SelQuery"
End If

' Build criteria string for Broker
For Each varItem In Me.lstBroker.ItemsSelected
strBroker = strBroker & ",'" & Me.lstBroker.ItemData(varItem) & "'"
Next varItem
If Len(strBroker) = 0 Then
strBroker = "Like '*'"
Else
strBroker = right(strBroker, Len(strBroker) - 1)
strBroker = "IN(" & strBroker & ")"
End If

' Build criteria string for Prod
For Each varItem In Me.lstProd.ItemsSelected
strProd = strProd & ",'" & Me.lstProd.ItemData(varItem) & "'"
Next varItem
If Len(strProd) = 0 Then
strProd = "Like '*'"
Else
strProd = right(strProd, Len(strProd) - 1)
strProd = "IN(" & strProd & ")"
End If

' Build criteria string for Status
For Each varItem In Me.lstStatus.ItemsSelected
strStatus = strStatus & ",'" & Me.lstStatus.ItemData(varItem) & "'"
Next varItem
If Len(strStatus) = 0 Then
strStatus = "Like '*'"
Else
strStatus = right(strStatus, Len(strStatus) - 1)
strStatus = "IN(" & strStatus & ")"
End If

'----------------------------------------
'Create SQL string
'######## add your field names here
strSQL = "SELECT [Broker], [Prod], [Status] FROM tblTFI" & _
" WHERE tblTFI.[Broker] " & strBroker & _
" AND tblTFI.[Prod] " & strProd & _
" AND tblTFI.[Status] " & strStatus

' Build criteria string for Dates
tmpDateB = Me.lstBegin
tmpDateE = Me.lstEnd

'check if end date is > begin date and both not NULL
If Not IsNull(tmpDateB) And Not IsNull(tmpDateE) Then
If tmpDateB > tmpDateE Then
tmpDate = tmpDateE
tmpDateE = tmpDateB
tmpDateB = tmpDate
End If
End If

'######## change [YourBEGINDate] to your field name
If Not IsNull(tmpDateB) Then
strSQL = strSQL & " AND [YourBEGINDate] >= #" & CDate(tmpDateB) & "#"
End If

'######## change [YourENDDate] to your field name
If Not IsNull(tmpDateE) Then
strSQL = strSQL & " AND [YourENDDate] <= #" & CDate(tmpDateE) & "#"
End If

strSQL = strSQL & ";"
'----------------------------------------

qry.SQL = strSQL
' DoCmd.OpenQuery "SelQuery"


cmdOK_Click_Exit:
' Turn on screen updating
DoCmd.Echo True
Exit Sub
cmdOK_Click_Err:
MsgBox "An unexpected error hass occurred." _
& vbCrLf & "Procedure: cmdOK_Click" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description:" & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_Exit
End Sub
'==== end code======


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


ryguy7272 said:
Thanks for the assist there Jim. The Select suggestion worked great.

So, I guess that's half of it; now I am fiddling around with the dates thing
and I think I am totally lost.

I entered two ListBoxes; one is named lstBegin and the other is named
lstEnd. I went from design mode to run mode, and now I get this message.
An Unexpected Error Occurred.
Procedure: cmdOK_Click
Error Number: 3128
Error Description: Could not update; currently locked.

This is what I get when I simply try to run one of the former queries, using
Broker or Status, which was working fine before I popped those dates in
there. I don't know what to do now. Does anyone know what would cause this
error?

This is soooo easy in Excel!!


--
RyGuy


Jim Burke in Novi said:
To SELECT specific fields you simply specify each field name separated by a
comma, e.g.

SELECT field, field2, field3
FROM tableName
WHERE ....

What I would do with the dates (someone might have a better suggestion) is
create two date fields on the form that the user can enter start and end
date. Have a button that runs the query and 'ignores' dates, in which case
they don't enter them, and a button that is for running the query using the
dates. Behind the scenes in the VBA code I would have two fields defined
called startDate and endDate, declared as dates (e.g. dim startDate as Date).
If the user clicks the button that 'ignores' the dates I would set these to
something like:

startDate = #1/1/1900#
enDate = #1/1/2200#

where all dates you can possibly encounter will fall within the range. WIth
the other button that says use the dates I'd use:

startDate = "#" & txtStartDate & "#" (the text box with the start date)
endDate = "#" & txtENdDAte & "#" (the text box with the end date)

The '#' delimiters are needed for date values when you hardcode them in an
SQL statement. Then the part of your WHERE clause that selects by date would
say:

" WHERE dateField BETWEEN " & startDate & " AND " & endDate

Also, in code you included, your WHERE clause needs '=' signs, e.g.

"WHERE tblTFI.[Broker] = " & strBroker

and if it's a text field then you also need to surround the value with
quotes, e.g.

"WHERE tblTFI.[Broker] = '" & strBroker & "' " ...

And you also need to make sure you hav spaces in your SQL between all
keywords, values, etc., e.g.

strSQL = "SELECT tblTFI.* FROM tblTFI " & _
" WHERE tblTFI.[Broker] =" & strBroker & _
" AND tblTFI.[Prod] =" & strProd & _
" AND tblTFI.[Status]= " & strStatus & ";"


Hope this helps and hope it's not too complicated for your purposes! There
may be a better way.

:

Hello! I am relatively new to Access development work. I have been doing
excel programming for a few yeas now, and understand that tool pretty well,
but using Access is a completely different experience. Anyway, I am
currently using a sample that I found at this site:

http://www.fontstuff.com/access/acctut18.htm

The file at the bottom of the page is: AccessAndSQL5_2000.zip

I mostly got it working for my particular scenario, thanks to some great
people on this DG. I still haven’t figured out two things.

1) How can I choose the columns that displayed in the query?

I am using this piece of code now:
strSQL = "SELECT tblTFI.* FROM tblTFI " & _
"WHERE tblTFI.[Broker] " & strBroker & _
"AND tblTFI.[Prod] " & strProd & _
"AND tblTFI.[Status] " & strStatus & ";"

I know the SELECT tblTFI.* part selects ALL columns form the table. I have
about 50 columns in the table, but want to display only about 12 or so. I
would really like to display certain columns, such as ‘Project Name’,
‘Broker’, ‘Project’, ‘Sub-Project’, ‘Status’, and a few others, but not ALL
columns.

2) How can I modify the code to ask me for a date range, such as a
beginning date and an ending date? Right now I build a query based on one of
three criteria: Broker, Product, or Status. I suspect I will have to add
something on the form, like a check box, that causes the date-range question
pop up after the query based on the three criteria is entered, and just
before the query is actually built. Else, if the box is not checked, just
return everything that matches the query criteria.

The code that I am running right now is listed below:
Option Compare Database
Option Explicit

' This code uses ADO and ADOX and is suitable for Access 2000 (and later).
' A reference must be set to Microsoft ADO Ext. 2.7 for DDL and Security.

Private Sub cmdOK_Click()
On Error GoTo cmdOK_Click_Err
Dim blnQueryExists As Boolean
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
Dim varItem As Variant
Dim strBroker As String
Dim strProd As String
Dim strProdCondition As String
Dim strStatus As String
Dim strSQL As String
blnQueryExists = False
Set cat.ActiveConnection = CurrentProject.Connection
For Each qry In cat.Views
If qry.Name = "SelQuery" Then
blnQueryExists = True
Exit For
End If
Next qry
' Create the query if it does not already exist
If blnQueryExists = False Then
cmd.CommandText = "SELECT * FROM tblStaff"
Click to expand...
Click to expand...
Click to expand...
 
R

ryguy7272

Ah, yes, I love it when the answer comes to me just moments after I post…


Before:
'######## change [YourBEGINDate] to your field name
If Not IsNull(tmpDateB) Then
strSQL = strSQL & " AND [YourBEGINDate] >= #" & CDate(tmpDateB) & "#"
End If

'######## change [YourENDDate] to your field name
If Not IsNull(tmpDateE) Then
strSQL = strSQL & " AND [YourENDDate] <= #" & CDate(tmpDateE) & "#"
End If

After:
'######## change [YourBEGINDate] to your field name
If Not IsNull(tmpDateB) Then
strSQL = strSQL & " AND [Date Rec] >= #" & CDate(tmpDateB) & "#"
End If

'######## change [YourENDDate] to your field name
If Not IsNull(tmpDateE) Then
strSQL = strSQL & " AND [Final Date] <= #" & CDate(tmpDateE) & "#"
End If

OK!! Problem resolved!!


Regards,
Ryan--

--
RyGuy


ryguy7272 said:
Wow!! This is very powerful stuff!! I think I am almost there, but it seems
like there is still one open item. It doesn't seem like the dates affect the
results of the query at all.

For the beginning date I added a ListBox and named it:
lstBegin
The row source is:
SELECT tblTFI.ID, tblTFI.[DATE REC] FROM tblTFI;
Multi Select = Simple

For the ending date I added another ListBox and named it:
lstEnd
The row source is:
SELECT tblTFI.ID, tblTFI.[FINAL DATE] FROM tblTFI;
Multi Select = Simple

The SQL is:
'----------------------------------------
'Create SQL string
'######## add your field names here
strSQL = "SELECT [Broker], [Prod], [Status], [Date Rec], [Final Date] FROM
tblTFI" & _
" WHERE tblTFI.[Broker] " & strBroker & _
" AND tblTFI.[Prod] " & strProd & _
" AND tblTFI.[Status] " & strStatus

As far as I can tell, the strings for each are:
tmpDateB = Me.lstBegin
tmpDateE = Me.lstEnd

And, these are used in the query here:
'######## change [YourBEGINDate] to your field name
If Not IsNull(tmpDateB) Then
strSQL = strSQL & " AND [YourBEGINDate] >= #" & CDate(tmpDateB) & "#"
End If

'######## change [YourENDDate] to your field name
If Not IsNull(tmpDateE) Then
strSQL = strSQL & " AND [YourENDDate] <= #" & CDate(tmpDateE) & "#"
End If

So, anyway, it seems like when I select the dates from the ListBoxes, it
doesn't affect the query results at all. I can certainly post all the code,
but I think the problem is in the parts that I posted here somewhere... I
certainly don't have anything against TextBoxes, and I would be eager to use
those to capture the info for the beginning and end dates, but I was afraid
to do that because I don't know how Access will handle the query if the data
that a user inputs is not one of the actual dates in the list. Maybe that
would be easier, and if the date is not in the list, just pick the next lower
date for the begin date and the next higher date for the end date.

BTW, I uncommented the line with:
DoCmd.OpenQuery "SelQuery"
Now the query results open automatically.

I'm going to fiddle with it a little more. In the meantime, if anyone has a
solution to help me resolve those date issues, please post back.

Thanks so much for everything!!
Ryan--

--
RyGuy


Steve Sanford said:
Hi Ryan,

I tried running the code you posted and it threw an error for me also. I
couldn't get rid of the error... it seems like it is a known problem with A2K
and ADOX.

I don't use ADO (yet) or ADOX, so I rewrote the Sub using DAO. You will need
to set a reference to "Microsoft DAO 3.6 Object Library". (I have A2K, your
version might be different)

Look for comments that begin with '######## toward the bottom of the code.
There are 3 places in the code will need to be changed: the first is the
names of the fields you want to see in the query. The other two are the names
of the date fields.

'==== beg code======
Private Sub cmdOK_Click()
On Error GoTo cmdOK_Click_Err

Dim db As DAO.Database
Dim qry As QueryDef
Dim blnQueryExists As Boolean
Dim strBroker As String
Dim strProd As String
Dim strProdCondition As String
Dim strStatus As String
Dim strSQL As String
Dim varItem As Variant
Dim tmpDate As Variant
Dim tmpDateB As Variant
Dim tmpDateE As Variant

Set db = CurrentDb
blnQueryExists = False

' check for specific query
For Each qry In db.QueryDefs
If qry.Name = "SelQuery" Then
blnQueryExists = True
Exit For
End If
Next qry

' set default SQL for query
strSQL = "SELECT [Broker], [Prod], [Status] FROM tblTFI"

' create query "SelQuery" if it doesn't exist
If Not blnQueryExists Then
Set qry = db.CreateQueryDef("SelQuery", strSQL)
End If
Application.RefreshDatabaseWindow

' Turn off screen updating
DoCmd.Echo False

' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "SelQuery") = acObjStateOpen Then
DoCmd.Close acQuery, "SelQuery"
End If

' Build criteria string for Broker
For Each varItem In Me.lstBroker.ItemsSelected
strBroker = strBroker & ",'" & Me.lstBroker.ItemData(varItem) & "'"
Next varItem
If Len(strBroker) = 0 Then
strBroker = "Like '*'"
Else
strBroker = right(strBroker, Len(strBroker) - 1)
strBroker = "IN(" & strBroker & ")"
End If

' Build criteria string for Prod
For Each varItem In Me.lstProd.ItemsSelected
strProd = strProd & ",'" & Me.lstProd.ItemData(varItem) & "'"
Next varItem
If Len(strProd) = 0 Then
strProd = "Like '*'"
Else
strProd = right(strProd, Len(strProd) - 1)
strProd = "IN(" & strProd & ")"
End If

' Build criteria string for Status
For Each varItem In Me.lstStatus.ItemsSelected
strStatus = strStatus & ",'" & Me.lstStatus.ItemData(varItem) & "'"
Next varItem
If Len(strStatus) = 0 Then
strStatus = "Like '*'"
Else
strStatus = right(strStatus, Len(strStatus) - 1)
strStatus = "IN(" & strStatus & ")"
End If

'----------------------------------------
'Create SQL string
'######## add your field names here
strSQL = "SELECT [Broker], [Prod], [Status] FROM tblTFI" & _
" WHERE tblTFI.[Broker] " & strBroker & _
" AND tblTFI.[Prod] " & strProd & _
" AND tblTFI.[Status] " & strStatus

' Build criteria string for Dates
tmpDateB = Me.lstBegin
tmpDateE = Me.lstEnd

'check if end date is > begin date and both not NULL
If Not IsNull(tmpDateB) And Not IsNull(tmpDateE) Then
If tmpDateB > tmpDateE Then
tmpDate = tmpDateE
tmpDateE = tmpDateB
tmpDateB = tmpDate
End If
End If

'######## change [YourBEGINDate] to your field name
If Not IsNull(tmpDateB) Then
strSQL = strSQL & " AND [YourBEGINDate] >= #" & CDate(tmpDateB) & "#"
End If

'######## change [YourENDDate] to your field name
If Not IsNull(tmpDateE) Then
strSQL = strSQL & " AND [YourENDDate] <= #" & CDate(tmpDateE) & "#"
End If

strSQL = strSQL & ";"
'----------------------------------------

qry.SQL = strSQL
' DoCmd.OpenQuery "SelQuery"


cmdOK_Click_Exit:
' Turn on screen updating
DoCmd.Echo True
Exit Sub
cmdOK_Click_Err:
MsgBox "An unexpected error hass occurred." _
& vbCrLf & "Procedure: cmdOK_Click" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description:" & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_Exit
End Sub
'==== end code======


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


ryguy7272 said:
Thanks for the assist there Jim. The Select suggestion worked great.

So, I guess that's half of it; now I am fiddling around with the dates thing
and I think I am totally lost.

I entered two ListBoxes; one is named lstBegin and the other is named
lstEnd. I went from design mode to run mode, and now I get this message.
An Unexpected Error Occurred.
Procedure: cmdOK_Click
Error Number: 3128
Error Description: Could not update; currently locked.

This is what I get when I simply try to run one of the former queries, using
Broker or Status, which was working fine before I popped those dates in
there. I don't know what to do now. Does anyone know what would cause this
error?

This is soooo easy in Excel!!


--
RyGuy


:

To SELECT specific fields you simply specify each field name separated by a
comma, e.g.

SELECT field, field2, field3
FROM tableName
WHERE ....

What I would do with the dates (someone might have a better suggestion) is
create two date fields on the form that the user can enter start and end
date. Have a button that runs the query and 'ignores' dates, in which case
they don't enter them, and a button that is for running the query using the
dates. Behind the scenes in the VBA code I would have two fields defined
called startDate and endDate, declared as dates (e.g. dim startDate as Date).
If the user clicks the button that 'ignores' the dates I would set these to
something like:

startDate = #1/1/1900#
enDate = #1/1/2200#

where all dates you can possibly encounter will fall within the range. WIth
the other button that says use the dates I'd use:

startDate = "#" & txtStartDate & "#" (the text box with the start date)
endDate = "#" & txtENdDAte & "#" (the text box with the end date)

The '#' delimiters are needed for date values when you hardcode them in an
SQL statement. Then the part of your WHERE clause that selects by date would
say:

" WHERE dateField BETWEEN " & startDate & " AND " & endDate

Also, in code you included, your WHERE clause needs '=' signs, e.g.

"WHERE tblTFI.[Broker] = " & strBroker

and if it's a text field then you also need to surround the value with
quotes, e.g.

"WHERE tblTFI.[Broker] = '" & strBroker & "' " ...

And you also need to make sure you hav spaces in your SQL between all
keywords, values, etc., e.g.

strSQL = "SELECT tblTFI.* FROM tblTFI " & _
" WHERE tblTFI.[Broker] =" & strBroker & _
" AND tblTFI.[Prod] =" & strProd & _
" AND tblTFI.[Status]= " & strStatus & ";"


Hope this helps and hope it's not too complicated for your purposes! There
may be a better way.
Click to expand...
Click to expand...
Click to expand...
 
S

Steve Sanford

Ryan,

Glad it is finally working...

Just one thing - the multi-select setting for the two date list boxes should
be set to "None", not "Simple". "Simple" allows multiple selections to be
made in the list box. It doesn't make sense to have multiple begining dates
and multiple ending dates - nor does the code use multiple beg/end dates.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


ryguy7272 said:
Ah, yes, I love it when the answer comes to me just moments after I post…


Before:
'######## change [YourBEGINDate] to your field name
If Not IsNull(tmpDateB) Then
strSQL = strSQL & " AND [YourBEGINDate] >= #" & CDate(tmpDateB) & "#"
End If

'######## change [YourENDDate] to your field name
If Not IsNull(tmpDateE) Then
strSQL = strSQL & " AND [YourENDDate] <= #" & CDate(tmpDateE) & "#"
End If

After:
'######## change [YourBEGINDate] to your field name
If Not IsNull(tmpDateB) Then
strSQL = strSQL & " AND [Date Rec] >= #" & CDate(tmpDateB) & "#"
End If

'######## change [YourENDDate] to your field name
If Not IsNull(tmpDateE) Then
strSQL = strSQL & " AND [Final Date] <= #" & CDate(tmpDateE) & "#"
End If

OK!! Problem resolved!!


Regards,
Ryan--

--
RyGuy


ryguy7272 said:
Wow!! This is very powerful stuff!! I think I am almost there, but it seems
like there is still one open item. It doesn't seem like the dates affect the
results of the query at all.

For the beginning date I added a ListBox and named it:
lstBegin
The row source is:
SELECT tblTFI.ID, tblTFI.[DATE REC] FROM tblTFI;
Multi Select = Simple

For the ending date I added another ListBox and named it:
lstEnd
The row source is:
SELECT tblTFI.ID, tblTFI.[FINAL DATE] FROM tblTFI;
Multi Select = Simple

The SQL is:
'----------------------------------------
'Create SQL string
'######## add your field names here
strSQL = "SELECT [Broker], [Prod], [Status], [Date Rec], [Final Date] FROM
tblTFI" & _
" WHERE tblTFI.[Broker] " & strBroker & _
" AND tblTFI.[Prod] " & strProd & _
" AND tblTFI.[Status] " & strStatus

As far as I can tell, the strings for each are:
tmpDateB = Me.lstBegin
tmpDateE = Me.lstEnd

And, these are used in the query here:
'######## change [YourBEGINDate] to your field name
If Not IsNull(tmpDateB) Then
strSQL = strSQL & " AND [YourBEGINDate] >= #" & CDate(tmpDateB) & "#"
End If

'######## change [YourENDDate] to your field name
If Not IsNull(tmpDateE) Then
strSQL = strSQL & " AND [YourENDDate] <= #" & CDate(tmpDateE) & "#"
End If

So, anyway, it seems like when I select the dates from the ListBoxes, it
doesn't affect the query results at all. I can certainly post all the code,
but I think the problem is in the parts that I posted here somewhere... I
certainly don't have anything against TextBoxes, and I would be eager to use
those to capture the info for the beginning and end dates, but I was afraid
to do that because I don't know how Access will handle the query if the data
that a user inputs is not one of the actual dates in the list. Maybe that
would be easier, and if the date is not in the list, just pick the next lower
date for the begin date and the next higher date for the end date.

BTW, I uncommented the line with:
DoCmd.OpenQuery "SelQuery"
Now the query results open automatically.

I'm going to fiddle with it a little more. In the meantime, if anyone has a
solution to help me resolve those date issues, please post back.

Thanks so much for everything!!
Ryan--

--
RyGuy


Steve Sanford said:
Hi Ryan,

I tried running the code you posted and it threw an error for me also. I
couldn't get rid of the error... it seems like it is a known problem with A2K
and ADOX.

I don't use ADO (yet) or ADOX, so I rewrote the Sub using DAO. You will need
to set a reference to "Microsoft DAO 3.6 Object Library". (I have A2K, your
version might be different)

Look for comments that begin with '######## toward the bottom of the code.
There are 3 places in the code will need to be changed: the first is the
names of the fields you want to see in the query. The other two are the names
of the date fields.

'==== beg code======
Private Sub cmdOK_Click()
On Error GoTo cmdOK_Click_Err

Dim db As DAO.Database
Dim qry As QueryDef
Dim blnQueryExists As Boolean
Dim strBroker As String
Dim strProd As String
Dim strProdCondition As String
Dim strStatus As String
Dim strSQL As String
Dim varItem As Variant
Dim tmpDate As Variant
Dim tmpDateB As Variant
Dim tmpDateE As Variant

Set db = CurrentDb
blnQueryExists = False

' check for specific query
For Each qry In db.QueryDefs
If qry.Name = "SelQuery" Then
blnQueryExists = True
Exit For
End If
Next qry

' set default SQL for query
strSQL = "SELECT [Broker], [Prod], [Status] FROM tblTFI"

' create query "SelQuery" if it doesn't exist
If Not blnQueryExists Then
Set qry = db.CreateQueryDef("SelQuery", strSQL)
End If
Application.RefreshDatabaseWindow

' Turn off screen updating
DoCmd.Echo False

' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "SelQuery") = acObjStateOpen Then
DoCmd.Close acQuery, "SelQuery"
End If

' Build criteria string for Broker
For Each varItem In Me.lstBroker.ItemsSelected
strBroker = strBroker & ",'" & Me.lstBroker.ItemData(varItem) & "'"
Next varItem
If Len(strBroker) = 0 Then
strBroker = "Like '*'"
Else
strBroker = right(strBroker, Len(strBroker) - 1)
strBroker = "IN(" & strBroker & ")"
End If

' Build criteria string for Prod
For Each varItem In Me.lstProd.ItemsSelected
strProd = strProd & ",'" & Me.lstProd.ItemData(varItem) & "'"
Next varItem
If Len(strProd) = 0 Then
strProd = "Like '*'"
Else
strProd = right(strProd, Len(strProd) - 1)
strProd = "IN(" & strProd & ")"
End If

' Build criteria string for Status
For Each varItem In Me.lstStatus.ItemsSelected
strStatus = strStatus & ",'" & Me.lstStatus.ItemData(varItem) & "'"
Next varItem
If Len(strStatus) = 0 Then
strStatus = "Like '*'"
Else
strStatus = right(strStatus, Len(strStatus) - 1)
strStatus = "IN(" & strStatus & ")"
End If

'----------------------------------------
'Create SQL string
'######## add your field names here
strSQL = "SELECT [Broker], [Prod], [Status] FROM tblTFI" & _
" WHERE tblTFI.[Broker] " & strBroker & _
" AND tblTFI.[Prod] " & strProd & _
" AND tblTFI.[Status] " & strStatus

' Build criteria string for Dates
tmpDateB = Me.lstBegin
tmpDateE = Me.lstEnd

'check if end date is > begin date and both not NULL
If Not IsNull(tmpDateB) And Not IsNull(tmpDateE) Then
If tmpDateB > tmpDateE Then
tmpDate = tmpDateE
tmpDateE = tmpDateB
tmpDateB = tmpDate
End If
End If

'######## change [YourBEGINDate] to your field name
If Not IsNull(tmpDateB) Then
strSQL = strSQL & " AND [YourBEGINDate] >= #" & CDate(tmpDateB) & "#"
End If

'######## change [YourENDDate] to your field name
If Not IsNull(tmpDateE) Then
strSQL = strSQL & " AND [YourENDDate] <= #" & CDate(tmpDateE) & "#"
End If

strSQL = strSQL & ";"
'----------------------------------------

qry.SQL = strSQL
' DoCmd.OpenQuery "SelQuery"


cmdOK_Click_Exit:
' Turn on screen updating
DoCmd.Echo True
Exit Sub
cmdOK_Click_Err:
MsgBox "An unexpected error hass occurred." _
& vbCrLf & "Procedure: cmdOK_Click" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description:" & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_Exit
End Sub
'==== end code======


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

Thanks for the assist there Jim. The Select suggestion worked great.

So, I guess that's half of it; now I am fiddling around with the dates thing
and I think I am totally lost.

I entered two ListBoxes; one is named lstBegin and the other is named
lstEnd. I went from design mode to run mode, and now I get this message.
An Unexpected Error Occurred.
Procedure: cmdOK_Click
Error Number: 3128
Error Description: Could not update; currently locked.

This is what I get when I simply try to run one of the former queries, using
Broker or Status, which was working fine before I popped those dates in
there. I don't know what to do now. Does anyone know what would cause this
error?

This is soooo easy in Excel!!


--
RyGuy


:

To SELECT specific fields you simply specify each field name separated by a
comma, e.g.

SELECT field, field2, field3
FROM tableName
WHERE ....

What I would do with the dates (someone might have a better suggestion) is
create two date fields on the form that the user can enter start and end
date. Have a button that runs the query and 'ignores' dates, in which case
they don't enter them, and a button that is for running the query using the
dates. Behind the scenes in the VBA code I would have two fields defined
called startDate and endDate, declared as dates (e.g. dim startDate as Date).
If the user clicks the button that 'ignores' the dates I would set these to
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using a Form to Create a Query. Problem with VBA code... 2
Form linked to Linked Excel Table via ComboBox 10
Delete a Query, or Empty it and Reload With New SQL? 3
Row Source in Form crashes Access 6
Build Dynamic Query from Form 6
Use QBE Grid with Double-Quotes and/or Apostrophe 2
Issue with UNC Path (I think). Trying to build Dynamic Report 11
Query to Filter by Dates, Using Form 7

Top