Date problem creating empty parameters on report

[John1Chr]

I am having a problem with accept_date and record_date. I can send a copy of the database to show the problem. It used to be that these two AB table fields were in number format but I changed them to DATE/TIME. It reads off of the form but then creates Expr 1 and Expr 2 in the queries and thus leaves the fields blank on the report.

Below is the code that is on the form run button. The Accept Date and Record Date are the problems. The table is now Date/Time and I tried to fix the code in the Accept date but left the record date code alone. These fields worked when they were in the number format.




Private Sub cmdAbRpt_Click()
On Error GoTo Err_cmdAbRpt_Click

Dim varItm As Variant 'Item in the listbox
Dim strSQL As String 'SQL statement
Dim FedFY As String 'Federal Fiscal Year input string
Dim StFy As String 'State Fiscal Year input string
Dim Sponsor As String 'Sponsor input string
Dim Dt As String 'Accept Date input string
Dim AcptDt As String 'Report order input string
Dim RecDt As String 'Report order input string
Dim rptReport As String 'Report input string
Dim qdf As QueryDef 'AB Data query

'set value for query variable
Set qdf = CurrentDb.QueryDefs("qryCreateQuery")

'Error check to see verifing a Federal Fiscal Year and/ or
'A State Fiscal Year was selected
If lstFedFY.ItemsSelected.Count = 0 And lstStFY.ItemsSelected.Count = 0 Then
MsgBox ("Select a Federal Fiscal Year and/or a state Fiscal Year")
GoTo Exit_cmdAbRpt_Click
End If

'This will make an SQL statement for each Federal Fiscal
'Year that is selected from the lstFedFY list box.
For Each varItm In lstFedFY.ItemsSelected
If (FedFY = "") Then
FedFY = "(AB.Federal_FY) = "
FedFY = FedFY & lstFedFY.ItemData(varItm)
Else
FedFY = FedFY & " Or "
FedFY = FedFY & "(AB.Federal_FY) = "
FedFY = FedFY & lstFedFY.ItemData(varItm)
End If
Next varItm
If Not (FedFY = "") Then
FedFY = "(" & FedFY & ")"
End If

