filtering a report based on form field value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gator - 28 May 2008 22:32 GMT

I have a form / subform
the form contains a field with date values and the subform contains funds
and amounts entered on that date. When my form is displaying a certain
record for a date, I want to be able to click on a button that will open a
report displaying the date payments for that date in the textbox. I figure
it's in the WHERE clause of the OpenReport...something like

docmd.OpenReport ReportName, acPreview, , "[ReportFieldName] =" &
Me![FormFieldName]

when I tried this it gave me an error

Signature

Gator

Reply to this Message

Graham Mandeno - 28 May 2008 23:16 GMT

Hi Gator

If [ReportFieldName] and [FormFieldName] are dates, then you must use the
special SQL syntax for date/time values.

The date must be enclosed in hash/pound signs (#) and should be in the
American format (mm/dd/yyyy) or in "international" format (yyyy-mm-dd). I
prefer the latter, because it is less ambiguous.

So, you need:

DoCmd.OpenReport ReportName, acPreview, ,"[ReportFieldName] =" _
& Format(Me![FormFieldName], "\#yyyy-mm-dd\#")

Signature

Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

>I have a form / subform
> the form contains a field with date values and the subform contains funds
[quoted text clipped - 8 lines]
>
> when I tried this it gave me an error

Reply to this Message

Gator - 29 May 2008 20:10 GMT

I can't get it to work...here is my code...

Private Sub Command14_Click()
On Error GoTo Err_Command14_Click

Dim stDocName As String

stDocName = "PayablesDetails"

DoCmd.OpenReport stDocName, acPreview, , "[PayDate_by_Day] =" &
Format(Me![PayDate], "\#mm/dd/yyyy\#")

Exit_Command14_Click:
Exit Sub

Err_Command14_Click:
MsgBox Err.Description
Resume Exit_Command14_Click

End Sub

Signature

Gator

> Hi Gator
>
[quoted text clipped - 21 lines]
> >
> > when I tried this it gave me an error

Reply to this Message

Gator - 29 May 2008 20:18 GMT

Also, when I click the button, a insert parameter popup comes up for me to
enter the date....I would rather Access read what date is displayed. --
Gator

> Hi Gator
>
[quoted text clipped - 21 lines]
> >
> > when I tried this it gave me an error

Reply to this Message

Gator - 29 May 2008 20:25 GMT

never mind ...i got it....thanks

Signature

Gator

> Hi Gator
>
[quoted text clipped - 21 lines]
> >
> > when I tried this it gave me an error

Reply to this Message

Graham Mandeno - 29 May 2008 22:50 GMT

Great! I'm glad you managed to sort it out.

Signature

Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

> never mind ...i got it....thanks
>
[quoted text clipped - 26 lines]
>> >
>> > when I tried this it gave me an error

Reply to this Message