Building Queries On the Fly from Martin Greene (1 Viewer)

BDW Jr

Registered User.
Local time
Today, 10:02
Joined
May 10, 2009
Messages
18
Hi! I came across this code from Martin Greene but need a little assistance on it.

http://www.fontstuff.com/images/acctut17n.gif
http://www.fontstuff.com/access/acctut17a.htm#download

I'm trying to create one query from a form that will help users that don't know exactly what they're looking for. If the user leaves the field blank, the query will bring up all values and not a blank query. When the user selects a specific criteria such as Requestor, the query will return all records with that requestor's name. If the user selects another field such as Data Type, the query will return all records with that requestor's name and data type requested. Basically, it will whittle down the original request of all records. Now what I'm trying to do is add a date range but keep getting an error message. I've searched this site and a bunch more but cannot find an answer. I'm pretty sure its something obvious that I'm not seeing. Below is my code. Can someone please help with the code? Much appreciated!


Private Sub cmdOK_Click()
' Pointer to error handler
On Error GoTo cmdOK_Click_err
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strRequestor As String
Me.QueryRptComm.Visible = True
Dim strCRDCR As String
Dim strFromDate As String
Dim strToDate As String
Dim strTypeofData As String
Dim strStatus As String
Dim strInitiatedBy As String
Dim strSQL As String
' Identify the database and assign it to the variable
Set db = CurrentDb
' Check for the existence of the query, create it if not found,
' and assign it to the variable

If Not QueryExists("qryALL") Then
Set qdf = db.CreateQueryDef("qryALL")
Else
Set qdf = db.QueryDefs("qryALL")
End If
' Get the values from the combo boxes
If IsNull(Me.cboRequestor.Value) Then
strRequestor = " Like '*' "
Else
strRequestor = "='" & Me.cboRequestor.Value & "' "
End If
If IsNull(Me.cboCRDCR.Value) Then
strCRDCR = " Like '*' "
Else
strCRDCR = "='" & Me.cboCRDCR.Value & "' "
End If
If IsNull(Me.cboFromDate.Value) Then
strFromDate = " Like '*' "
Else
strFromDate = "#" & Me.cboFromDate.Value & "# "
End If
If IsNull(Me.cboToDate.Value) Then
strToDate = " Like '*' "
Else
strToDate = "=#" & Me.cboToDate.Value & "# "
End If
If IsNull(Me.cboTypeofData.Value) Then
strTypeofData = " Like '*' "
Else
strTypeofData = "='" & Me.cboTypeofData.Value & "' "
End If
If IsNull(Me.cboStatus.Value) Then
strStatus = " Like '*' "
Else
strStatus = "='" & Me.cboStatus.Value & "' "
End If
If IsNull(Me.cboInitiatedBy.Value) Then
strInitiatedBy = " Like '*' "
Else
strInitiatedBy = "='" & Me.cboInitiatedBy.Value & "' "
End If
Me.cboRequestor.SetFocus
' Build the SQL string
strSQL = "SELECT tblCRDCR_Requests.* " & _
"FROM tblCRDCR_Requests " & _
"WHERE tblCRDCR_Requests.Requestor" & strRequestor & _
"AND tblCRDCR_Requests.DCRCR" & strCRDCR & _
"AND tblCRDCR_Requests.DateRequested" & strFromDate & _
"AND tblCRDCR_Requests.DateRequested" & strToDate & _
"AND tblCRDCR_Requests.TypeofData" & strTypeofData & _
"AND tblCRDCR_Requests.Status" & strStatus & _
"AND tblCRDCR_Requests.InitiatedBy" & strInitiatedBy & _
"ORDER BY tblCRDCR_Requests.RequestID,tblCRDCR_Requests.Requestor;"
' Pass the SQL string to the query
qdf.SQL = strSQL
' Turn off screen updating
DoCmd.Echo False
' Check the state of the query and close it if it is open
If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qryALL") = acObjStateOpen Then
DoCmd.Close acQuery, "qryALL"
End If
' Open the query
DoCmd.OpenQuery "qryALL"
cmdOK_Click_exit:
' Turn on screen updating
DoCmd.Echo True
' Clear the object variables
Set qdf = Nothing
Set db = Nothing
Exit Sub
cmdOK_Click_err:
' Handle errors
MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note of the following details:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_exit
End Sub
 

spikepl

Eledittingent Beliped
Local time
Today, 17:02
Joined
Nov 3, 2010
Messages
6,144
Don't do everything at once.

Make 1 criterion work - remove/comment out all the other.

Do the same for the next .. and next.

Once each and one works, start combining by adding one at a time. Yes, ONE at a time.

In all cases, the way to check and test is given here: http://www.baldyweb.com/immediatewindow.htm
 

jdraw

Super Moderator
Staff member
Local time
Today, 12:02
Joined
Jan 23, 2006
Messages
15,364
Did you work through the 6 part tutorial(s)?
Martin's material is very well prepared and received. I often refer people to his site.
I agree with spikepl that you should start simple -break it into pieces - then conquer and integrate your pieces. Work through his tutorials, you'll learn things.
Martin specifically talks about working and testing with the immediate window.
 

JHB

Have been here a while
Local time
Today, 17:02
Joined
Jun 17, 2012
Messages
7,732
As the other writes, one thing of a time, in the one date criteria you've the "=" sign in the other not!
BUT you'll not get a date range with the below, for that you need to use the clause "Between" or the signs "><=" in the right combination.
And if some of you fields are empty (no data in it), they will not be returned by the query using " Like '*' ".
Code:
If IsNull(Me.cboFromDate.Value) Then
strFromDate = " Like '*' "
Else
strFromDate = "#" & Me.cboFromDate.Value & "# "
End If
If IsNull(Me.cboToDate.Value) Then
strToDate = " Like '*' "
Else
strToDate = "[B][COLOR=Red]=[/COLOR][/B]#" & Me.cboToDate.Value & "# "
End If
 

BDW Jr

Registered User.
Local time
Today, 10:02
Joined
May 10, 2009
Messages
18
JHB, thanks for the tip. I changed:

"=Between #" to ">=#"

and

"=#" to " <= #"

And date range now works.

Much appreciated!
 

Users who are viewing this thread

Top Bottom