Query not working as expected 1

[jeffmorl]

We keep track of a worker's hour and whether those hours are "overtime" or "regular time" with an "earn_code" value. I had a query with input parameters built in, but that didn't give supervisors enough prompting. Thus the use of an input parameter form.

On the form, a supervisor can enter a userID, a date range and an option to see regular time, overtime or both. These input parameters feed a query that feeds a report.

Below is my code that builds the SQL for DoCmd.OpenReport method.

It only works when case2 (Overtime), and doesn't when case1 (Regular time) or case3 (both OT & Reg). When I copy/paste the sqlReport into the original query, it works fine for all cases.

I'm missing something, but have been looking at it so long, I can't see my problem.

Any help is greatly appreciated.

' set up parameter pass variables
Dim sqlBegDate As Date
Dim sqlEndDate As Date
Dim sqlEmp As Double
Dim sqlReport As String
Dim stDocName As String
Dim sqlReg As Double
Dim sqlOT As Double

' pass input parameters
sqlBegDate = Me.begDate
sqlEndDate = Me.endDate
sqlEmp = Me.dEmp
stDocName = "rDataEntryReport"
sqlReg = "0"
sqlOT = "1"


Select Case Options
    Case 1      ' regular time
        sqlEarnCode = " and t_dmz_equipment.earn_code = " & [sqlReg] & ""
    Case 2      ' overtime
        sqlEarnCode = " and t_dmz_equipment.earn_code = " & [sqlOT] & ""
    Case 3      ' regular and overtime
        sqlEarnCode = " and t_dmz_equipment.earn_code = " & [sqlReg] & "  or t_dmz_equipment.earn_code = " & [sqlOT] & ""
End Select

' build sql for query
sqlReport = " t_dmz_people.mpac = " & [sqlEmp] & "" ' AND ((T_DMZ_EQUIPMENT.START_DATE) Between #" & [sqlBegDate] & "# And #" & [sqlEndDate] & "# AND ((T_DMZ_EQUIPMENT.EARN_CODE)=0)"
sqlReport = sqlReport & " and t_dmz_equipment.start_date between #" & [sqlBegDate] & "# and #" & [sqlEndDate] & "#"
sqlReport = sqlReport & sqlEarnCode

Debug.Print sqlReport*

    DoCmd.OpenReport stDocName, acViewPreview, qDataEntryReport, sqlReport, acWindowNormal
    DoCmd.CLOSE acForm, "f_reportform"

End Sub

* t_dmz_people.mpac = 23331 and t_dmz_equipment.start_date between #1/1/2011# and #5/1/2011# and t_dmz_equipment.earn_code = 0

 

[traingamer]

If that's your actual code, you've turned some of it into a comment with an apostrophe:

' build sql for query
sqlReport = " t_dmz_people.mpac = " & [sqlEmp] & "" [b][red]'[/red][/b] AND ((T_DMZ_EQUIPMENT.START_DATE) Between #" & [sqlBegDate] & "# And #" & [sqlEndDate] & "# AND ((T_DMZ_EQUIPMENT.EARN_CODE)=0)"
sqlReport = sqlReport & " and t_dmz_equipment.start_date between #" & [sqlBegDate] & "# and #" & [sqlEndDate] & "#"
sqlReport = sqlReport & sqlEarnCode

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[jeffmorl]

That is the actual code but I think with the text wrap on this screen, it might show as a comment, which it is not.
Please see the Debug.Print piece --
t_dmz_people.mpac = 23331 and t_dmz_equipment.start_date between #1/1/2011# and #5/1/2011# and t_dmz_equipment.earn_code = 0

Sorry for the confusion.

 

[dhookom]

I don't ever use this argument of DoCmd.OpenReport

DoCmd.OpenReport stDocName, acViewPreview, [b][red]qDataEntryReport[/red][/b], sqlReport, acWindowNormal

I also never use table names as part of the where condition. Field names in your record source should be unique so table names aren't necessary.

Duane
Hook'D on Access
MS Access MVP

 

[jeffmorl]

I added the query name argument just this morning to see if it made a difference; it doesn't. Even without the query name argument and without the table_name in the string, the query still doesn't work as expected.

It's odd (to me, at least) that the query pulls in records for overtime hours and not regular time hours.

 

[dhookom]

How do you not get two "earn_code =" in your

Debug.Print sqlReport*

Have you tried stepping through your code?
Can you paste your code again as well as the debug.print


Duane
Hook'D on Access
MS Access MVP

 

[jeffmorl]

Have stepped through the code and everything works as it should, with the exception that I don't get results for regular hours.

Here is the cleaned up code, which still doesn't pull in regular hours.

Thanks again for the help.

