How to open a report with 2 items input by a user

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that needs to be opened with a SSN and a date. Here is what
I have so far:
SSN is the text box on the form where the user inputs the SSN
SpecificDate is the text box on the form where the user inputs the Date they
would like.

How do I open up the report using these to items.

Dim stDocName As String
stDocName = "SummaryReport"

Dim strWhere As String
strWhere = "SSN = '" & Me!SSN & "'"

strWhere = strWhere & " AND TodaysDate = #" & Format(Me.SpecificDate,
"mm/dd/yyyy") & "#"
DoCmd.openReport stDocName, acViewPreview, , strWhere
 
It isn't pulling any information. Everything is blank.
Could the input mask have something to do with it.

SSN- 000\-00\-0000;0;_
SpecificDate- 00\/00\/0000;0;_
 
Does the field TodaysDate in the table contain time also?
In that case you will get no match, so try this

strWhere = strWhere & " AND format(TodaysDate,"mm/dd/yyyy") = #" &
Format(Me.SpecificDate, "mm/dd/yyyy") & "#"
 
Both the SSN and SpecificDate are Unbound on the form for the user to enter.
TodaysDate is on the Report which is used for the date.
 
The Field TodaysDate is a field in the table that the report is bounded to,
does this field contain also the time, or just the date?
 
TodaysDate should not come from a table it should come from the
Me.SpecificDate textbox that the user inputs on the form. And this field
should be just the Date no time.
 
The report you are running is bounded to a table, all the data that display
in the report comes from a table.
The Where condition you are sending with the open report command line should
include the name of the field in the table filtered by a value

strWhere & "TodaysDate = #" & Format(Me.SpecificDate, "mm/dd/yyyy") & "#"

TodaysDate = the name of the field in the table that the report is bounded to
SpecificDate = the name of the field in the form.

If we are fine up to now, check the date values in the table, does the date
include time or just date
Just incase try this

strWhere = strWhere & " AND format(TodaysDate,"mm/dd/yyyy") = #" &
Format(Me.SpecificDate,
"mm/dd/yyyy") & "#"
 
Back
Top