Mutliple Between/And Criteria But Only One To Be Chosen (1 Viewer)

themurph2000

Fat, drunk, and stupid
Local time
Today, 08:16
Joined
Sep 24, 2007
Messages
181
Another one of those that should be a piece of cake but I'm having one of those :banghead: type of days...

I have a form where users can enter the beginning and ending dates into text boxes. They could also do it for the number of the week (1 to 52) or a single text box for the quarter.

I set up VBA code where, if you click on a button, it will clear out the data from the other two options before a query is run based on the parameters in those text boxes. Here's a sample piece of code.

Code:
Private Sub btn_UseDates_Click()
 Dim lngGreen As Long, lngWhite As Long
 
 lngGreen = RGB(0, 255, 0)
 lngWhite = RGB(255, 255, 255)
Me!txtDateFrom.BackColor = lngGreen
Me!txtDateTo.BackColor = lngGreen
Me!txtWeekFrom.BackColor = lngWhite
Me!txtWeekTo.BackColor = lngWhite
Me!txtQuarter.BackColor = lngWhite
Me!txtWeekFrom = ""
Me!txtWeekTo = ""
Me!txtQuarter = ""
End Sub

I've tried setting those txt boxes to Null and I've tried both "" and " ", but to no avail. The query will work if the three types of ranges (date, week, or quarter) are on OR lines, but not on the same line. Any advice?

Thanks
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:16
Joined
Jul 9, 2003
Messages
16,245
I'm guessing here because you don't say! I'm guessing that the query is not returning the results you want. From what you say, you think that it is because you need to set the text boxes to Null, however that will only make the query thats looking for the result from the textbox search for records which are Null. Does this sound about right as to what's wrong with your Query? if so please post the SQL of the query
 

themurph2000

Fat, drunk, and stupid
Local time
Today, 08:16
Joined
Sep 24, 2007
Messages
181
I'm guessing here because you don't say! I'm guessing that the query is not returning the results you want. From what you say, you think that it is because you need to set the text boxes to Null, however that will only make the query thats looking for the result from the textbox search for records which are Null. Does this sound about right as to what's wrong with your Query? if so please post the SQL of the query

That sounds just about right. Here's the SQL code

Code:
SELECT [DataSelect-AtRiskScores].ID, [DataSelect-AtRiskScores].EntryDate, [DataSelect-AtRiskScores].Week, [DataSelect-AtRiskScores].Qtr, [DataSelect-AtRiskScores].Observer, [DataSelect-AtRiskScores].[Location/Department], [DataSelect-AtRiskScores].Line, [DataSelect-AtRiskScores].TotalQRiskScore, [DataSelect-AtRiskScores].TotalSHERiskScore, [DataSelect-SafeScores].TotalQSafeScore, [DataSelect-NA-Scores].TotalQNAScore, [DataSelect-NA-Scores].TotalSHENAScore
FROM ([DataSelect-AtRiskScores] INNER JOIN [DataSelect-SafeScores] ON [DataSelect-AtRiskScores].ID = [DataSelect-SafeScores].ID) INNER JOIN [DataSelect-NA-Scores] ON [DataSelect-SafeScores].ID = [DataSelect-NA-Scores].ID
WHERE ((([DataSelect-AtRiskScores].EntryDate) Between [Forms]![frmMasterReportSelection]![txtDateFrom] And [Forms]![frmMasterReportSelection]![txtDateTo]) AND (([DataSelect-AtRiskScores].Week) Between [Forms]![frmMasterReportSelection]![txtWeekFrom] And [Forms]![frmMasterReportSelection]![txtWeekTo]) AND (([DataSelect-AtRiskScores].Qtr)=[Forms]![frmMasterReportSelection]![txtQuarter]))
ORDER BY [DataSelect-AtRiskScores].EntryDate, [DataSelect-AtRiskScores].Observer;

In a nutshell, there are three range options to choose from: dates, week numbers, and quarter. I'm trying to fool-proof it so users can only select one option. This will be the base of several future queries, mainly totaling queries, so people can print out reports.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:16
Joined
Jul 9, 2003
Messages
16,245

themurph2000

Fat, drunk, and stupid
Local time
Today, 08:16
Joined
Sep 24, 2007
Messages
181
Oh. I'm sure it works great, but I'm at work and YouTube is definitely blocked.
 

themurph2000

Fat, drunk, and stupid
Local time
Today, 08:16
Joined
Sep 24, 2007
Messages
181
Actually, I figured it out. The solution was a bit ugly but it worked.

I created invisible check boxes in the form. When you clicked on a button for the range you wanted (dates, weeks, or quarter), its check box was set to true and the other two were set to false. In the query, I then created a new field which was a nested IIF statement, selecting the correct field for whichever check box was true. Then for the criteria, I used two generic text boxes which were filled in when the user made their selection, and they became the parameters.

I have Jon K to thank for this. It was his idea that gave me the impetus to try this.
http://www.access-programmers.co.uk/forums/showthread.php?t=103312
 
Last edited:

Users who are viewing this thread

Top Bottom