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.Value)
txtOption Text (Text to replace OptionGroupValue)

fill the table with an appropriate Number-Text couples
build a query based on rptDateParameterReport.RecordSource table/query to tblOptions join and set the query to rptDateParameterReport.RecordSource.

Now you can set ControlSource of a correspondent control in rptDateParameterReport to tblOptions.txtOption

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.Value)
txtOption Text (Text to replace OptionGroupValue)

fill the table with an appropriate Number-Text couples
build a query based on rptDateParameterReport.RecordSource table/query to tblOptions join and set the query to rptDateParameterReport.RecordSource.

Now you can set ControlSource of a correspondent control in rptDateParameterReport to tblOptions.txtOption

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.

[alive84]

Post the whole SQL expression.

 
SELECT tblContacts.FirstName, tblContacts.LastName, tblCostCenter.CostCenter, tblCostCenter.OrgUnit, tblCostCenter.Region, tblWorkstream.Workstream, tblWorkstream.[Cap/Non-Cap], tblWork.EndDate, tblWork.StartDate, tblWork.[Int/Ext], tblWork.FTE, tblWork.[Fix/T&M], tblWork.[TSU/NLA], tblVendor.Vendor, tblContacts.ContactID, tblOptionValueIntExt.Text, tblOptionValueNLA.Text, tblOptionValueFix.Text
FROM tblContacts, tblWorkstream, tblWork, tblCostCenter, tblVendor, tblOptionValueIntExt, tblOptionValueNLA, tblOptionValueFix
WHERE (tblContacts.ContactID=tblWork.ContactID) And (tblCostCenter.CostCenterID=tblWork.CostCenterID) And (tblWorkstream.WorkstreamID=tblWork.WorkstreamID) And (tblVendor.VendorID=tblWork.VendorID) And (tblOptionValueIntExt.OptionValueIntExtID=tblWork.[Int/Ext]) And (tblOptionValueNLA.OptionValueNLAID=tblWork.[TSU/NLA]) And (tblOptionValueFix.OptionValueFIXID=tblWork.[Fix/T&M]) And ((tblWork.StartDate)>=forms!frmReportDate!txtDateFrom And (tblWork.EndDate)<=forms!frmReportDate!txtDateTo OR IsNull(forms!frmReportDate!txtDateTo));
 
 

[FishVal]

 
SELECT tblContacts.FirstName, tblContacts.LastName, tblCostCenter.CostCenter, tblCostCenter.OrgUnit, tblCostCenter.Region, tblWorkstream.Workstream, tblWorkstream.[Cap/Non-Cap], tblWork.EndDate, tblWork.StartDate, tblWork.[Int/Ext], tblWork.FTE, tblWork.[Fix/T&M], tblWork.[TSU/NLA], tblVendor.Vendor, tblContacts.ContactID, tblOptionValueIntExt.Text, tblOptionValueNLA.Text, tblOptionValueFix.Text
FROM tblContacts, tblWorkstream, tblWork, tblCostCenter, tblVendor, tblOptionValueIntExt, tblOptionValueNLA, tblOptionValueFix
WHERE (tblContacts.ContactID=tblWork.ContactID) And (tblCostCenter.CostCenterID=tblWork.CostCenterID) And (tblWorkstream.WorkstreamID=tblWork.WorkstreamID) And (tblVendor.VendorID=tblWork.VendorID) And (tblOptionValueIntExt.OptionValueIntExtID=tblWork.[Int/Ext]) And (tblOptionValueNLA.OptionValueNLAID=tblWork.[TSU/NLA]) And (tblOptionValueFix.OptionValueFIXID=tblWork.[Fix/T&M]) And ((tblWork.StartDate)>=forms!frmReportDate!txtDateFrom And (tblWork.EndDate)<=forms!frmReportDate!txtDateTo OR IsNull(forms!frmReportDate!txtDateTo));
 
 

Exactly what I have thought. Pay attention to criteria logic. It should evaluates to True if (tblWork.StartDate)>=forms!frmReportDate!txtDateFr om evaluates to True, and (tblWork.EndDate)<=forms!frmReportDate!txtDateTo OR IsNull(forms!frmReportDate!txtDateTo) evaluates to true. Since AND has higher preference than OR, the latter should be enclosed in brackets. Hope you see them in bold and underlined.

