need help with a query or two

[dvenn]

okay I need some help.. I am new here and fairly new to access..

I'll start with two tables

tblEmployees & tblMetrics

I have a report and a query as well...

The query pulls the data using the folowing criteria

>=DateAdd("d",-5,[Forms]![RUI]![ddate]) And <=[Forms]![RUI]![ddate]

This returns what I want (the last 5 days of data for each employee)...

I need to further scrutinize the data so that the query will only report out if data exists on the data specified on the form, if it does the run the dateadd portion..

any suggestions would be greatly appreciated

[Norie]

I'm a little unsure what you mean.

Do mean that the query will only run if a value is placed in ddate on the RUI form?

[dvenn]

if a user enters 6/25/05 in the form, I need to the query to first check if data exists for 6/25/05 for that employee... if it does then do the dateadd function.

However if data does not exist for 6/25/05 then do not report anything for that employee..


Hope this helps explain it better

[Norie]

How are you opening the query/report from the form?

Are you using a button?

If you where you could use DCount to find out if any records existed for the date and then open/not open the query/report as appropriate.

[dvenn]

I am using a button from the form to open the report, which launches the query...

I have tried some DCount things but I'm afraid I just didn't understand the concept enough to apply it accordingly

[dvenn]

here is what I tried....

[VBA]
Dim stDocName As String
stDocName = "Agent Level Daily Report (Standard)"

If DCount("[METID]", "Agent Level Daily Report Query (Current)", "[RPTD] = #" & Me!ddate & "#") = 0 Then
MsgBox "no records match", vbOKOnly
Else
DoCmd.OpenReport stDocName, acPreview
End If
[/VBA]

But when I run it I get an error " you cancelled the previous operation"

[Norie]

Do you have any code behind the buttons?

DCount is explained pretty well in Access help.

I can't give you a specific example as I don't know your exact data structure or field names

[dvenn]

here is the entire code.. I uncommented the DCount Section to show... I commented the plain docmd so as to not conflict

[VBA]
Private Sub AgentDailyReports_Click()
On Error GoTo Err_DailyReports_Click
Dim stDocName As String
stDocName = "Agent Level Daily Report (Standard)"

If DCount("[METID]", "Agent Level Daily Report Query (Current)", "[RPTD] = #" & Me!ddate & "#") = 0 Then
MsgBox "no records match", vbOKOnly
Else
DoCmd.OpenReport stDocName, acPreview
End If

' DoCmd.OpenReport stDocName, acPreview
Exit_DailyReports_Click:
Exit Sub
Err_DailyReports_Click:
MsgBox Err.Description
Resume Exit_DailyReports_Click

End Sub
[/VBA]

[Norie]

That looks fine to me.

What's the error?

[dvenn]

"you cancelled the previous operation"

[xCav8r]

You getting an error on DCount or DoCmd?

[dvenn]

honestly I don't know how to determine that... suggestions?

[xCav8r]

Add the command stop at the beginning of the procedure and hit F8 once you've reached the breakpoint to move forward one line at a time. Pay attention to when it jumps to your error handler. The line it jumps from is the one causing the error.