Access: Using a date field on a Form for a Query Criteria

[gheyman]

I have a form where the user selects a date.

Private Sub ComboQuoteValidity01_AfterUpdate()

 Select Case Me.ComboQuoteValidity01
        Case "All Quotes"
            Me.TextQuoteExpDate01 = "*"
        Case "Only Valid Quote"
            Me.TextQuoteExpDate01 = Date - 1
        Case "Current and Expired within 30 Days"
            Me.TextQuoteExpDate01 = Date - 29
        Case "Current and Expired within 60 Days"
            Me.TextQuoteExpDate01 = Date - 59
        Case "Current and Expired within 90 Days"
            Me.TextQuoteExpDate01 = Date - 89
        Case "Current and Expired within 180 Days"
            Me.TextQuoteExpDate01 = Date - 179
        Case "Current and Expired within 360 Days"
            Me.TextQuoteExpDate01 = Date - 359
        Case Else
            '
    End Select

End Sub

[Code/]


I want to use that date in a query that is run after the user makes a selection

In the Criteria field, in the query, I entered >="#[Forms]![frmCostHistory]![TextQuoteExpDate01]#" (Also tried >=[Forms]![frmCostHistory]![TextQuoteExpDate01])

But this doesn't seem to work.  the query results change when the forms date field is updated but the results are incorrect.

Example: If they enter todays date I am getting records where the dates are earlier than today.

 

[Joe4]

I know people do it, but I have never really liked trying to use values selected on a Form referenced in the criteria line of a query like that. I usually go one of two other ways:

1. Make my Form bound to a single record Table (this table is used to record and store my "criteria". Then, include that Table in my query (it does not need to actually be joined to the other tables, since it only has one record). Then I can use those values in my query.
- or -
2. I use the Form to build the SQL code of my query, and then assign that SQL code to an existing query. Sometimes I will do it in two steps. First, I will create a query that has all the fields and calculations that I want (let's call it Query1). Then I create another query called Query2 (it doesn't matter what you put in it, as we are going to be overwriting it). Then, I use VBA to write the SQL code behind Query2, soemthing like this:

    Dim mySQL As String
    
'   Build SQL for "on-the-fly" selection query (based on user form criteria entered)
    mySQL = "SELECT Query1.* FROM Query1 Where ..."


'   Assign SQL code built above to the query (which is the source for the report)
    CurrentDb.QueryDefs("Query2").Sql = mySQL

So, in the "..." after the "Where" is where you write the code for the criteria, using your Form variable value.
The easy way to do this is to first go out to Query2, and set it up to do an example of what you want (with your criteria). Then switch it to SQL View to see what it looks like. This is the strcuture that you want to replicate with the VBA code above.

So, I usually attach the VBA code above in a button on my Form that they click after filling out the criteria. You could even add another line of VBA to open Query2 at the end of it (using "OpenQuery" command).

 

[stumac]

Is your date field in the table you are querying actually a date field, because, the field on your form is a text field so you would get an error if it has a * in it.

If the field is a date then you can try converting your text >=Cdate([Forms]![frmCostHistory]![TextQuoteExpDate01]) - however, you will need to manage the "*" separately. Maybe something like:

Untested!

>=IIf([COLOR=#333333][Forms]![frmCostHistory]![TextQuoteExpDate01][/COLOR]="*",1/1/2000,CDate([COLOR=#333333][Forms]![frmCostHistory]![TextQuoteExpDate01][/COLOR]))

 

[gheyman]

I changed the code so that it doesn't put an "*" and it puts Date - 7300. That will do what I need. My data is not older then 20 years so that will make it include everything which was my goal for the "*"