Select Case Options
    Case 1      ' regular time
        sqlEarnCode = " and earn_code = " & [sqlReg] & ""
    Case 2      ' overtime
        sqlEarnCode = " and earn_code = " & [sqlOT] & ""
    Case 3      ' regular and overtime
        sqlEarnCode = " and earn_code = " & [sqlReg] & "  or earn_code = " & [sqlOT] & ""
End Select



' build sql for query
sqlReport = " MPAC= " & [sqlEmp] & ""
sqlReport = sqlReport & " and t_dmz_equipment.start_date between #" & [sqlBegDate] & "# and #" & [sqlEndDate] & "#"
sqlReport = sqlReport & sqlEarnCode

Debug.Print sqlReport
    
    DoCmd.OpenReport stDocName, acViewPreview, , sqlReport, acWindowNormal
    DoCmd.CLOSE acForm, "f_reportform"

*** And here is my resulting sqlReport - which works for OT hours but not for regular hours. Frustrating, to say the least.

  MPAC= 23331 and t_dmz_equipment.start_date between #1/1/2011# and #5/1/2011# and earn_code = 0

 

[dhookom]

Minimally, you might need to change to this:

Select Case Options
    Case 1      ' regular time
        sqlEarnCode = " and earn_code = " & [sqlReg] & ""
    Case 2      ' overtime
        sqlEarnCode = " and earn_code = " & [sqlOT] & ""
    Case 3      ' regular and overtime
        sqlEarnCode = " and earn_code IN ( " & [sqlReg] & "," & [sqlOT] & ") "
End Select

' build sql for query
sqlReport = " MPAC= " & [sqlEmp] & ""
sqlReport = sqlReport & " and t_dmz_equipment.start_date between #" & [sqlBegDate] & "# and #" & [sqlEndDate] & "#"
sqlReport = sqlReport & sqlEarnCode

Debug.Print sqlReport
    
    DoCmd.OpenReport stDocName, acViewPreview, , sqlReport, acWindowNormal
    DoCmd.CLOSE acForm, "f_reportform"

Duane
Hook'D on Access
MS Access MVP

 

[jeffmorl]

Duane - thanks for the suggestion. Still no regular hours appear on my report.

 

[dhookom]

This makes no sense in terms of data types:

Dim sqlReg As Double
Dim sqlOT As Double
sqlReg = "0"
sqlOT = "1"

You are creating numeric variables and assigning a string to them.

Duane
Hook'D on Access
MS Access MVP

 

[jeffmorl]

Thanks for working through with me.

I saw that too and changed it. Here is the code as written today and the resulting debug.pring test string.

' pass input parameters
sqlBegDate = Me.begDate
sqlEndDate = Me.endDate
sqlEmp = Me.dEmp
stDocName = "rDataEntryReport"
sqlReg = 0
sqlOT = 1

Select Case Options
    Case 1      ' regular time
        sqlEarnCode = " and earn_code = " & [sqlReg] & ""
    Case 2      ' overtime
        sqlEarnCode = " and earn_code = " & [sqlOT] & ""
    Case 3      ' regular and overtime
        sqlEarnCode = " and earn_code = " & [sqlReg] & "  or earn_code = " & [sqlOT] & ""
End Select



' build sql for query
sqlReport = " MPAC= " & [sqlEmp] & ""
sqlReport = sqlReport & " and start_date between #" & [sqlBegDate] & "# and #" & [sqlEndDate] & "#"
sqlReport = sqlReport & sqlEarnCode

Debug.Print sqlReport
    
    DoCmd.OpenReport stDocName, acViewPreview, , sqlReport, acWindowNormal
    DoCmd.CLOSE acForm, "f_reportform"

And the resulting sql string

 MPAC= 23331 and start_date between #4/1/2011# and #5/1/2011# and earn_code = 0

I even reversed the code after the MPAC= 23331. Still no regular hours on report. This really shouldn't be that difficult.

 MPAC= 23331 and  earn_code = 0 and start_date between #4/1/2011# and #5/1/2011#

 

[dhookom]

You are right, it shouldn't be that difficult. What happens if you take your record source query and go to the SQL view. Paste or type into the where clause:
earn_code = 0
Do you get any records returned?


Duane
Hook'D on Access
MS Access MVP

 

[jeffmorl]

I have a feeling this is making me stronger. At least it should.

With the same input parameters (MPAC = 23331 and earn_code = 0 and with the given dates), hundreds of records are returned.

