Query Report Not showing any print preview outputs

  • Thread starter Thread starter Rod
  • Start date Start date
R

Rod

I ahve database that stores several fields. But I need to print out 3 type
of reports using Cylinder , Core and Panel as command buttons and then using
UCS <= 32 (say) for the MPa value and to search for records using Start Date
and End Date. I can't seem to get any output alll ?

UCS and Start Date and End Date ( unbound Text boxes) all work if I use them
in the Criteria in the Query Report but never so far via this Front page Form
with the command buttons.

I am only new to MS Access and still learning. I did post this problem that
last month but no solution for far as yet.
 
Create a Report Filter (WhereCondition clause of OpenReport)
~~~

Hi Rod,

design a report to show the information (for ALL records)

set up a form, which I will call ReportMenu, with:
a combobox to choose UCS
a textbox or combo for Date1
a textbox or combo for Date2
a command button to launch the report

use the textboxes/comboboxes to filter the report

It is best to build a filter string for the report (as opposed to
imbedding a parameter in a query)--in this way, you can use textboxes,
comboboxes, listboxes, etc to make it easier for the user to choose
criteria and you can ignore criteria when it has not been specified...

~~~
here is an example that tests criteria and builds a filter string to use
as a parameter in OpenReport for the WHERECONDITION clause

DoCmd.OpenReport reportname[, View][, FilterName][, WhereCondition]

ie:

DoCmd.OpenReport "MyReport", acViewPreview,, "city IN ('Ct','JHB')"
~~~

assuming you are in the code behind the ReportMenu form...

here is a general case:

'~~~~~~~~~~~~~~~~~~~~~~~~~~
'tell Access you are going to create a variable
dim mFilter as variant

'initialize the variable
mFilter = null

'substitute YOUR controlname in here after "Me."
'we are testing to see if it is filled out
'if it is, we are going to make mFilter hold the criteria
If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" & me.controlname_for_text & "'"
end if

'test the next control
If not IsNull(me.date_controlname ) Then
'if we alread have criteria, add AND to specify that and more
mFilter = (mFilter + " AND ") _
& "[DateFieldname]= #" & me.controlname_for_date & "#"
end if

'test the next control
If not IsNull(me.numeric_controlname ) Then
mfilter = (mFilter + " AND ") _
& "[NumericFieldname]= " & me.controlname_for_number
end if


if not IsNull(mfilter) then
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SPACE UNDERSCORE at the end of a line means that the statement is
continued on the next line
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I have used:

