Creating report based on input form in MS Access

[Martyh]

Hi all,

I have button on my form which does the following:

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:

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

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:

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]

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]

Perhaps:
stLinkCriteria = "TDate = #" & Me.dteDate & "#"
Rather than:
stLinkCriteria = "TDate = " & Me.dteDate

 

[Gasman]

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]

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]

To bob fitz:

I have already attempted that... but still no "cigar"!!

 

[Martyh]

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]

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]

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]

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));

 

[bob fitz]

Can you post a copy of the db

 

[Gasman]

Gasman,

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

Yes, I know, but it still has to contain something?

 

[Martyh]

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]

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]

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