Missing data in report

  • Thread starter Thread starter SwEdIsH_OfFiCe_UsEr
  • Start date Start date
S

SwEdIsH_OfFiCe_UsEr

I have selected a query for my report, and the query asks for the parameters
before presenting data. The query itself works fine and returns all values I
want, but when I run it through the report and want it to present it in a
printable way, some of the data is missing. When I leave some of the
parameters blank (which makes it search for all values), it doesn't add that
information in the report, except for date which is always shown. But if I
search with that parameter, it is shown. This has really gotten me confused,
and I don't know why it is doing this. What is wrong?

Query SQL:

PARAMETERS [startDate] DateTime, [endDate] DateTime, [param1] Text ( 255 ),
[param2] Text ( 255 );
SELECT table1.field1, table1.dateField, table1.field2, table1.field3,
table1.field4
FROM table1.
WHERE (((table1.dateField) Between Nz([startDate],#1/1/1900#) And
Nz([endDate],#1/1/2100#)) AND ((table1.field1) Is Null Or (table1.field1)
Like "*" & [param1] & "*") AND ((table1.field2) Is Null Or (table1.field2)
Like "*" & [param2] & "*"));
 
It's difficult to determine which records aren't returned unless you tell us
what parameters are entered and what isn't showing in the report. IMHO, don't
use parameter prompts. References to controls on forms are much better.
Try:

PARAMETERS [startDate] DateTime, [endDate] DateTime, [param1] Text ( 255 ),
[param2] Text ( 255 );
SELECT table1.field1, table1.dateField, table1.field2, table1.field3,
table1.field4
FROM table1.
WHERE dateField Between Nz([startDate],#1/1/1900#) And
Nz([endDate],#1/1/2100#) AND
table1.field1 & "" Like "*" & [param1] & "*" AND
table1.field2 & "" Like "*" & [param2] & "*";
 
It still doesn't work. Those fields are only filled in if I search for a
specified value. The input parameters are StartDate, EndDate, Param1 and
Param2.

What are references controls on forms and how do I use them? Does that mean
I form in which I enter the values in different fields and then press search,
like an advanced search say Google's? That's really what I'm looking for. How
do I use them?

Duane Hookom said:
It's difficult to determine which records aren't returned unless you tell us
what parameters are entered and what isn't showing in the report. IMHO, don't
use parameter prompts. References to controls on forms are much better.
Try:

PARAMETERS [startDate] DateTime, [endDate] DateTime, [param1] Text ( 255 ),
[param2] Text ( 255 );
SELECT table1.field1, table1.dateField, table1.field2, table1.field3,
table1.field4
FROM table1.
WHERE dateField Between Nz([startDate],#1/1/1900#) And
Nz([endDate],#1/1/2100#) AND
table1.field1 & "" Like "*" & [param1] & "*" AND
table1.field2 & "" Like "*" & [param2] & "*";

--
Duane Hookom
Microsoft Access MVP


SwEdIsH_OfFiCe_UsEr said:
I have selected a query for my report, and the query asks for the parameters
before presenting data. The query itself works fine and returns all values I
want, but when I run it through the report and want it to present it in a
printable way, some of the data is missing. When I leave some of the
parameters blank (which makes it search for all values), it doesn't add that
information in the report, except for date which is always shown. But if I
search with that parameter, it is shown. This has really gotten me confused,
and I don't know why it is doing this. What is wrong?

Query SQL:

PARAMETERS [startDate] DateTime, [endDate] DateTime, [param1] Text ( 255 ),
[param2] Text ( 255 );
SELECT table1.field1, table1.dateField, table1.field2, table1.field3,
table1.field4
FROM table1.
WHERE (((table1.dateField) Between Nz([startDate],#1/1/1900#) And
Nz([endDate],#1/1/2100#)) AND ((table1.field1) Is Null Or (table1.field1)
Like "*" & [param1] & "*") AND ((table1.field2) Is Null Or (table1.field2)
Like "*" & [param2] & "*"));
Click to expand...
Click to expand...
 
Create an unbound form with a couple text boxes for the user to enter dates:

Name: txtStart
Format: Short Date

Name: txtEnd
Format: Short Date

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click

Dim stDocName As String
'===== added code ===========
Dim strWhere As String 'added 10 lines of code
strWhere = "1=1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [datefield]>=#" & _
Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [datefield]<=#" & _
Me.txtEnd & "# "
End If
'===== end added code ===========
stDocName = "rptLinks"
DoCmd.OpenReport stDocName, acPreview, , strWhere 'added strWhere

Exit_cmdOpenReport_Click:
Exit Sub

Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click

End Sub


Use the command button wizard to create a button that opens your report.
Then modify the code so it looks something like:

Dim st
--
Duane Hookom
Microsoft Access MVP


SwEdIsH_OfFiCe_UsEr said:
It still doesn't work. Those fields are only filled in if I search for a
specified value. The input parameters are StartDate, EndDate, Param1 and
Param2.

What are references controls on forms and how do I use them? Does that mean
I form in which I enter the values in different fields and then press search,
like an advanced search say Google's? That's really what I'm looking for. How
do I use them?

Duane Hookom said:
It's difficult to determine which records aren't returned unless you tell us
what parameters are entered and what isn't showing in the report. IMHO, don't
use parameter prompts. References to controls on forms are much better.
Try:

PARAMETERS [startDate] DateTime, [endDate] DateTime, [param1] Text ( 255 ),
[param2] Text ( 255 );
SELECT table1.field1, table1.dateField, table1.field2, table1.field3,
table1.field4
FROM table1.
WHERE dateField Between Nz([startDate],#1/1/1900#) And
Nz([endDate],#1/1/2100#) AND
table1.field1 & "" Like "*" & [param1] & "*" AND
table1.field2 & "" Like "*" & [param2] & "*";

--
Duane Hookom
Microsoft Access MVP


SwEdIsH_OfFiCe_UsEr said:
I have selected a query for my report, and the query asks for the parameters
before presenting data. The query itself works fine and returns all values I
want, but when I run it through the report and want it to present it in a
printable way, some of the data is missing. When I leave some of the
parameters blank (which makes it search for all values), it doesn't add that
information in the report, except for date which is always shown. But if I
search with that parameter, it is shown. This has really gotten me confused,
and I don't know why it is doing this. What is wrong?

Query SQL:

PARAMETERS [startDate] DateTime, [endDate] DateTime, [param1] Text ( 255 ),
[param2] Text ( 255 );
SELECT table1.field1, table1.dateField, table1.field2, table1.field3,
table1.field4
FROM table1.
WHERE (((table1.dateField) Between Nz([startDate],#1/1/1900#) And
Nz([endDate],#1/1/2100#)) AND ((table1.field1) Is Null Or (table1.field1)
Like "*" & [param1] & "*") AND ((table1.field2) Is Null Or (table1.field2)
Like "*" & [param2] & "*"));
Click to expand...
Click to expand...
Click to expand...
 
Splendid, it works! I have a few other fields I need to make searchable, but
I can't understand the code really. I need to make it return posts with a
certain value in a particular field. How do I add such strings of my own, how
does the code work?



Duane Hookom said:
Create an unbound form with a couple text boxes for the user to enter dates:

Name: txtStart
Format: Short Date

Name: txtEnd
Format: Short Date

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click

Dim stDocName As String
'===== added code ===========
Dim strWhere As String 'added 10 lines of code
strWhere = "1=1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [datefield]>=#" & _
Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [datefield]<=#" & _
Me.txtEnd & "# "
End If
'===== end added code ===========
stDocName = "rptLinks"
DoCmd.OpenReport stDocName, acPreview, , strWhere 'added strWhere

Exit_cmdOpenReport_Click:
Exit Sub

Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click

End Sub


Use the command button wizard to create a button that opens your report.
Then modify the code so it looks something like:

Dim st
--
Duane Hookom
Microsoft Access MVP


SwEdIsH_OfFiCe_UsEr said:
It still doesn't work. Those fields are only filled in if I search for a
specified value. The input parameters are StartDate, EndDate, Param1 and
Param2.

What are references controls on forms and how do I use them? Does that mean
I form in which I enter the values in different fields and then press search,
like an advanced search say Google's? That's really what I'm looking for. How
do I use them?

Duane Hookom said:
It's difficult to determine which records aren't returned unless you tell us
what parameters are entered and what isn't showing in the report. IMHO, don't
use parameter prompts. References to controls on forms are much better.
Try:

PARAMETERS [startDate] DateTime, [endDate] DateTime, [param1] Text ( 255 ),
[param2] Text ( 255 );
SELECT table1.field1, table1.dateField, table1.field2, table1.field3,
table1.field4
FROM table1.
WHERE dateField Between Nz([startDate],#1/1/1900#) And
Nz([endDate],#1/1/2100#) AND
table1.field1 & "" Like "*" & [param1] & "*" AND
table1.field2 & "" Like "*" & [param2] & "*";

--
Duane Hookom
Microsoft Access MVP


:

I have selected a query for my report, and the query asks for the parameters
before presenting data. The query itself works fine and returns all values I
want, but when I run it through the report and want it to present it in a
printable way, some of the data is missing. When I leave some of the
parameters blank (which makes it search for all values), it doesn't add that
information in the report, except for date which is always shown. But if I
search with that parameter, it is shown. This has really gotten me confused,
and I don't know why it is doing this. What is wrong?

Query SQL:

PARAMETERS [startDate] DateTime, [endDate] DateTime, [param1] Text ( 255 ),
[param2] Text ( 255 );
SELECT table1.field1, table1.dateField, table1.field2, table1.field3,
table1.field4
FROM table1.
WHERE (((table1.dateField) Between Nz([startDate],#1/1/1900#) And
Nz([endDate],#1/1/2100#)) AND ((table1.field1) Is Null Or (table1.field1)
Like "*" & [param1] & "*") AND ((table1.field2) Is Null Or (table1.field2)
Like "*" & [param2] & "*"));
Click to expand...
Click to expand...
Click to expand...
 
The code is building a where clause like you would see in the SQL view of a
query (without the word "WHERE"). The If IsNull(Me....) Then checks to see if
the user has entered anything into the control and will ignore the control if
null.

--
Duane Hookom
Microsoft Access MVP


SwEdIsH_OfFiCe_UsEr said:
Splendid, it works! I have a few other fields I need to make searchable, but
I can't understand the code really. I need to make it return posts with a
certain value in a particular field. How do I add such strings of my own, how
does the code work?



Duane Hookom said:
Create an unbound form with a couple text boxes for the user to enter dates:

Name: txtStart
Format: Short Date

Name: txtEnd
Format: Short Date

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click

Dim stDocName As String
'===== added code ===========
Dim strWhere As String 'added 10 lines of code
strWhere = "1=1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [datefield]>=#" & _
Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [datefield]<=#" & _
Me.txtEnd & "# "
End If
'===== end added code ===========
stDocName = "rptLinks"
DoCmd.OpenReport stDocName, acPreview, , strWhere 'added strWhere

Exit_cmdOpenReport_Click:
Exit Sub

Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click

End Sub


Use the command button wizard to create a button that opens your report.
Then modify the code so it looks something like:

Dim st
--
Duane Hookom
Microsoft Access MVP


SwEdIsH_OfFiCe_UsEr said:
It still doesn't work. Those fields are only filled in if I search for a
specified value. The input parameters are StartDate, EndDate, Param1 and
Param2.

What are references controls on forms and how do I use them? Does that mean
I form in which I enter the values in different fields and then press search,
like an advanced search say Google's? That's really what I'm looking for. How
do I use them?

:

It's difficult to determine which records aren't returned unless you tell us
what parameters are entered and what isn't showing in the report. IMHO, don't
use parameter prompts. References to controls on forms are much better.
Try:

PARAMETERS [startDate] DateTime, [endDate] DateTime, [param1] Text ( 255 ),
[param2] Text ( 255 );
SELECT table1.field1, table1.dateField, table1.field2, table1.field3,
table1.field4
FROM table1.
WHERE dateField Between Nz([startDate],#1/1/1900#) And
Nz([endDate],#1/1/2100#) AND
table1.field1 & "" Like "*" & [param1] & "*" AND
table1.field2 & "" Like "*" & [param2] & "*";

--
Duane Hookom
Microsoft Access MVP


:

I have selected a query for my report, and the query asks for the parameters
before presenting data. The query itself works fine and returns all values I
want, but when I run it through the report and want it to present it in a
printable way, some of the data is missing. When I leave some of the
parameters blank (which makes it search for all values), it doesn't add that
information in the report, except for date which is always shown. But if I
search with that parameter, it is shown. This has really gotten me confused,
and I don't know why it is doing this. What is wrong?

Query SQL:

PARAMETERS [startDate] DateTime, [endDate] DateTime, [param1] Text ( 255 ),
[param2] Text ( 255 );
SELECT table1.field1, table1.dateField, table1.field2, table1.field3,
table1.field4
FROM table1.
WHERE (((table1.dateField) Between Nz([startDate],#1/1/1900#) And
Nz([endDate],#1/1/2100#)) AND ((table1.field1) Is Null Or (table1.field1)
Like "*" & [param1] & "*") AND ((table1.field2) Is Null Or (table1.field2)
Like "*" & [param2] & "*"));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top