RE: Seeking Help With VBA and SQL Instant Query

Tech-Archive recommends: Speed Up your PC by fixing your registry

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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.
.

Quantcast