parameter query linked to a form

[Guest]

I have a report that is selected to run from a switchboard. A form pops up
to enter criteria to select an event and then a start and end date. The form
is based off a parameter query. Here's the SQL I currently have:

SELECT tblDetails.EventCategory, tblData.LoadDate, tblDetails.DetailsTime,
tblDetails.Description, tblDetails.LoadDateID
FROM tblData INNER JOIN tblDetails ON tblData.LoadDateID =
tblDetails.LoadDateID
WHERE (((tblDetails.EventCategory) Like
[Forms]![frmFilterEventCategory]![cboEvent] & "*") AND ((tblData.LoadDate)
Between [Forms]![frmFilterEventCategory]![txtBeginDate] And
[Forms]![frmFilterEventCategory]![txtEndDate]));

With the current date criteria in the query, the form opens to a blank
screen. If I take the date criteria out and just leave the event criteria,
it opens fine and I can enter all the proper data although, of course, the
dates won't query properly. I also have the same date criteria entered in
the report header so it will print the proper date span on the report. That
seems to work OK. I've tried deleting the event criteria from the query to
see if that changes anything, and I tried deleting the criteria from the
report header, but i still get a blank screen whenever there's data in the
date criteria of the query.

Is there something in my query that I'm doing wrong?

TIA,
Patti

 

[Jeff Boyce]

Patti

I suspect Access doesn't understand that the contents of the "date" controls
are dates.

What happens if you modify the SQL to something like:

"...
AND ((tblData.LoadDate) Between #" &
[Forms]![frmFilterEventCategory]![txtBeginDate] & "# And #" &
[Forms]![frmFilterEventCategory]![txtEndDate]) & "# ...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

Jeff,
I tried your suggestion and I get errors. When I try running the query wile
in "query design mode" using my original SQL, it works fine. I'm wondering
if there's something wrong with the text boxes in the form? I've tried
looking in the properties menu and can't seem to find anything that lets me
tell its a date field or something???
--
Patti

Patti

I suspect Access doesn't understand that the contents of the "date" controls
are dates.

What happens if you modify the SQL to something like:

"...
AND ((tblData.LoadDate) Between #" &
[Forms]![frmFilterEventCategory]![txtBeginDate] & "# And #" &
[Forms]![frmFilterEventCategory]![txtEndDate]) & "# ...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I have a report that is selected to run from a switchboard. A form pops up
to enter criteria to select an event and then a start and end date. The form
is based off a parameter query. Here's the SQL I currently have:

SELECT tblDetails.EventCategory, tblData.LoadDate, tblDetails.DetailsTime,
tblDetails.Description, tblDetails.LoadDateID
FROM tblData INNER JOIN tblDetails ON tblData.LoadDateID =
tblDetails.LoadDateID
WHERE (((tblDetails.EventCategory) Like
[Forms]![frmFilterEventCategory]![cboEvent] & "*") AND ((tblData.LoadDate)
Between [Forms]![frmFilterEventCategory]![txtBeginDate] And
[Forms]![frmFilterEventCategory]![txtEndDate]));

With the current date criteria in the query, the form opens to a blank
screen. If I take the date criteria out and just leave the event criteria,
it opens fine and I can enter all the proper data although, of course, the
dates won't query properly. I also have the same date criteria entered in
the report header so it will print the proper date span on the report. That
seems to work OK. I've tried deleting the event criteria from the query to
see if that changes anything, and I tried deleting the criteria from the
report header, but i still get a blank screen whenever there's data in the
date criteria of the query.

Is there something in my query that I'm doing wrong?

TIA,
Patti

 

[Jeff Boyce]

Your query works... tell me again why you can't use your query, then?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Jeff,
I tried your suggestion and I get errors. When I try running the query wile
in "query design mode" using my original SQL, it works fine. I'm wondering
if there's something wrong with the text boxes in the form? I've tried
looking in the properties menu and can't seem to find anything that lets me
tell its a date field or something???
--
Patti

Patti

I suspect Access doesn't understand that the contents of the "date" controls
are dates.

What happens if you modify the SQL to something like:

"...
AND ((tblData.LoadDate) Between #" &
[Forms]![frmFilterEventCategory]![txtBeginDate] & "# And #" &
[Forms]![frmFilterEventCategory]![txtEndDate]) & "# ...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I have a report that is selected to run from a switchboard. A form

pops
up

to enter criteria to select an event and then a start and end date.

The
form

is based off a parameter query. Here's the SQL I currently have:

SELECT tblDetails.EventCategory, tblData.LoadDate, tblDetails.DetailsTime,
tblDetails.Description, tblDetails.LoadDateID
FROM tblData INNER JOIN tblDetails ON tblData.LoadDateID =
tblDetails.LoadDateID
WHERE (((tblDetails.EventCategory) Like
[Forms]![frmFilterEventCategory]![cboEvent] & "*") AND ((tblData.LoadDate)
Between [Forms]![frmFilterEventCategory]![txtBeginDate] And
[Forms]![frmFilterEventCategory]![txtEndDate]));

With the current date criteria in the query, the form opens to a blank
screen. If I take the date criteria out and just leave the event criteria,
it opens fine and I can enter all the proper data although, of course, the
dates won't query properly. I also have the same date criteria entered in
the report header so it will print the proper date span on the report. That
seems to work OK. I've tried deleting the event criteria from the

