Results 1 to 14 of 14
  1. #1
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91

    Best way to use a date input into a form as date criteria for a query?

    hello,



    I am trying to let a user select dates that will feed a query's between [Start] and [End]. (Note, will probably feed multiple queries) (I already have form controls and table created...)

    I am thinking that the input of the user could then define two variables, and then those variables could be called in a query.

    Is this a good way to go about this?

    Thanks,

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,947
    The form has 2 textboxes:txtStart ,txtEnd.
    user picks the dates, the query:
    select * from table where [dteFld] between forms!myForm!txtStart and forms!myForm!txtEnd

    Open query with: docmd.openquery "qsDateRange"

  3. #3
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    I believe this is exactly what I tried first, though I just manually ran the query to test it. The problem I encountered, is that the end date in my case needs to have a 1 added to it, so that we are capturing the following day and can then adjust it for DST and Time Zone. *this is hitting an SQL server for tables and the admin of that system refuses to adjust the times, so I am forced to do it on my end in reports.

    WHen I did "between forms!myForm!txtStart and forms!myForm!txtEnd+1" it actually pulls two additional dates from the query.

    EDIT: This appears to happen ONLY when referencing the form controls, rather than a raw date or selecting it from a table behind the form. However, selecting the table level value for these dates seems to require a select statement inside the WHERE clause, and this considerably slows down the query performance, probably 100x. It seems to be doing the sql statement for every single row it tries to retrieve from the external tables.
    Last edited by rdougherty; 01-10-2019 at 12:46 PM. Reason: uir

  4. #4
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    UPDATE: why does changing my end date to "forms!myForm!txtEnd+0" result in a 1/5/2019 when the original date in the form field is 1/4/2019? This is the date I want, but why does adding zero to it accomplish it, but adding 1 did not?

    This is a date/time field...

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,446
    the end date in my case needs to have a 1 added to it
    Without specifying what 1 is, you're relying on Access to decide if it's 1 second, 1 day, 1 year, etc.
    Use DateAdd function but be aware that if your date/time fields contain time, and you don't specify time in the end date, the results get cut off at midnight on dteEnd.

    You also need to be aware of date criteria requirements. You can format the date any way you want, but AFAIK, as criteria it must be in US date format. Your regional settings also can come in to play, but shouldn't be an issue if criteria is in the expected format.

    seems to require a select statement inside the WHERE clause
    This would be a sub query, which will slow performance dramatically. I don't see the need if you get the end date input right.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  6. #6
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    thanks all...

    as a follow-up, I am getting a "Datatype Mismatch" error on the following criteria statement in a query, but ONLY after I have attempted to run the query in VBA. If I close the database and re-open it, and manually double-click run the query, it interprets it just fine with no errors. Once I try to run it as VBA by either execute or openquery method, it then reverts to providing the error message taht it requires more parameters, then manually running the query no longer works, and the datatype mismatch happens. The referenced form is open the whole time...

    *Error msg does NOT appear when replacing the following with precise dates "between 1/1/2019 and 1/20/2019" as an example: It IS a proper date field.

    Code:
    Between [Forms]![frm_Process]![Report Start Date] And (DateAdd("d",[Forms]![frm_Process]![Report End Date],2))
    After putting quotes around the "2" in the above code statement, the query will always run manually, so that was probably needed - shoulda know that...

    But calling the query in VBA still appears to create an error of "Too few parameters, expected 2". What else do I need?

    Code:
    d.Execute ("qry_Append_InvoicedBy_DateRange"), dbFailOnError
    Last edited by rdougherty; 01-24-2019 at 08:31 AM.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,446
    If you try to run a query (not sql) in vb that references form controls the vb side cannot resolve the query parameters (form references). There are a few work arounds:
    - right click on the query background (in design view) and define the parameters in the property sheet
    - construct and run sql from vb only and properly concatenate and delimit text and date values
    - more complicated, define querydef(s) and parameters, SET the parameters to be equal to the form controls or variables based on those controls, SET the query def to be the sql statement and execute the query def. Either method 2 or 3 requires you to properly delimit.

    If you're going to go the sql in vb route, post the relevant part of the procedure. Just seeing part of the sql doesn't help much. Also wondering why the approach from post 2 doesn't seem to fit the bill.

  8. #8
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    What I think works best for me at the level of VBA I am comfortable with at this time, and least amount of dev and research, is to:


    • Define a variable that grabs the date from a field on a form. (Needs to update on Load and AfterUpdate of control, of course)
    • Variable is named as criteria in the query - rather than writing out the SQL statement in VBA and executing it that way.


    Can this not be done?

    I need vars named StartDate and EndDate, which I was able to do with a test of:
    Code:
    Public Function StartDate() As Date   StartDate = #1/1/2019#
    End Function
    
    
    Public Function EndDate() As Date
       EndDate = #1/31/2019#
    End Function
    The above works as a means of showing the value in a query, but does not work as criteria in said query. Can you see what I am missing?
    * as a reminder, what I am trying to do is grab two dates from a form and add 2 days to the end date the user inputs before defining the variables.
    Code:
    Between [Forms]![frm_Process]![Report Start Date] And (DateAdd("d",[Forms]![frm_Process]![Report End Date],2))

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,446
    You're going to have to post either the entire sql, or the code for the procedure that either runs that sql or calls/runs the query, or both if both are involved. You say in one or more posts that this is a query, yet you're posing vba code. As I mentioned in my second (?) post, each method involves some specialized tweaks and at this point, I still can't tell exactly which/what you're doing. Or you can post a zipped copy of the db with instructions on what to open/run.

  10. #10
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    vba is only being used to run a sequence of pre-written query database objects. I'd prefer to do this unless there is no other way, due to my current level of understanding how to hook things into VBA.

    Posting a database or precise SQL is not practical, due to the amount of redactions I'd need to do, but all I am doing is this...


    • VBA command executes a dozen or more pre-written queries
    • Two of the queries need to reference user-defined inputs for dates.
    • User inputs two dates into a form that serves no other purpose other than to capture user-defined parameters, and house the button that executes the whole procedure.(<done)
    • Passing these "raw" to the query is not sufficient. My use-case requires the "End Date" to have two days added to it.
    • As you've indicated, Between [Forms]![frm_Process]![Report Start Date] And (DateAdd("d",[Forms]![frm_Process]![Report End Date],"2")) causes the query to not understand the request when the query is called from a VBA procedure. (remember, we're doing this so code can run through several queries w/o human input)


    Can't I just create these variables and then insert them into the existing query somehow?


    • Start Date, End Date, End Date +2


    Or, are you saying that there is no way to do this? The Query parameters workaround did not seem to work for this use-case.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,446
    Can't I just create these variables and then insert them into the existing query somehow?
    You should be able to, as in

    TheDate: DateAdd("d", 2,[End Date])

    You have the interval after the date when it should be before it.
    Code:
    And (DateAdd("d",[Forms]![frm_Process]![Report End Date],2))

  12. #12
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    okay, thanks. I have swapped the parameters inside the dateadd() funtion, which still works (did work previously, too) with a manual query execution. Still gives the same error in VBA when query is executed.

    Can you please spell out how to define these variables, and where, and then how to refer to them in my query? I have been reading some resources, but it is hard to decipher how to apply to my situation.

    Not sure if I need to define the vars as they are entered into the form (would also double-up and define when form is loaded, in case that dates are already stored there and don't need to be altered) or just on a module that runs at some point?

    Thanks again,


    EDIT Updates:
    I am erasing a comment about an error code that was caused by something else. I was using ,dbfailonerror even though it was a docmd.openquery command... the below appears to work!!

    Code:
    Option Compare Database
    
    Public Function StartDate() As Date
    
    
    StartDate = [Forms]![frm_Process]![Report Start Date]
    
    
    End Function
    Public Function EndDate() As Date
    
    
    EndDate = [Forms]![frm_Process]![Report End Date]
    
    
    End Function
    Public Function EndDatePlus() As Date
    
    
    EndDatePlus = DateAdd("d", 2, [Forms]![frm_Process]![Report End Date])
    
    
    End Function
    Sequel related to query being called...
    Code:
    INSERT INTO tbl_InvoicedBy_DateRange ( InvoiceId, [CreatedOn (Raw)], [Created By] )SELECT dbo_Invoice.InvoiceId, dbo_Invoice.CreatedOn AS [CreatedOn (Raw)], dbo_Invoice.CreatedByName AS [Created By]
    FROM dbo_Invoice
    WHERE (((dbo_Invoice.CreatedOn) Between StartDate() And EndDatePlus()));

  13. #13
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    Update 2. The above has worked fine! Thanks for pointing me in the right direction.

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,446
    Didn't see a notification for your posts, sorry. If you can get this from a public function into your form, it stands to reason that you should be able to do so on that form - especialy since you're only referring to form controls anyway. If you're happy with the way you have it, then end of story, but something is a bit amiss. Now for your code. I have made a minor change in each indented line and a major one at the top (I presume all that code is at the top of the module, otherwise I wouldn't expect to see Option anything.
    Code:
    Option Compare Database
    OPTION EXPLICIT
    
    Public Function StartDate() As Date
     StartDate = [Forms]![frm_Process].[Report Start Date]
    End Function
    
    Public Function EndDate() As Date
     EndDate = [Forms]![frm_Process].[Report End Date]
    End Function
    
    Public Function EndDatePlus() As Date
     EndDatePlus = DateAdd("d", 2, [Forms]![frm_Process].[Report End Date])
    End Function
    Hmmm, musta clicked submit but I wasn't finished. I blame the dog!
    Not having Option Explicit means variables don't have to be declared. That's just asking for trouble. You click "always require variable declaration" in the vb editor options. As for ! vs . I'll keep it simple: any reference to a form control using ! will not be checked when code is compiled (you do compile your code, yes?) and will fail only at run time when Access tries to resolve it. Try this in your code - misspell a control name, precede it with ! , save then compile. No problem. Now change the ! to . and keep the misspelled name then save and compile. What happens? Shouldn't matter if this is in the form of Forms!frmMtForm!txtMyTextbox or Me!txtMyTextbox

    This does not apply to certain DAO recordset methods. For the technical explanation see
    https://stackoverflow.com/questions/...-and-ms-access
    and/or
    https://bytecomb.com/the-bang-exclam...erator-in-vba/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. date/time input mask and date ordering
    By jsberry in forum Database Design
    Replies: 1
    Last Post: 06-19-2017, 01:04 PM
  2. Query with criteria a date field of a form
    By panoss in forum Queries
    Replies: 1
    Last Post: 12-22-2014, 09:03 AM
  3. Replies: 9
    Last Post: 02-21-2014, 06:02 PM
  4. Date criteria using between and form date picker
    By killermonkey in forum Queries
    Replies: 3
    Last Post: 03-21-2013, 12:44 PM
  5. Replies: 3
    Last Post: 08-21-2012, 03:05 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums