Query returns nothing

[jtmott]

Hello,
I have a query that run on tables through ODBC off an AS400. The query sums information based on today's date. it works fine except first thing in the mornings before information for the current date has been entered at which time the query does not return any output. I have tried Null statements and if statements but I am receiving the same results. The goal is to return today's date with Earned labor = 0 when today's date is not present in the AS400 table.


any help would be greatly appreciated.
example in SQL view:
SELECT CDate(Mid([BPCSF6_ITHL01!TTDTE],5,2) & "/" & Right([BPCSF6_ITHL01!TTDTE],2) & "/" & Left([BPCSF6_ITHL01!TTDTE],4)) AS [Date], Sum(BPCSF6_ITHL01!TQTY*ATCCST!LABCST)/0.57 AS [Earned Labor] INTO [Earned Labor Table]
FROM (BPCSF6_ITHL01 LEFT JOIN BPCSF6_IIML01 ON BPCSF6_ITHL01.TPROD = BPCSF6_IIML01.IPROD) LEFT JOIN ATCCST ON BPCSF6_ITHL01.TPROD = ATCCST.PARTC
WHERE (((BPCSF6_ITHL01.TPROD)<>"60148-A") AND ((BPCSF6_ITHL01.TTDTE)>=20150101) AND ((BPCSF6_ITHL01.TTYPE)="PR" Or (BPCSF6_ITHL01.TTYPE)="CI" Or (BPCSF6_ITHL01.TTYPE)="IC" Or (BPCSF6_ITHL01.TTYPE)="RD" Or (BPCSF6_ITHL01.TTYPE)="Y") AND ((BPCSF6_IIML01.IITYP)="1" Or (BPCSF6_IIML01.IITYP)="2") AND ((BPCSF6_ITHL01.TWHS)="TX") AND ((BPCSF6_IIML01.IPROD)<>"60149-A"))
GROUP BY CDate(Mid([BPCSF6_ITHL01!TTDTE],5,2) & "/" & Right([BPCSF6_ITHL01!TTDTE],2) & "/" & Left([BPCSF6_ITHL01!TTDTE],4))
HAVING (((CDate(Mid([BPCSF6_ITHL01!TTDTE],5,2) & "/" & Right([BPCSF6_ITHL01!TTDTE],2) & "/" & Left([BPCSF6_ITHL01!TTDTE],4)))=Date()));

[June7]

Well, that's the way queries work. If there is no data then the query has no records and there is nothing for any calculations to work with.

Why do you need to show 0 labor for current date in a query?

I don't use dynamic parameters in queries. I apply filter criteria to form or report based on unfiltered query.

[jtmott]

I use this query results to display on a HTML page for a status board along with other data collected.

[June7]

Suggest you have code that tests if there are records and if not, display text: "No data yet today."

[orange]

The goal is to return today's date with Earned labor = 0 when today's date is not present in the AS400 table.
any help would be greatly appreciated.

If you know there are no records for today, why not just have a query to update [Earned Labor Table] with Today's date and 0 AS [Earned Labor].

[June7]

Yes, a dummy record is an option.

As Orange suggests, manually create record with 0 Labor and run UPDATE sql action to change the date every day.

[jtmott]

This query is part of a macro that is triggered to run every 5 min throughout the day. Is there perhaps code that would allow me to post results from another table when there is no results from this query?

[June7]

Probably. What code do you have now?

But the dummy record would also accomplish what you want.

[jtmott]

This the VBA code I'm using.
Dim db As Database
Set db = CurrentDb
Dim rs As Recordset
Set rs = db.OpenRecordset("Select * from Status_Table where ID = 1")

'Get the number to print/display:
Dim LG As Variant
LG = rs!Labor_Goal
Dim EL As Variant
EL = rs!Earned_Labor
Dim LN As Variant
LN = rs!Labor_Needed
Dim AC As Variant
AC = rs!Accidents
Dim PG As Variant
PG = rs!Packed_goal
Dim PK As Variant
PK = rs!Packed
Dim PN As Variant
PN = rs!Packed_Needed
Dim PL As Variant
PL = rs!Past_Lines
Dim PT As Variant
PT = rs!Past_Total
rs.Close

[orange]

How about some logic

If  today'sRecordCount = 0 then
    update  [Earned Labor Table] with Today's date and 0 AS [Earned Labor]
Else
    run your existing sql
end if

[orange]

This the VBA code I'm using.
Dim db As Database
Set db = CurrentDb
Dim rs As Recordset
Set rs = db.OpenRecordset("Select * from Status_Table where ID = 1")

'Get the number to print/display:
Dim LG As Variant
LG = rs!Labor_Goal
Dim EL As Variant
EL = rs!Earned_Labor
Dim LN As Variant
LN = rs!Labor_Needed
Dim AC As Variant
AC = rs!Accidents
Dim PG As Variant
PG = rs!Packed_goal
Dim PK As Variant
PK = rs!Packed
Dim PN As Variant
PN = rs!Packed_Needed
Dim PL As Variant
PL = rs!Past_Lines
Dim PT As Variant
PT = rs!Past_Total
rs.Close

And what do you do with this???

[jtmott]

Setting up a dummy record combined with using EOF statements in my VBA code did the trick.
Thanks for pointing me in the right direction.

[orange]

Glad you have it resolved.