Creating report based on input form in MS Access (1 Viewer)

Martyh

Registered User.
Local time
Today, 03:00
Joined
May 2, 2000
Messages
192
Hi all,

I have button on my form which does the following:

Code:
Private Sub cmdPrintRollCall_Click()
On Error GoTo Err_cmdPrintRollCall_Click
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "rptRollCall1stXR-12"
stLinkCriteria = "TDate = " & Me.dteDate   'the date on the Form

DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria
Err_cmdPrintRollCall_Click:

End Sub

When I run this code the following happens:

ParameterERror.JPG

When I enter the date (04/15/2022) I get:

RollcallError.JPG

the incorrect answer!!


The parameter value and SQL query is given in the Report record source as:
PARAMETERS TDate DateTime; SELECT stayID, ProNum, ParticipantLastName, BookedIN, BookedOUT, RoomBedID, RoomNo, Bed FROM qsRoomsBookedForEmergency WHERE ((((Abs([Tdate]-[BookedIN])<[Days]) And ([TDate]-[BookedIN]>=0))=True));

And when I independantly run the report (of course I have to put in the parameter) it comes up with the appropriate results as follows:

RollcallError2.JPG


So... I would like to use the form to specify Me.dteDate and then run the report. What am I doing wrong?

Perplexed,

Marty H.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:00
Joined
Feb 28, 2001
Messages
27,001
The "what am I doing wrong" is that TDate is somehow not defined for stLinkCriteria. The "Parameter" request invariably means that some item in the query is not properly defined, so Access doesn't know what it is.

My off-handed guess is that you need to qualify it, because perhaps Access doesn't know where TDate is found. If that is a field in a table, qualify by prefixing the table name, as myTable.TDate or something like that. If it is NOT a field in a table, then where would TDate be found?
 

bob fitz

AWF VIP
Local time
Today, 07:00
Joined
May 23, 2011
Messages
4,717
Perhaps:
stLinkCriteria = "TDate = #" & Me.dteDate & "#"
Rather than:
stLinkCriteria = "TDate = " & Me.dteDate
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:00
Joined
Sep 21, 2011
Messages
14,048
As you are concatenating controls and sql

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?

Dates with # and in mm/dd/yyyy or yyyy-mm-dd format

Numbers do not need anything
 

Martyh

Registered User.
Local time
Today, 03:00
Joined
May 2, 2000
Messages
192
TDate is the parameter which I am using ...

Just before I call the report I am setting it to the date on the form (Me.dteDate)

I am not sure what you are asking...

PS thanks for the Instant response!!
 

Martyh

Registered User.
Local time
Today, 03:00
Joined
May 2, 2000
Messages
192

To: bob fitz and GasMan​

I think that since TDate is already specified as a date, then nothing is required... anyways have tried single right thru to triple quotes, and "#" nothing seems to work any better!

Marty
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:00
Joined
Sep 21, 2011
Messages
14,048
TDate is the parameter which I am using ...

Just before I call the report I am setting it to the date on the form (Me.dteDate)

I am not sure what you are asking...

PS thanks for the Instant response!!
You are not though?, you are testing TDate for the value of Me.dteDate
What are the date fields in the report?
 

bob fitz

AWF VIP
Local time
Today, 07:00
Joined
May 23, 2011
Messages
4,717

To: bob fitz and GasMan​

I think that since TDate is already specified as a date, then nothing is required... anyways have tried single right thru to triple quotes, and "#" nothing seems to work any better!

Marty
Might be benificial to post a copy of the db.
 

Martyh

Registered User.
Local time
Today, 03:00
Joined
May 2, 2000
Messages
192
Gasman,

the parameter is defined in the report's recordsource which is :

PARAMETERS TDate DateTime; SELECT stayID, ProNum, ParticipantLastName, BookedIN, BookedOUT, RoomBedID, RoomNo, Bed FROM qsRoomsBookedForEmergency WHERE ((((Abs([Tdate]-[BookedIN])<[Days]) And ([TDate]-[BookedIN]>=0))=True));
 

Martyh

Registered User.
Local time
Today, 03:00
Joined
May 2, 2000
Messages
192
its a BIG db ... so it will take a little while before I can post some thing representative of the problem !! as well I am bound by certain rules!
 

bob fitz

AWF VIP
Local time
Today, 07:00
Joined
May 23, 2011
Messages
4,717
its a BIG db ... so it will take a little while before I can post some thing representative of the problem !! as well I am bound by certain rules!
Make a copy. Remove all data. Add a few ficticious records to illistrate the problem.
Compact and Repaire the db. Zip it and post the zipped file,
 

Martyh

Registered User.
Local time
Today, 03:00
Joined
May 2, 2000
Messages
192
Just as a followup, I corrected the problem by simply forgetting about the report, and putting out a text file that had ALL the fields I needed including Me.dteDate. I still don't see why the issue though!

Thanks for your help.

Marty
 

Users who are viewing this thread

Top Bottom