Cannot get syntax for OpenReport correct

  • Thread starter Thread starter Jeff Conrad
  • Start date Start date
J

Jeff Conrad

Hi,

Using Access 97 here.
I am completely fed up at the moment.
I have been working on solving this very easy problem
for over an hour and a half and I am no closer.

All I want to do is open a report from an unbound form and restrict the
records to display ALL appointments from the CURRENT DATE for the MANAGER
selected in a text box.
Simple right? Not for me apparently.

I have searched the Help, googled like crazy and my syntax looks correct so
what gives?

By the way, Access Help on the OpenReport method was NO help at all!
This is the ENTIRE example listed in Access 97 for OpenReport Method:

"The following example prints Sales Report while using the existing query
Report Filter:

DoCmd.OpenReport "Sales Report", acViewNormal, "Report Filter"

Oh wow, big help! It doesn't even include an example using the where part!!!

My report's record source is a saved query.
The saved query simply gets the information from the table for the current
date for ALL managers. So there is a where part in the SQL on the date.
If I open the query from the Database Window it opens just fine with no
prompts.
If I open the report from the Database Window it opens just fine with no
prompts.

Here is my latest (of many) syntax currently to open the report:
DoCmd.OpenReport "rptTodaysAppointments", acPreview, , "[UserID]=" &
Me.txtManager

It says "Enter Parameter value - Admin"
Huh????

UserID is text and I have tried countless variations with single quotes and
double quotes.
UserID is not the primary key; an Autonumber is.
Does the Where part only apply to the Primary Key Field?
Can I only use this on a bound form?
Do I have to include the date part as well?
The date field is called TaskDate in the table.
The form control is called txtManager.

Any help would make my day!
Thanks,
Jeff Conrad
Bend, Oregon
 
Hi Jeff

You're on the right track. Ignore the 3rd argument (Filter) and use the 4th
argument (WhereCondition).

Your problem is that the contents of your txtManager box is text, and so it
is being interpreted as a name, not a value, by the SQL parser.

This SQL statement:
Select * from tblAppointments where [UserID]=Admin
will have the parser wondering what Admin is. Since there is no field of
that name, it will assume it's a parameter and prompt for it.

To make it work, you must enclose Admin in quotes:
Select * from tblAppointments where [UserID]='Admin'
(either single or double quotes will work)

Now, the WhereCondition argument is just the bit after the "where" keyword -
namely:
[UserID]='Admin'

To construct this in VBA, you need to join together three strings:
1. [UserID]=' (including the first quote)
2. The contents of the textbox (Admin)
3. ' (the final quote)

So, your WhereCondition is:
"[UserID]='" & Me.txtManager & "'"

If you want to include only records for today's date, then add that
condition also:

Dim sWhere as String
sWhere = "[UserID]='" & Me.txtManager & "' and [TaskDate]=Date()"
DoCmd.OpenReport "rptTodaysAppointments", acPreview, , sWhere

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jeff Conrad said:
Hi,

Using Access 97 here.
I am completely fed up at the moment.
I have been working on solving this very easy problem
for over an hour and a half and I am no closer.

All I want to do is open a report from an unbound form and restrict the
records to display ALL appointments from the CURRENT DATE for the MANAGER
selected in a text box.
Simple right? Not for me apparently.

I have searched the Help, googled like crazy and my syntax looks correct so
what gives?

By the way, Access Help on the OpenReport method was NO help at all!
This is the ENTIRE example listed in Access 97 for OpenReport Method:

"The following example prints Sales Report while using the existing query
Report Filter:

DoCmd.OpenReport "Sales Report", acViewNormal, "Report Filter"

Oh wow, big help! It doesn't even include an example using the where part!!!

My report's record source is a saved query.
The saved query simply gets the information from the table for the current
date for ALL managers. So there is a where part in the SQL on the date.
If I open the query from the Database Window it opens just fine with no
prompts.
If I open the report from the Database Window it opens just fine with no
prompts.

Here is my latest (of many) syntax currently to open the report:
DoCmd.OpenReport "rptTodaysAppointments", acPreview, , "[UserID]=" &
Me.txtManager

It says "Enter Parameter value - Admin"
Huh????

UserID is text and I have tried countless variations with single quotes and
double quotes.
UserID is not the primary key; an Autonumber is.
Does the Where part only apply to the Primary Key Field?
Can I only use this on a bound form?
Do I have to include the date part as well?
The date field is called TaskDate in the table.
The form control is called txtManager.

