UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Opening A Query Using An Unbound Form, Access 2000    
 
   
wmix
post Feb 27 2018, 04:25 PM
Post#1



Posts: 29
Joined: 12-April 17



Hi Everyone,
I'm having issues with a "simple" end-user form.
In my Q&A with my end users they decided they wanted a "simple" form where they could choose a year and Quarter - not have to type specific dates.
Trying to solve this problem I came up with an Unbound form (frmCommissionReport) to do what the end users have asked for.
The problem, I cannot get the queries (I have quite a few that need to run based on user input) to run based on collected data.
Here's what I have so far (not sure if this is best way to write it but this is what I came up with):

Query qCommissionCandyExpired with SQL is as follows:

SELECT tblWeeklyCandyExpired.MachineID, tblWeeklyCandyExpired.ExpiredCandyAmount, tblWeeklyCandyExpired.ExpiredCandyDate
FROM tblWeeklyCandyExpired
WHERE (((tblWeeklyCandyExpired.ExpiredCandyDate) Between [Forms]![frmCommissionReport]![CommissionDate1] And [Forms]![frmCommissionReport]![CommissionDate2]));

Then I have an Unbound Form called frmCommissionReport that asks the user to select a Quarter and enter a Year.

When I open qCommissionCandyExpired when the form is closed I am prompted to enter both dates.
However, when I run the form the query opens up with no data in it.
I added a message box into each Case statement to verify I was getting a "proper" date. It looks correct as 01/01/2018 and 03/31/2018 and so on for each Case statement.
I've been staring at this for so long I'm guessing I'm missing something.
Thoughts? Any help is greatly appreciated!


Currently my Visual Basic Code is as follows (sorry, the formatting is not working out - I attached a txt file with the code to make it easier if that helps).

Option Compare Database

Private Sub ButtonRunReport_Click()

If Nz(Me.CommissionYear, "") = "" Then
MsgBox "A Commission Year Must Be Selected"
Exit Sub
ElseIf Me.CommissionYear < 1990 Then
MsgBox "Year Must be 1990 or Greater"
Exit Sub
End If

On Error GoTo Err_ButtonRunReport_Click

Dim CommDate1 As String, CommDate2 As String
Dim strdate1(0 To 1) As String
Dim strdate2(0 To 1) As String
Dim CommissionDate1 As Date
Dim CommissionDate2 As Date

Select Case FrameQuarter
Case 1
strdate1(0) = "January 1,"
strdate1(1) = CommissionYear
CommDate1 = Join(strdate1)
strdate2(0) = "March 31,"
strdate2(1) = CommissionYear
CommDate2 = Join(strdate2)
CommissionDate1 = CDate(CommDate1)
CommissionDate2 = CDate(CommDate2)
MsgBox "Dates are " & CommissionDate1 & " and " & CommissionDate2

Case 2
strdate1(0) = "April 1,"
strdate1(1) = CommissionYear
CommDate1 = Join(strdate1)
strdate2(0) = "June 30,"
strdate2(1) = CommissionYear
CommDate2 = Join(strdate2)
CommissionDate1 = CDate(CommDate1)
CommissionDate2 = CDate(CommDate2)
MsgBox "Dates are " & CommissionDate1 & " and " & CommissionDate2

Case 3
strdate1(0) = "July 1,"
strdate1(1) = CommissionYear
CommDate1 = Join(strdate1)
strdate2(0) = "September 30,"
strdate2(1) = CommissionYear
CommDate2 = Join(strdate2)
CommissionDate1 = CDate(CommDate1)
CommissionDate2 = CDate(CommDate2)
MsgBox "Dates are " & CommissionDate1 & " and " & CommissionDate2

Case 4
strdate1(0) = "October 1,"
strdate1(1) = CommissionYear
CommDate1 = Join(strdate1)
strdate2(0) = "December 31,"
strdate2(1) = CommissionYear
CommDate2 = Join(strdate2)
CommissionDate1 = CDate(CommDate1)
CommissionDate2 = CDate(CommDate2)
MsgBox "Dates are " & CommissionDate1 & " and " & CommissionDate2

End Select

DoCmd.OpenQuery "qCommissionCandyExpired", acNormal, acEdit

Exit_ButtonRunReport_Click:
Exit Sub

Err_ButtonRunReport_Click:
MsgBox Err.Description
Resume Exit_ButtonRunReport_Click

End Sub

Attached File(s)
Attached File  AccessReportQuestion.txt ( 2.28K )Number of downloads: 5
 
Go to the top of the page
 
tina t
post Feb 27 2018, 04:48 PM
Post#2



