Solved

Dates in where clause

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

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…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

770 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