Solved

Dates in where clause

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

948 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

23 Experts available now in Live!

Get 1:1 Help Now