Posts: 5,424
Joined: 11-November 10
From: SoCal, USA


i don't know about the newer versions of Access, but in the older versions (.mdb) Access sometimes has trouble recognizing a reference where the value is Date/Time. try adding a parameter clause to the SQL statement, as

PARAMETERS Forms!frmCommissionReport!CommissionDate1 DateTime, Forms!frmCommissionReport!CommissionDate2 DateTime
SELECT tblWeeklyCandyExpired.MachineID, tblWeeklyCandyExpired.ExpiredCandyAmount, tblWeeklyCandyExpired.ExpiredCandyDate
FROM tblWeeklyCandyExpired
WHERE (((tblWeeklyCandyExpired.ExpiredCandyDate) Between [Forms]![frmCommissionReport]![CommissionDate1] And [Forms]![frmCommissionReport]![CommissionDate2]));

hth
tina
This post has been edited by tina t: Feb 27 2018, 04:49 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
SerranoG
post Feb 28 2018, 10:17 AM
Post#3


UtterAccess VIP
Posts: 2,213
Joined: 1-December 03
From: Lansing, MI USA


Hmmm... try something like this.

CODE
Private Sub ButtonRunReport_Click()

    On Error GoTo Err_ButtonRunReport_Click

    Dim intMonth as Integer, dtmCommDateStart as Date, dtmCommDateEnd as Date

    If Nz(Me.CommissionYear, 1899) < 1900 Or Nz(Me.CommissionYear,1899) > Year(Date()) Then
        MsgBox "Year must be 1900 to the Present.", vbCritical, "Invalid Year"
    End If

    'Since your quarters start at January, it simplifies calculations.
    intMonth = 3 * Me.FrameQuarter - 2

    dtmCommDateStart = DateSerial(Me.CommissionYear, intMonth, 1)
    dtmCommDateEnd = DateDiff("d", -1, DateSerial(Me.CommissionYear, intMonth + 3, 1))
  
    MsgBox "Dates are " & Format(dtmCommDateStart, "Short Date") & " and " & Format(dtmCommDateEnd, "Short Date")

    Me.CommissionDate1 = dtmCommDateStart
    Me.CommissionDate2 = dtmCommDateEnd

    Me.CommissionDate1.Requery
    Me.CommissionDate2.Requery

    DoCmd.OpenQuery "qCommissionCandyExpired", acNormal, acEdit

Exit_ButtonRunReport_Click:
    Exit Sub

Err_ButtonRunReport_Click:
    MsgBox Err.Description, vbCritical, "Error " & Err.Number & " in " & Me.Name & ", Sub ButtonRunReport_Click"
    Resume Exit_ButtonRunReport_Click

End Sub




--------------------
Greg Serrano
Michigan Dept. of Environmental Quality
Air Quality Division
Go to the top of the page
 
wmix
post Feb 28 2018, 03:28 PM
Post#4



Posts: 29
Joined: 12-April 17



Thanks SerranoG. This code is much easier to work with and I appreciate the insight. This code cleans things up significantly.

Unfortunately my dates are still not transmitting to the query. Still working on that part.
Go to the top of the page
 
wmix
post Feb 28 2018, 03:38 PM
Post#5



Posts: 29
Joined: 12-April 17



Thanks tina t, I never thought about declaring the Parameters. I just added this now.
Unfortunately I'm still running into issues, the query opens with no data. Still trying to figure this out.
Go to the top of the page
 
zaxbat
post Feb 28 2018, 04:31 PM
Post#6



Posts: 952
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


With each new app i make amendments to my report form. My latest one looks like this. Since most reports include a date range that gets a lot of attention. The WHERE clause from query code for adhering to the selected date range is here:

"WHERE ( (IIf(IsDate([MedEndDate]),[MedEndDate],#" & AdjustedEndDate & "#) Between #" & GlobalStartDate & "# And #" & GlobalEndDate & "#) )"

NOTE that GlobalStartDate and GlobalEndDate are public string variables generated by the users choice of date range from the report form.

I can give you a zip file with the report form and VBA that runs it if you are interested.
This post has been edited by zaxbat: Feb 28 2018, 04:40 PM
Attached File(s)
Attached File  ReportForm.jpg ( 202.35K )Number of downloads: 4
 

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
wmix
post Mar 6 2018, 02:11 PM
Post#7



Posts: 29
Joined: 12-April 17



Thanks zaxbat for the generous offer, that's very kind of you.

I was able to get this to work, I had to walk away for a few days and it finally hit me, I had a typo i not l - so hard to see! But I was able to get it to run.

Thanks everyone for your input and help!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd June 2018 - 08:15 PM