query
to

see if that changes anything, and I tried deleting the criteria from the
report header, but i still get a blank screen whenever there's data in the
date criteria of the query.

Is there something in my query that I'm doing wrong?

TIA,
Patti

 

[Guest]

Yes, the query works fine when I run it from the query objects list.
However, for the end user, I have designed a switchboard for "Print Reports".
There are several options, one of which is "Report by Event". When
selected, a form opens, based on the query in question, allowing the user to
select an event from a combo box, and then enter the start and end date from
2 unbound text boxes. When I have parameters entered in the criteria
portion of the date field in the query, the form opens, but it is a blank
screen. If I take the date parameters out of the query, the form opens as it
should and they can enter dates in the text boxes, but of course, the report
will include all dates since there's no criteria in the query any more. I
can't figure out why the form will not open properly with date criteria
entered, but opens fine without it. The event criteria seems to work just
fine its just the date field thats creating the problem.

I hope this explains it a bit better.
--
Patti

Your query works... tell me again why you can't use your query, then?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Jeff,
I tried your suggestion and I get errors. When I try running the query wile
in "query design mode" using my original SQL, it works fine. I'm wondering
if there's something wrong with the text boxes in the form? I've tried
looking in the properties menu and can't seem to find anything that lets me
tell its a date field or something???
--
Patti

Patti

I suspect Access doesn't understand that the contents of the "date" controls
are dates.

What happens if you modify the SQL to something like:

"...
AND ((tblData.LoadDate) Between #" &
[Forms]![frmFilterEventCategory]![txtBeginDate] & "# And #" &
[Forms]![frmFilterEventCategory]![txtEndDate]) & "# ...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


I have a report that is selected to run from a switchboard. A form pops
up
to enter criteria to select an event and then a start and end date. The
form
is based off a parameter query. Here's the SQL I currently have:

SELECT tblDetails.EventCategory, tblData.LoadDate, tblDetails.DetailsTime,
tblDetails.Description, tblDetails.LoadDateID
FROM tblData INNER JOIN tblDetails ON tblData.LoadDateID =
tblDetails.LoadDateID
WHERE (((tblDetails.EventCategory) Like
[Forms]![frmFilterEventCategory]![cboEvent] & "*") AND ((tblData.LoadDate)
Between [Forms]![frmFilterEventCategory]![txtBeginDate] And
[Forms]![frmFilterEventCategory]![txtEndDate]));

With the current date criteria in the query, the form opens to a blank
screen. If I take the date criteria out and just leave the event
criteria,
it opens fine and I can enter all the proper data although, of course, the
dates won't query properly. I also have the same date criteria entered in
the report header so it will print the proper date span on the report.
That
seems to work OK. I've tried deleting the event criteria from the query
to
see if that changes anything, and I tried deleting the criteria from the
report header, but i still get a blank screen whenever there's data in the
date criteria of the query.

Is there something in my query that I'm doing wrong?

TIA,
Patti

 

[Guest]

Jeff,
I FINALLY figured it out!!! In the form properties, I had the Record Source
linked to the query. I took that out and left the record source blank and
now it works fine!!!
--
Patti

Your query works... tell me again why you can't use your query, then?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Jeff,
I tried your suggestion and I get errors. When I try running the query wile
in "query design mode" using my original SQL, it works fine. I'm wondering
if there's something wrong with the text boxes in the form? I've tried
looking in the properties menu and can't seem to find anything that lets me
tell its a date field or something???
--
Patti

Patti

I suspect Access doesn't understand that the contents of the "date" controls
are dates.

What happens if you modify the SQL to something like:

"...
AND ((tblData.LoadDate) Between #" &
[Forms]![frmFilterEventCategory]![txtBeginDate] & "# And #" &
[Forms]![frmFilterEventCategory]![txtEndDate]) & "# ...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


I have a report that is selected to run from a switchboard. A form pops
up
to enter criteria to select an event and then a start and end date. The
form
is based off a parameter query. Here's the SQL I currently have:

SELECT tblDetails.EventCategory, tblData.LoadDate, tblDetails.DetailsTime,
tblDetails.Description, tblDetails.LoadDateID
FROM tblData INNER JOIN tblDetails ON tblData.LoadDateID =
tblDetails.LoadDateID
WHERE (((tblDetails.EventCategory) Like
[Forms]![frmFilterEventCategory]![cboEvent] & "*") AND ((tblData.LoadDate)
Between [Forms]![frmFilterEventCategory]![txtBeginDate] And
[Forms]![frmFilterEventCategory]![txtEndDate]));

With the current date criteria in the query, the form opens to a blank
screen. If I take the date criteria out and just leave the event
criteria,
it opens fine and I can enter all the proper data although, of course, the
dates won't query properly. I also have the same date criteria entered in
the report header so it will print the proper date span on the report.
That
seems to work OK. I've tried deleting the event criteria from the query
to
see if that changes anything, and I tried deleting the criteria from the
report header, but i still get a blank screen whenever there's data in the
date criteria of the query.

Is there something in my query that I'm doing wrong?

TIA,
Patti