Parameter query with a crosstab query

G

Guest

My report is based on a crosstab query, when I add a parameter, and try to
open the report, i get the following error Microsoft Jet Databases Engine
does not recogniz " as a valid field name or expression
This is how my parameter looks like
[Forms]![ServiceLevel]![BeginDate]
[Forms]![ServiceLevel]![EndDate]

I also have have the following sql part of the query
Select qryPipelineSum.Date
FROM qryPipelineSum
WHERE (((qryPipelineSum.Date) Between [forms]![ServiceLevel]![beginDate] And
[forms]![ServiceLevel]![EndDate]))

When I enter sql part of the query I that is when I get the following error
Microsoft Jet Databases Engine does not recogniz " as a valid field name or
expression
When I take it out, the report runs but does not filter the dates that I
wanted.

Please help!
 
A

aaron.kempf

effectively, access just craps out when you build query on top of query

ms wont fix it; and it pisses me off

i'd reccomend taking either of these routes:

a) build query on top of query
b) use temp tables
c) use SQL 2005 Pivot Keyword and a sproc
 
G

Guest

Hi JOM,

Please see the tutorial on crosstab queries that I have made available:

Crosstab Queries
http://www.access.qbuilt.com/html/crosstab_queries.html

Pay particular attention to paragraph 15, which references KB 209778:

http://support.microsoft.com/?id=209778

If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?" at the bottom of the message thread.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

My report is based on a crosstab query, when I add a parameter, and try to
open the report, i get the following error Microsoft Jet Databases Engine
does not recogniz " as a valid field name or expression
This is how my parameter looks like
[Forms]![ServiceLevel]![BeginDate]
[Forms]![ServiceLevel]![EndDate]

I also have have the following sql part of the query
Select qryPipelineSum.Date
FROM qryPipelineSum
WHERE (((qryPipelineSum.Date) Between [forms]![ServiceLevel]![beginDate] And
[forms]![ServiceLevel]![EndDate]))

When I enter sql part of the query I that is when I get the following error
Microsoft Jet Databases Engine does not recogniz " as a valid field name or
expression
When I take it out, the report runs but does not filter the dates that I
wanted.

Please help!
 
G

Guest

Aaron:

Before getting all pissed off, perhaps you should take the time to learn
Access. There is a solution for JOM.

Tom
____________________________________________

:

effectively, access just craps out when you build query on top of query

ms wont fix it; and it pisses me off

i'd reccomend taking either of these routes:

a) build query on top of query
b) use temp tables
c) use SQL 2005 Pivot Keyword and a sproc
 
G

Guest

Tom,

Thanks for the reply, I did try everything but something weird is going on.
The query runs ok, but when I try to run it it gives me an error Microsoft
Jet Databases Engine does not recognize " as a valid field name or expression

If I Put the beginning date as 08/01/05 and ending date as 08/31/05 it runs
ok, but If I try to put a different date like 08/01/05 and ending date as
08/15/05, then I get the error, I have looked at the reports to makes sure
that I don't filters turned on but still only works with those 2 dates put in
ie the 08/01/05 and 08/31/05

I have no data function on the report but this is not being called. So if I
put those dates and an employee did not work, then it pulls all the records
that fall under that category.....................

Here is my actual preview code that is supposed to open the report
Private Sub cmdOk_Click()
On Error GoTo Err_cmdOk_Click

Dim stDocName As String

If IsNull(Me.cmbDaily) Then
MsgBox "Select an employee to Preview."
Me.cmbDaily.SetFocus
Exit Sub
End If

'Check to see that ending date is later than beginning date.
If IsDate(BeginDate) And IsDate(EndDate) Then
If EndDate < BeginDate Then
MsgBox "The ending date must be later than the beginning date."
EndDate.SetFocus
Exit Sub
End If
Else
MsgBox "Please use a valid date for the beginning date and the
ending date values."
Exit Sub
End If

strWhereEmpl = "EmpID = " & Forms![ServiceLevel]!cmbDaily
stDocName = "Daily Service Level Summary"

DoCmd.OpenReport stDocName, acPreview, , strWhereEmpl

Exit_cmdOk_Click:
Exit Sub

Err_cmdOk_Click:
MsgBox Err.Description
Resume Exit_cmdOk_Click

End Sub

*************************************************************the run time
error am getting is 3070
************************************************************



Tom Wickerath said:
Hi JOM,

Please see the tutorial on crosstab queries that I have made available:

Crosstab Queries
http://www.access.qbuilt.com/html/crosstab_queries.html

Pay particular attention to paragraph 15, which references KB 209778:

http://support.microsoft.com/?id=209778

If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?" at the bottom of the message thread.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

My report is based on a crosstab query, when I add a parameter, and try to
open the report, i get the following error Microsoft Jet Databases Engine
does not recogniz " as a valid field name or expression
This is how my parameter looks like
[Forms]![ServiceLevel]![BeginDate]
[Forms]![ServiceLevel]![EndDate]

I also have have the following sql part of the query
Select qryPipelineSum.Date
FROM qryPipelineSum
WHERE (((qryPipelineSum.Date) Between [forms]![ServiceLevel]![beginDate] And
[forms]![ServiceLevel]![EndDate]))

When I enter sql part of the query I that is when I get the following error
Microsoft Jet Databases Engine does not recogniz " as a valid field name or
expression
When I take it out, the report runs but does not filter the dates that I
wanted.

Please help!
 
G

Guest

Hi JOM,

Can you send me a compacted (preferably zipped) copy of your database? It
will be easier to troubleshoot the error message if I can work with the
database. My "munged" e-mail address is indicated below.

Tom

QWaos168@XScom cast. Dnet (<--Remove all capitalized letters and spaces).
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Tom,

Thanks for the reply, I did try everything but something weird is going on.
The query runs ok, but when I try to run it it gives me an error Microsoft
Jet Databases Engine does not recognize " as a valid field name or expression

If I Put the beginning date as 08/01/05 and ending date as 08/31/05 it runs
ok, but If I try to put a different date like 08/01/05 and ending date as
08/15/05, then I get the error, I have looked at the reports to makes sure
that I don't filters turned on but still only works with those 2 dates put in
ie the 08/01/05 and 08/31/05

I have no data function on the report but this is not being called. So if I
put those dates and an employee did not work, then it pulls all the records
that fall under that category.....................

Here is my actual preview code that is supposed to open the report
Private Sub cmdOk_Click()
On Error GoTo Err_cmdOk_Click

Dim stDocName As String

If IsNull(Me.cmbDaily) Then
MsgBox "Select an employee to Preview."
Me.cmbDaily.SetFocus
Exit Sub
End If

'Check to see that ending date is later than beginning date.
If IsDate(BeginDate) And IsDate(EndDate) Then
If EndDate < BeginDate Then
MsgBox "The ending date must be later than the beginning date."
EndDate.SetFocus
Exit Sub
End If
Else
MsgBox "Please use a valid date for the beginning date and the
ending date values."
Exit Sub
End If

strWhereEmpl = "EmpID = " & Forms![ServiceLevel]!cmbDaily
stDocName = "Daily Service Level Summary"

DoCmd.OpenReport stDocName, acPreview, , strWhereEmpl

Exit_cmdOk_Click:
Exit Sub

Err_cmdOk_Click:
MsgBox Err.Description
Resume Exit_cmdOk_Click

End Sub

*************************************************************the run time
error am getting is 3070
************************************************************
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top