PDA

View Full Version : need help with a query or two



dvenn
06-27-2005, 08:56 AM
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
06-27-2005, 09:22 AM
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
06-27-2005, 10:08 AM
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
06-27-2005, 10:24 AM
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
06-27-2005, 10:27 AM
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
06-27-2005, 10:37 AM
here is what I tried....


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


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

Norie
06-27-2005, 10:40 AM
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
06-27-2005, 10:42 AM
here is the entire code.. I uncommented the DCount Section to show... I commented the plain docmd so as to not conflict


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

Norie
06-27-2005, 12:16 PM
That looks fine to me.

What's the error?

dvenn
06-27-2005, 01:19 PM
"you cancelled the previous operation"

xCav8r
06-27-2005, 04:46 PM
You getting an error on DCount or DoCmd?

dvenn
06-28-2005, 08:28 AM
honestly I don't know how to determine that... suggestions?

xCav8r
06-28-2005, 10:12 AM
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.