'This will make an SQL statement for each State Fiscal
'Year that is selected from the lstStFy list box.
For Each varItm In lstStFY.ItemsSelected
If (StFy = "") Then
StFy = "(AB.State_FY) = """
StFy = StFy & lstStFY.ItemData(varItm) & """"
Else
StFy = StFy & " Or "
StFy = StFy & "(AB.State_FY) = """
StFy = StFy & lstStFY.ItemData(varItm) & """"
End If
Next varItm

'Test that either a Federal or State Fiscal Year is chosen
If lstFedFY.ItemsSelected.Count = 0 Then
StFy = "(" & StFy & ")"
ElseIf lstStFY.ItemsSelected.Count = 0 Then
StFy = ""
ElseIf lstFedFY.ItemsSelected.Count > 0 Then
StFy = " AND (" & StFy & ")"
End If

'This will make an SQL statement for each Sponsor
'That is selected from the lstSponsor list box.
For Each varItm In lstSpn.ItemsSelected
If (Sponsor = "") Then
Sponsor = "(AB.Sponsor) = """
Sponsor = Sponsor & lstSpn.ItemData(varItm) & """"
Else
Sponsor = Sponsor & " Or "
Sponsor = Sponsor & "(AB.Sponsor) = """
Sponsor = Sponsor & lstSpn.ItemData(varItm) & """"
End If
Next varItm
If Not (Sponsor = "") Then
Sponsor = " AND (" & Sponsor & ")"
End If

'This will make an SQL statement for the Accecpt Date range
If (IsNull(txtAcptDtL) And IsNull(txtAcptDtG)) Then
AcptDt = ""
ElseIf Not ((IsNull(txtAcptDtL)) Or IsNull(txtAcptDtG)) Then
AcptDt = "(AB.Accept_Date) < #" & txtAcptDtL & "#"
AcptDt = AcptDt & " AND "
AcptDt = AcptDt & "(AB.Accept_Date) > #" & txtAcptDtG & "#"
ElseIf Not (IsNull(txtAcptDtL)) Then
AcptDt = "(AB.Accept_Date) < #" & txtAcptDtL & "#"
ElseIf Not (IsNull(txtAcptDtG)) Then
AcptDt = "(AB.Accept_Date) > #" & txtAcptDtG & "#"
End If
If Not (AcptDt = "") Then
AcptDt = " AND (" & AcptDt & ")"
End If

'This will make an SQL statement for the Record Date range
If (IsNull(txtRecDtL) And IsNull(txtRecDtG)) Then
RecDt = ""
ElseIf Not ((IsNull(txtRecDtL)) Or IsNull(txtRecDtG)) Then
RecDt = "(AB.Record_Date) < " & txtRecDtL
RecDt = RecDt & " AND "
RecDt = RecDt & "(AB.Record_Date) > " & txtRecDtG
ElseIf (IsNull(txtRecDtL)) Then
RecDt = "(AB.Record_Date) > " & txtRecDtG
ElseIf (IsNull(txtRecDtG)) Then
RecDt = "(AB.Record_Date) < " & txtRecDtL
End If
If Not (RecDt = "") Then
RecDt = " AND (" & RecDt & ")"
End If

'Creates an SQL statement that builds the report the user wanted
'by putting together all the selected criteria.
strSQL = "SELECT DISTINCT AB.Trans_Number, AB.Record_Date, AB.Accept_Date, "
strSQL = strSQL & "AB.Tran_Code, AB.Sponsor, AB.Tran_Agency, AB.Federal_FY, "
strSQL = strSQL & "AB.Increase_Decrease_Ind, AB.Line_Description, "
strSQL = strSQL & "AB.State_FY, Sum(AB.Dollar_Amount) AS Sum_Dollar_Amount "
strSQL = strSQL & "FROM AB WHERE (" & FedFY & StFy & Sponsor & AcptDt & RecDt & ")"
strSQL = strSQL & "GROUP BY AB.Trans_Number, AB.Record_Date, AB.Accept_Date, "
strSQL = strSQL & "AB.Tran_Code, AB.Sponsor, AB.Tran_Agency, AB.Federal_FY, "
strSQL = strSQL & "AB.Increase_Decrease_Ind, AB.Line_Description, "
strSQL = strSQL & "AB.State_FY ORDER BY AB.State_FY; "

'Assign the SQL statement to the Create_Query query
qdf.SQL = strSQL

'Executes the query and then closes it
'DoCmd.OpenQuery qdf.Name, acViewDesign
'DoCmd.Close acQuery, qdf.Name, acSaveYes

'Opens the report and populates it with data from the query
rptReport = "rptCreateAB"
DoCmd.OpenReport rptReport, acViewPreview

Exit_cmdAbRpt_Click:
Exit Sub

Err_cmdAbRpt_Click:
MsgBox Err.Description
Resume Exit_cmdAbRpt_Click

End Sub

 

[TheAceMan1]

How are ya BrGenCAJ . . .

[blue]It reads off of the form but then [purple]creates Expr 1 and Expr 2[/purple] in the queries and thus leaves the fields blank on the report.[/blue]

You have to [blue]reinherit the fields[/blue] with the new data type:
[blue]In query design view delete Expr1 & Expr2 and drag the fields from the table back onto the grid.[/blue] Thats it!

[purple]Be aware . . . this change in data type will cause the display of the fields to take on dates matching the numeric values! You'll have some editing to do here . . .[/purple]

See Ya! . . . . . .

 

[John1Chr]

Aceman,

Thanks for looking at it. I figured it out today. The reason that I kept getting the Expr 1 & Expr 2 was that I had the table fields as Accept _Date and Record _Date. I can't believe that it was that simple yet I spent a couple of hours and couldn't see it. A simple space in both of those fields messed me up. I don't think that anyone could see it unless I sent the whole database.

Thanks, again.