?
Solved

Dates in where clause

Posted on 2014-02-02
5
Medium Priority
?
360 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1000 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 1000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

719 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