Help with Date Parameter

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ben - 31 Oct 2007 19:13 GMT

I have a Select Query where I want the user to be prompted to input the
specific date for records. The label is called "Business Date" and is on the
form "Daily Input Form". In the query design view, under Query =>
Parameters, I typed "Business Date" in the left column and selected
"Date/Time" in the right column.

When I run the query, records for all dates are displayed instead of just
the date in the prompt.

HELP! I am a beginner / novice, and tried looking for the answer in this
discussion group & Access Help. I'm sorry if this is a stupid question.

Reply to this Message

DanielWalters6 - 31 Oct 2007 19:55 GMT

A new posting is never stupid, if you've searched this forum and can't find
the results you need. Posting is a good way to help yourself and others!

Best wishes

Reply to this Message

fredg - 31 Oct 2007 20:06 GMT

> I have a Select Query where I want the user to be prompted to input the
> specific date for records. The label is called "Business Date" and is on the
[quoted text clipped - 7 lines]
> HELP! I am a beginner / novice, and tried looking for the answer in this
> discussion group & Access Help. I'm sorry if this is a stupid question.

You only want records on the one date entered?

You need to set the query criteria to:
[Forms]![Daily Input Form]![Business Date]

Because your form and field names contain a space, you MUST enclose
those names within brackets.

Note: using spaces in Form and Field names is not a good idea.
Daily_Import_Form is just as easy to read, yet Access considers it as
one word. Brackets will not be necessary (but are still good
programming practice).

Next ....
In the Query Parameter dialog box, write:
[Forms]![Daily Input Form]![Business Date]
and select DateTime in the datatype box.
If you have additional criteria, you must include them, as above.

Include the brackets.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

Ben - 31 Oct 2007 20:35 GMT

So my SQL view appears as follows:
PARAMETERS [Forms]![Daily Input Form]![Business Date] DateTime;
SELECT .........

However, the value for all the other values are showing 0 instead of the
actual value. For example, there is a field called "FTE" with a value of 3
on X date. When I input X in the parameter popup, the results are displayed
with a '0' instead of a '3'.

> > I have a Select Query where I want the user to be prompted to input the
> > specific date for records. The label is called "Business Date" and is on the
[quoted text clipped - 28 lines]
>
> Include the brackets.

Reply to this Message

John Spencer - 01 Nov 2007 00:31 GMT

DId you put the parameter prompt under the field you wish to apply the
criteria to?

Field: SomeDateField
Criteria: [Forms]![Daily Input Form]![Business Date]

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

> So my SQL view appears as follows:
> PARAMETERS [Forms]![Daily Input Form]![Business Date] DateTime;
[quoted text clipped - 36 lines]
>>
>> Include the brackets.

Reply to this Message

Ben - 01 Nov 2007 16:43 GMT

Yes - I actually deleted the data in the table and input again. This time it
worked.

Another question - when the parameter window pops up - is there a way to
display text other than "[Forms]![Daily Input Form]![Business Date]" such as
"Enter Business Date of Report" ?

Thanks!

> DId you put the parameter prompt under the field you wish to apply the
> criteria to?
[quoted text clipped - 49 lines]
> >>
> >> Include the brackets.

Reply to this Message

fredg - 01 Nov 2007 16:58 GMT

> Yes - I actually deleted the data in the table and input again. This time it
> worked.
[quoted text clipped - 58 lines]
>>>>
>>>> Include the brackets.

Where did you get [Forms]![Daily Input Form]![Business Date] from?

Aren't you using a control on a form to enter the date parameter in?
That's what [Forms]![Daily Input Form]![Business Date] is looking for.
A control named [Business Date] on the form named [Daily Input Form].
If you use a form you will not be prompted at all.

If you're not actually using a form to enter the parameter in, and you
wish to be prompted for the date, change the query criteria (and the
query parameter dialog as well) to:
[Enter Date]

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message