filtering a report based on form field value

  • Thread starter Thread starter Gator
  • Start date Start date
G

Gator

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
 
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\#")
 
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

--
Gator


Graham Mandeno said:
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\#")
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Gator said:
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
Click to expand...
Click to expand...
 
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


Graham Mandeno said:
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\#")
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Gator said:
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
Click to expand...
Click to expand...
 
never mind ...i got it....thanks
--
Gator


Graham Mandeno said:
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\#")
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Gator said:
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
Click to expand...
Click to expand...
 
Great! I'm glad you managed to sort it out.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Gator said:
never mind ...i got it....thanks
--
Gator


Graham Mandeno said:
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\#")
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Gator said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top