Option Button Values and Date Creator Problems on a Report.

[alive84]

Hi there,

I have a two problems concerning option button values on a report and data report creator reports.

The situation:

I have three option value boxes two have 3 option and one has only two option buttons. They have values 1, 2, 3. Everything is standard. Now when I create the report, I have only the number for each record on the report, which is kind of ugly and not user-friendly. Is there a chance to turn this values into actual text?

For example. optButton with value 1 should receive Internal.

I have tried to overcome the problem with an iif, doesn't do anything.

Another problem that I have. I have build an "search date creator report". Basically, that tool creates a report when the user types in Start Date and End Date. However, I would like that this report creator also shows Date from to (empty).

Here's the code:

 
Private Sub cmdReport_Click()
    On Error GoTo Err_cmdReport_Click
 
    Dim stDocName As String
 
    stDocName = "rptDateParameterReport"
 
'Check values are entered into Date From and Date To text boxes
'if so run report or cancel request
 
    If Len(Me.txtdatefrom & vbNullString) = 0 Or Len(Me.txtDateTo & vbNullString) = 0 Then
        MsgBox "Please ensure that a report date range is entered into the form", _
               vbInformation, "Required Data..."
        Exit Sub
    Else
        DoCmd.OpenReport stDocName, acPreview
    End If
Exit_cmdReport_Click:
    Exit Sub
 
Err_cmdReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdReport_Click
 
End Sub
 

I do not see where the vba is actually doing something with the values typed in, except persuing a test if something is in the boxes. Is it even possible to search empty values?

Thanks for the comments and hints.

[FishVal]

Hi there,

I have a two problems concerning option button values on a report and data report creator reports.

The situation:

I have three option value boxes two have 3 option and one has only two option buttons. They have values 1, 2, 3. Everything is standard. Now when I create the report, I have only the number for each record on the report, which is kind of ugly and not user-friendly. Is there a chance to turn this values into actual text?

For example. optButton with value 1 should receive Internal.

I have tried to overcome the problem with an iif, doesn't do anything.

Another problem that I have. I have build an "search date creator report". Basically, that tool creates a report when the user types in Start Date and End Date. However, I would like that this report creator also shows Date from to (empty).

Here's the code:

 
Private Sub cmdReport_Click()
    On Error GoTo Err_cmdReport_Click
 
    Dim stDocName As String
 
    stDocName = "rptDateParameterReport"
 
'Check values are entered into Date From and Date To text boxes
'if so run report or cancel request
 
    If Len(Me.txtdatefrom & vbNullString) = 0 Or Len(Me.txtDateTo & vbNullString) = 0 Then
        MsgBox "Please ensure that a report date range is entered into the form", _
               vbInformation, "Required Data..."
        Exit Sub
    Else
        DoCmd.OpenReport stDocName, acPreview
    End If
Exit_cmdReport_Click:
    Exit Sub
 
Err_cmdReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdReport_Click
 
End Sub
 

I do not see where the vba is actually doing something with the values typed in, except persuing a test if something is in the boxes. Is it even possible to search empty values?

Thanks for the comments and hints.

Hi, Sacha.

I guess the OptionGroup control is bound to some field in a table where it stores numerical values. You need to build a table

tblOptions
keyOptionID Long, PK (OptionGroup.Va lue)
txtOption Text (Text to replace OptionGroupValu e)

fill the table with an appropriate Number-Text couples
build a query based on rptDateParamete rReport.RecordS ource table/query to tblOptions join and set the query to rptDateParamete rReport.RecordS ource.

Now you can set ControlSource of a correspondent control in rptDateParamete rReport to tblOptions.txtO ption

Good luck.

[alive84]

Hi, Sacha.

I guess the OptionGroup control is bound to some field in a table where it stores numerical values. You need to build a table

tblOptions
keyOptionID Long, PK (OptionGroup.Va lue)
txtOption Text (Text to replace OptionGroupValu e)