TextFieldname to show how text needs to be delimited - with single quote
marks (you can also use double quote marks

DateFieldname to show that dates need to be delimited with #

NumericFieldname to show that numbers are not delimited

each time, we are testing to see if a filter control is filled out.

If it is, we are going to see if we first need to add AND (if the filter
string already says something)
Then we are going to add the criteria for that filter
mFilter = (mFilter + " AND ") & ... ' some other criteria

make sure that the referenced fields are in the underlying RecordSet for
the report -- and it is sometimes necessary for the fields to be in
controls on the report (the Visible property can be No)

For a Date Range, you would do:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If not IsNull(me.date1_controlname ) Then
mFilter = (mFilter + " AND ") _
& "[DateFieldname]>= #" & me.controlname_for_date1 & "#"
end if

If not IsNull(me.date2_controlname ) Then
mFilter = (mFilter + " AND ") _
& "[DateFieldname] <= #" & me.controlname_for_date2 & "#"
end if

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you are not using the American Date Format, then you may wish to use
the ISO 8601 representation of the date:

Format(me.date_controlname, "\#yyyy\-mm\-dd\#")
instead of
"#" & me.date_controlname & "#"

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

IF you want to also PRINT the criteria on the report

put a LABEL on your PageFooter
name --> Label_Criteria

then, in the OnFormat event of the ReportHeader

'~~~~~~~~~~~~~~~~~~
if len(trim(nz(Me.Filter,""))) > 0 then
me.Label_Criteria.Caption = Me.Filter
me.Label_Criteria.Visible = true
else
me.Label_Criteria.Visible = false
end if
'~~~~~~~~~~~~~~~~~~

hopefully, you can adapt this logic to your question -- if not, we can
help you further

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Difference between + and &
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

& and + are both Operators

The standard Concatenation Operator is ampersand (&). If a term that is
concatenated is Null (has no data; unknown), all terms will display if
you use ampersand.

The Addition Operator is the plus sign (+) … but, even if one of the
terms has a value, the result will be Null if any term is Null (kind of
like multiplying by 0). As in math, what is enclosed in parentheses will
be evaluated first.

Null + "anything" = Null
Null & "anything = "anything"

"something " + "anything" = "something anything"
"something " & "anything" = "something anything"
no difference because both of the terms have a value

Null + "" = Null
Null & "" = ""

(Null + " ") & "Lastname" = "Lastname"
(Null & " ") & "Lastname" = " Lastname"
in the second case, the parentheses do not make a difference, each term
is concatenated -- and note the space in the result before Lastname

Do you see the difference between using + and using & ? For instance, if
you want to add a space between first and last name but you are not sure
that first name will be filled out, you can do this:

(Firstname + " ") & Lastname

What is in the parentheses is evaluated first -- then it is concatenated
to what comes next

You might also want to do this:

(Firstname + " ") & (Middlename + " ") & Lastname

Combining + and & in an expression gives you a way to make the result
look right without having to test if something is not filled out.

What if firstname is filled but nothing else? There will be a space at
the end. Usually, this is not a problem but if you want to chop it off,
you can wrap the whole expression in the Trim function, which truncates
leading and trailing spaces.

Trim((Firstname + " ") & (Middlename + " ") & Lastname)

here is something you may want to read:

Access Basics on Access MVP site
http://www.accessmvp.com/Strive4Peace/Index.htm
8-part free tutorial that covers essentials in Access


Warm Regards,
Crystal

*
(: have an awesome day :)
*
 
Hi Strive4,

I read your suggestion but not sure which section of the Report Programs
this applys? I am not a VB or coding guru hence I prefer the easier path
using the MS Access Expert system to generate this simple report which has
Search From Date = mm/dd/yy and To Date = mm/dd/yy for
1. <= UCS MPa
2. for command button = Core or
for command button = Cylinder or
for command button = Panel
we click on on one of the above command buttons to get a print preview report.


Below the full code that was automatically generated and I still can not get
to work !??

=====================================================
Option Compare Database

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String

stDocName = "Core_or_cylinder"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub
Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Dim stDocName As String

stDocName = "Core_or_cylinder"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click

End Sub
Private Sub Query1_Click()
On Error GoTo Err_Query1_Click

Dim stDocName As String

stDocName = "Core_cylinder Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Query1_Click:
Exit Sub

Err_Query1_Click:
MsgBox Err.Description
Resume Exit_Query1_Click

End Sub

Private Sub Core_MouseUp(Button As Integer, Shift As Integer, X As Single, Y

As Single)

End Sub

Private Sub Preview_report_Click()
On Error GoTo Err_Preview_report_Click

Dim stDocName As String

stDocName = "Core_cylinder Query"
DoCmd.OpenReport stDocName, acPreview

Exit_Preview_report_Click:
Exit Sub

Err_Preview_report_Click:
MsgBox Err.Description
Resume Exit_Preview_report_Click

End Sub
Private Sub Command52_Click()
On Error GoTo Err_Command52_Click

Dim stDialStr As String
Dim PrevCtl As Control
Const ERR_OBJNOTEXIST = 2467
Const ERR_OBJNOTSET = 91
Const ERR_CANTMOVE = 2483

Set PrevCtl = Screen.PreviousControl

If TypeOf PrevCtl Is TextBox Then
stDialStr = IIf(VarType(PrevCtl) > V_NULL, PrevCtl, "")
ElseIf TypeOf PrevCtl Is ListBox Then
stDialStr = IIf(VarType(PrevCtl) > V_NULL, PrevCtl, "")
ElseIf TypeOf PrevCtl Is ComboBox Then
stDialStr = IIf(VarType(PrevCtl) > V_NULL, PrevCtl, "")
Else
stDialStr = ""
End If

Application.Run "utility.wlib_AutoDial", stDialStr

Exit_Command52_Click:
Exit Sub

Err_Command52_Click:
If (Err = ERR_OBJNOTEXIST) Or (Err = ERR_OBJNOTSET) Or (Err =

ERR_CANTMOVE) Then
Resume Next
End If
MsgBox Err.Description
Resume Exit_Command52_Click

End Sub

Private Sub Command69_Click()
On Error GoTo Err_Command69_Click


DoCmd.Close

Exit_Command69_Click:
Exit Sub

Err_Command69_Click:
MsgBox Err.Description
Resume Exit_Command69_Click

End Sub


=====================================================


strive4peace said:
Create a Report Filter (WhereCondition clause of OpenReport)
~~~

Hi Rod,

design a report to show the information (for ALL records)

set up a form, which I will call ReportMenu, with:
a combobox to choose UCS
a textbox or combo for Date1
a textbox or combo for Date2
a command button to launch the report

use the textboxes/comboboxes to filter the report

It is best to build a filter string for the report (as opposed to
imbedding a parameter in a query)--in this way, you can use textboxes,
comboboxes, listboxes, etc to make it easier for the user to choose
criteria and you can ignore criteria when it has not been specified...

~~~
here is an example that tests criteria and builds a filter string to use
as a parameter in OpenReport for the WHERECONDITION clause

DoCmd.OpenReport reportname[, View][, FilterName][, WhereCondition]

ie:

DoCmd.OpenReport "MyReport", acViewPreview,, "city IN ('Ct','JHB')"
~~~

assuming you are in the code behind the ReportMenu form...

here is a general case:

'~~~~~~~~~~~~~~~~~~~~~~~~~~
'tell Access you are going to create a variable
dim mFilter as variant

'initialize the variable
mFilter = null

'substitute YOUR controlname in here after "Me."
'we are testing to see if it is filled out
'if it is, we are going to make mFilter hold the criteria
If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" & me.controlname_for_text & "'"
end if

'test the next control
If not IsNull(me.date_controlname ) Then
'if we alread have criteria, add AND to specify that and more
mFilter = (mFilter + " AND ") _
& "[DateFieldname]= #" & me.controlname_for_date & "#"
end if

'test the next control
If not IsNull(me.numeric_controlname ) Then
mfilter = (mFilter + " AND ") _
& "[NumericFieldname]= " & me.controlname_for_number
end if


if not IsNull(mfilter) then
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SPACE UNDERSCORE at the end of a line means that the statement is
continued on the next line
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I have used:

TextFieldname to show how text needs to be delimited - with single quote
marks (you can also use double quote marks

DateFieldname to show that dates need to be delimited with #

NumericFieldname to show that numbers are not delimited

each time, we are testing to see if a filter control is filled out.

If it is, we are going to see if we first need to add AND (if the filter
string already says something)
Then we are going to add the criteria for that filter
mFilter = (mFilter + " AND ") & ... ' some other criteria

make sure that the referenced fields are in the underlying RecordSet for
the report -- and it is sometimes necessary for the fields to be in
controls on the report (the Visible property can be No)

For a Date Range, you would do:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If not IsNull(me.date1_controlname ) Then
mFilter = (mFilter + " AND ") _
& "[DateFieldname]>= #" & me.controlname_for_date1 & "#"
end if

If not IsNull(me.date2_controlname ) Then
mFilter = (mFilter + " AND ") _
& "[DateFieldname] <= #" & me.controlname_for_date2 & "#"
end if

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you are not using the American Date Format, then you may wish to use
the ISO 8601 representation of the date:

Format(me.date_controlname, "\#yyyy\-mm\-dd\#")
instead of
"#" & me.date_controlname & "#"

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

IF you want to also PRINT the criteria on the report

put a LABEL on your PageFooter
name --> Label_Criteria

then, in the OnFormat event of the ReportHeader

'~~~~~~~~~~~~~~~~~~
if len(trim(nz(Me.Filter,""))) > 0 then
me.Label_Criteria.Caption = Me.Filter
me.Label_Criteria.Visible = true
else
me.Label_Criteria.Visible = false
end if
'~~~~~~~~~~~~~~~~~~

hopefully, you can adapt this logic to your question -- if not, we can
help you further

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Difference between + and &
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

& and + are both Operators

The standard Concatenation Operator is ampersand (&). If a term that is
concatenated is Null (has no data; unknown), all terms will display if
you use ampersand.

The Addition Operator is the plus sign (+) … but, even if one of the
terms has a value, the result will be Null if any term is Null (kind of
like multiplying by 0). As in math, what is enclosed in parentheses will
be evaluated first.

Null + "anything" = Null
Null & "anything = "anything"

"something " + "anything" = "something anything"
"something " & "anything" = "something anything"
no difference because both of the terms have a value

Null + "" = Null
Null & "" = ""

(Null + " ") & "Lastname" = "Lastname"
(Null & " ") & "Lastname" = " Lastname"
in the second case, the parentheses do not make a difference, each term
is concatenated -- and note the space in the result before Lastname

Do you see the difference between using + and using & ? For instance, if
you want to add a space between first and last name but you are not sure
that first name will be filled out, you can do this:

(Firstname + " ") & Lastname

What is in the parentheses is evaluated first -- then it is concatenated
to what comes next

You might also want to do this:

(Firstname + " ") & (Middlename + " ") & Lastname

Combining + and & in an expression gives you a way to make the result
look right without having to test if something is not filled out.

What if firstname is filled but nothing else? There will be a space at
the end. Usually, this is not a problem but if you want to chop it off,
you can wrap the whole expression in the Trim function, which truncates
leading and trailing spaces.

Trim((Firstname + " ") & (Middlename + " ") & Lastname)

here is something you may want to read:

Access Basics on Access MVP site
http://www.accessmvp.com/Strive4Peace/Index.htm
8-part free tutorial that covers essentials in Access


Warm Regards,
Crystal

*
(: have an awesome day :)
*

I ahve database that stores several fields. But I need to print out 3 type
of reports using Cylinder , Core and Panel as command buttons and then using
UCS <= 32 (say) for the MPa value and to search for records using Start Date
and End Date. I can't seem to get any output alll ?

UCS and Start Date and End Date ( unbound Text boxes) all work if I use them
in the Criteria in the Query Report but never so far via this Front page Form
with the command buttons.

I am only new to MS Access and still learning. I did post this problem that
last month but no solution for far as yet.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query Report using Start and End Dates with one Criterion 9
Access Access Report Works on One Machine but not Another 3
output report from filtered query 1
Printing a report 2
Can print but not preview report 2
output report from a query with criteria 3
Report Criteria in Form 3
Previewing a report 3

Back
Top