This is the WHERE section of a query builder SQL statement

 (((T_DMZ_PEOPLE.MPAC)=23331) AND ((T_DMZ_EQUIPMENT.START_DATE) Between #3/1/2011# And #5/1/2011#) AND ((T_DMZ_EQUIPMENT.EARN_CODE)=0))

Even when I replace sqlReport values to this, I get no records returned on my report.

 

[dhookom]

Can you provide the SQL view of the report's record source?

Duane
Hook'D on Access
MS Access MVP

 

[jeffmorl]

Here it is.

SELECT T_DMZ_PEOPLE.MPAC, T_DMZ_EQUIPMENT.START_DATE, [t_dmz_people].[fname] & " " & [t_dmz_people].[lname] AS EmpName, T_DMZ_EQUIPMENT.WORK_ORDER, T_DMZ_EQUIPMENT.BUILDING, T_DMZ_EQUIPMENT.PROBLEM, T_DMZ_EQUIPMENT.WORK_PERFORMED, T_DMZ_EQUIPMENT.HRS_WORKED, T_EQUIP_NO.EquipNo AS [Larry #], T_EQUIP_NO.EquipDesc AS [Larry Desc], [tMPAC-Equipment].FULL AS [MPAC #], T_DMZ_EQUIPMENT.EARN_CODE, T_DMZ_EQUIPMENT.ITEM, tServiceCode.DESCRIPTION
FROM (((T_DMZ_EQUIPMENT LEFT JOIN [tMPAC-Equipment] ON T_DMZ_EQUIPMENT.MPAC_EQUIPNO = [tMPAC-Equipment].FULL) LEFT JOIN T_DMZ_PEOPLE ON T_DMZ_EQUIPMENT.MPAC = T_DMZ_PEOPLE.MPAC) LEFT JOIN T_EQUIP_NO ON T_DMZ_EQUIPMENT.EQUIP_ID = T_EQUIP_NO.EquipNo) LEFT JOIN tServiceCode ON T_DMZ_EQUIPMENT.SERVICE_CODE = tServiceCode.[SERVICE CODE]
ORDER BY T_DMZ_EQUIPMENT.START_DATE, T_DMZ_EQUIPMENT.WORK_ORDER, T_DMZ_PEOPLE.LNAME, T_DMZ_EQUIPMENT.WORK_ORDER, T_DMZ_EQUIPMENT.BUILDING;

I just find it frustrating that the docmd.openreport doesn't retrieve regular hours when the query sql does.

 

[dhookom]

What do you get with a SQL of:

SELECT T_DMZ_EQUIPMENT.EARN_CODE, Count(*) as NumOf
FROM T_DMZ_EQUIPMENT
WHERE T_DMZ_EQUIPMENT.START_DATE Between #3/1/2011# And #5/1/2011#
GROUP BY T_DMZ_EQUIPMENT.EARN_CODE;

Is the EARN_CODE column right or left aligned?


Duane
Hook'D on Access
MS Access MVP

 

[jeffmorl]

When I enter that sql into a query builder, the following is returned;

EARN_CODE	NumOf
0.00	682
1.00	8

The table field properties are Double, Fixed, 0 Decimal Places, text align is General.

 

[dhookom]

I think I have heard of issues in the past with Fixed. Can you try:

Select Case Options
    Case 1      ' regular time
        sqlEarnCode = " and int(earn_code) = " & [sqlReg] & ""
    Case 2      ' overtime
        sqlEarnCode = " and int(earn_code) = " & [sqlOT] & ""
    Case 3      ' regular and overtime
        sqlEarnCode = " and int(earn_code) = " & [sqlReg] & "  or int(earn_code) = " & [sqlOT] & ""
End Select

Duane
Hook'D on Access
MS Access MVP

 

[jeffmorl]

Duane -
I think I finally got it. Not sure what happened "exactly" but when I copy/pasted the sqlReport string into the underlying report query I would get what I expected. This was after going into the table structure and making sure everything there was as it should be. Here is the code as I now have it, explicitly spelling out everything.

 (((T_DMZ_PEOPLE.MPAC)=23331) and ((T_DMZ_EQUIPMENT.start_date) between #4/1/2011# and #5/1/2011#)) and  ((T_DMZ_EQUIPMENT.earn_code) = 0)

or

 (((T_DMZ_PEOPLE.MPAC)=23331) and ((T_DMZ_EQUIPMENT.start_date) between #1/1/2011# and #5/1/2011#)) and ((T_DMZ_EQUIPMENT.earn_code) in (0 ,1))

Also, when I did not use the IN statement in the earn_code = 0 or earn_code = 1, it gave me more employees than I asked for. By using

sqlEarnCode = " and ((T_DMZ_EQUIPMENT.earn_code) in (" & [sqlReg] & " ," & [sqlOT] & "))"

it works just fine.

Bottom line, I'm not exactly certain what made it work all of a sudden (because I didn't change anyting in the table/fields structure), and that's what has me more troubled than before.

I'll keep my eyes on it. There have been lots of hours spent on this and you can't believe how much I appreciate your help. Thank you very much!

 

[dhookom]

The use of " OR " rather than " IN " would have required addition ()s to make it work as desired.

Duane
Hook'D on Access
MS Access MVP