subform requery? based upon criteria

[InsaneMember]

here's my problem. i have 3 combo boxes that are going to be used to determine the criteria for a sub-form. (month, payperiod--period 1 or 2, and year)

there are 2 payperiods in a month, (we're paid from the first to the 15th, and the 16th to the last day of the month)

i have a query that does the hours for the day. it shows ALL the records. i don't want that. i just want to see it by pay-periods.

the fields in the query i'm using are:

date crew hours ot_hours

i'm clueless on how to achieve this. ANY help would be helpful. thanks!

 

[InsaneMember]

bump

 

[SteveCarrier]

Hello,

In your query criteria you can set certain dates. For example if you only wanted to see the dates 01/01/02 to 01/15/02, set up variables called strDate1 and strDate2, and set strDate1 to the first date you want ie. 01/01/02 and strDate2 to the second date ie. 01/15/02. Then use this query:

SELECT CREW, HOURS, OT_HOURS FROM your-table WHERE DATE > #&quot; & strDate1 & &quot;# AND DATE < #&quot; & strDate2 & &quot;#;&quot;

The easiest way to get the dates into the variables is to use two combo boxes with pay period start and end dates in them and then when you choose a start and end date set the value of the combo boxes to the strDate1 and strDate2 variables.

The query above will then only show the pay data for in between these two dates.

HTH

Steve Carrier

 

[InsaneMember]

hmm... i thought about that. but i'm really new to this that i'm even clueless on the syntax!

okay, how do i get the subform.property =sql statement? looking at the help files, i have to enter it in as a string. ie db.setdefquery = &quot;select * from table&quot;

i tried creating a string with what i need:

sqlquery= &quot;select * from table where [date]>&quot;+strDateStart+&quot;and [date]<&quot;+strDateEnd

then try to use the .setquerydef ie:

db.setquerydef= sqlquery

i get a type mismatch, etc.

THERE HAS GOT TO BE AN EASIER WAY!

also, to help make it easier for the user, the combo box for the period:

if period 1 then
startdate=1
enddate= 15
else
startdate=16
(then a bunch of code to determine the last date of the month)
end if

 

[SteveCarrier]

you are right about the query but it has to have # in front and after the dates:

strSQL = &quot;SELECT * from table where date > #&quot; & strDate1 & &quot;# AND date < #&quot; & strDate2 & &quot;#;&quot;

you then set your subform or form's recordsource property to this query:

your-subform.recordsource = strSQL

Now you can reference all of the fields from the table through the subform's properties. If you are coding in the actual form, you can use:

strCrew = me.Crew
strHours = me.Hours
strOTHours = me.Ot_Hours

if you are in another form you need to reference the form directly:

strCrew = Form_your-subform.Crew
strHours = Form_your-subform.Hours
strOTHours = Form_your-subform.Ot_Hours

Then set your labels or textbox on the forms equal to the variable strCrew, strHours, strOTHours, etc.

HTH

Steve Carrier

 

[Guest_imported]

excellent!! that's just what i need! i'll tell you how it turns out.

 

[InsaneMember]

hmm... another problem... how do you convert type= date into type = string?

 

[SteveCarrier]

I would normally use a text field for the dates instead of a date field but I believe if you want to convert a string to a date you can use a Date(strDate1) function.

HTH

Steve Carrier

 

[InsaneMember]

how do yo convert a date to a string?

here's what's going on...

the 3 combo boxes are supposed to give me dates.

combo box 1= month
2=pay period
3=year

i need to combine them all, to create a date so i can put it into the query.

 

[SteveCarrier]

Use the cstr() function to convert anything to a string. For example:

strMonth = cstr(cboMonth)
strPayPeriod = cstr(cboPayPeriod) ----> this being the day
strYear = cstr(Year)

Than combine into one string:

strDate1 = strMonth & &quot;/&quot; & strPayPeriod & &quot;/&quot; & strYear
same for strDate2

Now you can use the variable in the query.

HTH

Steve

 

[InsaneMember]

i'm learning something new everyday... cstr()

well, it didn't work. i think i'm doing something wrong...

okay, i don't have the combo boxes linked to a table, instead i typed in the values i wanted at creation (using a wizard).

the first combo box is the month field. it has the months written out, ie January, February, etc.

the second combo box is the period. first period or second.

the third combo box is the year.

so... to take the month and the year i want for the combo box, i used the property .values. (ie cmbMonth.values)

the weird one is the period. i used an _afterupdate event to get it to put information in some variables, called starDate, endDate.

here's my code so far:

Option Compare Database
Dim begDate, lasDate As String

'update the sub form if user changes month in combo box
Private Sub cmbMth_AfterUpdate()
FilterMonth
End Sub

'update the subform if user changes the year in combo box
Private Sub cmbyear_afterupdate()
FilterMonth
End Sub

'the update for the pay period
'if first period, the beginning day is 1, and end date is 15
'if second period, beginning is 16 and end date is last date
'of month
Private Sub cmbPay_afterupdate()
If cmbPay.Value = &quot;1-15&quot; Then
begDate = 1
lasDate = 15
End If

If cmbPay.Value = &quot;16-EOM&quot; Then
begDate = 16
lasDate = String(1, Day(DateSerial(cmbYear.Value, cmbMth.Value + 1, 0))) 'this supposedly finds the last day
'of the month
End If
End Sub

'this section does not work; i get errors saying that there
'no value in the combo boxes.
Private Sub form_initialize()
cmbMth.Value = Month(Date)
begDate = 1
lasDate = 15
cmbYear.Value = year(Date)
End Sub

'the function that allows me to filter pay periods.
Function FilterMonth()
Dim PayPeriodStart, PayPeriodEnd As Date
Dim Store As String

PayPeriodStart = CStr(DateValue(cmbMth.Value + &quot; &quot; + begDate + &quot;, &quot; + cmbYear.Value))
PayPeriodEnd = CStr(DateValue(cmbMth.Value + &quot; &quot; + lasDate + &quot;, &quot; + cmbYear.Value))

Store = &quot;SELECT * FROM [Calc Hours]where date > #&quot; + PayPeriodStart + &quot;# and date < #&quot; + PayPeriodEnd + &quot;#&quot;

Set PayPInfo = [Calc Hours].createquerydef(&quot;PeriodQuery&quot;, Store)

[Calc Hours subform].Requery

End Function