Select Records based on dates or no dates or both

[usatraveler]

Trying to setup a query that I can ask the user from a form if they want to see only the records with "purchase dates" or "no purchase dates" or "show both". I have been able to put in a hard coded "Is Null" or "Is Not Null" which works for the first two choices but I have not been able to figure out a way to let the user decide from the form. I have played around with iif statements with no luck.



Is there a good place for beginner to get training on Querys

Thanks

[Bulzie]

One way. Create a combo box with the 3 options. then depending on what they select, update the RecordSource on your Form and Requery.

If me.cboChoice = "Purchase Dates" then

Forms!frmMain.Recordsource = "Select * From myTable WHERE (PurchaseDate Is Not Null)"

Else

If me.cboChoice = "No Purchase Dates" then

Forms!frmMain.Recordsource = "Select * From myTable WHERE (PurchaseDate Is Null)"

Else

Forms!frmMain.Recordsource = "Select * From myTable"

End If

End If

Forms!frmMain.Requery

[usatraveler]

I appreciate your help and will need some more guidance if you have the time. Very new to Databases and Querys and want to learn. Here are my names for the elements and want to make certain that I am piecing this together correctly. First question is the location of the code. Would that go in the Criteria in the Query for the OrderDate? If you need more information please let me know. Thanks.

- ComboBox with the three choices = CboShowOrders
- The three choices in the ComboBox are "All Records";"Orders Only";"No Orders"
- Table name = ProposalTable
- Field name for the date = OrderDate
- Form name = ProposalDates

If me.CboShowOrders = "Orders Only" then
Forms!ProposalDates.OrderDate = "Select * From ProposalTable WHERE (OrderDate Is Not Null)"
Else
If me.CBoShowOrders = "No Orders" Then
Forms!ProposalDates.OrderDate = "Select * From ProposalTable WHERE (OrderDate Is Null)"
Else
Forms!ProposalDates.OrderDate = "Select * From ProposalTable"
End If
End If

Forms!ProposalDates.Requery

[Bulzie]

1. The RecordSource on your form will be ProposalTable and on the form detail section you put the fields. Go to Design of Form and Data table to enter the Recordsource.
2. Put combo box cboShowOrders at the top of form in header usually.
3. In design mode of cboShowOrders, choose Events tab and go to AfterUpdate property(select the button with ...), this is where you will run the code.

If me.CboShowOrders = "Orders Only" then

Forms![ProposalDates].RecordSource = "Select * From ProposalTable WHERE (OrderDate Is Not Null)"

Else

If me.CBoShowOrders = "No Orders" Then

Forms![ProposalDates].RecordSource = "Select * From ProposalTable WHERE (OrderDate Is Null)"

Else

Forms![ProposalDates].RecordSource = "Select * From ProposalTable"

End If

End If

Forms![ProposalDates].Requery

Reply Reply With Quote