How can I make my select statement include two statements

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have a form which has a control called txtmontha and a command button that
opens another form when the value of txtmontha is equal to a field
txtmonthlabel in a table tblmaintabs. I want to add another control as a
combo box on my form where the user can select a company name. so that the
value of the combo box (cmbselectcompany)equals a specific company
(txtcompany) in the table as well as the month. However if the combox is
blank ie the user doesn't select a company, I want all the records shown on
my destination form for the selected month.
How do I adapt this statement to include whether or not the user selects a
company?

Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [tblmaintabs]
WHERE [txtmonthlabel] = #" & Format(strtxtdate, "mmmm/yyyy") & "#;"

Thanks
Tony
 
Forms!frmMain!SubForm1.Form.RecordSource = _
"SELECT * FROM [tblmaintabs] " & _
"WHERE [txtmonthlabel] = #" & Format(strtxtdate, "mmmm/yyyy") & "# " & _
"AND (companyname = '" & Me!cmbselectcompany & "' " & _
"OR Me!cmbselectcompany IS NULL)"
 
Thanks Douglas. I get an error message that says "The expression you entered
refers to an object that is closed or doesn't exist" I've checked all the
field names and they appear to be corrct so what do you think the message may
refer to?
Thanks
Tony

Douglas J. Steele said:
Forms!frmMain!SubForm1.Form.RecordSource = _
"SELECT * FROM [tblmaintabs] " & _
"WHERE [txtmonthlabel] = #" & Format(strtxtdate, "mmmm/yyyy") & "# " & _
"AND (companyname = '" & Me!cmbselectcompany & "' " & _
"OR Me!cmbselectcompany IS NULL)"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Tony Williams said:
I have a form which has a control called txtmontha and a command button
that
opens another form when the value of txtmontha is equal to a field
txtmonthlabel in a table tblmaintabs. I want to add another control as a
combo box on my form where the user can select a company name. so that the
value of the combo box (cmbselectcompany)equals a specific company
(txtcompany) in the table as well as the month. However if the combox is
blank ie the user doesn't select a company, I want all the records shown
on
my destination form for the selected month.
How do I adapt this statement to include whether or not the user selects a
company?

Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [tblmaintabs]
WHERE [txtmonthlabel] = #" & Format(strtxtdate, "mmmm/yyyy") & "#;"

Thanks
Tony
Click to expand...


.
Click to expand...
 
I've now got somewhere there with this
Dim strtxtcompany As String
Dim strtxtdate As Date
strtxtdate = Me.txtmontha.Value
strtxtcompany = Me.cmbselectcompany.Value
Forms!frmMain!SubForm1.SourceObject = "subformFDA"
'Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [tblmaintabs]
WHERE [txtmonthlabel] = #" & Format(strtxtdate, "mmmm/yyyy") & "#;"
Forms!frmMain!SubForm1.Form.RecordSource = "SELECT * FROM [tblmaintabs]
WHERE [txtmonthlabel] = #" & Format(strtxtdate, "mmmm/yyyy") & "# AND
([txtcompany] = '" & strtxtcompany & "' OR '" & strtxtcompany & "' IS NULL);"

If I select a company and date it works fine. However if I don't select a
company I get an Invalid use of Null message. Can you help?
Thanks Douglas
Tony
 
Back
Top