Any help would make my day!
Thanks,
Jeff Conrad
Bend, Oregon
Click to expand...
 
Jeff said:
Using Access 97 here.
I am completely fed up at the moment.
I have been working on solving this very easy problem
for over an hour and a half and I am no closer.

All I want to do is open a report from an unbound form and restrict the
records to display ALL appointments from the CURRENT DATE for the MANAGER
selected in a text box.
Simple right? Not for me apparently.

I have searched the Help, googled like crazy and my syntax looks correct so
what gives?

By the way, Access Help on the OpenReport method was NO help at all!
This is the ENTIRE example listed in Access 97 for OpenReport Method:

"The following example prints Sales Report while using the existing query
Report Filter:

DoCmd.OpenReport "Sales Report", acViewNormal, "Report Filter"

Oh wow, big help! It doesn't even include an example using the where part!!!

My report's record source is a saved query.
The saved query simply gets the information from the table for the current
date for ALL managers. So there is a where part in the SQL on the date.
If I open the query from the Database Window it opens just fine with no
prompts.
If I open the report from the Database Window it opens just fine with no
prompts.

Here is my latest (of many) syntax currently to open the report:
DoCmd.OpenReport "rptTodaysAppointments", acPreview, , "[UserID]=" &
Me.txtManager

It says "Enter Parameter value - Admin"
Huh????

UserID is text and I have tried countless variations with single quotes and
double quotes.
Click to expand...

Try this:

