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

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,804
Office Version
  1. 365
Platform
  1. Windows
I have a form where the user selects a date.

Code:
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.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,895
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
    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).
 
Last edited:

stumac

Active Member
Joined
Jul 16, 2010
Messages
471
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!
Code:
>=IIf([COLOR=#333333][Forms]![frmCostHistory]![TextQuoteExpDate01][/COLOR]="*",1/1/2000,CDate([COLOR=#333333][Forms]![frmCostHistory]![TextQuoteExpDate01][/COLOR]))
 

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,804
Office Version
  1. 365
Platform
  1. Windows
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 "*"
 

Watch MrExcel Video

Forum statistics

Threads
1,122,303
Messages
5,595,324
Members
413,986
Latest member
Elizsk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top