fill the table with an appropriate Number-Text couples
build a query based on rptDateParamete rReport.RecordS ource table/query to tblOptions join and set the query to rptDateParamete rReport.RecordS ource.

Now you can set ControlSource of a correspondent control in rptDateParamete rReport to tblOptions.txtO ption

Good luck.

Once again, thanks to your help, I solved the first problem.

do you know, how I could change this WHERE part of my SQL-Query, so that it is optional to use "Date to"?

(((tblWork.StartDate)>=forms!frmReportDate!txtDateFrom) And ((tblWork.EndDate)<=forms!frmReportDate!txtDateTo));
 

thanks,

[FishVal]

Once again, thanks to your help, I solved the first problem.

do you know, how I could change this WHERE part of my SQL-Query, so that it is optional to use "Date to"?

(((tblWork.StartDate)>=forms!frmReportDate!txtDateFrom) And ((tblWork.EndDate)<=forms!frmReportDate!txtDateTo));
 

thanks,

(((tblWork.StartDate)>=forms!frmReportDate!txtDateFrom) And ((tblWork.EndDate)<=forms!frmReportDate!txtDateTo) OR IsNull(forms!frmReportDate!txtDateTo));
 

Did you mean this?

[alive84]

(((tblWork.StartDate)>=forms!frmReportDate!txtDateFrom) And ((tblWork.EndDate)<=forms!frmReportDate!txtDateTo) OR IsNull(forms!frmReportDate!txtDateTo));
 

Did you mean this?

Unfortunately not, because like that it shows me all records in the table. thanks

[FishVal]

Unfortunately not, because like that it shows me all records in the table. thanks

It shows all records from DateFrom if DateTo was not entered.
If this is not what you've wanted, then please clarify what you've meant saying

how I could change this WHERE part of my SQL-Query, so that it is optional to use "Date to"?

[alive84]

It shows all records from DateFrom if DateTo was not entered.
If this is not what you've wanted, then please clarify what you've meant saying

Sorry, I mean that I could search all DateFrom without setting a DateTo, but it should also be possible to set a DateTo (if wanted). Both choices should be possible.

For example: I set DateFrom as 01.01.2007 Empty DateTo, and it shows me now all records where DateFrom is >01.01.2007 with an empty DateTo.
But it should also be possible when I give this input: DateFrom 01.01.2007 and DateTo 01.09.2007

[FishVal]

Sorry, I mean that I could search all DateFrom without setting a DateTo, but it should also be possible to set a DateTo (if wanted). Both choices should be possible.

For example: I set DateFrom as 01.01.2007 Empty DateTo, and it shows me now all records where DateFrom is >01.01.2007 with an empty DateTo.
But it should also be possible when I give this input: DateFrom 01.01.2007 and DateTo 01.09.2007

So what is wrong with the criteria I've previously posted.
Here I've removed unnecessary brackets.

tblWork.StartDate>=forms!frmReportDate!txtDateFrom And (tblWork.EndDate<=forms!frmReportDate!txtDateTo OR IsNull(forms!frmReportDate!txtDateTo));

[alive84]

So what is wrong with the criteria I've previously posted.
Here I've removed unnecessary brackets.

tblWork.StartDate>=forms!frmReportDate!txtDateFrom And (tblWork.EndDate<=forms!frmReportDate!txtDateTo OR IsNull(forms!frmReportDate!txtDateTo));

the problem is that it shows me even Dates before 01.01.2007 when I am not entering anything in DateTo. However, when I enter a DateTo it shows me all records, where there is a DateTo in the Table. It would be neat, if it also shows me all records with DateFrom 01.01.2007 with an empty DateTo.

I have played around with the WHERE statement you gave me...but no solution yet.

thanks

[FishVal]

the problem is that it shows me even Dates before 01.01.2007 when I am not entering anything in DateTo. However, when I enter an DateTo it shows me all records, where there is a DateTo in the Table. It would be neat, if it also shows me all records with DateFrom 01.01.2007 with an empty DateTo.

I have played around with the WHERE statement you gave me...but no solution yet.

thanks

Post the whole SQL expression.