Problems with SQL coding on VB command button

[Guest]

Hello there;
I have been working on a form that incorporates one of three search fields
as well as a to and From Date fields to display records within these search
parameters. However, I have been having a problem with getting the coding
corrected, and wondering if I can get any assistance as to why this isn't
working, or what it is I am missing. I have included the code I have so far
listed below:

Private Sub cmdSubmit_Click()

'Create variables for the search parameters
Dim txtAgentID, txtLnGroup, txtProgram As String

txtAgentID = Me.txtAgentID.Value
txtLnGroup = Me.txtLineGroup.Value
txtProgram = Me.txtSoftware.Value

'Check to see which field is filled in, and how to react to information
If txtAgentID = "" And txtLnGroup = "" And txtProgram = "" Then
MsgBox "No information was entered. Please Re-enter your information for
what your looking for. ", vbOKCancel, "Error Message"

Else
Dim strAgtID, strTxtAgtID As String
strAgtID = Me.txtAgentID.Value
strTxtAgtID = "SELECT AgentID, Date, Software, ProbDesc, LineGroup FROM
EOSMain WHERE EOSMain.[AgentID] = 'strAgtID';"

Dim strLineGroup, strTxtLineGroup As String
strLineGroup = Me.txtLineGroup.Value
strTxtLineGroup = "SELECT AgentID, Date, Software, ProbDesc, LineGroup
FROM EOSMain WHERE EOSMain.[LineGroup] = 'strLineGroup';"

Dim strProgram, txtPrgrm As String
strProgram = Me.txtSoftware.Value
strPrgrm = "SELECT AgentID, Date, Software, ProbDesc, LineGroup FROM
EOSMain WHERE EOSMain.[Software] = 'strProgram';"
End If

' Create parameters for Date Selection
Dim strFilter As String
Dim IsDate As String

If IsDate(Me.txtDateStart) And IsDate(Me.txtEndDate) Then
strFilter = "BETWEEN #" & Me.txtDateStart & "# And #" &_
Me.txtEndDate & "#"
ElseIf IsDate(Me.txtDateStart) And Not IsDate(Me.txtEndDate) Then
strFilter = ">= #" & Me.txtDateStart & "#"
ElseIf Not IsDate(Me.txtDateStart) And IsDate(Me.txtEndDate) Then
strFilter = "<= #" & Me.txtEndDate & "#"
End If

If Len(strFilter) = 0 Then 'No dates were entered, include everything
Me.FilterOn = False
Else0
Me.Filter = "EOSMain.[Date] " & strFilter
Me.FilterOn = True
End If
Me.Requery

'Take the above variables and send them to the correct Report
Dim stDocName As String
stDocName = "SoftwareSearch"
DoCmd.OpenReport stDocName, acPreview

Loop
End Sub


The problem that I am having is with the Between section as I am not too
familiar with this section. AM I missing something, and do I have the coding
correct? or is there something more that I need to be aware of?????

Thank you for any assistance at all that you can offer me regarding this
problems.....

dakoris73

 

[Svetlana]

Hi dakoris73, you could use Nz() function to handle your null values if
any.

Also i see that there are sql clauses that are not written right
Examble:
strTxtAgtID = "SELECT AgentID, Date, Software, ProbDesc, LineGroup FROM
EOSMain WHERE EOSMain.[AgentID] = 'strAgtID';"

you must change it to

strTxtAgtID = "SELECT AgentID, Date, Software, ProbDesc, LineGroup FROM
EOSMain WHERE EOSMain.[AgentID] =" & CLng(strAgtID) & ";"

if your AgentID is a long Data type

or

strTxtAgtID = "SELECT AgentID, Date, Software, ProbDesc, LineGroup FROM
EOSMain WHERE EOSMain.[AgentID] =""" & strAgtID & """;"

if your AgentID is string data type - I hope not

Additional for dates before passing them into your sql clauses try to
validate them as dates with the function CDate().

Make these changes and see how it works.

 

[Guest]

Hello Svetlana;

Thank you for your prompt response to my request. I am a little unsure as to
the CDate() function that you are refering to, and not sure if this will help
my situation. on the original post, where I included the code, the section
that uses the IsDate feature is giving an error message there refering to
"Expecting Array" error message. Not sure if this is because my coding if off
on that section or not. I also should probably mention that I am still quite
unfamiliar with all the newonces with Access, as I am still learning this
portion of programming, so just thought that I would let you know.... and
thank you again for any assistacnce you might be able to assist me with on
this.....