DoCmd.OpenReport "rptTodaysAppointments", acPreview, , _
"[UserID]=""" & Me.txtManager & """"

UserID is not the primary key; an Autonumber is.
Does the Where part only apply to the Primary Key Field?
Click to expand...

Not at all. It's just that numeric fields don't use quotes
around them. Don't forget that date values have to be
enclosed in # signs.

Can I only use this on a bound form?
Click to expand...

Bound or unbound doesn't matter, just so long as it has the
values you need or you can calculate the values.

Do I have to include the date part as well?
Click to expand...

I can't tell from here ;-)

The date field is called TaskDate in the table.
The form control is called txtManager.
Click to expand...

It depends on how you set up the query. If the query has
the date criteria in it, then no, you don't want to do it
again. OTOH, if the query does not have the date criteria
(recommended), then you could use a text box on the form for
the user to enter the starting date:

DoCmd.OpenReport "rptTodaysAppointments", acPreview, , _
"[UserID]=""" & Me.txtManager & """ AND [TaskDate] >= " _
& Format(Me.txtStartDate, "\#m\/d\/yyyy\#")
 
Hi Graham!

Spot on!!
Your answer not only gave me the correct solution, but a detailed
explanation as well!
Your comments should be right in the help file in my opinion.

Now I can sleep tonight without having nightmares about
single quotes, double quotes, etc.

Thank you so much, you saved my sanity (what little there is).
Jeff Conrad
Bend, Oregon


Graham Mandeno said:
Hi Jeff

You're on the right track. Ignore the 3rd argument (Filter) and use the 4th
argument (WhereCondition).

Your problem is that the contents of your txtManager box is text, and so it
is being interpreted as a name, not a value, by the SQL parser.

This SQL statement:
Select * from tblAppointments where [UserID]=Admin
will have the parser wondering what Admin is. Since there is no field of
that name, it will assume it's a parameter and prompt for it.

To make it work, you must enclose Admin in quotes:
Select * from tblAppointments where [UserID]='Admin'
(either single or double quotes will work)

Now, the WhereCondition argument is just the bit after the "where" keyword -
namely:
[UserID]='Admin'

To construct this in VBA, you need to join together three strings:
1. [UserID]=' (including the first quote)
2. The contents of the textbox (Admin)
3. ' (the final quote)

So, your WhereCondition is:
"[UserID]='" & Me.txtManager & "'"

If you want to include only records for today's date, then add that
condition also:

Dim sWhere as String
sWhere = "[UserID]='" & Me.txtManager & "' and [TaskDate]=Date()"
DoCmd.OpenReport "rptTodaysAppointments", acPreview, , sWhere

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jeff Conrad said:
Hi,

Using Access 97 here.
I am completely fed up at the moment.
I have been working on solving this very easy problem
for over an hour and a half and I am no closer.

All I want to do is open a report from an unbound form and restrict the
records to display ALL appointments from the CURRENT DATE for the MANAGER
selected in a text box.
Simple right? Not for me apparently.

I have searched the Help, googled like crazy and my syntax looks correct so
what gives?

By the way, Access Help on the OpenReport method was NO help at all!
This is the ENTIRE example listed in Access 97 for OpenReport Method:

"The following example prints Sales Report while using the existing query
Report Filter:

DoCmd.OpenReport "Sales Report", acViewNormal, "Report Filter"

Oh wow, big help! It doesn't even include an example using the where part!!!

My report's record source is a saved query.
The saved query simply gets the information from the table for the current
date for ALL managers. So there is a where part in the SQL on the date.
If I open the query from the Database Window it opens just fine with no
prompts.
If I open the report from the Database Window it opens just fine with no
prompts.

Here is my latest (of many) syntax currently to open the report:
DoCmd.OpenReport "rptTodaysAppointments", acPreview, , "[UserID]=" &
Me.txtManager

It says "Enter Parameter value - Admin"
Huh????

UserID is text and I have tried countless variations with single quotes and
double quotes.
UserID is not the primary key; an Autonumber is.
Does the Where part only apply to the Primary Key Field?
Can I only use this on a bound form?
Do I have to include the date part as well?
The date field is called TaskDate in the table.
The form control is called txtManager.

Any help would make my day!
Thanks,
Jeff Conrad
Bend, Oregon
Click to expand...
Click to expand...
 
Hi Marsh,

Thanks so much for your time.
Your additional comments on the date part syntax were perfect!!

Between your comments and Graham's comments I learned a LOT this
evening. I'm definitely saving this information!

Thanks again,
Jeff Conrad
Bend, Oregon

Marshall Barton said:
Jeff said:
Using Access 97 here.
I am completely fed up at the moment.
I have been working on solving this very easy problem
for over an hour and a half and I am no closer.

All I want to do is open a report from an unbound form and restrict the
records to display ALL appointments from the CURRENT DATE for the MANAGER
selected in a text box.
Simple right? Not for me apparently.

I have searched the Help, googled like crazy and my syntax looks correct so
what gives?

By the way, Access Help on the OpenReport method was NO help at all!
This is the ENTIRE example listed in Access 97 for OpenReport Method:

"The following example prints Sales Report while using the existing query
Report Filter:

DoCmd.OpenReport "Sales Report", acViewNormal, "Report Filter"

Oh wow, big help! It doesn't even include an example using the where part!!!

My report's record source is a saved query.
The saved query simply gets the information from the table for the current
date for ALL managers. So there is a where part in the SQL on the date.
If I open the query from the Database Window it opens just fine with no
prompts.
If I open the report from the Database Window it opens just fine with no
prompts.

Here is my latest (of many) syntax currently to open the report:
DoCmd.OpenReport "rptTodaysAppointments", acPreview, , "[UserID]=" &
Me.txtManager

It says "Enter Parameter value - Admin"
Huh????

UserID is text and I have tried countless variations with single quotes and
double quotes.
Click to expand...

Try this:

DoCmd.OpenReport "rptTodaysAppointments", acPreview, , _
"[UserID]=""" & Me.txtManager & """"

UserID is not the primary key; an Autonumber is.
Does the Where part only apply to the Primary Key Field?
Click to expand...

Not at all. It's just that numeric fields don't use quotes
around them. Don't forget that date values have to be
enclosed in # signs.

Can I only use this on a bound form?
Click to expand...

Bound or unbound doesn't matter, just so long as it has the
values you need or you can calculate the values.

Do I have to include the date part as well?
Click to expand...

I can't tell from here ;-)

The date field is called TaskDate in the table.
The form control is called txtManager.
Click to expand...

It depends on how you set up the query. If the query has
the date criteria in it, then no, you don't want to do it
again. OTOH, if the query does not have the date criteria
(recommended), then you could use a text box on the form for
the user to enter the starting date:

DoCmd.OpenReport "rptTodaysAppointments", acPreview, , _
"[UserID]=""" & Me.txtManager & """ AND [TaskDate] >= " _
& Format(Me.txtStartDate, "\#m\/d\/yyyy\#")
Click to expand...
 
Back
Top