Solved

Dates in where clause

Posted on 2014-02-02
5
349 Views
Last Modified: 2014-02-02
Hi,
I'm struggling with a between dates where clause, I have this at the moment

DoCmd.OpenForm "frmBkingEditAll", , , "[CollDate] >= #" & txtStart & "# And [CollDate] <= #" & txtEnd & "#"

But i get all dates.

As always any help is much appreciated.
0
Comment
Question by:Stephen Byrom
  • 2
  • 2
5 Comments
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 39827508
I did not see any issue with your code.

Just check the total number of records on the source table or query and compare it with the the total count of records that displayed in the form. If they are not the same then your the filtering worked well.

Sincerely,

Ed
0
 
LVL 19

Assisted Solution

by:MINDSUPERB
MINDSUPERB earned 250 total points
ID: 39827516
Or you might need to do the filtering within the query itself.

If your "frmBkingEditAll" is sourced from a query, in the criteria field under your CollDate column enter the one below:

Between [Forms]![Form1]![txtStart] And [Forms]![Form1]![txtEnd]

Then in your code just enter:

DoCmd.OpenForm "frmBkingEditAll"


Note: Replace Form1 with the name of your form where your txtStart and txtEnd are.

Sincerely,

Ed
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 250 total points
ID: 39827523
Is the Data Type of [CollDate] in your table set to Date and not Text?  If it already is then one other thing you can try is
DoCmd.OpenForm "frmBkingEditAll", , , "[CollDate] >= #" & CDate(txtStart) & "# And [CollDate] <= #" & CDate(txtEnd) & "#"

Open in new window

Ron
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 39827524
Hi,
I use the form "frmBkingEditAll" to edit shipments based on parameters which are set using another unbound form, so I don't really want to tie the query down to this one set of parameters.
Strangely enough, the where clause does work if I change the start and end dates on the filter form a couple of times. I have the event fire when the user clicks a button after entering the dates, but if the dates are the default then the form opens blank
0
 
LVL 1

Author Closing Comment

by:Stephen Byrom
ID: 39827529
I managed to get it working by removing the default dates and leaving the controls blank when the filter form opens.
Then the user must select both dates before hitting the OK button and the editing form opens within the correct dates.

Thanks to you both for your time
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question