Solved

Dates in where clause

Posted on 2014-02-02
5
341 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now