If…Then…Else, Yes/No Procedure (A97SR2)

Topic

New Reply

I would like to evaluate a yes/no field on one record with an If…Then…Else Statement. Could someone help me with the procedures and variables for this. The yes/no field is named [fldTax]. The yes/no has been previously selected by the user and is called from a query named [qryTaxable]. If the field has been selected (yes), then I want to run -> DoCmd.OpenQuery “qryRunTaxedReport”. If it has not been selected, then I want to run -> DoCmd.OpenQuery”qryRunNoTaxReport”. Thanks for the help.

Reply | Quote

Replies

When you say the yes/no field is called from a query (qryTaxable), what do you mean by this? Is there only one record returned by this query? How do you access the record with the yes/no in it?
If only one record is returned by qryTaxable then the following should work in VBA code:
Set rs = dbs.OpenRecordset(“qryTaxable”)
If (rs.yes/no fieldname) Then
DoCmd.OpenQuery “qryRunTaxedReport”
Else
DoCmd.OpenQuery”qryRunNoTaxReport”
End If

HTH
Pat

Reply | Quote

I tried your VBA code and it came back with – compile error – variable not defined. A further explanation of what I am doing is this: From a one field form the user can enter many different workorder numbers – – when a command button is pressed the workorder numbers are processed one at a time and printed with a Do…Loop. Within this Do…Loop, I want to evaluate whether the workorder is taxable or not taxable (this taxable yes/no field is within each workorder number record has been pre selected for each workorder number). With one query, it will print the workorder with tax and the other query will print it without tax. The Do…Loop works just fine. The method that is used for processing one at a time is: AppendQuery, SELECT TOP1 to a table and then process it – when done, delete that record. I have much difficulty understanding Dim, Set and variables. I have been looking at many error messages; variable not defined, object required, keyword not found, etc., etc. The help information is leaving me confused.

Reply | Quote

For a start you said you wanted to run queries, I now know you mean reports. These queries are the record source of the reports you want to run, right?

Why don’t you put the Yes/No field (let’s call it TaxYesNo) on the form, but NOT enabled and LOCKED so users cannot change it but the VBA code can use it.

Tell me, are there 2 different reports defined or is there 1 report and you just use the different queries as the record source of the report?

If there are 2 different reports, then the following code should be inserted into your DO loop:
If TaxYesNo Then
DoCmd.OpenReport “TaxReportName”,,strCriteria
Else
DoCmd.OpenReport “NoTaxReportName”,,strCriteria
End If

Be sure to substitute your report name in the above code and your fieldname for the YesNo tax field.
Pat

Reply | Quote

Actually the two queries, “qryUpdateSummaryTable” & “qryUpdateSummaryTableNoTax” are update queries that send all the different dollar totals for the one workorder to a summary table. This summary table is then used through a query to provide information to one of the 4 subreports of the main report (& not a part of the event procedure). The following is the event procedure with comments.

Private Sub Command3_Click()

Dim Db As Database
Dim rs As Recordset
Set Db = CurrentDb
Set rs = Db.OpenRecordset(“tblBatchWO”)
‘the form, “frmPrintWO” is used to enter the workorder numbers
‘in the “fldWONUM” of the “tblBatchWO”

Do
If rs.RecordCount = 0 Then
Exit Do
Else
DoCmd.SetWarnings False
‘take the first wo number and append it
‘to the tblWOnumber table
DoCmd.OpenQuery “qryApnd1toWOnum”

‘evaluate taxable or not
DoCmd.OpenQuery “qryPrintOneWO”
‘the “qryPrintOneWO” contains the yes/no field, “fldNotTaxable”
‘the next If…Then…Else should update the Summary table

If “fldNotTaxable” Then ‘this line does not work. It comes
‘back with a run time error 13 – type mismatch. This is the
‘place where I am totally lost. The two following queries work
‘just fine if I process them manually
DoCmd.OpenQuery “qryUpdateSummaryTable”
DoCmd.Requery
Else
DoCmd.OpenQuery “qryUpdateSummaryTableNoTax”
DoCmd.Requery
End If

‘now print a workorder
DoCmd.OpenReport “rptPrintOneWO”, acViewNormal
‘now delete the w.o. number just printed
‘and the corresponding w.o. number in Batch
DoCmd.OpenQuery “qryDel1fromBatchAndWO”
‘now requery the subform
DoCmd.Requery “frmPrintWOsubform”
End If
Loop While rs.RecordCount > 0

Set Db = Nothing
Set rs = Nothing

End Sub

Reply | Quote

Hi Ron,
What does qryPrintOneWO do?
Can you post it here? This will determine what the following line should look like:
<>

Pat

Reply | Quote

The “qryPrintOneWO” ties together the workorder number with its basic information – registration # – customer number – date started – date finished – labor rate – work description – and if the workorder is taxable. Then via relationship, the registration # gets the appropriate details from the aircraft table – make – model – serial number. Also it is related to the customer table with the customer number to get the company name – address – notes – etc. It is the source for the report that is generated. I was hoping that I could also use it to feed the information to the summary table which is the first subreport on the main report.

Reply | Quote

Is “fldNotTaxable” a control on your form that you are running this from?
If so, then take away the quotes as Hans has suggested and put Me! prior to it, eg. Me!fldNotTaxable .
If not, then where does it come from and what is it’s name.
Pat

Reply | Quote

fldNotTaxable is not a control on the form. I am going to try to simplify this whole thing and disregard all previous discussion. As an example, I have just run a query “qryA” that returns one record with two fields: [fldWOnum], the workorder number; and [fldNotTaxable], a yes/no field. What I want to do is: If the [fldNotTaxable] is “yes”, I want to run another query, “qryNoTax”. If the [fldNotTaxable] is “no”, I want to run a query, “qryWithTax”. I am trying to accomplish this with an If…Then…Else within an event procedure. Might there be an easier way to accomplish this?

Reply | Quote

What you could do is:
1. Put an extra column in both queries being the fldNotTaxable field from the query qryA.
For the Criteria of field FldNotTaxable in query qryNoTax you would set it to True.
For the Criteria of field FldNotTaxable in query qryWithTax you would set it to False.
In this way all you have to run in the Event PRocedure is both queries, without any tests.
or
2. You could read query qryA in a recordset and make the decision from that, a la:
Set rs = dbs.OpenRecordSet(“qryA”)
If rs.fldNotTaxable Then
DoCmd.OpenQuery “qryNoTax”
Else
DoCmd.OpenQuery “qryWithTax”
End If

HTH
Pat

Reply | Quote

The two query program in the event procedure worked PERFECTLY!!! Tested it over and over again. I am one happy camper and will put the Excedrin away. Thanks again! Will probably return with my next dilemma.

Reply | Quote

You write[indent]

---

If “fldNotTaxable” Then ‘this line does not work.

---

[/indent]You should use the actual field name without quotes; if the actual field name contains spaces, it should be enclosed in square brackets [ and ]. So if your field is literally named fldNotTaxable, you should use

If fldNotTaxable Then

If the field is named Not Taxable, you should use

If [Not Taxable] Then

Reply | Quote