Cmd Btn to open Report W/ Specific Data

[Joseph]

I know how to code the button to open the report from a form, but getting the
correct data, specific data, I am having trouble with.

I have 4 db all linked together in some way.

Cadets - Cadet Info, ie: Name, Rank, Address, etc
Merits - Basically a progress report for the cadets
Staff - Staff Info, ie, Name, Rank, Address, etc
Interface - All the forms, queries, reports with linked tables to the
cooresponding databases

With the db Merit, I have 1 main table (Merits) and 14 subtables linked
together using the MeritsID pkey. I am not right now enforcing integrity
with the tables and have one to many relationships.

When I open the form frmMerits, all the tables link up correctly and I have
no problem entering the data, but when I want to preview the report for that
Cadet on that Date, I get either nothing on the report or a No Data Error.

I use the syntax

Private Sub PPTR_Click()
On Error GoTo Err_PPTR_Click

Dim stDocName As String
Dim stCriteria As String

stDocName = "Daily"
stCriteria = "'[Date]=' & Me![Date] & '[CadetId]='&Me![CadetId]"
DoCmd.OpenReport stDocName, acPreview,, stCriteria

Exit_PPTR_Click:
Exit Sub

Err_PPTR_Click:
MsgBox Err.Description
Resume Exit_PPTR_Click

End Sub

Where am I going wrong in this? All my other forms to reports work fine.

Thank you

 

[Ofer Cohen]

To create a criteria for Date Text and Number is different

For Date Add # before and after the variable
stCriteria = "[Date]= #" & Me![Date] & "#"

For Text Add ' before and after the variable
stCriteria = "[TextFieldName]= '" & Me![TextFieldName] & "'"

For Number Nothing
stCriteria = "[NumberFieldName]= " & Me![NumberFieldName]

=========================================
For your case, to join both field try - if CadetId is text field:
stCriteria = "[Date]= #" & Me![Date] & "# And [CadetId]='" & Me![CadetId] &
"'"

If CadetId is Numeric field try:
stCriteria = "[Date]= #" & Me![Date] & "# And [CadetId]=" & Me![CadetId]


***Note: its not rocomanded to use Key names in Access as fields name (Date)
**