SELECT tblContacts.FirstName, tblContacts.LastName, tblCostCenter.CostCenter, tblCostCenter.OrgUnit, tblCostCenter.Region, tblWorkstream.Workstream, tblWorkstream.[Cap/Non-Cap], tblWork.EndDate, tblWork.StartDate, tblWork.[Int/Ext], tblWork.FTE, tblWork.[Fix/T&M], tblWork.[TSU/NLA], tblVendor.Vendor, tblContacts.ContactID, tblOptionValueIntExt.Text, tblOptionValueNLA.Text, tblOptionValueFix.Text
FROM tblContacts, tblWorkstream, tblWork, tblCostCenter, tblVendor, tblOptionValueIntExt, tblOptionValueNLA, tblOptionValueFix
WHERE (tblContacts.ContactID=tblWork.ContactID) And (tblCostCenter.CostCenterID=tblWork.CostCenterID) And (tblWorkstream.WorkstreamID=tblWork.WorkstreamID) And (tblVendor.VendorID=tblWork.VendorID) And (tblOptionValueIntExt.OptionValueIntExtID=tblWork.[Int/Ext]) And (tblOptionValueNLA.OptionValueNLAID=tblWork.[TSU/NLA]) And (tblOptionValueFix.OptionValueFIXID=tblWork.[Fix/T&M]) And ((tblWork.StartDate)>=forms!frmReportDate!txtDateFrom And ((tblWork.EndDate)<=forms!frmReportDate!txtDateTo OR IsNull(forms!frmReportDate!txtDateTo)));
 

Compare these math expressions:
2*3+1
2*(3+1)

[alive84]

Exactly what I have thought. Pay attention to criteria logic. It should evaluates to True if (tblWork.StartDate)>=forms!frmReportDate!txtDateFr om evaluates to True, and (tblWork.EndDate)<=forms!frmReportDate!txtDateTo OR IsNull(forms!frmReportDate!txtDateTo) evaluates to true. Since AND has higher preference than OR, the latter should be enclosed in brackets. Hope you see them in bold and underlined.

SELECT tblContacts.FirstName, tblContacts.LastName, tblCostCenter.CostCenter, tblCostCenter.OrgUnit, tblCostCenter.Region, tblWorkstream.Workstream, tblWorkstream.[Cap/Non-Cap], tblWork.EndDate, tblWork.StartDate, tblWork.[Int/Ext], tblWork.FTE, tblWork.[Fix/T&M], tblWork.[TSU/NLA], tblVendor.Vendor, tblContacts.ContactID, tblOptionValueIntExt.Text, tblOptionValueNLA.Text, tblOptionValueFix.Text
FROM tblContacts, tblWorkstream, tblWork, tblCostCenter, tblVendor, tblOptionValueIntExt, tblOptionValueNLA, tblOptionValueFix
WHERE (tblContacts.ContactID=tblWork.ContactID) And (tblCostCenter.CostCenterID=tblWork.CostCenterID) And (tblWorkstream.WorkstreamID=tblWork.WorkstreamID) And (tblVendor.VendorID=tblWork.VendorID) And (tblOptionValueIntExt.OptionValueIntExtID=tblWork.[Int/Ext]) And (tblOptionValueNLA.OptionValueNLAID=tblWork.[TSU/NLA]) And (tblOptionValueFix.OptionValueFIXID=tblWork.[Fix/T&M]) And ((tblWork.StartDate)>=forms!frmReportDate!txtDateFrom And ((tblWork.EndDate)<=forms!frmReportDate!txtDateTo OR IsNull(forms!frmReportDate!txtDateTo)));
 

Compare these math expressions:
2*3+1
2*(3+1)

Oooo my god, I didn't know that SQL has this logic too, gosh...I am sorry, but that's now also something I will never ever forget. Thanks, its now working superb.

[FishVal]

Oooo my god, I didn't know that SQL has this logic too, gosh...I am sorry, but that's now also something I will never ever forget. Thanks, its now working superb.

The rules of Boolean algebra were postulated by George Boole in smnthng like 1848, much before SQL. :)
Boolean algebra (logic)
You are welcome.